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

  This article was originally published by Microsoft's Azure Database Support Blog. You can find the original article here.