Azure PostgreSQL Lesson Learned #8: Post-Upgrade Performance Surprises (The One-Step Fix)

Co‑authored with angesalsaa​ 

Symptoms

• Upgrade from PostgreSQL 12 → higher version succeeds.
• After migration, workloads show:
  • Queries running slower than before.
  • Unexpected CPU spikes during normal operations.
• No obvious errors in logs or connectivity issues.

Root Cause

Missing or stale statistics can lead to bad query plans, which in turn might degrade performance and consume excessive memory.
After a major version upgrade, the query planner relies on outdated or default estimates because the optimizer’s learned patterns are not refreshed. This often results in:

• Sequential scans instead of index scans.
• Inefficient join strategies.
• Increased CPU and memory usage.

Contributing Factors

• Large tables with skewed data distributions.
• Complex queries with multiple joins.
• Workloads dependent on accurate cost estimates.

Specific Conditions We Observed

• Any source server version can be impacted once you upgrade to higher version.
• No ANALYZE or VACUUM run post-upgrade.

Operational Checks

Before troubleshooting, confirm:

• Query plans differ significantly from pre-upgrade.
• pg_stats indicates outdated or missing statistics.

Mitigation

Goal: Refresh statistics so the planner can optimize queries.

Run ANALYZE on all tables:

ANALYZE;

Important Notes:

• These commands are safe and online.
• For very large datasets, consider running during low-traffic windows.
• We recommend running the ANALYZE command in each database to refresh the pg_statistic table.

Post-Resolution

• Queries return to expected performance.
• CPU utilization stabilizes.
• Execution plans align with indexes and cost-based optimization.

Prevention & Best Practices

• Always schedule ANALYZE immediately after major upgrades.
• Automate stats refresh in your upgrade runbook.
• Validate plans for critical queries before going live.

Why This Matters

Skipping this step can lead to:

• Hours of degraded performance.
• Emergency escalations and customer dissatisfaction.
• Misdiagnosis as engine regression when it’s just missing stats.

Key Takeaways

• Issue: Post-upgrade query slowness and CPU spikes due to stale/missing statistics.
• Fix: Run ANALYZE immediately after upgrade.
• Pro Tip: Automate this in CI/CD or maintenance scripts for zero surprises.

References

Major Version Upgrades - Azure Database for PostgreSQL | Microsoft Learn