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
