# Database Connection Pools > Why too many connections takes down production, what a database connection actually costs, and how pool sizing keeps app and database alive. --- # Database Connection Pools Your app was fine all week. Then traffic doubled, and the database fell over with `too many connections` while CPU sat at 20% - plenty of headroom, yet everything timed out. The villain is almost never query speed here. It's how many doors you tried to open into the database at once. This guide gives you the mental model for what a connection actually costs, why a pool fixes it, and how to size one without guessing. ## How to read this Read the three phases in order - they build on each other. If you've ever stared at a `connection pool timeout` log line at 3am, Phase 3 is your destination, but Phase 1 and 2 are what make it make sense. ## The phases 1. [What a connection actually costs](01-what-a-connection-costs.md) - the mental model: a connection is memory plus a handshake, not a free function call. 2. [How a pool works and how to size it](02-how-a-pool-works.md) - reuse a fixed set of connections, and pick the number on purpose. 3. [When pools break: exhaustion, leaks, and serverless storms](03-when-pools-break.md) - the failure modes that page you, and how to survive them. --- # What a connection actually costs Here's the trap, and almost everyone falls in it once. You learned that a database is a thing you talk to. You send a query, you get rows back. So when you needed to talk to it from your code, you opened a connection, ran the query, and moved on. It worked. It worked in development, it worked in the demo, it worked for the first thousand users. Then it didn't. And the error wasn't `query too slow` - it was `FATAL: sorry, too many clients already` or `remaining connection slots are reserved`. Your queries were fine. You ran out of *doors*. To understand why, you have to unlearn one thing: a connection is not like calling a function. It feels free because the code looks tiny. It is not free. ## A connection is a living thing on the server When your app opens a database connection, the database doesn't hand you a lightweight token. On a server like PostgreSQL, it forks a whole backend process to serve you. That process holds memory for your session: work buffers for sorting and joins, caches, the state of your current transaction, prepared statements, temporary tables. It sits there, alive, waiting for your next query - even when you're doing nothing. Think of it less like dialing a phone number and more like hiring an employee. Opening a connection is an interview and onboarding. The connection sitting idle is that employee on payroll, taking up a desk, whether or not there's work to do. ```text Your app PostgreSQL server | | |---- TCP connect ------------------->| (network round trip) |<--- "who are you?" -----------------| |---- auth / password / TLS -------->| (more round trips) |<--- "ok, here's your backend" -----| (server forks a process, | | allocates memory for it) |---- SELECT ... -------------------->| |<--- rows ---------------------------| | (process stays alive, | | holding memory, idle) | ``` *What just happened:* opening one connection cost several network round trips for the handshake, plus the server spinning up a dedicated process that now consumes memory for as long as the connection lives. None of that is the query. That's the price of admission, paid before you run anything. ## Two costs, and they bite at different times It helps to separate the two ways a connection costs you, because they hurt in different situations. **The setup cost (latency).** The TCP handshake, the authentication, the TLS negotiation if you're encrypting - these are round trips across the network. If your database is a millisecond away and you open a connection per request, you've added that handshake to every single request. A query that takes 2ms now lives behind 5ms of "hello, who are you, let me allocate you a process." You made the fast part wait on the slow part. **The standing cost (memory and slots).** Every open connection holds server memory whether it's busy or idle. And the database has a hard ceiling on how many it will accept at once - in PostgreSQL that's the `max_connections` setting, often defaulted to around 100. Hit that ceiling and the next connection attempt is *rejected*. Not slowed. Rejected, with the `too many clients` error. The database is protecting itself, because each connection it accepts is more memory it has promised to hold. > The dangerous part: the standing cost is invisible in development. With one developer and a handful of requests, you never approach the ceiling. The wall is real, but you only meet it under load - in production, at the worst possible moment. ## Why "open one per request" melts the database Now put it together. Imagine your app opens a fresh connection for every incoming web request, runs its query, and closes it. At ten requests a second, that's ten handshakes a second, ten processes flickering into existence and dying. Wasteful, but survivable. Now you go viral and it's five hundred requests a second, each holding its connection for the few hundred milliseconds it takes to do real work. Do the arithmetic: requests arriving faster than they finish means open connections pile up. You blow past `max_connections` in seconds. ```text Requests per second climbing → 10 req/s : ~a few connections alive at once → fine 100 req/s : ~dozens alive at once → getting warm 500 req/s : hundreds wanted, ceiling is 100 → REJECTED "too many clients" ``` *What just happened:* the failure isn't gradual degradation - it's a cliff. Below the ceiling you're fine; cross it and new connections are flatly refused. CPU can be near idle while the database refuses everyone, because the limit you hit was the *connection count*, not the *compute*. This is exactly the "20% CPU, total outage" mystery from the overview. For builders: this is the moment most people first reach for a connection pool - not because they read about it, but because production taught them. The pool exists precisely so you stop paying the setup cost on every request and stop piling toward the standing-cost ceiling. That's Phase 2. ## The mental model to keep Burn this one sentence in: **a connection is expensive to open and expensive to keep, and the database can only keep so many.** Everything else in this guide is a consequence of that one fact. Pools, sizing, leaks, the serverless storm - all of it is people trying to live within those two costs and that one hard ceiling. If a database is new to you, the broader picture of what you're connecting *to* lives in [what a database is](/guides/what-a-database-is). And when the ceiling itself becomes the bottleneck no matter how clever your pool is, that's a scaling problem - see [scaling a database](/guides/scaling-a-database). ```quiz [ { "q": "On a server like PostgreSQL, what does opening a single connection typically allocate?", "choices": [ "Nothing until you run a query", "A dedicated backend process holding memory for your session", "A shared read-only buffer used by all clients", "A temporary file on disk that is deleted on the next query" ], "answer": 1, "explain": "The server forks a backend process that holds session memory for as long as the connection lives - even while idle." }, { "q": "Why can the database refuse new connections while its CPU is nearly idle?", "choices": [ "The query planner is broken under load", "CPU and connections are the same limit", "It hit max_connections - a hard ceiling on connection count, not compute", "Idle CPU always means the disk is full" ], "answer": 2, "explain": "max_connections caps how many connections the server will accept. Hit it and new ones are rejected outright, regardless of CPU headroom." }, { "q": "What is the 'setup cost' of a connection?", "choices": [ "The memory a connection holds while idle", "The handshake round trips: TCP, auth, and TLS before any query runs", "The time the query itself takes to execute", "The cost of writing the result rows to disk" ], "answer": 1, "explain": "Setup cost is latency from the handshake - TCP, authentication, TLS - paid before a single query runs. Standing cost is the idle memory and slot." } ] ``` --- # How a pool works and how to size it Phase 1 left you with a problem: opening a connection per request is expensive to do and expensive to keep, and the database has a hard ceiling. The fix isn't "open connections faster" - it's to stop opening them per request at all. A connection pool does that, and the idea is almost embarrassingly simple. Keep a small set of connections open all the time. Lend one out when code needs it, take it back when the code is done, and lend the same one to the next request. Connections never close between requests - they get *reused*. ## The pool is a box of pre-opened connections Picture a box. When your app starts, the pool opens, say, ten connections and parks them in the box, ready to go. The handshake - the expensive setup from Phase 1 - happens once, at startup, not on the hot path. Now a request comes in and needs the database. Instead of opening a connection, it asks the pool: *give me one*. It borrows a connection from the box, runs its query, and - this is the crucial part - **returns it to the box** instead of closing it. The next request borrows that same warm, already-authenticated connection. ```text Connection pool (size 10) ┌─────────────────────────────┐ │ [c1][c2][c3][c4] ... [c10] │ all opened once, at startup └─────────────────────────────┘ │ borrow ▲ return ▼ │ request A runs query, gives it back │ borrow ▲ return ▼ │ request B reuses the SAME connection ``` *What just happened:* the handshake cost from Phase 1 got paid once per connection at startup and then amortized across thousands of requests. Each request now pays roughly zero setup cost - it borrows a connection that's already open and authenticated. ## "Acquire, use, release" - and release is sacred Almost every pool library, in every language, gives you the same three-beat rhythm: **acquire** a connection, **use** it, **release** it back. Names differ - get/borrow/checkout, return/release/close - but it's always those three beats. ```text conn = pool.acquire() # borrow from the box (may wait if box is empty) try: conn.execute("SELECT ...") rows = conn.fetch() finally: pool.release(conn) # ALWAYS give it back, even if the query threw ``` *What just happened:* the connection is borrowed, used, and returned in a `try/finally` so it goes back to the box even if the query raises an error. That `finally` is not decoration - it's the difference between a healthy pool and a slow death we'll cover in Phase 3. Most mature libraries wrap this for you so you can't forget. In Python it's a context manager (`with pool.acquire() as conn:`); in other ecosystems it's a callback or a scope that auto-releases at the end. **Prefer the wrapped form every time.** The manual `acquire`/`release` above is shown so you understand what the wrapper is doing - in real code, let the language hand the connection back for you. ## Sizing: bigger is not better Here's where intuition lies to you. You had an outage from too few connections, so the instinct is to crank the pool size way up - set it to 500 and never run out, right? No. A bigger pool makes things *worse* past a point, for two reasons. **Reason one: the database's hard ceiling is still there.** Your pool size is how many connections *you* hold open. The database's `max_connections` is how many it will accept *total*, across every client. If you run five copies of your app (five servers, five containers) and each has a pool of 100, that's 500 connections demanded against a ceiling of maybe 100. You didn't fix the outage - you moved it. Pool size must be reasoned about *per total deployment*, not per process. ```text 3 app instances × pool size 100 = 300 connections demanded database max_connections = 100 └─ 200 over the ceiling → rejections ``` *What just happened:* the math that matters is instances times pool size against the server ceiling. A pool that looks safe on one box becomes an overdraft when you scale out horizontally. Always multiply. **Reason two: the database does less work when you stop over-feeding it.** A database has a finite number of CPU cores and disk spindles. Throw 200 concurrent queries at a machine with 8 cores and they don't all run at once - they fight over the same cores, locks, and disk, generating context-switching overhead and contention. Throughput can actually *drop* past the sweet spot, because queries spend their time waiting on each other instead of finishing. A widely cited starting point from the PostgreSQL community, popularized by the HikariCP pool, is to begin near `connections = (cores × 2) + effective_spindle_count` and then measure. The exact formula matters less than the lesson: **the right number is small, often surprisingly small - typically dozens, not hundreds - and you find it by measuring, not by maximizing.** Start conservative, watch your latency and throughput under real load, and adjust. > A small pool that keeps the database happy will out-throughput a huge pool that makes the database thrash. Counterintuitive, but it's the whole reason sizing is a skill and not "set it to a big number." ## What happens when the box is empty So the pool is small on purpose. What happens when every connection is borrowed and a new request shows up wanting one? The pool makes that request **wait**. It blocks, holding its place in line, until someone releases a connection back into the box. This is by design and it's healthy - a short wait under a brief spike is far better than crashing the database with unbounded connections. But waiting has a limit too. Every pool has an **acquire timeout** (sometimes called connection timeout - confusingly, since it's not about the network). If no connection frees up within that window - say, 30 seconds - the waiting request gives up and throws a `pool timeout` / `unable to acquire connection` error. That error means "I asked for a connection, waited my whole patience, and the box never had one free." ```text pool full, all 10 borrowed │ request K asks for a connection │ ├─ a connection frees up in time → K gets it, runs ✓ │ └─ nothing frees up before timeout → K throws "pool timeout" ✗ ``` *What just happened:* an exhausted pool degrades in two stages - first requests *wait* (latency climbs), then they *time out* (errors appear). Seeing pool-timeout errors is your signal that demand is outrunning the pool, the queries are too slow, or - the nasty one - connections aren't being given back. That last case is Phase 3. For builders: the three numbers you'll actually configure are **pool size** (how many connections in the box), **acquire timeout** (how long a request waits for a free one), and often a **max idle / max lifetime** (when to retire and reopen a connection so it doesn't go stale). Start with a small pool, a sane timeout, and measure before you touch anything. ```quiz [ { "q": "What makes a connection pool faster than opening a connection per request?", "choices": [ "It compresses query results before sending them", "It pays the handshake cost once at startup, then reuses warm connections", "It runs queries on the app server instead of the database", "It caches query results so the database is never touched" ], "answer": 1, "explain": "The expensive setup (TCP, auth, TLS) happens once when connections are opened. Requests then borrow and return already-open connections, paying almost no setup cost." }, { "q": "You run 3 app instances, each with a pool size of 100, against a database with max_connections = 100. What's the problem?", "choices": [ "Nothing - 100 is the per-instance limit", "The pools demand up to 300 connections against a ceiling of 100, causing rejections", "The database will automatically raise its ceiling to 300", "Each instance only ever uses one connection" ], "answer": 1, "explain": "Pool size is per process; the database ceiling is total. Multiply instances by pool size and compare against max_connections - 300 demanded vs 100 allowed overflows." }, { "q": "Why can a pool that is too LARGE reduce throughput?", "choices": [ "Large pools use more network bandwidth per query", "Too many concurrent queries contend for finite cores, disk, and locks, causing thrash", "The pool library scans all connections on every acquire", "Larger pools force the database into read-only mode" ], "answer": 1, "explain": "A database has finite CPU and disk. Past a sweet spot, more concurrent connections fight over those resources, and throughput can drop instead of rise." } ] ``` --- # When pools break: exhaustion, leaks, and serverless storms You've got a pool. It's sized sensibly. And then one night the pager goes off anyway: requests are timing out, latency is a sawtooth, and the database logs show connections maxed. A pool doesn't make connection problems disappear - it makes them *legible*. The failures now show up at the pool boundary, and once you know the three classic shapes, you can read them like a chart. ## Failure one: exhaustion (the pool is genuinely too small) The straightforward case first. Sometimes the pool is exhausted because real demand genuinely exceeds it. Traffic spiked, or your queries got slower, so each connection is held longer, so the box drains faster than it refills. The symptom is the two-stage decline from Phase 2: latency climbs first (requests waiting for a free connection), then `pool timeout` errors appear (requests giving up). The tell that this is *true* exhaustion and not a bug: the pressure tracks traffic. It's worst at peak, it eases when traffic drops, and connections do come back. ```text latency │ ╭─╮ ← peak traffic: requests queue for connections │ ╭───╯ ╰──╮ │ ╭────╯ ╰──── ← off-peak: pool drains slower, recovers └────────────────────── time ``` *What just happened:* the latency hump lines up with the traffic curve and recovers on its own. That correlation is the signature of true exhaustion - the fix is more capacity (a slightly bigger pool, faster queries so connections are held less time, or scaling the database itself, which is [scaling a database](/guides/scaling-a-database)). ## Failure two: the leak (connections borrowed and never returned) This is the cruel one, because it looks like exhaustion but it never recovers. A **connection leak** is code that borrows a connection from the pool and forgets to return it. The connection is gone from the box forever - not closed, not usable, merely held by some code path that wandered off. Leak a connection per request and your pool drains one slot at a time. The box empties. Then *every* request starts timing out, traffic high or low, and a restart "fixes" it (the pool refills) only for it to drain again. The classic cause is exactly the missing `finally` from Phase 2: an error fires mid-request, the code jumps to the error handler, and the line that releases the connection never runs. ```text Healthy: acquire ──▶ use ──▶ release (box stays full) Leak: acquire ──▶ use ──▶ 💥 error │ └─ jumps to handler, release SKIPPED connection lost from the box forever ``` *What just happened:* one error path skipped the release, so that connection never came back. Repeat per request and the pool bleeds dry. The signature that distinguishes a leak from true exhaustion: it gets monotonically worse over time regardless of traffic, and a restart resets the clock instead of fixing the cause. The fix is structural, not heroic. **Never release by hand on the happy path and hope.** Use the language's scoped form so the connection is returned no matter how the block exits: ```python # Good: the context manager releases on every exit - return, raise, anything. with pool.acquire() as conn: rows = conn.execute("SELECT ...").fetchall() # connection is back in the box here, even if execute() raised ``` *What just happened:* the `with` block guarantees the connection returns to the pool whether the body finishes normally or throws. This is the same `try/finally` from Phase 2, made automatic - and it's the single most effective leak prevention there is. > Most pool libraries can also set a **leak detection threshold**: log a warning if a connection is held longer than, say, 30 seconds. Turn it on. It points a finger at the exact code path holding connections hostage, instead of leaving you guessing. ## Failure three: the serverless connection storm This one blindsides people moving to serverless (Lambda, Cloud Functions, and friends), because the platform's whole model fights the pool's whole model. A pool works because it's *long-lived*: one process, holding a box of connections, reusing them across thousands of requests. Serverless is the opposite - each function instance is short-lived and isolated, and the platform spins up a *fresh instance per concurrent request* to scale. There's no shared long-lived process to host a shared pool. So the naive approach - open a connection (or a tiny pool) inside the function - turns catastrophic under load. Traffic spikes to 1,000 concurrent requests, the platform obliges by launching 1,000 function instances, and each one independently opens a connection. That's 1,000 connections stampeding your database at once. The ceiling from Phase 1 is obliterated instantly. ```text 1 request → 1 function instance → 1 connection 1,000 requests at once → platform spins up ~1,000 instances → ~1,000 connections opened simultaneously → database max_connections (≈100) → instant "too many clients" ``` *What just happened:* serverless scales by multiplying isolated instances, and each instance opening its own connection multiplies connections in lockstep with traffic. The pool's "reuse across requests" superpower can't happen, because there's no shared process to reuse across. The fix is an **external pooler** - a connection-pooling proxy that lives *between* your functions and the database, as its own long-lived service. Your thousand function instances connect to the pooler (which is cheap to connect to); the pooler maintains one small, sane pool of real connections to the database and multiplexes everyone's queries over them. In the PostgreSQL world this is PgBouncer (or a managed equivalent your cloud or database provider offers); other databases have their analogues. ```text 1,000 function instances │ (cheap client connections) ▼ ┌──────────────┐ │ pooler │ holds ONE small real pool │ (PgBouncer) │ └──────┬───────┘ │ (e.g. 20 real connections) ▼ database ✓ never sees the storm ``` *What just happened:* the pooler absorbs the stampede. A thousand functions hit the pooler, but the database only ever sees the pooler's small, fixed set of real connections. The "reuse a fixed set" idea from Phase 2 still wins - it just had to move out of the function and into a shared proxy. ## How to read the three at a glance When connections are maxed and the pager's screaming, this is the triage: - **Tracks traffic, recovers on its own?** → true exhaustion. Add capacity or speed up queries. - **Monotonically worse, restart resets it?** → a leak. Find the unreturned connection; switch to scoped acquire. - **On serverless and spikes instantly with concurrency?** → connection storm. Put a pooler in front. For builders: the throughline across all three failures and both earlier phases is one idea - **a fixed, reused set of connections, always returned, sized to what the database can actually serve.** Pools, scoped acquires, and external poolers are three forms of that same discipline. Get it right and the database stops being the thing that takes you down at 3am. ```quiz [ { "q": "A connection leak and true exhaustion both show pool-timeout errors. What distinguishes a leak?", "choices": [ "A leak only happens at peak traffic", "A leak gets monotonically worse over time regardless of traffic, and a restart temporarily resets it", "A leak shows no errors, only slower queries", "A leak fixes itself when traffic drops" ], "answer": 1, "explain": "True exhaustion tracks the traffic curve and recovers. A leak drains the pool steadily no matter the load, and restarting only refills the box until it bleeds out again." }, { "q": "What is the single most reliable way to prevent connection leaks?", "choices": [ "Increase the pool size so leaks don't matter", "Call release() at the end of every function", "Use the language's scoped form (e.g. a context manager) that returns the connection on every exit path", "Restart the app on a schedule" ], "answer": 2, "explain": "A scoped acquire returns the connection whether the block finishes normally or throws, eliminating the missing-finally bug that causes most leaks." }, { "q": "Why does a naive in-function pool cause a 'connection storm' on serverless platforms?", "choices": [ "Serverless functions run queries more slowly", "The platform spins up an isolated instance per concurrent request, so each opens its own connection - connections scale with traffic", "Serverless disables connection pooling entirely", "Functions share one connection that gets overloaded" ], "answer": 1, "explain": "Serverless scales by multiplying isolated short-lived instances. With no shared long-lived process, each instance opens its own connection, so a traffic spike becomes a connection spike. An external pooler absorbs it." } ] ```