Connection Pooling

Every new connection pays the costs from The Lifecycle of One HTTP Request: a The Three-Way Handshake (1 RTT), a The TLS Handshake (1–2 RTT plus asymmetric crypto), and a congestion window that starts at ~14 kB and must grow (Congestion Control: Protecting the Network). A database connection then adds its own protocol setup on top of the same transport: PostgreSQL’s startup message, SCRAM-SHA-256 authentication (two more round trips), parameter negotiation, and — because PostgreSQL forks a backend process per connection — several milliseconds of fork() and 5–10 MB of memory on the server (Creating Processes: fork, exec, wait). MySQL uses a thread per connection; the cost is smaller but not zero. Then the application’s own setup: SET search_path, SET timezone, prepared statements, an ORM’s type introspection.

Measured on a 1 ms intra-datacentre link, a cold PostgreSQL connection with TLS costs on the order of 5–20 ms end to end; a query on a warm one costs 0.2 ms. Across a region boundary the connection costs hundreds of milliseconds. A handler that opens one per query is two orders of magnitude slower than it needs to be, and a fleet of such handlers exhausts the server’s connection limit (max_connections, default 100) long before it exhausts its CPU.

An HTTP client pool is a set of open connections per origin (host + port + scheme). A request takes an idle one, or opens a new one up to a limit, or waits. Because HTTP requests carry all their context in headers, any request can use any connection to the same origin: the connection has no memory of the previous request. That makes HTTP pools simple — the only per-connection state is whether it is idle, and the only correctness concern is the idle-timeout race from Keep-Alive and Connection Reuse.

What differs is the defaults, and they bite. Node’s built-in http.Agent did not reuse connections until Node 19 made keepAlive: true the default; before that every fetch-style call from a service opened a new socket unless the code passed an agent. undici (the engine behind Node’s fetch) pools by origin with connections per pool. Python’s requests.get() creates and discards a session per call — no reuse at all — while requests.Session() (urllib3’s PoolManager, 10 connections per host by default) reuses. Go’s http.Transport reuses but keeps only MaxIdleConnsPerHost = 2 idle by default, so a burst of 50 concurrent calls opens 50 connections and closes 48 of them — visible as TIME_WAIT growth and wasted handshakes. Java’s HttpClient and Apache HttpClient pool; the JDK’s old HttpURLConnection does, but per-JVM with a small cap.

For HTTP/2 the pool is usually a single connection per origin carrying many streams, and the sizing question becomes the server’s SETTINGS_MAX_CONCURRENT_STREAMS and whether the client opens a second connection when it is reached (HTTP/2: Streams on One Connection).

A database connection is not interchangeable in the same way. It carries a session: an open transaction, SET variables, prepared statements, temporary tables, advisory locks, a cursor position. A pool must therefore hand a connection to exactly one caller at a time for the duration of a unit of work, and reset it on return — roll back any open transaction, DISCARD ALL or the equivalent — so the next borrower does not inherit a half-finished transaction or a search_path someone else set. Application-side pools (HikariCP in Java, pgx’s pool in Go, node-postgres Pool, SQLAlchemy’s QueuePool) do this per process.

A server-side pooler such as PgBouncer sits between many application processes and one database, multiplexing thousands of client connections onto a few dozen real backends. Its pooling mode determines what state it can preserve: session mode ties a backend to a client for the client’s lifetime (safe, little multiplexing); transaction mode assigns a backend only for the duration of a transaction (high multiplexing, but session-level features — SET, prepared statements by name, LISTEN, advisory locks held across transactions — break or need care); statement mode is for autocommit-only workloads. Choosing transaction mode without auditing the application for session state is a classic outage.

The distinction with HTTP is the important part: an HTTP connection is stateless per request and can be reused by anyone; a database connection is a stateful session that must be borrowed exclusively and cleaned. Treat them as different resources with different failure modes even though both are "a TCP socket kept open".

Bigger is not better. A database does useful work on roughly as many connections as it has cores (plus a few waiting on I/O); beyond that, extra connections only add context switches, lock contention and memory. PostgreSQL’s own guidance and HikariCP’s well-known analysis both land near cores × 2 (+ disk spindles) for the *total* across every application instance — a 4-core database serving 20 app pods should not have 20 × 20 = 400 connections. Little’s law gives the demand side: connections needed ≈ request rate × time each holds a connection. 500 queries/s at 4 ms each need about 2 busy connections; if a query is slow at 400 ms, the same load needs 200, which is why one slow query drains a pool.

Pool exhaustion is the failure mode. When every connection is borrowed, the next request waits (a wait queue — Queue) until one is returned or a wait timeout fires: pool timeout, connection pool exhausted, SQLSTATE 53300 too many connections on the database side, ECONNREFUSED-looking stalls on the HTTP side. Causes are always one of: a leak (a handler that takes a connection and never releases it on an exception path), a slow consumer (one query or one downstream call holding connections for seconds), or fan-out (a request that needs three connections at once — nested transactions, parallel queries — so a pool of 10 deadlocks at four concurrent requests each holding two and waiting for a third). The symptom looks like the database is slow while the database is idle: the wait is in the application, in line for a connection.