A database can run out of something before it runs out of CPU. Every query needs a connection while it runs, and every database has only so many to give.

Every query holds a connection

To talk to a database, an app server opens a connection: the database's slot for one conversation. While a query runs, its connection is busy; when the answer comes back, it's free for the next query.

So how many connections are busy at once depends on two numbers: how many queries arrive each second, and how long each one takes.

Queries per second times seconds per query: this is Little's Law, the same arithmetic as people in a shop, arrivals times how long each stays.

The limit

Each database node accepts a fixed number of connections, its max_connections. The cap is there because every open connection costs the database memory, and in PostgreSQL a whole process of its own, busy or idle. A connection opened past the cap is refused straight away (PostgreSQL says "sorry, too many clients already"). In practice, app servers keep a pool of open connections and lend one to each query: a query that finds its pool empty waits for one, and fails only if it waits too long, while the cap limits how many connections all the pools together may hold open. Retrystorm simplifies: a connection is held only while its query runs, as if each query opened its own, and the experiments below use no pool, so a query that finds every connection busy is refused.

That makes connections a limit of their own. A database whose queries mostly wait on disk can refuse requests while half its CPU sits idle. The dashboard that only shows CPU says everything is fine.

Slow queries use up connections

Make every query twice as slow and the same traffic needs twice the connections. Nothing about the load has changed, and the database starts turning requests away.

This is why a slowdown anywhere in the database, a busy disk or a lock, so often turns into errors: the slow queries hold their connections longer, and new queries find none left.

More connections

Every node has its own connections, so read replicas add more. But with reads sent to the replicas, the primary keeps only the writes. One replica takes every read on its own, with the same 200 connections the primary had.

Paying for connections

Connections come with the node, so buying them means buying nodes: one bigger node, or several smaller ones. Several small replicas can give the same connections for less: a smaller machine has fewer cores and connections, not slower queries. Leave some room, though: the busier a node is, the slower it answers, and the longer each query holds its connection.

Often the cheapest fix of all is a faster query. An index is a sorted lookup structure the database keeps beside a table, so it can find matching rows without scanning them all; one that turns a 60 ms query into a 10 ms one needs a sixth of the connections. The simulator can't change your queries, but the arithmetic is the same.

Connection pools

Opening a connection takes time, so app servers keep a pool of open connections and reuse them. Each app replica is its own process with its own pool, and the database counts every open connection against its limit, even an idle one. Two rules follow.

First, app replicas times pool size must stay at or under max_connections. Adding app replicas can push a database over its limit without a single extra query, so size pools from the database's limit down, not from the app up.

Second, the pools together must fit the concurrency your queries need, arrivals times query time by Little's Law. If they don't, queries wait in the app for a pooled connection, and are refused once they've waited too long. When many app processes each need a few connections, a pooler such as PgBouncer sits in front of the database and shares a few real connections among them.

In the wild

The ideas in this chapter, and where you'll meet them in real systems and interviews:

Your practice

Inkwell Blogs pages now show related posts, and the query behind them takes about 60 ms. One page in four fails with "too many connections" while the database's CPU sits at half. The app tier is fixed. Choose the database's tier and replicas so the reads have enough connections, for as little as you can.