Database connection pooling: why and how to size it
Connections are expensive, and databases allow only a limited number. A pool reuses a small set. Learn to size it and avoid the traps.
Opening a database connection is expensive: a TCP handshake, TLS, authentication and server-side setup. In PostgreSQL each connection also uses a server process and several MB of memory. Opening one for every request is slow and wasteful.
What a pool does
A connection pool opens a fixed set of connections once and lends them to requests. A request borrows a connection, runs its queries, and returns it. Others reuse it right away.
- Faster requests (no setup cost).
- A cap on connections so the database is not overwhelmed.
- If all connections are busy, requests queue briefly instead of crashing the database.
The hard limit
Databases have a maximum (max_connections). With 20 app servers each opening 50 connections, you ask for 1,000, and the database refuses or slows to a crawl. Add up the pool sizes of all instances and compare with the limit.
How big should a pool be?
Smaller than people think. A database can only run as many queries truly in parallel as it has CPU cores and disk capacity. More connections than that just fight each other and add contention. A common starting heuristic (from the HikariCP documentation) is about (2 x CPU cores) + number of disks on the database server, shared across all app instances. Then measure.
Signs of a pool that is too small: requests waiting for a connection. Too large: the database CPU is saturated and latency rises for everyone.
External poolers: PgBouncer
When many app instances each need a few connections, an external pooler such as PgBouncer sits in front of PostgreSQL and multiplexes thousands of client connections onto a small number of real ones. Modes:
- Session: a client keeps one server connection for its whole session.
- Transaction: the connection is lent only during a transaction. Most efficient, but session features such as temporary tables, advisory locks and some prepared-statement setups need care.
- Statement: per statement; limited.
Serverless and short-lived processes
Serverless functions can start hundreds of instances at once, each opening its own connection, and quickly exhaust the database. Use a pooler or a database proxy, or a driver designed for serverless.
Common bugs
- Connection leak: code borrows a connection and never returns it (an error path skips the release). The pool slowly empties and the app hangs. Always release in
finallyor use the library's helper. - Holding a connection while waiting for a slow external API. Return it first.
- No timeouts: set a timeout for acquiring a connection, for queries, and for idle connections.
- Long transactions that hold a connection for ages.
Monitoring
Watch active, idle and waiting connections, pool wait time, and the database's own connection count.
Takeaway: always pool, size pools small and by the database's capacity, use PgBouncer for many clients, and never leak or hoard connections.
Backend & Database series, Part 2 (performance and operations): 3 of 6. Previous: The N+1 query problem and how to fix it.
