Connection pooling is the technique of keeping a set of open database connections and reusing them across many requests instead of opening and closing a new connection for each one. In database performance, a connection pool removes the cost of connection setup and, just as importantly, limits how many connections reach the database at the same time, which protects the server from overload.
A pool can live inside the application, as a library such as HikariCP for Java or the pool built into many database drivers, or as a separate proxy such as PgBouncer or ProxySQL that many application instances share. Both forms hand a connection to a client for the duration of its work and take it back afterward. The set of open connections itself is called a connection pool.
Why opening database connections is expensive
A new database connection requires several network round trips and server-side work before the first query can run. The client opens a TCP connection, usually negotiates TLS, authenticates, and the server allocates per-session memory; PostgreSQL even starts a new operating system process for every connection.
Depending on network distance, TLS and authentication method, setup typically costs from about one millisecond on a local network to tens of milliseconds across regions or with strong password hashing such as SCRAM. For a query that runs in two milliseconds, that overhead can dominate the response time.
Idle connections are not free either. Each one holds memory on the server, often several megabytes per PostgreSQL backend, and thousands of connections add contention in lock tables, snapshots and the scheduler. Throughput usually peaks at a modest number of active connections, roughly a small multiple of CPU cores, and falls beyond it.
How a connection pool works
A pool maintains a bounded set of open connections. When application code requests a connection, the pool lends an idle one, or opens a new one if the maximum has not been reached, or makes the caller wait in a queue until a connection is returned.
| Parameter | Typical range | What it controls |
|---|---|---|
| Maximum pool size | 5-50 per application instance | Upper bound on concurrent database sessions |
| Minimum idle connections | 0 to the maximum size | Warm connections kept ready for bursts |
| Acquisition timeout | 1-30 seconds | How long a caller waits before failing |
| Idle timeout | 1-10 minutes | When unused connections are closed |
| Maximum lifetime | 15-60 minutes | Forced recycling before network devices drop connections |
| Validation query or check | On borrow or periodically | Detects broken connections before use |
Before a connection returns to the pool, the pool or the proxy resets session state: it rolls back any open transaction and, depending on configuration, discards temporary tables, session variables and prepared statements. Without a reset, one request can leak settings into the next.
Pooling modes in external poolers
External poolers such as PgBouncer can share one server connection among many clients at different granularities. The mode decides how many clients one server connection can serve and which database features still work.
| Mode | Server connection released | Trade-off |
|---|---|---|
| Session pooling | When the client disconnects | Full feature support, least multiplexing |
| Transaction pooling | At the end of each transaction | High multiplexing; session state must not span transactions |
| Statement pooling | After each statement | Maximum sharing; multi-statement transactions not allowed |
Transaction pooling is the usual choice for web workloads, because it lets thousands of client connections share a few dozen server connections. It breaks features that rely on session state, such as session-level advisory locks, SET commands outside a transaction, LISTEN and, in older versions, server-side prepared statements; PgBouncer 1.21 and later can track protocol-level prepared statements in this mode.
Application pools compared with proxy poolers
Application-side pools and proxy poolers solve overlapping but different problems, and large systems often use both. The comparison below shows where each one fits.
| Location | Strength | Limitation |
|---|---|---|
| In-application pool | No extra hop; lowest latency | Total connections grow with every instance |
| Proxy pooler near the database | Global cap across all clients | Extra component to run and monitor |
| Managed cloud proxy | Handles failover and many short-lived clients | Provider-specific behavior and cost |
| Serverless driver over HTTP | Suits functions without persistent sockets | Different protocol and feature set |
The proxy becomes essential when the number of application instances is large or elastic. Fifty containers with a pool of 20 each could open 1,000 connections; a shared pooler keeps the database at a fixed, safe number regardless of how far the application scales out.
PHP is a special case: each request runs in a separate worker process with no shared memory between workers, so persistent connections only keep one idle connection per worker, without queueing or a global limit. True pooling for PHP applications therefore usually requires an external pooler such as PgBouncer or ProxySQL.
Sizing mistakes and failure symptoms
The most common mistake is a pool that is too large, not too small. Extra connections do not add CPU or disk capacity; they only make more queries compete for the same resources, which raises latency for all of them.
- Pool exhaustion: callers time out waiting for a connection because code leaks connections or holds them during slow external calls.
- Connection storms: all instances reconnect at once after a failover; stagger reconnects and cap the pool.
- Stale connections: firewalls or load balancers silently drop idle sockets; set a maximum lifetime below their timeout.
- Multiplied limits: the sum of all pools must stay below the server’s max_connections with headroom for administration.
- Long transactions: a connection held for a whole user request reduces the effective pool size.
A practical starting point is a total pool of two to four connections per CPU core of the database server, adjusted by measuring throughput and queue wait time under realistic load.
In simple terms
Opening a connection to a database is like dialing a phone call before you can talk. It takes time, and the database can only handle so many calls at once.
Connection pooling keeps a few lines open all the time. When the application needs to ask something, it grabs an open line, asks, and hands the line back. Nobody waits for a new call to connect. The database also never drowns in too many calls.
A practical example
An online booking platform runs its API on up to 120 containers that scale with traffic. Each container had its own pool of 15 connections to a PostgreSQL primary with 16 CPU cores. At peak, the containers tried to open 1,800 connections, the server hit max_connections, and new requests failed while existing ones slowed because of memory pressure and lock contention.
The team placed PgBouncer in transaction pooling mode between the API and the database, reduced each container pool to 5, and limited PgBouncer to 60 server connections. Client connections now multiplex onto that fixed set. Peak CPU on the database dropped, errors from refused connections disappeared, and median query latency improved by roughly a third, because far fewer queries competed at the same moment.