The Connection Pool

A database connection costs a process on the Postgres side and a TCP and TLS handshake on both sides, so a service does not open one per request. It opens a fixed number once, keeps them in a pool, and checks one out for the milliseconds a request needs it. The pool's size is the service's real concurrency limit for anything that touches the database, and it is the first thing that saturates on the on-sale minute, well before the CPU does. Stagedoor's arithmetic is the one Chapter 1 set up: 8 event loops × 20 connections is 160 against max_connections = 200 on pg-primary, with 40 left for the worker, the migrator and a human with psql.

Marek found the pool sized at 100 per process. Nobody had multiplied. This topic is the multiplication, plus the three properties of a pool that decide whether a slow database turns into a slow service or a dead one: how long a request waits for a connection, how long it keeps one, and what happens to the connections after the database restarts.

Why a Pool

Opening a connection to Postgres takes 20 to 50 milliseconds: a TCP handshake, a TLS handshake, authentication, and on the server a new backend process forked to serve exactly this connection. PostgreSQL Deep Dive describes that process model in its first chapter; the consequence for the application is that a connection is expensive to make and expensive for the server to keep, so the number of them is a budget. A pool opens N connections at startup, hands one to each request that asks, and takes it back when the request is done. The 20 to 50 milliseconds are paid once per connection instead of once per request, and the 180-millisecond seat hold of Chapter 1 spent 2 of them acquiring a connection that was already open.

pool = AsyncConnectionPool(
    cfg.database_url,
    min_size=5,                # opened at start-up, kept warm
    max_size=cfg.pool_size,    # 20 on the api, 5 on the worker
    timeout=5.0,               # seconds a request waits for a free connection
    max_lifetime=1800,         # recycle every 30 minutes
    check=AsyncConnectionPool.check_connection,   # validate on checkout
)
await pool.open(wait=True)     # min_size connections exist before the port is bound

The pool is created once, in the same startup function that Chapter 4 used to parse the configuration, and it is the only object in the process that knows the database's address. A request never opens a connection. It asks the pool, and if one of the 20 is free it gets it in microseconds; if none is free it waits, for at most 5 seconds, and then gets an error instead. Every other line in that block is a section of this topic.

Sizing

The pool is per process. Stagedoor runs four uvicorn processes on each of two hosts, so max_size=20 in the config is 160 connections on the database, and the number that matters is always processes × instances × pool size. The database's limit is shared with everyone: the worker's pool of 5, the migration step at deploy, the monitoring agent, and whoever opens psql during an incident. Against max_connections = 200, 160 for the API leaves 40 for all of that, which is enough. A third API host would not be.

The useful size is smaller than people expect. Postgres executes one query per connection on one core at a time, so 200 connections on a 16-core pg-primary are not 200 queries running in parallel; they are 16 running and 184 waiting for a core, a disk or a lock. Past a few times the core count, adding connections adds contention and no throughput. The pool of 20 per loop exists to bound how much of the database one process can occupy, not to give each request its own line to the server, and the right number is found by measuring acquire wait against database load, not by matching the request rate.

Exhaustion Is a Queue

When all 20 connections are checked out, the twenty-first request does not fail. It waits, inside the pool's acquire call, until one comes back. On an ordinary evening that wait is zero. On the on-sale minute, with 3,000 requests a second arriving and each database transaction taking 90 milliseconds, it is the number that decides the buyer's latency, and it is invisible: the handler's own timer starts after the wait, the database's timer never sees it, and the only place it shows is the pool's acquire time, which nothing reports unless the service reports it. Chapter 13 puts that number on the dashboard, and it is the metric that says "the pool is the bottleneck" before anything else does.

The 5-second acquire timeout is what turns a stuck database into a visible failure. Without it, a pg-primary that stops answering leaves every request on the instance waiting inside the pool forever; the process is alive, the health check that does not touch the database still passes, and the load balancer keeps sending buyers to an instance that will never answer them. With it, the twenty-first request gets a PoolTimeout after 5 seconds, the handler maps it to a 503 with a Retry-After, the buyer sees an error page instead of a spinner, and the load balancer's error rate tells the on-call engineer where to look.

Hold It Briefly

A connection is useful to the database only while a statement is running on it. Stagedoor's first checkout handler took a connection at the top of the request, held it while it called Payrail, and returned it at the end. Payrail answers in 400 milliseconds at the median and takes up to the 3-second timeout under load, and during that time the connection served nothing. Twenty connections, each pinned for 3 seconds, is a process that can complete roughly six database-touching requests a second, while the database sits idle and the pool's queue grows. Chapter 8's worker had the same bug in its own way, holding a connection through a 4-second PDF render.

Check out for the transaction and return at commit. The unit of work in Topic 32 is exactly the scope: acquire, run the statements that must be atomic, commit, release, in milliseconds. A call to Payrail, to Redis, to the email provider or to anything else that leaves the process happens with no connection in hand, before the transaction begins or after it commits. The pool then serves the requests that need the database, and the number of buyers waiting on Payrail has no effect on the number of buyers who can hold a seat.

Health and Recycling

A pooled connection can die while it sits idle. pg-primary restarts for a minor version upgrade; a failover promotes pg-replica-a and the address the pool connected to is now a replica that refuses writes; a firewall drops a TCP session that has been quiet for ten minutes. The pool does not know, because it learns about a connection only by using it. Without a check, the first 20 requests after the restart each receive a dead connection, fail with a connection error, and the pool replaces the connection only after the failure. Twenty buyers see a 500 for a restart that took two seconds.

Two mechanisms cover it, and Marek uses both. A validation check on checkout sends a trivial round trip before handing the connection over, which costs a millisecond per checkout and catches a connection the server already closed. A maximum lifetime retires every connection after 30 minutes regardless of health, so that a connection pointing at the wrong host after a failover is gone within half an hour and the pool re-resolves the address when it opens the replacement. The driver decides which of these it does by default and which it does only when asked, and the honest practice is to read that page of the driver's documentation rather than assume; psycopg's pool recycles after an hour on its own and validates on checkout only when given a check function.

The Pooler in Front

The arithmetic stops fitting when the service grows: a third API host, three workers, a reporting service, and the sum passes 200. Raising max_connections is the wrong reflex, because the database gains nothing from more waiting connections. The right one is a server-side pooler, PgBouncer or pgcat, in front of pg-primary: hundreds of client connections on its outside, tens of server connections on its inside, each server connection lent to a client for the duration of one transaction and taken back at commit. The application's pools now connect to the pooler and can be sized generously, and the database sees a fixed 40.

The price is what the application may no longer assume. In transaction pooling, two consecutive transactions on one client connection can run on two different server connections, so anything set at the session level leaks: a session-level SET of the tenant, the way Chapter 5 first wrote it, is inherited by whichever client gets that server connection next, which is another organizer's report reading the wrong tenant. Session-level advisory locks, LISTEN, temporary tables and session variables have the same problem. Everything the service needs to say to the database must be said inside a transaction, with SET LOCAL or the local form of set_config, and the storage layer is the one place that has to know this. The pooler's own configuration, its modes and its failover are PostgreSQL Deep Dive's subject; this book's part is writing the application so that putting one in front of it changes nothing.