Finding and Fixing Connection Pool Exhaustion

Most pool libraries and poolers expose the same four measurements. Watch these, in this order.

1. Checked out connections. How many connections are currently assigned to a request. Healthy systems sit well below the cap and oscillate.
2. Idle connections. How many sit in the pool ready to hand out. Zero idle plus a pinned checked-out count is saturation.
3. Checkout wait time (sometimes called acquisition time or queue time). This is the leading indicator. Small values under 1 ms are healthy; climbing values mean congestion before any error appears the How Tech treatment of pool metrics.
4. Acquisition failures and timeouts. The lagging indicator. A single timeout every few hours is a warning, not a fluke.

PgBouncer, the most common Postgres pooler, keeps these per pool. Its admin console exposes pool state, and the project documents how connections are assigned and queued: once the per-database, per-user or pool size limits would be exceeded, new clients are queued rather than connected the PgBouncer features page. That queueing behavior is exactly why the first symptom is wait time and not an error. A pool of 50 that is capped at max_client_connections will hold clients in the queue, and only aborts them if the client limit itself is breached, as described in the ScaleGrid PgBouncer walkthrough.

Two measurements need care. First, pool utilization is a ratio, not a count: 90 percent used on a pool of 10 is a very different risk than 90 percent on a pool of 200, because the same small burst of extra requests tips the smaller pool over. Second, client-side metrics only see one instance. In a containerized deployment, each pod reports its own pool, and each pod can look fine while the aggregate demand blows past the database limit the Webalert guide's sizing section. To see the truth, either aggregate pool metrics across pods or read them from the database itself.

The pool metrics tell you the pool is full. pg_stat_activity tells you why the connections are not coming back. It exposes one row per server backend, with the current state, the start time of the state and the query text the MyDBOps guide to connection states.

Start with an aggregate by state and application:

SELECT state, count(*), max(now() - state_change) AS longest_in_state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY count(*) DESC;

Then find the sessions that have been sitting in a transaction without doing work:

SELECT pid, usename, application_name, state,
       now() - state_change AS idle_for,
       now() - xact_start   AS transaction_age
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY transaction_age DESC;

The states map to specific failure modes. Active means a query is running, so a connection is held for a legitimate reason, though possibly for too long. Idle means the client is connected but not running anything, which with a pooler is usually fine and without one is wasted capacity. Idle in transaction means the client opened a transaction and then paused without committing, which holds a connection and any locks it acquired; the MyDBOps guide notes this state can hold locks and affect concurrency that same state guide. Idle in transaction (aborted) is the same, but the transaction already hit an error and nobody rolled it back, so the connection is stuck in a zombie state again the state reference.

A high count of idle in transaction sessions is the single strongest signal that connections are not being returned. The usual cause is application code that begins a transaction, performs an external call or slow computation, and only then commits. A transaction that is held open while waiting on a network round trip that takes 300 ms will hold its connection for that whole period, and with concurrency of 100 requests per second the pool quietly drains the Medium analysis of pool queueing.

For long-held active sessions, the next question is whether the query itself is slow. That is where EXPLAIN comes in. A plan shows the estimated cost of each node, and EXPLAIN ANALYZE runs the query and reports actual times, which exposes sequential scans on large tables and misestimated row counts the PostgreSQL EXPLAIN documentation. A common result is a filter on a column with no usable index, so the server scans every row while the connection stays checked out the Crunchy Data EXPLAIN walkthrough. Reading the plan inside-out, from the bottom-most scan upward, tells you which node is doing the work the Thoughtbot guide to reading plans.

If the plan shows a sequential scan that should be an index scan, that is a query problem masquerading as a pool problem, and fixing the index returns the connection faster without touching pool size. Indexes speed up reads but add overhead to writes, so the fix is not "index everything" the PostgreSQL index chapter. An expression index on the filtered form of a column, such as lower(email), is the standard way to make case-insensitive lookups use an index the Heroku index guide.

The most common sizing mistake is sizing per instance and forgetting the fleet. The database connection budget is shared capacity.

usable connections
  = max_connections
  - admin and migration reserve
  - monitoring reserve
  - other application clients

maximum pool per instance
  = usable connections / maximum concurrent instances

Walk through the numbers. Suppose max_connections is set to 100. Reserve 5 for an admin and migration user and 5 for monitoring, leaving 90 usable. If the deployment can autoscale to 30 instances, the pool on each instance must be 3, not 20. The formula forces you to size against the maximum instance count, not today's steady state the Webalert sizing formula.

This is where a pooler changes the arithmetic. With PgBouncer in front, the pool per instance only limits client connections to the pooler, and the pooler's pool_size limits actual server connections. A pool_size of 20 at the pooler means at most 20 server connections regardless of how many pods connect, which is why production setups put the pooler between the app and the database the ScaleGrid PgBouncer article. PgBouncer keeps memory per connection small, around 2 kB by default, so holding many client connections is cheap the PgBouncer features page.

Before you size, check which pooling mode you are in. In session pooling a server connection stays assigned to a client for the whole session, so the pool behaves much like a direct connection count. In transaction pooling the server connection returns to the pool as soon as the transaction ends, which is what makes a small server pool serve many clients, but it breaks session-level features such as SET, LISTEN and session-level advisory locks the PgBouncer feature map. Heroku's guidance recommends transaction pooling for most application workloads because clients rarely run consecutive transactions with zero pause between them the Heroku PgBouncer best practices. If your application depends on session state, moving to transaction pooling will break it, so verify that first.

Capping is a two-layer job, and the layers must agree. Set the pooler's pool_size and max_client_connections on the pooler, and set max_connections and per-user limits on Postgres. Leave headroom in the server for the superuser reserve, which lets an administrator connect when the database is otherwise full the Webalert explanation of the two limits.

A practical checklist before you turn any knob:

Pooler-side limits are shipped per user and database, and query_wait_timeout can be set at those levels, so you can cap the noisy service without starving the quiet one the PgBouncer release notes. On the server side, the reserve matters most during incidents: if every slot is taken by application connections, an operator cannot log in to kill offending sessions. Keep the reserve and keep an eye on idle in transaction alerts, because a steadily climbing connection count that never recovers is a leak, while a count that spikes with traffic and recovers is a sizing or slow-query issue the Webalert leak versus spike distinction.

One more cap belongs in the application: never hold a connection across an external call. Check the connection out, do the database work, return it, then make the HTTP request. This single rule fixes a large share of pool exhaustion incidents because it removes the slow-return pattern that drains the pool even when the database itself is healthy the Radview fix playbook.

When the pool is saturated, work down this list in order. It is ordered by how often the cause turns up and how cheap it is to rule out.

First, leaks. Code that checks out a connection and never returns it, typically because an exception path skips the cleanup, permanently removes a connection from the pool. A graph of checked out connections that ratchets up and never comes back down is the tell the How Tech failure modes. Fix the code, then restart the affected instances to reclaim the leaked slots.

Second, long transactions. A transaction that includes an external call, a large batch or a lock wait holds its connection for the whole duration. The state query above finds these. The fix is to move non-database work outside the transaction and to set a statement timeout.

Third, slow queries. These hold connections longer than necessary and under load cascade into exhaustion the Webalert guide. Read the plan, add or fix the index, and re-measure. A partial index on a filtered subset, such as rows where a status flag is true, keeps the index small and the lookup fast the Heroku partial index example.

Fourth, fleet multiplication. Each instance has its own pool, and autoscaling multiplies demand. This is the most-missed cause in containerized setups the Webalert guide on instance multiplication. Put a pooler in front and cap at the pooler.

Commit-time and lock behavior belong on this list too. Transaction isolation determines how much a session can block others: at the stricter levels, concurrent transactions can fail with serialization errors, and at read committed a writer can wait a long time behind another transaction the PostgreSQL isolation documentation. A practical overview of the levels and their anomalies helps you choose the least-strict level your workload tolerates the Nile explanation of isolation.