Fix: PostgreSQL Too Many Connections

!Symptoms

• Application errors: 'FATAL: too many connections for role'
• New connections refused: 'sorry, too many clients already'
• Connection pool exhaustion in application logs
• Database operations hang waiting for available connections

?Root Causes

• max_connections set too low for the workload
• Application not using connection pooling
• Connection leaks: connections opened but never closed
• Too many application replicas each opening their own pool
• Long-running queries holding connections open
• Idle connections from killed app processes not being released

#Diagnosis Steps

1. 1Check current connections: `SELECT count(*) FROM pg_stat_activity;`
2. 2Check max_connections: `SHOW max_connections;`
3. 3See connections by application: `SELECT application_name, count(*) FROM pg_stat_activity GROUP BY 1;`
4. 4Find idle connections: `SELECT * FROM pg_stat_activity WHERE state = 'idle' ORDER BY state_change;`
5. 5Check for long-running queries: `SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC;`

>Fix

1. 1Terminate idle connections: `SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND state_change < now() - interval '10 minutes';`
2. 2Increase max_connections in postgresql.conf and restart (requires downtime)
3. 3Deploy PgBouncer or pgpool-II as a connection pooler in front of PostgreSQL
4. 4Fix connection leaks in application code (ensure connections are returned to pool)
5. 5Reduce pool size per application instance and scale instances instead

*Prevention

• Always use a connection pooler (PgBouncer) in production
• Set appropriate connection pool sizes based on max_connections / number_of_instances
• Set idle connection timeout in your connection pool configuration
• Monitor active connections with alerts at 80% of max_connections
• Use connection pool health checks and idle connection eviction

Related Error Messages