Connection Pooling
Concept
Opening a database connection is expensive. TCP handshake, TLS negotiation, authentication, and — for something like Postgres — forking a dedicated backend OS process (5+ MB of RAM, its own memory for query planning and buffers) all happen before a single query runs. Doing that from scratch on every request is wasteful, and it doesn't scale: a database has a hard ceiling on concurrent connections (Postgres's max_connections defaults to 100), while a modern application tier can easily have hundreds or thousands of concurrent requests in flight across many instances, each wanting its own connection.
Connection pooling breaks that 1:1 coupling. A pool holds a set of already-established, already-authenticated connections; a request borrows one, uses it, and returns it — rather than opening and tearing one down per request. This can happen at two layers:
- Client-side pooling — a pool embedded in the application (e.g., a JDBC connection pool, SQLAlchemy's pool, Node's
pg-pool), local to each application instance. - Proxy-based pooling (e.g., PgBouncer for Postgres, ProxySQL for MySQL) — a lightweight process that sits between the application fleet and the database, multiplexing many client connections down to a much smaller number of real database connections. This matters most when the application fleet is large: a hundred instances each holding a client-side pool of 20 connections is 2,000 real database connections regardless of how idle most of them are, while a proxy can multiplex that same load onto a few dozen.
PgBouncer offers three pooling modes with different tradeoffs: session pooling (a client keeps its assigned server connection for its entire session — safest, least efficient), transaction pooling (the server connection is returned to the pool after each transaction — much more efficient, but breaks session-scoped features like SET variables and prepared statements that aren't reset per transaction), and statement pooling (returned after every statement — most efficient, most restrictive, no explicit transactions).
Tradeoffs
| Aspect | Small pool | Large pool |
|---|---|---|
| Database load | Low — few real connections, low memory per instance on the DB | High — every connection reserves DB-side memory even when idle |
| Application throughput under load | Can bottleneck — requests queue for a free connection | Higher headroom for concurrent queries |
| Failure blast radius | Contained | A connection-exhaustion event can starve the whole DB for every service sharing it |
| Layer | Where multiplexing happens | Best for |
|---|---|---|
| Client-side pool only | Per instance | Small fleets, direct DB access |
| Proxy (PgBouncer/ProxySQL) | Fleet-wide, in front of the DB | Large or auto-scaling fleets, serverless/Lambda-style bursty connection patterns |
Sizing a pool isn't "bigger is always safer" — a pool sized well past what the database's CPU/IO can actually serve concurrently just moves the queue from the application (waiting for a free pool connection) to the database (thrashing between too many active queries), often making latency worse. The right size is close to what the database can genuinely execute in parallel without contention, not the peak number of requests you expect.
When to use / when not to
- Use connection pooling for any application talking to a relational database in production — this isn't an optimization to defer, it's close to a correctness requirement once you have more than a handful of concurrent requests.
- Use a proxy-based pooler (PgBouncer, ProxySQL, RDS Proxy) when you have a large or elastically-scaling fleet, serverless functions that each open a fresh connection per invocation, or multiple services sharing one database — anywhere the number of application-side pools would otherwise multiply past what the database can hold.
- Stick to transaction pooling mode for most workloads for the efficiency win, but audit for session-scoped features (advisory locks,
SETsession variables,LISTEN/NOTIFY, prepared statement caching) that need session pooling instead. - Don't oversize a pool "to be safe" — cap it near what the database can actually execute concurrently and let requests queue briefly rather than let the database thrash under too many simultaneous active queries.
- Don't rely on default pool sizes from an ORM's out-of-the-box config in production — they're rarely tuned to your actual instance's connection ceiling.
Common pitfall
Connection exhaustion from a fleet that scales out without the database's connection ceiling scaling with it. An autoscaling group that goes from 5 to 50 instances, each opening a client-side pool of 20 connections, jumps from 100 to 1,000 database connections in the time it takes the scale-out to happen — blowing straight past max_connections and causing every instance, old and new, to start failing to connect. This is exactly the failure mode proxy-based pooling exists to prevent: put the multiplexer in front of the database so the database-facing connection count stays roughly fixed regardless of how the application fleet scales. The second pitfall is leaked connections — a code path that borrows a connection and doesn't return it on an error path — which slowly exhausts the pool over hours until every request starts timing out waiting for one.
Engineering Lens
Connection pooling is a small mechanism that maps onto a much bigger architectural theme: stateful resources don't scale the same way stateless compute does. Application instances can be added freely because they're (ideally) stateless, but a database has a hard, physical ceiling on concurrent connections that doesn't move just because the app tier autoscaled. Recognizing that mismatch — and designing a multiplexing layer to absorb it — is the same reasoning that shows up in rate limiting, bulkheading, and capacity planning generally: identify the resource that doesn't scale elastically, and protect it deliberately rather than assuming everything scales together. In a review, being able to say "our app tier autoscales to N instances, so here's how we keep the database-facing connection count bounded" is a concrete, checkable signal that the design accounted for a real production failure mode rather than only load-tested the happy path.