Pool sizing, pgBouncer, why 500 app instances kill one Postgres — and the serverless version of the same failure, where every invocation brings its own connection.
Module P-4 — Connection Pooling and the Database Bottleneck
What this module covers: A Postgres connection is a forked operating-system process, which is why the default limit is 100 rather than 100,000 — and that one fact is where two Foundation promises come due. Inside: why small pools are faster under load, derived from Little's Law; why
N instances × pool sizeis the only connection number that matters; the serverless case where 1,000 concurrent invocations meet a 100-connection ceiling and break a service that had nothing to do with the traffic; the three fixes and what each costs; session vs transaction vs statement pooling; and pool-wait time, the metric holding most of your p99 that appears on nobody's dashboard.
Two Foundation Bills, Payable in One Integer
Module F-9 claimed that scaling one tier relocates the bottleneck rather than removing it. Module F-10 claimed that serverless function concurrency exhausts database connections. Both become the same integer here: max_connections, default 100.
You autoscaled the stateless tier — correctly — and every new instance arrived carrying its own pool. Or you moved to functions, which cannot share a pool because F-10's stateless-by-force constraint forbids one, so each concurrent invocation opened its own. Either way the app tier scaled beautifully and the bottleneck moved to a shared counter on the database, where scaling out is not an available move. This module is the bill for F-10's abstraction; Module A-10 is the same bill in another currency, when the function also can't hold your WebSocket.
Engineers price a database connection like an HTTP connection — a socket, some buffers, cheap. In Postgres it is a forked backend process, and per connection you get:
- An OS process with its own page tables and scheduler entry, plus a few megabytes of private memory that never becomes shared. Forking it is why connecting to Postgres costs far more than connecting to Redis, and why "a connection per request" is a design error rather than a style preference.
- The right to allocate
work_memper sort or hash node in the running query, not per connection. One query with three hash joins holds three multiples of it — which is how databases get OOM-killed while every graph looks healthy. - A snapshot, if it's inside a transaction, so it joins the vacuum horizon. That's how F-13's idle-in-transaction connection blocks dead-tuple cleanup database-wide.
max_connections = 100 is therefore a memory and scheduler budget expressed as a count. Raising it to 2,000 creates 2,000 processes contending for the same cores and shared_buffers, swapping a polite refusal of the 101st client for death by memory pressure.
Analogy: the database is a restaurant with a fixed number of tables and a fixed number of cooks.
max_connectionsis the tables. The tempting move on a busy night is to cram in more tables, but the cooks didn't multiply, so every dish arrives late and the kitchen falls behind on all of it at once. The restaurant that keeps ten tables and queues everyone else at the door serves more diners per hour. That queue at the door is your pool's wait queue — the cheapest place in the system for a request to wait, and the last place anyone measures.
Why Small Pools Are Faster Under Load
Under saturation, reducing pool size lowers latency and raises throughput. Not a trade — both, in the same direction. The standard incident response is the opposite.
Module F-4 supplies the derivation. L = λW: connections needed busy at once is arrival rate times hold time. Take 400 req/s where each request runs one 5 ms query, then repeat it after a bad plan pushes that query to 200 ms:
Two connections is the honest answer at the healthy load. Intuition wants pool size to track concurrent requests; Little's Law says it tracks concurrent requests × the fraction of their life spent inside the database, which is usually small. That gap is why real pool numbers look absurdly low. In the degraded row the pool didn't fail — it reported that arrival rate exceeded service capacity, which is what a full queue is for. This is F-13's slow-query cascade, in arithmetic.
So why doesn't a bigger pool help? Because S isn't a constant — it depends on how many queries execute concurrently on fixed cores and a fixed disk. Past hardware saturation, extra in-flight queries add no throughput; they divide the same throughput into slower pieces. Then second-order costs push throughput down: context switching between hundreds of runnable backends, contention on buffer-mapping and lock-manager partitions, work_mem allocations evicting your working set from cache (F-2), and more concurrent snapshots holding back the vacuum horizon. Hence the principle: queueing outside the database is nearly free; queueing inside it is expensive. A request in a pool's FIFO costs a promise and a timer; a request inside the database costs a process, several megabytes, a snapshot, and a share of every lock partition it touched.
The community starting point, popularised by HikariCP's documentation, is connections ≈ (2 × core_count) + effective_spindles. It is a hypothesis to load-test, not a law — effective_spindles is a rotational-disk-era proxy for storage concurrency, and on NVMe at 500K–1M random IOPS it means something quite different from its name. It also lands in the low tens for the whole database, not per instance.
Why this matters in production: the right move during pool exhaustion is almost never "raise the pool." It's to find what raised hold time — a slow query, a lock wait, an external call inside a transaction — because L = λW makes pool demand linear in W, and W is the term you can usually cut by an order of magnitude. Raising C to match a broken W relocates the collapse into the database, where it costs more and hides better.
The Only Connection Number That Matters Is N × Pool Size
A pool is configured per process. The limit is global. Nearly every exhaustion incident lives in that gap.
| Consumer | Count | Connections |
|---|---|---|
| API instances × pool | 12 × 20 | 240 |
| Worker instances × pool | 4 × 10 | 40 |
| Cron / scheduled jobs | 3 | 3 |
| Migration job during deploy | 1 | 1–5 |
| Metrics exporter | 2 | 2–10 |
An analyst's psql session | 1 | 1 |
superuser_reserved_connections | — | 3 reserved |
Total against max_connections = 100 | ~290 |
This is a deploy-time cliff, not a load-time one: a rolling deploy briefly runs old and new instances together, doubling the top row, which is why the incident tracks deploys rather than traffic. Your autoscaler's max-replica setting is a database configuration setting — if max_replicas × pool_size exceeds max_connections, you have configured an outage and scheduled it for the next spike. And the bottom rows are invisible: nobody counts the exporter or the migration container, and those are what tip you over.
The rule, with its cost: allocate max_connections as a budget across all consumers, then derive per-instance pool size by division. The cost is that pool size now depends on replica count, so it must be computed at deploy time from the autoscaler's ceiling or set low enough for it — meaning you deliberately run a pool smaller than one instance could use at peak.
The Serverless Version: 1,000 Invocations, 100 Slots
A function runtime has no shared pool because it has no shared process. Each concurrent invocation is an isolated environment whose pool has a maximum useful size of one, since it serves one request. Warm containers reuse a connection across invocations, which helps — and is why the problem is intermittent and confusing — but the scaling unit is concurrency, and concurrency is what you don't control.
Sign in to keep reading
The rest of this module is free — sign in with Google to unlock it and track your progress.
Sign in & RegisterDiscussion
0Join the discussion