Connection Exhaustion

Scenario

During peak business hours, the application starts throwing FATAL: sorry, too many clients already. New connections are refused. The DBA checks and finds max_connections = 100 with 98 connections currently active — most of them idle. The application uses a connection pool but the pool is misconfigured to open 20 connections per application server, and 6 application servers are running (120 total connections needed).

How to Identify

Conditions:

• Application errors: FATAL: sorry, too many clients already
• pg_stat_activity shows connections at or near max_connections limit
• Many connections in idle or idle in transaction state (pool leak)
• No PgBouncer or connection pooler in front of PostgreSQL
• max_connections is set to a high value that’s pressuring shared memory

Analysis Steps

-- Check current connection count vs limit
SELECT count(*) AS total_connections,
       current_setting('max_connections')::int AS max_connections,
       current_setting('superuser_reserved_connections')::int AS reserved_for_super
FROM pg_stat_activity;

-- Break down connections by state
SELECT state, count(*) AS connections
FROM pg_stat_activity
GROUP BY state
ORDER BY connections DESC;
-- idle = parked connections (pool)
-- idle in transaction = dangerous: holding locks/transactions open

-- Identify idle connections held too long
SELECT pid, usename, application_name, client_addr, state, backend_type,
       now() - state_change AS idle_duration
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY idle_duration DESC
LIMIT 20;

-- Identify idle-in-transaction connections
SELECT pid, usename, state, now() - query_start AS txn_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY txn_duration DESC;
-- Long idle-in-transaction = application bug or missing COMMIT

-- Check connection counts by user and application
SELECT usename, application_name, count(*) AS connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name
ORDER BY connections DESC;

Pitfalls

• Raising max_connections beyond 200-500 on a typical server causes excessive shared memory use and context-switching overhead — each connection uses 5-10 MB of RAM.
• The real fix for connection exhaustion is a connection pooler (PgBouncer) — not raising max_connections.
• superuser_reserved_connections = 3 (default) reserves 3 connections for superusers to maintain access during emergencies. Never set this to 0.
• idle in transaction connections hold locks and prevent autovacuum from cleaning up dead tuples — they degrade performance beyond just connection count.
• PgBouncer in transaction mode is most efficient: a PostgreSQL connection is only held for the duration of a SQL transaction, not the full client lifetime.

Resolution Approach

Short term: terminate idle connections, set idle_in_transaction_session_timeout. Long term: deploy PgBouncer in transaction pooling mode in front of PostgreSQL, reduce max_connections to match actual server capacity (100-200 for most servers), configure application pools to connect through PgBouncer.