Drill 79 — The Connection Pool That Ran Dry

Theme: Networking & DNS · Format: 20-min drill · Difficulty: core

The story

A retail bank's account-services API talks to one PostgreSQL primary through one shared connection pool — twenty connections, sized once at launch and never revisited. Most of what flows through it is small and fast: balance lookups, payment posts, transaction history for the last thirty days. There is also one heavier endpoint, /reports/statement-activity, that analysts use to pull a customer's full history. It runs a wide, unindexed scan, but it's rare, so nobody has ever worried about it.

It's the last business day of the quarter and traffic is already running hot. An operations analyst, chasing a reconciliation, opens the statement-activity report for a large corporate account and — getting no result on the first try — reloads it four more times. Each of those requests grabs a connection from the pool and holds it for thirty to forty seconds while the scan grinds through the database.

Within a minute the mobile app starts failing. The status page for payments goes red. Balance lookups that normally return in twenty milliseconds are timing out. The on-call engineer pulls up the database dashboard expecting to find it on fire — and finds it nearly idle. CPU is at fifteen percent. Disk is fine. Replication lag is zero. There are no slow-query alerts, no locks, no deadlocks. The database is healthy, and the application is down.

⏸ Stop here and ask the room: the database is healthy — low CPU, no locks, no slow-query alarms. So why is every fast payment request timing out?

Run it — facilitator script

Timing for a 20-minute session: 2 min story · 10 min staged questions · 3 min reveal · 5 min transfer.

Where is the contention, if it isn't in the database?

• Listen for: the connection pool, not the database, is the exhausted resource. A pool is a fixed number of connections; a handful of slow queries can check out all of them and hold them, so fast queries have nothing left to acquire. The database is idle precisely because almost nothing is reaching it — the work is stuck queuing for a connection, upstream.
• Weak signal: "scale up the database" or "the query is slow, add an index" — it treats a healthy database as the culprit and misses that the fast path never even got a connection.

The fast queries didn't fail — they waited. Why is a wait as bad as an error?

• Listen for: an unbounded checkout queue converts a resource shortage into a latency cliff for everyone. Once all twenty connections are held, every new request blocks waiting for one; the queue grows, waits exceed the client timeout, and callers see 500s and timeouts even though each individual query is fast. Waiting without a bound is just a slow way to fail.
• Weak signal: "just increase the pool size" — a bigger pool delays the wall, doesn't remove it, and can overload the database once the slow queries scale with it.

One endpoint took down the others. What structural property was missing?

• Listen for: isolation. Reporting and transactional work shared one pool, so a slow neighbor could starve the hot path — no bulkhead. Separate pools per workload, plus statement timeouts so no query can hold a connection indefinitely, plus load shedding so the reporting endpoint sheds before it drains the shared resource.
• Weak signal: "tell the analyst not to reload" — it blames the user for a system that had no containment; any burst, retry storm, or scheduled report would have done the same.

The reveal

The database was never the problem. Five overlapping statement-activity scans, each holding a connection for thirty to forty seconds, checked out the entire twenty-connection pool. Every fast payment and balance query then blocked in the pool's checkout queue, waited past its client timeout, and returned an error — while the database sat at fifteen percent CPU with nothing to do. The fix was not a bigger database or a faster query. The team split the pool by workload (a small reporting pool that can never starve the transactional one), added statement timeouts so no query holds a connection past its budget, bounded the checkout queue so waits fail fast, and made the reporting endpoint shed load when the pool saturates. The next quarter-end passed without a blip.

Why this happens — the mechanics

A connection pool is a fixed, shared, and therefore contendable resource that sits between the application and the database. Throughput through it is bounded by (number of connections ÷ how long each request holds one). When a class of requests holds connections for tens of seconds, a pool of twenty can be fully checked out by a mere handful of them — and every other request, however cheap, now competes for a connection that doesn't exist. The database looks healthy because it is healthy: the contention is upstream of it, in the pool, so none of the database's own signals — CPU, locks, slow queries — ever move.

The organizational seam is that "reporting" and "transactional" were two workloads with completely different latency profiles sharing one undifferentiated resource. Nobody owned the question "what happens to payments when a report runs slowly at peak?" A pool with no per-workload isolation, no statement timeout, and an unbounded checkout queue turns any slow neighbor into a full outage. This is the noisy-neighbor and bulkhead lesson wearing a database costume — the guarantee has to live in how the shared resource is partitioned and bounded, not in hoping the slow query stays rare.

Diagrams

Problem — how the failure unfolds:

sequenceDiagram
    autonumber
    participant R as Report job
    participant T as Txn API
    participant P as Conn Pool
    participant DB as Database
    Note over R: analyst reloads<br/>report 5x at peak
    R->>P: check out all conns
    P->>DB: slow scan queries
    Note over P,DB: 20 of 20 held<br/>for 30-40s each
    T->>P: need a connection
    P--xT: none free, queue grows
    Note over T: fast queries wait<br/>past client timeout
    T--xR: 500s and timeouts
    Note over DB: CPU 15 pct, no locks<br/>database is healthy

Solution — where the guardrails go:

flowchart TD
    A["Report spike hits<br/>shared pool"] --> B["Right-size pool +<br/>bounded checkout queue"]
    A --> C["Per-workload pools<br/>(reporting vs txn)"]
    A --> D["Statement timeouts<br/>on every query"]
    B --> E["Pool-saturation SLI<br/>(in-use / wait time)"]
    C --> E
    D --> E
    E -.-> AL["Shed load + page<br/>when pool over 90 pct"]
    AL --> H["Txn path stays fast<br/>DB stays healthy"]

What good looks like

• Right-sized pool with a bounded wait: size the pool to what the database can usefully run at once (roughly cores × 2, ~20 here), and cap the checkout wait at 250 ms — past that, fail fast with a clear "pool exhausted" error instead of queuing unboundedly.
• Per-workload pools (bulkhead): a reporting pool capped at 5 connections and a separate transactional pool of 20, so reporting can consume at most its own share and can never starve payments. Batch/analytics traffic gets its own pool too.
• Statement timeouts everywhere: transactional statement_timeout = 2s, reporting = 30s; any query that overruns is killed and its connection returned, so no single query can pin a connection for 40 seconds.
• Load shedding on saturation: when in-use connections exceed 90% of the pool or checkout wait exceeds 250 ms, return 503 on non-critical endpoints (reporting first) to protect the hot path, rather than letting the pool drain.
• A pool-saturation SLI: graph pool utilization and p99 checkout-wait time; warn at 80% utilization, page when p99 checkout wait > 100 ms for 2 minutes — a signal that lives in the pool, not the database.
• A game-day: in staging, fire the slow reporting query at peak transactional load and assert transactional p99 stays under budget (e.g. < 50 ms) and the reporting pool, not the whole app, is what saturates.

The lesson

A healthy database behind an exhausted connection pool still reads as a total outage — because the resource that ran out is the pool, and none of the database's own signals will show it. Isolate workloads into separate pools, put a timeout on every query, and bound every wait, so a slow neighbor degrades itself instead of everyone.

Named concepts: connection-pool exhaustion, bulkhead isolation, statement timeouts, load shedding.

Real-world echoes

• Database connection-pool exhaustion is one of the most common root causes in application-tier postmortems: a slow query, a long-running report, or a downstream stall holds pooled connections, the pool empties, and unrelated fast endpoints fail with timeouts while the database itself looks perfectly healthy. It's why pool libraries expose checkout-timeout and pool-utilization metrics, and why mature teams split pools by workload and cap statement time — the same bulkhead reasoning behind Netflix's Hystrix thread-pool isolation.

Transfer

Which shared connection pool in your stack could a single slow query drain — and which fast, customer-facing endpoint would start failing first when it does? "The database is over-provisioned" is not an answer; name the pool, its size, and what shares it.

Make it a ticket: for your busiest pool, split reporting/batch traffic into its own capped pool, set a statement_timeout on every workload, bound the checkout queue to fail fast at 250 ms, and add a pool-utilization SLI that pages before the pool runs dry — then prove it with a game-day.

Related

• Drill 75 — The TTL That Was Too Long (a sibling networking drill: "the config looks right, the users still hurt").
• Drill 21 — The Thundering Herd (a burst overwhelming a shared, bounded resource).
• Drill 24 — The Autoscaler That Fed the Fire (scaling the wrong tier makes contention worse).
• Pattern #114 — Bulkhead Isolation (partition shared resources so one workload can't sink the rest).