Connection pool exhaustion: trace waits before increasing capacity

TL;DR: A full connection pool says all its leases are occupied. Measure why they remain occupied and where requests wait before enlarging the pool; extra connections can increase contention in the database that caused the wait.

There can be two queues and two meanings of idle

An application pool lends database connections to request handlers. When all permitted connections are checked out, another handler waits to acquire one. If PgBouncer sits between the application and PostgreSQL, it can introduce another queue waiting for a server connection. A client connection count and a PostgreSQL backend count therefore need not match.

Pool mode changes the association. PgBouncer's configuration reference distinguishes session pooling from transaction pooling, where the server connection can return to its pool after the transaction. Check the application's use of session state before changing modes; a smaller backend count does not establish behavioral compatibility.

The branch to other work explains a common surprise: the application pool is full while the database has low CPU. A handler may hold a lease while calling a remote service, or fail to return it on an exception. Database “idle” describes backend activity, not whether the application's lease is available.

Calculate the occupancy the workload implies

In a hypothetical steady workload, handlers acquire connections at 240 operations per second and hold each for an average of 50 milliseconds. Little's Law estimates average checked-out occupancy as 240 × 0.050 = 12 connections. This assumes stable flow through the measured boundary and counts leases, not HTTP requests that may acquire several times.

After a query regression, average hold time rises to 250 milliseconds. At the same throughput, implied occupancy becomes 60. A 30-connection pool cannot sustain that workload without queueing or rejecting some demand. Enlarging it may help if the database has measured spare capacity; if hold time rose because of contention, admitting more work can make it worse.

Averages do not size the final pool. Bursts and long transactions create tails, and a production limit must fit the database budget across all replicas. Eight pods with a maximum of 30 each can potentially demand 240 connections before rollout surge, jobs or administration. An autoscaler that doubles pods also doubles that potential demand.

Compare application and database evidence

For PostgreSQL, this read-only query summarizes backend states without printing query text:

SELECT state, wait_event_type, count(*) AS sessions
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND datname = current_database()
  AND pid <> pg_backend_pid()
GROUP BY state, wait_event_type
ORDER BY sessions DESC, state;

Run it through an authorized monitoring identity. The statistics documentation defines state and wait events and explains visibility restrictions. An idle in transaction backend has an open transaction despite no current query; an active backend may be waiting. Correlate with transaction age and application traces instead of reading either label as a complete diagnosis.

Repair the hold time or limit the demand

If handlers retain connections during remote calls, shorten the transaction and lease scope where correctness permits. If a slow query is responsible, separate plan cost, lock waits and I/O before choosing a repair. If a connection leak is suspected, exercise exception, cancellation and timeout paths in a controlled test and verify that checked-out counts recover. Restarting a pod can temporarily clear the symptom without proving the release path is fixed.

Bound the acquisition wait within the request's remaining deadline. A request that has already expired should not spend another minute waiting for a connection and then begin expensive work. Cancellation needs an end-to-end path; returning an HTTP timeout does not prove the database stopped executing. Deadline propagation supplies the budget model.

The interview follow-up is whether to raise the pool from 30 to 60. Require evidence that the database can sustain the additional concurrency and that the full replica fleet stays within its connection budget. Prefer a controlled increase with latency and lock-wait observation over a fleet-wide guess.

Self-check: database CPU is 15%, all 30 application leases are occupied, and most backend sessions are idle in transaction. Is more CPU the first repair? The current evidence points toward transaction or lease scope. Find the retaining handlers and their waits, reduce unnecessary hold time safely, and verify acquisition latency and open-transaction age recover together.

For a Java service, application-server diagnosis connects these pool waits to Tomcat workers, thread snapshots and JVM memory evidence.