Databases

Connection pools: amortising the cost of a Postgres backend

Crux Every Postgres connection forks a heavyweight OS process; a pool caches a small set of open connections so queries never pay the setup cost again.

Your altitude — climbing toward senior

ZeroJuniorMiddleSenior

You are at junior altitude — the surface

◷ 8 min

Otto writes new Client(); client.connect(); await client.query(...); client.end() on every request. P99 is 80 ms — mostly the connect. Sven switches to a pool. P99 drops to 4 ms, same database, same queries.

What opening a Postgres connection actually costs

Opening a Postgres connection is not cheap: TCP handshake, TLS handshake, Postgres protocol startup, authentication, and forking a backend OS process. Together: 5–50 ms per new connection and ~10 MB of memory per backend that Postgres must allocate.

Doing this for every web request makes connection setup the dominant cost — before a single byte of SQL runs.

A pool amortises the cost: open the connections once at startup, reuse them for thousands of queries. The pool’s overhead per request is a sub-millisecond list lookup, not a full OS round-trip.

The taxi-rank metaphor

A connection pool is a taxi rank, not a ride-share app. The rank has a few cabs idling at the kerb, ready to go. You walk up, take a cab, ride, return it. Total time per trip: the ride itself.

Calling a new ride-share per block means waiting for dispatch, waiting for arrival, then the ride. Most of the time is setup. A pool eliminates the dispatch — a few connections sit open, ready to be borrowed for the next query.

Without pool	With pool
New TCP + TLS + auth per request: 5–50 ms overhead	Borrow idle connection: under 1 ms overhead
Each backend: ~10 MB Postgres memory	Pool holds 5–20 connections; memory fixed
40 workers × 20 connections = 800 — exceeds max_connections=100	PgBouncer multiplexes 800 clients onto 50 backends

Two layers: client-side and server-side

Modern stacks use two pool layers:

Client-side pool — lives inside each application process (node-postgres Pool, HikariCP for Java, asyncpg for Python). Caches 5–20 TCP connections per worker. Eliminates per-request connect cost.
Server-side pooler (PgBouncer, Supavisor, Odyssey) — a separate process in front of Postgres. Accepts thousands of client connections from all workers and routes them onto a small set of real Postgres backends. Eliminates the max_connections cap problem.

The combination: each worker has ~10 cheap client-pool connections to PgBouncer; PgBouncer maintains 20–50 real backends. 10,000 application clients become sustainable on a Postgres with max_connections = 100.

Why this works

Why does Postgres use one OS process per connection instead of threads? History: Postgres predates good threading on most Unix systems. The process model isolates crashes (one backend OOM does not corrupt others) and simplifies memory management — each backend has a private heap. The downside is the irreducible per-connection overhead that pools are designed to absorb.

The scale failure without a server-side pooler

A team runs 4 workers, client pool max: 20, Postgres max_connections = 100. They scale to 40 workers. Total connections: 40 × 20 = 800. Postgres hard-caps at 100. Result: FATAL: sorry, too many clients already on 700 attempts. The app falls over.

Fix: insert PgBouncer. Workers connect to PgBouncer (8000 client connections are cheap — ~2 KB each). PgBouncer maintains 50 real backends. Total Postgres backends: 50. Problem gone.

Quiz

Why is opening a new Postgres connection per HTTP request a bad idea?

Quiz

An app worker has a client pool of max 20. You scale to 40 workers and Postgres has max_connections=100. What happens?

Order the steps

Order the steps that happen when an application borrows a connection from a pool:

1 Application code calls pool.query(sql, params)
2 Pool looks for an idle connection in its cache
3 If found: borrow it; if not and under max: open a new one; if at max: wait or reject
4 Pool sends the query to Postgres over the borrowed connection
5 Postgres executes the query and returns rows
6 Pool returns the connection to the idle list — does NOT close it
7 Application code receives the rows and continues

Complete the analogy

Fill in the blank: a connection pool is to Postgres connections what a thread pool is to OS ___ — a small cache of expensive, reusable resources that amortises setup cost across many short tasks.

Recall before you leave

01
In two sentences: what is a connection pool and why is one always needed in production Postgres?
02
What are the two pool layers and what does each one solve?
03
Why does Postgres use one OS process per connection, and what is the practical consequence for pooling?

Recap

Postgres creates one OS process per connection — each backend consumes ~10 MB and a slot in max_connections (default 100). Opening a connection costs 5–50 ms of TCP + TLS + auth overhead. A connection pool fixes both problems: a client-side pool caches connections per worker so queries never pay the setup cost; a server-side pooler like PgBouncer multiplexes thousands of client connections onto a few dozen real backends, keeping Postgres backend count well under max_connections. Without both layers, scaling from 4 to 40 workers with a pool of 20 per worker generates 800 connection attempts against a 100-slot limit — guaranteed failures.

Connected lessons

builds on

MVCC: why readers and writers never wait for each otherjunior

unlocks

deepens into

appears again in258

Continue the climb ↑PgBouncer session, transaction, and statement modes