debugsqlpostgresqlCritical
Connection pool exhaustion: symptoms, diagnosis, and remediation
Viewed 0 times
connection poolmax_connectionspg_stat_activityidle connectionsPgBouncerconnection exhaustion
Error Messages
FATAL: remaining connection slots are reserved for non-replication superuser connectionsFATAL: sorry, too many clients alreadyProblem
Application requests hang indefinitely or fail with 'remaining connection slots are reserved for non-replication superuser connections' when the connection limit is reached.
Solution
Diagnose and fix connection pool exhaustion:
-- Check current connections:
SELECT count(*), state, wait_event_type, wait_event
FROM pg_stat_activity
GROUP BY state, wait_event_type, wait_event
ORDER BY count DESC;
-- Find idle connections holding slots:
SELECT pid, usename, application_name, state, query_start,
now() - state_change AS idle_for
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY idle_for DESC;
-- Emergency: terminate idle connections older than 10 minutes:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND now() - state_change > interval '10 minutes'
AND pid <> pg_backend_pid();
-- Long-term fix: deploy PgBouncer in transaction mode
-- Check current connections:
SELECT count(*), state, wait_event_type, wait_event
FROM pg_stat_activity
GROUP BY state, wait_event_type, wait_event
ORDER BY count DESC;
-- Find idle connections holding slots:
SELECT pid, usename, application_name, state, query_start,
now() - state_change AS idle_for
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY idle_for DESC;
-- Emergency: terminate idle connections older than 10 minutes:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND now() - state_change > interval '10 minutes'
AND pid <> pg_backend_pid();
-- Long-term fix: deploy PgBouncer in transaction mode
Why
PostgreSQL allocates a process per connection. Each connection consumes ~5-10MB of RAM and fd slots. Applications that open connections but do not release them promptly (ORM leaks, no connection pooling) exhaust max_connections (default 100).
Gotchas
- superuser_reserved_connections (default 3) are always reserved; real limit is max_connections - 3
- PgBouncer in session mode does not help; use transaction mode for connection multiplexing
- LISTEN/NOTIFY and prepared statements are incompatible with PgBouncer transaction mode
- Raising max_connections has diminishing returns beyond ~500 due to lock contention in shared memory
Code Snippets
Single query showing connection headroom
-- Connection usage summary:
SELECT max_conn, used, res_for_super,
max_conn - used - res_for_super AS available
FROM
(SELECT count(*) used FROM pg_stat_activity) t1,
(SELECT setting::int res_for_super FROM pg_settings WHERE name='superuser_reserved_connections') t2,
(SELECT setting::int max_conn FROM pg_settings WHERE name='max_connections') t3;Context
High-traffic web applications connecting directly to PostgreSQL without a pooler
Revisions (0)
No revisions yet.