system-design

Connection Pooling and Resource Limits

Opening a database connection is expensive, and databases can’t handle many of them. A pool solves both — and misconfigured pools are behind a surprising share of production outages.

Prerequisites: Concurrency Basics, Performance Metrics Time to read: ~14 minutes

The problem

Your app queries the database. Naively, each query opens a connection, runs, and closes it.

🚨 Two problems make this unworkable:

1. Opening a connection is expensive. A database connection requires a TCP handshake, TLS negotiation, and authentication — several round trips and server-side setup. Doing this per query adds milliseconds to every request and burns CPU. → Networking

2. Databases can’t handle many connections. 🚨 Each connection consumes server resources — Postgres famously allocates a whole backend process (~5-10MB) per connection, so a few thousand connections exhausts memory and the database thrashes on context-switching. Databases perform best with a limited number of connections (often a few hundred).

A connection pool solves both: maintain a fixed set of open, reused connections.

How pooling works

Instead of: open → query → close  (per request)
Pool:       borrow a connection from the pool → query → return it to the pool

The connections stay open and are reused. A fixed pool of N connections
serves all requests, borrowing and returning.

✅ No per-query connection cost (connections are already open — reused). ✅ Bounded database connections (the pool size caps them, protecting the database). ✅ Requests wait for a free connection if all are busy (natural backpressure).

The pool lives in the application (a library like HikariCP, or the language’s driver pool), or as a separate proxy (PgBouncer, ProxySQL) between the app and database.

Sizing the pool: the math that matters

🚨 This is where people get it wrong, and it causes real outages.

Use Little’s Law:

concurrency needed = throughput × latency
1000 QPS × 10ms query time = 10 concurrent queries → a pool of ~10-20

🚨 Counter-intuitively, bigger pools are often worse. More connections than the database can efficiently handle causes contention and context-switching that slows everything. The optimal pool size is usually smaller than people expect — often just a few times the CPU core count of the database.

📐 The formula (from HikariCP): roughly connections = ((core_count × 2) + effective_spindle_count) for the database. A database with 8 cores might be optimal at ~20 connections total, not 200.

The classic outage: pool × instances

🚨 The single most important connection-pooling failure mode, and a strong interview point:

50 app servers, each with a 100-connection pool
= 5,000 connections to a database that performs best at ~200
→ the database thrashes, refuses connections, and falls over

🚨 This is exactly the autoscaling-vs-database problem: you autoscale the stateless app tier to 50 instances, each opens its pool, and the aggregate connection count overwhelms the database — which doesn’t scale the same way. The app tier scaled beautifully and killed the database.

Fixes:

Smaller per-instance pools — size for the aggregate across all instances, not per-instance.
🚨 A connection pooler in front (PgBouncer) — the standard answer. It multiplexes thousands of client connections onto a small number of real database connections. So 50 app servers × 100 connections → PgBouncer → 200 real database connections. This decouples app-tier scaling from database connection limits.

🎙️ “With 50 app servers each holding a 100-connection pool, that’s 5,000 connections to a database that’s happiest at 200 — it’ll thrash and fall over. This is the app-tier-scales-but-the-database-doesn’t problem. I’d put PgBouncer in front to multiplex those thousands of client connections onto a couple hundred real ones.”

Pool exhaustion

🚨 When all pool connections are busy, requests wait for one — and if the wait is unbounded, they pile up:

A slow query holds a connection longer, so fewer are available, so more requests wait, so latency climbs — a feedback loop.
If the pool is exhausted and requests queue without a timeout, the whole service hangs.

Symptoms: requests timing out while the database CPU is low (the requests are waiting for a connection, not for the database). 🚨 This is a common, confusing outage — the database looks fine, but the app is stuck waiting on the pool.

Handling: a connection acquisition timeout (fail fast rather than hang), monitoring pool utilization and wait time (a leading indicator), and finding/fixing the slow queries holding connections.

Resource limits generally

🚨 Connection pools are one instance of a broader principle: bound every resource. Unbounded resources are how a small problem becomes an outage:

Connection pools — bound database connections.
Thread pools — bound concurrency (bulkheads).
Queue sizes — bounded queues, or memory exhaustion (backpressure).
File descriptors — 🚨 the default ulimit -n (often 1024) stops a server long before RAM does; raise it for connection-heavy services. → Concurrency
Memory limits — per-request and per-connection, to prevent one request OOMing the process.
Rate limits — bound request rate. → Rate Limiting

The principle: unbounded is a bug. Every pool, queue, and buffer needs a limit and a policy for what happens at the limit (wait, reject, shed).

Serverless makes this harder

🚨 Serverless breaks traditional pooling: each function instance is separate and short-lived, so there’s no long-lived process to hold a pool, and 1,000 concurrent function invocations means 1,000 connections. This is the serverless database problem, and the fix is a proxy (RDS Proxy) or a database designed for per-request access (DynamoDB, HTTP-based databases). → Serverless

⚖️ Trade-offs

Choice	Gain	Cost
Connection pool	Reuse connections; bound them	Sizing complexity; exhaustion risk
Smaller pool	Database happier; less contention	Fewer concurrent queries
Larger pool	More concurrency	Database contention; can be slower
PgBouncer / proxy	Decouples app scaling from DB connections	An extra hop and component
Acquisition timeout	Fail fast, no hang	Some requests rejected under load
Bounded resources generally	Prevents cascade/OOM	Rejects/waits at the limit

In the real world

The “50 servers × 100 connections = dead database” outage is a genuinely common production incident — teams autoscale the app tier, the aggregate connection count exceeds the database’s limit, and it falls over, confusingly, with the database CPU low. PgBouncer (or equivalent) is the standard fix, and it’s a strong thing to know.
HikariCP’s pool-sizing guidance (“about the pool size” wiki) is the reference — its counter-intuitive point that smaller pools are faster (because the database handles fewer connections more efficiently) reshaped how people size pools.
The serverless connection problem drove AWS to build RDS Proxy specifically — a managed connection pooler for Lambda — because the mismatch between serverless’s per-invocation model and databases’ connection limits was breaking real systems.

🚨 Interview traps

No connection pooling — per-query connections are expensive and exhaust the database.
Oversized pools — bigger is often worse; the database thrashes.
The 50-servers × 100-connections outage — not accounting for aggregate connections across instances.
No PgBouncer for high-instance-count or serverless setups.
No acquisition timeout — pool exhaustion hangs the service.
Not bounding other resources (threads, queues, file descriptors).
Ignoring the serverless connection problem.

🎙️ Soundbites

“A connection pool reuses a fixed set of open connections — avoiding the per-query handshake cost and bounding the connections the database sees, which matters because Postgres allocates a whole backend process per connection.”
“By Little’s Law, 1000 QPS at 10ms is ~10 concurrent queries, so a pool of 10-20 — and counter-intuitively bigger is often worse, because more connections than the database handles well causes contention that slows everything.”
“The classic outage: 50 app servers each with a 100-connection pool is 5,000 connections to a database happiest at 200 — the app tier scaled but killed the database. I’d put PgBouncer in front to multiplex thousands of client connections onto a couple hundred real ones.”
“Pool exhaustion looks like requests timing out while the database CPU is low — they’re waiting for a connection, not the database. So I’d set an acquisition timeout to fail fast and monitor pool wait time.”
“Every resource needs a bound — connections, threads, queues, file descriptors. Unbounded is a bug; it’s how a small problem becomes an outage.”

🛠️ Try it

1. Measure connection cost. Time 1,000 queries opening a fresh connection each vs 1,000 reusing a pooled connection. The per-connection overhead — handshake, TLS, auth — is dramatic, and it’s the argument for pooling.

2. Find the optimal pool size. Load-test a database at fixed QPS with pool sizes of 5, 20, 100, 500. Watch throughput and latency — often a smaller pool is faster, because the database handles fewer connections more efficiently. This is the counter-intuitive HikariCP result, verified.

3. Cause the aggregate-connection outage. Run many app instances (or simulate), each with a large pool, against one small database. Watch the aggregate connections exhaust the database while any single instance looks fine. Then add PgBouncer and watch it multiplex them down.

4. Exhaust a pool. Set a small pool and slow queries. Watch requests time out while the database CPU is low — the confusing “database looks fine but the app is stuck” symptom. Add an acquisition timeout and watch it fail fast instead of hanging.

Check yourself

1. Why do you need connection pooling — what two problems does it solve?

**Connection setup cost.** Establishing a database connection is expensive — it requires a TCP handshake, TLS negotiation, and authentication, several network round trips plus server-side setup — adding milliseconds and CPU to every query if done per-request. A pool keeps connections open and reuses them, so requests pay this cost once at startup, not on every query. **Database connection limits.** Databases can't efficiently handle many concurrent connections — Postgres allocates an entire backend process (~5-10MB of memory) per connection, so a few thousand connections exhausts memory and the database spends its time context-switching between processes rather than doing work, degrading throughput for everyone. Databases perform best with a limited number of connections (often a few hundred). A pool caps the number of connections at its fixed size, protecting the database from being overwhelmed by connections while still serving many requests by borrowing and returning from the fixed set. So pooling simultaneously eliminates the per-query connection overhead (reuse) and bounds the load on the database (fixed size) — two problems that both make naive per-query connections unworkable at any scale.

2. Why can a bigger connection pool be worse than a smaller one?

Because the bottleneck isn't the number of connections your application can open — it's how many concurrent connections the *database* can service efficiently, and that number is smaller than intuition suggests. Each connection the database handles consumes resources (in Postgres, a whole backend process), and the database can only truly execute as many queries in parallel as it has CPU cores (and disk spindles) to run them on. Beyond that, additional connections don't add parallelism — they add contention: the database context-switches between more processes, competes for locks and shared buffers, and thrashes, which slows down *every* query, not just the excess ones. So a pool of 500 connections to an 8-core database performs *worse* than a pool of 20, because the 500 create overhead the database struggles under, while 20 keep it busy without contention. HikariCP's guidance formalizes this: optimal database connections are roughly `(cores × 2) + spindles`, so an 8-core database is often best at ~20 connections. Requests beyond that concurrency should *wait* for a connection (natural backpressure) rather than opening more, because opening more makes throughput lower for everyone. This is counter-intuitive — people assume more connections means more capacity — but a right-sized small pool maximizes the database's actual throughput, which is why sizing pools too large is a common mistake.

3. Explain the "50 servers × 100 connections" outage and how PgBouncer fixes it.

When you scale a stateless application tier horizontally — say to 50 app servers — and each server holds its own connection pool of, say, 100 connections, the *aggregate* connection count is 50 × 100 = 5,000 connections to the database. But the database performs best at a few hundred connections (say 200), so 5,000 connections overwhelm it: it thrashes on per-connection processes, exhausts memory, and starts refusing new connections, taking the whole system down. Confusingly, each individual app server looks fine (it has its 100 connections), and even the database CPU may not be maxed — the failure is connection *count*, not query load. It's exactly the app-tier-scales-but-the-database-doesn't problem: you scaled the stateless tier beautifully, and the aggregate connections killed the stateful tier that doesn't scale the same way (autoscaling the app tier without accounting for database connections is the classic version). **PgBouncer** (a connection pooler / proxy that sits between the app and database) fixes it by multiplexing: the 50 app servers connect to PgBouncer (thousands of client connections), and PgBouncer maintains only a small pool of *real* connections to the database (say 200), reusing them across all the client connections. So the database sees 200 connections regardless of how many app servers exist, decoupling app-tier scaling from the database's connection limit. This is the standard answer for high-instance-count and serverless architectures, and knowing it — and the underlying "size for aggregate, not per-instance" principle — is a strong interview signal.

4. What does connection pool exhaustion look like, and how do you handle it?

Pool exhaustion is when all connections in the pool are busy, so incoming requests must *wait* for one to be returned. The tell-tale symptom is **requests timing out while the database CPU is low** — because the requests aren't waiting on the *database* (which is idle or lightly loaded), they're waiting on the *pool* to hand them a connection. This is confusing precisely because the database looks healthy, so people investigate the wrong thing. It also has a feedback loop: a slow query holds its connection longer, leaving fewer available, so more requests wait, so latency climbs, so the pool stays exhausted — a small slowdown cascades into a stuck service. Handling: set a **connection acquisition timeout** so a request waiting too long for a connection fails fast (returning an error the client can handle) rather than hanging indefinitely and piling up — bounded waiting instead of unbounded. **Monitor pool utilization and acquisition wait time** as leading indicators (rising wait time signals impending exhaustion before it causes visible failures). And **find and fix the slow queries** holding connections (a missing index, a lock, an N+1), since those are usually the root cause — the pool is exhausted because connections are being held too long. The broader lesson is that the pool is a bounded resource, and like any bounded resource, it needs a policy for what happens at the limit (fail fast) and monitoring of how close you are to it.

5. Why does serverless break traditional connection pooling, and what's the fix?

Traditional connection pooling relies on a long-lived application *process* that maintains a pool of open connections and reuses them across many requests over its lifetime. Serverless breaks both assumptions: each function invocation runs in a separate, short-lived instance, so there's no persistent process to hold a pool, and the platform scales by spinning up *many* concurrent instances — 1,000 concurrent invocations means potentially 1,000 separate instances, each opening its own connection (often just one, but times 1,000). So you get the same aggregate-connection problem as the 50-servers-×-100-connections outage, but worse and less controllable, because serverless scales elastically and unpredictably: a traffic spike could momentarily create thousands of function instances, each connecting to a database sized for a couple hundred connections, exhausting it instantly. Each invocation also pays the full connection-setup cost since there's no reuse. The fixes: put a **connection proxy** in front (AWS built RDS Proxy specifically for this — it maintains a warm pool of real database connections and multiplexes the thousands of transient function connections onto them, exactly as PgBouncer does for regular servers); or use a **database designed for per-request access** (DynamoDB and other key-value stores handle massive concurrent access without persistent connections, and HTTP-based databases like Neon and Aurora Data API let functions connect over HTTP without holding a connection). The alternative — serverless *containers* like Cloud Run, where one instance serves many concurrent requests — sidesteps the problem by having a long-lived process that can pool normally, which is one reason serverless containers are often preferable to functions for database-backed workloads.