Database Connection Pool Exhaustion — Incident RCA

On April 12, 2025, at 16:22 UTC, our primary PostgreSQL database connection pool was exhausted, causing severe API degradation for 45 minutes. A combination of factors led to the incident: (1) a slow analytical query that held connections for 8+ minutes, (2) connection leaks in a recently deployed background job (order-sync-worker v1.2.0), and (3) a traffic spike from a marketing campaign that increased connection demand by 40%. With all 200 connections in use, new requests queued and eventually timed out. P99 API latency reached 30 seconds. Resolution required killing the long-running query, rolling back the leaky worker, and temporarily increasing the pool size.

The primary root cause was connection pool exhaustion from two concurrent issues:

1. Slow query: An ad-hoc analytical query performed a full table scan on the 50M-row orders table. It held 5 connections for 8+ minutes. The query lacked proper indexing and had no statement timeout.

2. Connection leak: The order-sync-worker v1.2.0 had a bug where connections were acquired but not released in an error path. Under load, the worker opened connections faster than they were closed, eventually consuming the entire pool.

A contributing factor was the traffic spike from the marketing campaign, which increased normal connection usage and reduced the buffer before exhaustion.

• Duration: 45 minutes (degraded), 10 minutes (severe)
• API impact: P99 latency 30s, 15% of requests failed with 504
• Users affected: ~25,000 requests during window
• Data loss: None. No corruption or failed transactions.
• Revenue impact: Checkout and API-dependent features degraded

Detection (16:22 UTC): Pool exhausted (200/200). API latency spiked. Alerts fired. MTTD: ~7 minutes from first slow query to full exhaustion.

Response (16:22–16:28 UTC): Incident declared. DB team identified long-running query + leaky order-sync-worker. Kill query, scale worker to 0.

Resolution (16:28–17:07 UTC): Connections freed. Rollback worker to v1.1.9. Add index for analytical query. Pool stable by 16:42. All systems operational at 17:07. MTTR: 45 minutes.

1. Why did API fail? → Connection pool exhausted, requests queued and timed out.
2. Why exhausted? → Slow query held 5 connections 8+ min; leaky worker consumed rest.
3. Why slow query? → Full table scan on 50M-row orders table, no index, no statement_timeout.
4. Why leaky worker? → order-sync-worker v1.2.0 had bug: connections not released in error path.
5. Why deployed? → ROOT CAUSE: No integration test for connection lifecycle, no pool utilization alerting at 80%.

• statement_timeout (5 min) for all analytical queries
• Fix connection leak, add integration test
• Index for orders (created_at, status)
• Alert at 80% pool utilization
• PgBouncer for read replicas
• Ad-hoc queries: read replicas only, strict timeouts

[ERROR] PostgreSQL connection pool at 200/200. New connections queued.

SELECT * FROM orders WHERE status='pending' -- 8m 12s, 5 connections held. Full table scan.

[WARN] order-sync-worker Connection acquired but not released in error path. Pool usage: 187/200

• Connection leaks compound quickly. A single leaky service can exhaust the entire pool under load.
• Slow queries are connection hogs. Statement timeouts and query review are essential.
• Traffic spikes expose capacity limits. We need better auto-scaling and pool sizing for campaigns.
• Ad-hoc queries need guardrails. Analytical queries should use read replicas and have strict timeouts.
• Database connection pool postmortem — this documents exhaustion, slow queries, and leak recovery.