Database Connection Pooling

Connection pooling keeps a set of open database connections that application code borrows for each query or transaction and returns afterwards, instead of opening a new connection every time. Opening a connection is slow (network handshake, TLS, authentication, server-side process setup), and databases can only handle a limited number of connections efficiently. Pools make database access fast and protect the database from being overwhelmed.

Why connections are expensive

• Setup latency: TCP and TLS handshakes plus authentication can take several milliseconds, often more than the query itself.
• Server resources: many databases dedicate a process or thread and memory to each connection. PostgreSQL, for example, uses a process per connection, so thousands of idle connections waste memory and slow the server.
• Contention: past a point, more concurrent connections make total throughput worse, because they compete for CPU, locks and disk.

How a pool works

• Minimum and maximum size: keep a few connections warm; cap the total.
• Acquire timeout: if no connection is free within, say, 1-2 seconds, fail fast instead of piling up requests.
• Health checks and max lifetime: validate or recycle connections periodically, so failovers, network changes and server restarts don't leave dead connections in the pool.
• Idle timeout: close connections that sit unused too long.

Sizing the pool

Bigger isn't better. A database with a handful of CPU cores and fast disks often reaches peak throughput with a few dozen active connections in total; beyond that, queries just wait on each other inside the database. A common starting point is a total across all app instances of roughly a small multiple of the database's CPU cores, tuned by load testing. See capacity planning.

Multiply per-instance pool size by the number of instances. Fifty app servers with a pool of 20 each is 1,000 connections, which can exhaust the database even though each pool looks small. Autoscaling app servers can push it over the limit during a traffic spike.

External poolers

When many app instances (or many short-lived processes) connect to one database, a proxy pooler such as PgBouncer for PostgreSQL or a cloud database proxy sits in between. Applications open many cheap connections to the proxy, which multiplexes them onto a small number of real database connections.

Serverless and pool exhaustion

• Serverless functions can scale to thousands of concurrent instances, each with its own connections, and overwhelm a database instantly. Use a proxy pooler, a database built for many connections, or an HTTP-based data API, and cap function concurrency. See serverless.
• Pool exhaustion usually comes from leaked connections (not returned on an error path), long transactions holding connections, or slow queries during an incident. Monitor active, idle and waiting counts and wait time, and always return connections in a finally block or with a managed helper.
• Don't hold a connection during slow work such as calling another service; acquire it just for the database work.