Azure PostgreSQL Lesson Learned #3: Fix FATAL: sorry, too many clients already
We encountered a support case involving Azure Database for PostgreSQL Flexible Server where the application started failing with connection errors. This blog explains the root cause, resolution steps, and best practices to prevent similar issues.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
Azure PostgreSQL Lesson Learned#5: Why SKU Changes from Non-Confidential to Confidential Fail
Co-authored with HaiderZ-MSFT Issue Summary The customer attempted to change the server configuration from Standard_D4ds_v5 (non-Confidential Compute) to Standard_DC4ads_v5 (Confidential Compute) in the West Europe region. The goal was to enhance the performance and security profile of the server. However, the SKU change could not be completed due to a mismatch in security profiles between the current and target SKUs. Root Cause The issue occurred because SKU changes from non-Confidential to Confidential Compute types are not supported in Azure Database for PostgreSQL Flexible Server. Each compute type uses different underlying hardware and isolation technologies. As documented in Azure Confidential Computing for PostgreSQL Flexible Server, operations such as Point-in-Time Restore (PITR) from non-Confidential Compute SKUs to Confidential ones aren’t allowed. Similarly, direct SKU transitions between these compute types are not supported due to this security model difference. Mitigation To resolve the issue, the customer was advised to migrate the data to a new server created with the desired compute SKU (Standard_DC4ads_v5). This ensures compatibility while achieving the intended performance and security goals. Steps: Create a new PostgreSQL Flexible Server with the desired SKU (Confidential Compute). Use native PostgreSQL tools to migrate data: pg_dump -h <source_server> -U <user> -Fc -f backup.dump pg_restore -h <target_server> -U <user> -d <database> -c backup.dump 3. Validate connectivity and performance on the new server. 4. Decommission the old server once migration is confirmed successful. Prevention & Best Practices To avoid similar issues in the future: Review documentation before performing SKU changes or scaling operations: Azure Confidential Computing for PostgreSQL Flexible Server Confirm compute type compatibility when planning scale or migration operations. Plan migrations proactively if you anticipate needing a different compute security profile. Use tools such as pg_dump / pg_restore or Azure Database Migration Service. Check regional availability for Confidential Compute SKUs before deployment. Why these matters Understanding the distinction between Confidential and non-Confidential Compute is essential to maintain healthy business progress. By reviewing compute compatibility and following the documented best practices, customers can ensure smooth scaling, enhanced security, and predictable database performance.Azure PostgreSQL Lesson Learned#11: Major Version Upgrade Failure Due to Unsupported Extensions
Co-authored with HaiderZ-MSFT Symptoms: How the Failure Appears When attempting a Major Version Upgrade in Azure Portal, customers typically encounter upgrade fails immediately or gets stuck and sometimes, portal error indicating unsupported extensions detected This is an expected behavior as Azure validates extension compatibility before performing any in‑place upgrade. Root Cause: Unsupported PostgreSQL Extensions Block MVU Azure Database for PostgreSQL Flexible Server only supports a specific list of extensions during major version upgrades. If your source server contains any extension outside that allowlist, the major version upgrade will be blocked. Step-by-Step Troubleshooting & Resolution Guide STEP 1 — Audit All Extensions on the Server Azure PostgreSQL does not allow cross-database queries, so you must inspect each database individually. ➤ Option A: Check Extensions in Current Database SELECT current_database() AS database_name, extname AS extension_name, extversion AS version FROM pg_extension; ➤ Option B: Auto-Generate Extension Check Queries for All Databases SELECT 'SELECT ''' || datname || ''' AS database_name, extname, extversion FROM pg_extension ORDER BY extname;' AS query_to_run FROM pg_database WHERE datistemplate = false; Copy the generated queries and run them to produce a server-wide extension inventory. 👉 If ANY unsupported extension appears, the upgrade will fail. STEP 2 — Remove Unsupported PostgreSQL Extensions To unblock the Azure PostgreSQL MVU: DROP EXTENSION IF EXISTS <extension_name> CASCADE; ⚠️ The CASCADE option may drop dependent objects — always verify impact prior to execution. STEP 3 — Validate Allowed Extensions in Server Parameters In Azure Portal: Server → Server Parameters → azure.extensions Ensure only supported extensions are listed Remove or correct unsupported entries Reference: Allow extensions STEP 4 — Re-Run MVU With unsupported extensions removed and supported ones validated: The server was in Ready state Free storage exceeded Azure’s 10–20% pre‑upgrade requirement Major Version Upgrades - Azure Database for PostgreSQL | Microsoft Learn Trigger MVU again via Portal: Portal → Server → Overview → Upgrade Select your target PostgreSQL major version. If all extension issues are resolved, the upgrade completes successfully. Final Outcome After removing unsupported PostgreSQL extensions and validating the azure.extensions parameter: ✔️ MVU completed without errors ✔️ Server upgraded to the desired PostgreSQL version Best Practices to Avoid Future Upgrade Failures These recommendations help maintain a healthy Azure PostgreSQL Flexible Server environment: Audit Extensions Regularly Especially before major version upgrades. Limit Extensions via azure.extensions Only enable what you actively use. Clean Up Deprecated Extensions Remove unused or legacy extensions before they accumulate. Align with Azure's supported extension list Automate Extension Validation This reduces last-minute deployment surprises. Helpful References Concepts - Major Version Upgrades in Azure Database for PostgreSql Major Version Upgrades in Azure PostgreSQL PostgreSQL Extensions on Azure
