Connection Pooling

1The core idea#

Opening a database connection is expensive, and not in a vague way.

• For Postgres, every new connection forks a backend process on the server, roughly 5 to 10 MB of RAM each.
• On top of that you pay a TCP handshake, a TLS negotiation if SSL is on, and an auth round trip.
• Across an availability zone that is easily 5 to 50 ms before you run a single query.
• Do that per request and you have bolted a tax onto every call. At 2,000 requests per second with a 20 ms setup cost, you burn 40 seconds of pure connection overhead every wall-clock second, work no user asked for.

A connection pool is a cache of already-open connections.

• Instead of open, query, close, your app borrows a live connection, uses it, and returns it.
• The next request grabs the same warm connection. No handshake, no fork, no auth round trip.
• The 20 ms tax drops to roughly the cost of a hash-map lookup.

The model I give juniors: connections are checkout counters at a store.

• You do not build a new counter per customer and tear it down when they leave.
• You keep a fixed number open and make people queue for the next free one.
• The pool is that set of counters, plus the line waiting.

2How it works#

A pool lives inside your application process, or in a separate proxy in front of the database. It holds connections in two states:

• Idle: ready to lend.
• In-use: checked out by a request.
• A request checks one out, runs its query, checks it back in.
• If all connections are busy, the request waits in a queue until one frees up or a timeout fires.

The knobs that come up constantly:

• Max pool size. The hard cap on concurrent open connections. The single most important setting. Too high drowns the database. Too low and requests pile up in the queue.
• Min idle. How many warm connections to keep when traffic is quiet, so the first burst does not pay setup cost.
• Acquire timeout. How long a request waits in the checkout queue before it gives up and throws. Usually a few seconds. This is your pressure-release valve.
• Idle timeout. Close connections that sit unused too long, handing resources back to the database.
• Max lifetime. Force-retire a connection after, say, 30 minutes even if healthy. Protects you from stale connections after a failover or a load balancer timeout that silently killed the socket on one side.

A worked sizing example, with real numbers#

Start with how much one connection can do.

• Say each query takes 5 ms of database time.
• One connection, used back to back, serves 1000 ms / 5 ms = 200 queries per second.
• So a pool of 20 connections gives you 20 x 200 = 4000 queries per second per app instance, if the database can keep up.

Now the trap.

• Suppose Postgres is configured with max_connections = 200, and you run 40 app instances.
• If each instance sets a pool of 30, peak demand is 40 x 30 = 1200 connections against a ceiling of 200.
• The database accepts the first 200 and rejects the other 1,000.
• Five connection attempts out of six fail with "too many clients already," and your service throws a storm of errors that looks exactly like a database outage.
• It is not. It is arithmetic. Reproduce it and the DB barely sweats while your error rate sits at 83 percent.

The sizing rule I actually use:

pool_size_per_instance = (DB_max_connections - reserved) / app_instances

Reserve a slice for migrations, admin tools, and replicas. With max_connections = 200, reserve 20, and 40 instances:

(200 - 20) / 40 = 4.5  ->  round down to 4 per instance

Four feels small, and that surprises people. Here is why small is right.

• The database has a finite number of CPU cores and disks. An 8-core box truly runs about 8 queries in parallel.
• Shoving 200 concurrent connections at it means 200 queries fighting over 8 cores, context-switching and lock-contending.
• Each one runs slower and the queue builds anyway.
• HikariCP's well-known formula is connections = (cores x 2) + effective_spindles, which for an 8-core SSD box lands around 17 to 20 total connections across the whole fleet, not per instance.
• I have seen p99 latency drop by half just by shrinking a pool from 100 to 20, same traffic, same hardware.

So two forces to balance out loud in an interview.

• Setup-cost savings push the pool bigger.
• Database core count and max_connections push it smaller.
• Land where throughput is highest, not where the number looks impressive.

Little's Law, the one-liner that impresses#

connections_needed = arrival_rate x avg_hold_time.

• Take 2,000 requests per second, each holding a connection for 5 ms.
• You need 2000 x 0.005 = 10 connections to keep up on average.
• Add headroom for variance and spikes, call it 15 to 20.
• That lands in the same neighborhood as the HikariCP core-count number above, which is the tell that both are right.
• State this and you have shown you can size from first principles, not by copying a default.

3Trade-offs#

The big architectural decision is where the pool lives.

The decision I make most often, laid out as a head-to-head:

On the serverless side, know the failure you are trading for.

• Lambda spins up hundreds of concurrent executions, each wanting its own connection, and Postgres dies.
• Transaction mode hands you a different server connection per transaction, so SET commands and server-side prepared statements do not stick across statements.
• You have to know that, or you ship a subtle bug that only shows up under load.

4Where it is used#

• PgBouncer is the default answer for Postgres at scale. Figma, Instagram, and basically every large Postgres shop run it. Tiny, single-threaded, absurdly efficient, a few MB of RAM holding thousands of client sockets.
• ProxySQL plays the same role for MySQL and adds query routing and read/write splitting.
• HikariCP is the JVM in-app pool everyone uses, the benchmark others get compared to.
• RDS Proxy and Cloud SQL Auth Proxy are the managed options when you want pooling plus failover handling without running a proxy yourself. The standard fix for Lambda-plus-RDS connection storms.
• Serverless Postgres like Neon and Supabase ship PgBouncer baked in and expose a separate "pooled" connection string, because their whole model assumes thousands of short-lived clients.

When to reach for it:

• The moment you have more than one app instance or any serverless surface in front of a relational database.
• For a single box with one process, your driver's default in-app pool is usually enough.

5Common pitfalls#

• Pool size times instance count exceeds max_connections. The number one incident. Always do the fleet-wide multiplication before you ship. 40 instances times a pool of 30 is 1,200, and if max_connections is 200, you have already lost.
• Holding a connection across a slow external call. Check out a connection, call a payment API that takes 800 ms, then write to the database, and you have pinned a connection for 800 ms of doing nothing. Effective capacity collapses: a pool of 20 that could serve 4,000 queries per second now serves about 25. Do the slow work first, then grab the connection.
• Leaked connections from a missing release. Forget to return a connection in an error path and the pool bleeds dry over hours. Always release in a finally block or use the language's context manager.
• Transaction-mode PgBouncer with prepared statements. Silent correctness bugs. Either use session mode, or configure the driver to disable server-side prepared statements.
• No acquire timeout. Without one, a request waits forever when the pool is exhausted, threads back up, and a slow database turns into a fully hung service. Set it to a few seconds and fail fast.
• Idle-in-transaction connections. An open transaction that never commits holds locks and a connection hostage. Set idle_in_transaction_session_timeout on Postgres as a backstop.

6How it shows up in interviews#

Interviewers use connection pooling as a litmus test for whether you have actually run a database in production. The probes are predictable:

• "Your service autoscaled to 300 instances and the database started refusing connections, why?"
• "How would you pick the pool size?"
• "What breaks when you put PgBouncer in transaction mode?"

The one thing they want to hear: connections are a scarce, server-side resource, not a free client-side object.

Weak answer (junior): "I'd use a connection pool so I don't open a new connection every request. It makes things faster because connections get reused. I'd set the pool size pretty high, maybe 100, so we can handle lots of traffic."

• Recites the definition.
• Treats more connections as strictly better.
• Never mentions the database ceiling.
• Would cause the exact outage the question is fishing for: 100 on each of 40 instances asks Postgres for 4,000 connections against a limit of 200.

Strong answer (senior): "Connections are backend processes on Postgres, capped by max_connections, so the pool is really a concurrency limiter, not just a cache. I size it from the database out, not the app in. If the DB allows 200 connections and I run 40 instances, I reserve about 20 for admin and replicas and land around 4 per instance. I'd sanity-check against Little's Law: at 2,000 requests per second with a 5 ms hold time I only need about 10 connections to keep up, so a small pool is correct and usually lowers p99 because the database stops thrashing its 8 cores. If this were serverless I'd put PgBouncer in transaction mode to multiplex thousands of clients onto a few dozen server connections, and I'd flag that it breaks server-side prepared statements, so I'd turn those off in the driver. I'd also set an acquire timeout of a couple seconds so a saturated pool fails fast instead of hanging every thread."

Size the pool from the database ceiling, not from the number that looks impressive.

See the related Design Interview Question: "A Lambda function connecting to RDS starts throwing 'too many connections' under load. Walk me through the fix."