Connection Pool Exhaustion That Adding Instances Makes Worse

Three layers, and they are not the same thing.

• Symptom: 502/504 at the load balancer. That is what users see; it is the last link in the chain, not a starting point.
• Proximate cause: connection pool exhaustion - 180 of 200 threads waiting on a connection. This explains why requests time out, but not why the pool is exhausted.
• Root cause candidates, which must be separated with data:
  1. Connections held too long. p99 of 1.9 s and rising for six weeks smells like data growth hitting a missing or inefficient index, not a sudden change. If each request holds a connection for ~2 s instead of 50 ms, the pool's effective throughput drops by 40x.
  2. No headroom in the sizing math. 10 instances x 20 connections = 200, exactly the database limit, with 30 of those reserved. Any admin session, any replacement instance during a deployment, or any scale-out event pushes past the limit and produces hard connection refused errors - a different, faster failure than pool wait.
  3. Retry amplification. Three retries with no backoff means one failing request becomes four at the worst possible moment, precisely when the system is already saturated. The 12:00–13:00 clustering suggests a traffic peak, and the retry storm turns a slow patch into a collapse.

Evidence to collect:

• pg_stat_activity grouped by wait event and state during the window - are sessions active, idle-in-transaction, or waiting on a lock?
• pg_stat_statements top queries by total and mean time, run before and after the six-week trend began; look for a sequential scan on a growing table.
• Pool metrics: wait time, checkout count, and peak concurrent connections versus the limit over 24 hours.
• Client-side retry counts and the fraction of traffic that is retried, plus the arrival rate during 12:00–13:00.
• Whether any deployment or scale event coincided with the failures - that would confirm the headroom hypothesis.

Each new instance adds 20 more connections to a database that is already at its ceiling. This is the classic anti-pattern: the pool is sized per instance, so the effective connection count is (instances x pool size). At 11 instances you are at 220 against a 200 limit, so new connections are refused outright. Scaling out - the obvious response to slow requests - converts a queueing problem into a connection-refused problem. That is why the incident escalates rather than resolves.

The fix has three independent parts, and skipping any one leaves the system fragile:

1. Decouple application connections from database connections. Put a connection pooler in front of PostgreSQL in transaction pooling mode. Then 10 instances can each hold 20 application-side connections while the pooler maintains, say, 60–80 actual database sessions. Application concurrency and database concurrency stop being the same number. Caveat: transaction mode breaks session-level features (prepared statements, session variables, advisory locks). Audit for those before switching.

2. Fix the slow query. Pool exhaustion is a consequence of connections being held too long. Add the missing index, remove the N+1 access on the slowest endpoint, and set a statement_timeout so a pathological query releases its connection instead of pinning it. Without this, the pooler just moves the queue one hop upstream.

3. Stop the retry storm. Exponential backoff with jitter, a retry budget (for example, retries capped at 10 percent of total requests), a circuit breaker, and idempotency keys so retries are safe. A retry policy without jitter synchronises clients into waves.

Also, right-size deliberately: per-instance pool around 10–12 behind the pooler, database max_connections set from measured need rather than left at a default, and the autoscaling maximum taken into account in the arithmetic. Write that formula down - it is the thing that was missing.

Immediate mitigation (minutes to hours, no code deploy required):

1. Enable backoff with jitter and reduce retry count on the client side - this is often configurable without a release and cuts the amplification immediately.
2. Reduce per-instance pool size so 10 instances plus headroom stay comfortably under the database limit. This trades some concurrency for eliminating hard refusals.
3. Add statement_timeout and a lock timeout so a stuck query cannot hold a connection indefinitely.
4. Shed or queue non-essential load at the edge for the 12:00–13:00 window to protect the interactive path.
5. Hold deployments and scale-out events during the window until the pooler is in place.

Verification - four independent checks, because any one alone can pass while the system is still broken:

• Pool wait: p99 connection checkout wait under 50 ms sustained, and zero 'pool exhausted' log lines for seven consecutive days including the 12:00–13:00 window.
• Capacity headroom: peak database connections during the window stay below 70 percent of the limit, and remain below the limit during a deliberate rolling deployment and a scale-out to the autoscaling maximum. Testing only steady state hides the exact failure that started this.
• Error rate: 5xx below 0.01 percent, with the retry fraction visible and bounded rather than inferred.
• Root cause: the p99 of the slowest endpoint returns to a sub-300 ms baseline and stays flat for two weeks. If it does not, the query fix was not the fix and the six-week trend will resume.

Load-test to confirm: replay 2x peak with instance churn and forced retries. If the pool still drains and the database stays under 70 percent, the design has headroom rather than luck.