Azure PostgreSQL Lesson Learned #3: Fix FATAL: sorry, too many clients already

Co-authored with angesalsaa​ 

Symptoms

• Application errors indicating connection failures
• Error message in logs:

FATAL: sorry, too many clients already

• High CPU utilization during peak traffic
• Short-lived connections (<60s) causing spikes

Root Cause

The server hit its configured connection limit (max_connections).

Azure PostgreSQL Flexible Server calculates this limit based on compute tier and memory. Each connection consumes resources. When the limit is exceeded, new connections fail.

Contributing Factors

• No connection pooling implemented
• Application opened thousands of concurrent sessions
• Lack of proactive monitoring on connection usage
• High traffic bursts during peak hours

Specific Conditions

• Customer had multiple microservices creating short-lived connections
• PgBouncer was not enabled

Azure Monitor Metrics

Use the active_connection metrics from your instance to monitor the current connection rate, and be sure to review any failed connections as well. Would you like more information on the metrics? Take a look at this Monitoring and metrics - Azure Database for PostgreSQL | Microsoft Learn

Mitigation

To resolve the issue:

1. Identify active connections
   SELECT usename, application_name, COUNT(*) AS sessions FROM pg_stat_activity
   GROUP BY usename, application_name
   ORDER BY sessions DESC;
   
   
2. Terminate stale sessions
   
   SELECT pg_terminate_backend(pid) FROM pg_stat_activity
   WHERE state = 'idle in transaction'
   AND now() - xact_start > interval '10 minutes';
   
   
3. Enabled PgBouncer
   • How to enable it ?
     Navigate to Azure Portal → Open your Azure PostgreSQL Flexible Server → go to Server Parameters → search for PgBouncer, and change the pgbouncer.enabled setting to true. There's no need to restart the server.
   • Once you enable that, you can found following allowed parameters and tune them according to your need or keep them default PgBouncer - Azure Database for PostgreSQL | Microsoft Learn
   • Important fact : It is enabled by default on port 6432, and you can connect to it using the same host name as your PostgreSQL server, but with port 6432 instead of the default port 5432.
     
     
4. Sometimes customers simply increase the max_connections value, which is often not a good trade-off for performance. When you first set up an Azure Database for PostgreSQL Flexible Server instance, the service automatically determines the maximum number of concurrent connections your server can handle based on its configuration. This baseline cannot be changed.
5. You can, however, adjust the max_connections setting to control how many connections are allowed at a given time. After changing this setting, a server restart is required for the new limit to take effect. While it’s technically possible to increase max_connections beyond the default, we strongly advise against doing so. Instead, identify why such a high number of connections is being opened and address the root cause before considering any increase.
   
   Review for further information for limits > Limits in Flexible Server - Azure Database for PostgreSQL | Microsoft Learn
   
   
6. Also, review this article to understand the types of connections your application establishes with the database server.Identify and solve connection performance in Azure Postgres​​ 

Post-resolution:

• Server resumed normal operations
• Application connections stabilized
• CPU utilization returned to normal

Prevention & Best Practices

• Enable PgBouncer in transaction pooling mode for web/API workloads
• Implement retry/backoff logic to avoid connection storms
• Monitor connection metrics via Azure Monitor
• Set alerts when usage exceeds 80% of max_connections, using metric active_connections

Why This Matters

Failing to manage connections can lead to:

• Application downtime
• Increased latency and resource contention
• Business-critical transaction failures

By following these practices, customers can ensure scalable, reliable PostgreSQL performance on Azure.

Key Takeaways

• Don’t blindly increase max_connections it can degrade performance
• Use PgBouncer transaction pooling for efficient connection management
• Monitor and alert on connection usage proactively