Reusing database connections with a pool

12 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

Opening a database connection is expensive (network round trips, TLS, authentication, and server-side memory), so applications keep a pool of open connections and lend them out per request or transaction.
The pool size is a capacity limit for the database, not a performance knob to turn up. Small pools usually beat large ones.
Pools exist at two layers: in-process (HikariCP, database/sql, SQLAlchemy) and external proxies (PgBouncer, RDS Proxy, ProxySQL) that multiplex many clients onto few server connections.
Most incidents come from multiplication (pods times pool size), leaks, and missing timeouts.

How it works

What a connection costs

A new PostgreSQL connection needs a TCP handshake, often a TLS handshake, authentication (SCRAM is several round trips), and a fork of a backend process. That is 5 to 30 ms before the first query, and real memory on the server. Be careful how you measure it: top counts shared buffers and copy-on-write pages in every backend, so it overstates the cost. Measured properly, one connection's private overhead was about 7.6 MiB with huge pages off and 1.3 MiB with them on, and it grows as the backend fills caches and sorts (Freund). MySQL uses threads instead of processes, which is cheaper but still bounded. Doing this per request turns a 2 ms query into a 20 ms one and caps throughput on connection setup.

The pool lifecycle

A pool holds idle connections. acquire hands out an idle one, opens a new one if under max_size, or waits up to acquire_timeout and fails. release returns the connection, usually after resetting session state. Background housekeeping closes connections idle longer than idle_timeout (while keeping min_idle), retires connections older than max_lifetime so failovers and DNS changes are picked up, and validates connections that may have been cut by a firewall or load balancer.

Sizing with Little's law

Connections in use equal arrival rate times hold time. At 3,000 queries per second with 4 ms average hold, you need about 12 connections busy on average; 20 gives headroom for bursts. Beyond that, extra connections do not add throughput because the database has a fixed number of cores and disks. A 16-core database with 500 active connections spends its time context switching and fighting over locks. A common starting point is a small multiple of database cores across all clients combined. HikariCP's guide suggests (cores x 2) + effective spindles and cites an Oracle test where cutting the pool from 2,048 to 96 connections took response times from about 100 ms to 2 ms (HikariCP wiki). Treat the formula as a first guess and tune it against measured waits.

External poolers and modes

In-process pools cannot share across processes. With 300 pods each holding 20 connections, the database sees 6,000. An external pooler such as PgBouncer accepts thousands of client connections and maps them onto a small server pool. Its modes decide when a server connection is returned (PgBouncer features). The default is session mode, so you get little multiplexing until you change it:

Session mode: held for the client's whole session. Safe, but little multiplexing.
Transaction mode: held only for one transaction. Very high multiplexing, but session features (session-level prepared statements, advisory locks, SET, temporary tables, LISTEN) break or leak across clients.
Statement mode: returned after every statement. Multi-statement transactions are impossible.

Down the stack

Layer 1The contract

A pool promises: acquire returns a usable connection, held exclusively by the caller, within acquire_timeout, or fails with an error; on release the connection's session state is reset so the next borrower does not inherit it; and the pool never holds more than max_size server connections. An external pooler in transaction mode narrows the promise further: you own a server connection only between BEGIN and COMMIT, so anything that lives in the session is outside the contract.

Layer 2The mechanism

PostgreSQL's postmaster forks one backend process per connection. The client sends a StartupMessage, completes authentication (SCRAM-SHA-256 is a SASL exchange of two round trips), receives ParameterStatus and BackendKeyData messages, and waits for ReadyForQuery. That last message carries a transaction status byte: I idle, T in a transaction, E in a failed transaction. PgBouncer reads this byte to know when a transaction ended, and in transaction mode hands the server connection back to its pool at that moment; in session mode it runs server_reset_query (by default DISCARD ALL) on release. Its other defaults are small on purpose: default_pool_size 20 server connections per database and user pair, and max_client_conn 100 (PgBouncer config). Protocol-level prepared statements work in transaction mode only since PgBouncer 1.21 (October 2023), which tracks them per client; 1.24 (January 2025) turned this on by default with max_prepared_statements = 200 (PgBouncer changelog). In-process pools differ in defaults that bite: HikariCP's connectionTimeout is 30 seconds, maxLifetime 30 minutes and maximumPoolSize 10 (HikariCP), while Go's database/sql keeps only 2 idle connections by default (Go docs), so bursty services churn connections unless it is raised.

Layer 3The machine

A fresh connection costs a TCP handshake (1 round trip), a TLS 1.3 handshake (1 more), and SCRAM's 2 round trips, during which the server computes 4,096 iterations of PBKDF2 with SHA-256 (the scram_iterations default, tunable since Postgres 16) (Postgres docs). Then fork() creates a process with copy-on-write pages; as the backend fills catalog caches and allocates work_mem it grows to several MB of private memory. Each connection is also a file descriptor plus kernel socket buffers. With thousands of backends, the kernel spends real time context switching (a few microseconds each), and the database pays for bookkeeping proportional to connection count, such as building snapshots over the process array. Before Postgres 14, every snapshot scanned all backends, so 10,000 idle connections halved the throughput of one active one; the Postgres 14 rewrite of GetSnapshotData() roughly doubled it in that test (16,034 to 33,140 TPS), but idle connections are still not free (Citus). Idle connections also die silently: an AWS NAT gateway, for example, drops a flow idle for 350 seconds and answers the next packet with a RST (AWS docs), while Linux's default tcp_keepalive_time is 7,200 seconds, so the pool's own keepalive or max_lifetime must be shorter than the middlebox timeout.

Why it's built this way

Connection setup costs several round trips, a key derivation, and a process fork, so connections are reused rather than created per request.
Every connection costs server memory and scheduling work, so the total must be capped.
Each application process caps only its own pool, so large fleets add a shared proxy that caps the total.
A proxy can only reassign a server connection at a safe boundary, so it watches transaction status and gives up session features.
Long-lived TCP connections get cut by middleboxes and outlive failovers, so pools retire and validate them on a schedule.

Diagram

sequenceDiagram
    participant H as Request handler
    participant P as Pool
    participant DB as Database
    H->>P: acquire(timeout 500 ms)
    alt idle connection available
        P-->>H: reuse connection
    else below max_size
        P->>DB: connect, TLS, auth
        DB-->>P: new connection
        P-->>H: new connection
    else pool exhausted
        P-->>H: wait, then timeout error
    end
    H->>DB: BEGIN, queries, COMMIT
    H->>P: release (reset state)

Worked example

A checkout service runs 120 pods, each with a pool of 25. That is 3,000 potential connections against a Postgres primary with max_connections = 1,000 and 32 cores. During a deploy, new pods warm their pools before old ones drain, briefly doubling demand. Connections are refused and checkout fails.

The fix has three parts. First, measure: peak load is 8,000 queries per second at 3 ms hold time, so only about 24 connections are busy across the fleet. Second, put PgBouncer in transaction mode in front of the database with a server pool of 64. Third, shrink each pod pool to 10 with acquire_timeout of 500 ms so overload fails fast instead of queueing forever. Clients now hold 1,200 cheap connections to PgBouncer, the database sees at most 64, and P99 drops because the database stops thrashing. The team switches the driver to protocol-level prepared statements, which PgBouncer 1.21 and later track in transaction mode.

In the wild

GitHub, 2022: the proxy runs out of connections four times in a week. On 16, 17, 22 and 23 March, peak-hour load on the shared mysql1 primary plus one badly performing query pushed GitHub's database proxy to its maximum connection count. Writes failed across git operations, webhooks, pull requests and Actions, and each recovery was a primary failover. Fixes were an index for the query and webhook throttling at peak (GitHub).
GitHub, 2023: inconsistent pool settings on the proxy layer. On 4 May, degraded git, API, Actions and Copilot traffic traced back to connection pool exhaustion in the database proxy, because earlier pool configuration changes had been rolled out unevenly. GitHub audited the pool settings and changed its automation to apply them dynamically and consistently (GitHub).
Val Town, 2024: deadlocked queries keep their slots. A burst of parallel edits deadlocked several queries. The pool had no query timeout, so those connections never came back, one host's pool drained, and requests routed there stalled for almost seven hours because health checks did not test the database. The fixes were query timeouts and pool health in health checks, with a review planned so that servers at capacity reject requests instead of queueing them (Val Town).

Trade-offs

Option	Strengths	Weaknesses	Choose when
No pool	Simplest	Setup cost per request, connection storms	Batch jobs, very low traffic
In-process pool	No extra hop, full session features	Multiplies with instance count	Few app instances
Proxy, session mode	Centralised limits, drop-in	Little multiplexing	Many clients need session state
Proxy, transaction mode	Thousands of clients on few connections	Breaks session-level features	Large fleets, serverless functions
Managed proxy (RDS Proxy)	Handles failover, IAM auth	Cost, added latency, pinning rules	Managed databases with Lambda clients

Pitfalls in production

Pool multiplication. Autoscaling adds pods and silently adds connections until max_connections is hit. Budget connections per service and use a proxy for large fleets.
Leaks. A code path forgets to release on an error, and the pool drains over hours. Use scoped acquisition (with, defer, try-with-resources) and enable leak detection that logs the acquiring stack.
No acquire timeout. Requests queue indefinitely for a connection, hiding overload and exhausting threads. Set a short timeout and shed load.
Stale connections after failover. The pool keeps sockets to the old primary. Set max_lifetime with jitter and validate on borrow after errors.
Session state bleeding through transaction pooling. A SET search_path from one client affects another. Avoid session state or use session mode for those clients.
Long transactions holding connections. A slow external call inside a transaction pins a connection. Never do network calls while holding a transaction open, and set statement and lock timeouts so a deadlocked query cannot hold its slot forever.
Locked out at the limit. When every slot is taken, operators cannot connect to kill the offending queries. Postgres keeps 3 slots for superusers by default, and Postgres 16 added reserved_connections for a pg_use_reserved_connections role; use them for admin and monitoring access.

Leadership lens

Treat database connections as a shared, budgeted resource. Each service gets an allocation, reviewed like any quota.
Ask for pool metrics on every service: active, idle, waiting, acquire latency, and timeouts. Waiting greater than zero is an early overload signal.
Decide on a proxy before the fleet grows, since retrofitting transaction mode surfaces hidden session-state dependencies.
Make deploy surges part of capacity planning: during a rolling deploy, old and new pods both hold pools.

Check yourself

Why can raising pool size from 20 to 200 per instance make latency worse?
Show answer
The database has fixed cores and lock capacity, so more concurrent queries add contention and context switching, and every instance multiplies the total.
What breaks when moving from session to transaction pooling?
Show answer
Anything that assumes the same server connection across transactions: session-level SET, temporary tables, advisory locks, LISTEN, and session-level prepared statements.
Why set max_lifetime even if connections look healthy?
Show answer
It forces periodic reconnection, so pools pick up failovers, DNS changes, and credential rotation, and it avoids long-lived connections being cut silently by middleboxes.

Sources and further study

Primary

PostgreSQL: Message Flow: the startup, authentication and ReadyForQuery exchange that every pooler parses.
Andres Freund, Measuring the memory overhead of a Postgres connection: how to measure per-backend memory without being fooled by RSS.
Andres Freund, Improving Postgres connection scalability: snapshots: why idle connections cost throughput and what Postgres 14 changed.

Systems docs

PgBouncer changelog: when prepared statements in transaction mode arrived and when they became the default.
HikariCP: About Pool Sizing: the case for small pools, with the core-count formula.
Go: Managing connections: SetMaxOpenConns, SetMaxIdleConns and SetConnMaxLifetime in practice.

Books

Release It! (2nd edition), Michael T. Nygard, chapter 2 "Case Study: The Exception That Grounded an Airline" and chapter 4 "Stability Antipatterns" (Blocked Threads): a leaked pool connection taking down a whole system.
Site Reliability Engineering, Beyer et al., chapter 22 "Addressing Cascading Failures": how resource exhaustion spreads and why queues need limits.

Other curricula

Azure Architecture Center: Improper Instantiation antipattern: frames pooling as "create once, share", and warns that database connections are scarce and should not be held idle.

Watch

Connection pooling, routing, and queuing with PgBouncer (Postgres Conference): what PgBouncer does beyond basic pooling.
Webinar: Postgres Connection Pooling (EDB): PgBouncer and Pgpool compared, with a demo.

Free resources

PgBouncer: Features: pooling modes and exactly which SQL features each mode supports.
PgBouncer: Configuration: every pool size and timeout setting, with defaults.
PostgreSQL Wiki: Number Of Database Connections: why fewer active connections usually means more throughput.
Amazon RDS Proxy: a managed pooler, including connection pinning rules.

Open in the interactive app: practice, quiz, mock interview →