Connection pool

What is a connection pool?

A connection pool is a set of open connections to a database that your application keeps ready and reuses. Instead of reconnecting for every request, your code borrows a connection, runs a query or transaction, and hands it back for the next one. The pool also caps how many connections the app holds at once, so a spike cannot open unbounded sessions. It pays off most for OLTP workloads of many short queries, the same way an API client reuses a kept-alive connection.

Why opening a connection is expensive

A fresh connection is not cheap. The client opens a TCP socket, does a network handshake, sends credentials, and waits for the server to authenticate and set up session state. On PostgreSQL the server also forks a dedicated backend process per connection, which costs memory before a single query runs. That is a few milliseconds once, but thousands of times a second it swamps the real work, so the pool pays it once and reuses it.

Why a bigger pool is often slower

The counterintuitive part: a bigger pool usually hurts past a fairly low number. A server can only run as many queries at once as it has CPU cores and disks; past that, extra connections add context switching, lock contention, and memory pressure instead of throughput.

HikariCP, a widely used Java pool, cites a PostgreSQL-derived starting point: connections = (core_count * 2) + effective_spindle_count. A four-core server with one disk lands near nine connections, not the hundreds people often set. So size the pool against throughput time, and read a queue on it as a bottleneck analysis problem, not a reason to add connections.

When the pool runs dry

Pool exhaustion catches teams out because it shows up as a hang, not a clean error. When every connection is checked out, the next request does not fail; it waits for one to free up and only errors when the connection timeout expires. In ADO.NET, the default maximum pool size is 100 and the timeout is 15 seconds, so a bug that leaks connections leaves requests frozen for 15 seconds at a time while the database looks idle. So track how long requests wait for a connection, not just database CPU, so data observability covers the pool.

PgBouncer pooling modes

A dedicated pooler like PgBouncer sits in front of PostgreSQL and multiplexes many clients onto far fewer server connections. It has three modes that trade reuse against compatibility.

• Session pooling. A server connection stays with one client for its whole session. Every PostgreSQL feature works, but reuse is low.

• Transaction pooling. The connection returns to the pool after each transaction, so many clients share it. Highest reuse and the common choice, but it breaks anything that outlives one transaction.

• Statement pooling. Like transaction pooling, but multi-statement transactions are disallowed.

Transaction pooling has a real catch. Because the next transaction can land on a different connection, session-scoped features stop working: prepared statements, temporary tables, SET and RESET, LISTEN, and session-level advisory locks. This is about session state, not ACID transactions, which still hold on whatever connection serves each one.