PostgreSQL Connection Exhaustion

Investigation principle

Treat connection count as part of workload/concurrency design, not just a max_connections setting.

Common wrong assumptions

• “Raise max_connections.” Each backend uses memory, and more active backends than cores means they compete for CPU and locks. A higher limit often turns a connection error into a slow outage.
• “Each service’s pool is small.” Pools multiply. Forty pods with a pool of twenty is 800 possible connections, before deploys briefly run old and new pods together.
• “Idle connections are harmless.” Plain idle sessions cost memory. idle in transaction sessions hold locks and old snapshots, which block DDL and stop vacuum from cleaning up.
• “A pooler solves it.” Transaction pooling breaks session state such as session-level SET, advisory locks and LISTEN. With RDS Proxy, pinned sessions quietly lose the pooling benefit.

First queries to run

Read-only. Run them during the problem window if you can; most of these views show current state, not history.

-- Who holds connections, and in what state
SELECT usename,
       application_name,
       state,
       count(*)                       AS sessions,
       max(now() - state_change)      AS longest_in_state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3
ORDER BY sessions DESC;

-- Headroom
SELECT current_setting('max_connections')::int AS max_connections,
       count(*)                                AS client_backends
FROM pg_stat_activity
WHERE backend_type = 'client backend';

When a deeper investigation is justified

• Connection spikes line up with deploys, retries or autoscaling, and nobody owns the total pool budget.
• idle in transaction sessions show up regularly and the code path is unknown.
• You are deciding between a bigger instance, a pooler and application changes, and each one has a different cost.

If old transactions are part of the spike, use the locking and concurrency guide to identify who is blocking whom.