Database Connection Pool Exhaustion
Web & Application Troubleshooting
Chapter 3 · Database Connection Pool Exhaustion
Chapter 2 fully identified the checkout ticket's error: pool_timeout, retryable, correlation ID in hand. This chapter is about actually resolving it — connection pools, why they exist, the two genuinely different reasons one runs dry, and why the tempting quick fix can make the real cause worse.
What a Connection Pool Is and Why It Exists
Opening a new database connection is expensive — a TCP handshake, authentication, session setup — expensive enough that doing it fresh for every single request would be far too slow. A connection pool solves this by keeping a fixed set of connections already open, which application code borrows for the duration of a query and returns immediately afterward, ready for the next request to reuse.
Reading Pool Metrics
Active connections are currently checked out and in use; idle ones are open but available; waiting is the number of requests currently blocked, unable to get a connection at all — exactly this course's own opening example from Chapter 1. All 20 connections in use, none idle, 14 more requests queued behind them: the pool is genuinely exhausted. The interesting question is why.
Two Genuinely Different Root Causes
| Cause | What's actually happening |
|---|---|
| Genuine capacity shortage | Traffic has grown, and the pool size was never increased to match — under peak load, demand simply exceeds the pool's supply, spread evenly across many ordinary, short-lived connections |
| A small number of connections held too long | The pool isn't undersized at all — a small number of misbehaving requests (a slow query, a forgotten commit/rollback, a leaked connection) are holding onto connections far longer than normal, starving everyone else |
This is genuinely the same shape of question System Monitoring & Performance Diagnosis's own Chapter 9 asked about a rising resource trend — is this real growth, or a leak — just applied to a connection pool instead of memory.
Telling the Two Apart: Connection Hold Time
The active/idle/waiting counts alone can't distinguish the two — both look identical from that view alone. What actually separates them is how long connections are being held. If most connections check out and return quickly, and the pool is simply saturated by genuinely high concurrent traffic, that's a capacity problem. If a small handful of connections are held for seconds or minutes while everything else churns normally in milliseconds, that's a small number of culprits starving the whole pool — not a sizing problem at all.
Finding the Specific Culprit
Most databases expose exactly this — which queries are currently running, and for how long:
One query has been running for over four minutes — dramatically longer than everything else on the list, which is finishing in a couple of seconds. This isn't a capacity problem; it's one specific query holding a connection hostage while everything else is genuinely fine.
SHOW PROCESSLIST; (or SELECT * FROM information_schema.PROCESSLIST; for a queryable form) shows the same information on MySQL — each connection's current query and how long it's been running, sorted the same way.
Working Example: Fully Resolving the Checkout Ticket
Chapter 1's ticket, finally closed out: pg_stat_activity confirms exactly one query — a full scan of order_items with no supporting index on the column it filters by — has been running for over four minutes, while every other query on the system finishes normally. That single stuck query is holding a connection the whole time, and under peak checkout traffic, enough concurrent requests hit the same slow code path to exhaust the pool entirely, producing the "some requests fail, not all" pattern Chapter 2 identified. The actual fix is adding the missing index (or rewriting the query) — not increasing the pool size, which would only mask the same problem a little longer while adding more load to an already-struggling database.
Hands-On Exercises
Explain why "active: 20, waiting: 14" alone can't tell you whether a connection pool is genuinely undersized or being starved by a small number of misbehaving connections.
📄 View solutionExplain why simply increasing the pool size can make things worse if the real cause is a leak or a slow query, rather than genuine capacity shortage.
📄 View solutionIn this chapter's worked example, explain what the pg_stat_activity query actually revealed, and why that specifically confirms this was a "held too long" problem, not a capacity problem.
Chapter 3 Quick Reference
- A connection pool exists because opening a new database connection per request is too expensive to do every time
- Two genuinely different causes of exhaustion: genuine capacity shortage vs. a small number of connections held too long
- The active/idle/waiting counts alone can't distinguish them — connection hold time is what actually separates the two
pg_stat_activity(PostgreSQL) /SHOW PROCESSLIST(MySQL) find the specific stuck query directly- Increasing pool size doesn't fix a leak — it delays the same symptom and adds real load to the database
- Next chapter: Caching Layer Problems: Stale Data & Cache Stampede