The database scaling playbook

Pressure drops every evening. The engineer reads the district meters before spending anything, and finds one leaking main and a street of running taps.

At the water utility	In the database
reading the district meters first	pg_stat_statements (steps 1–2)
fixing the leak costs a plumber, not a pump	tuning the top statements (step 3)
the main feeds only so many service pipes; new blocks share a header tank	the connection budget and a pooler (steps 4–5)
a roof tank for water drawn over and over	a cache for hot reads (step 6)
a downstream reservoir, refilled hours behind, for drinking taps only	a replica: lagging, read-only (step 7)
a bigger pump at the works	scaling up (step 8)
pressure zones inside one network	partitions on one server (step 9)
a second utility across the river	a shard (step 10)

In real terms: each step costs more than the one before, so you take it only when a measurement shows its trigger.

Breaks down when: a town can run two utilities that never trade water. Shards of one database usually share customers, orders and reports, so every cross-town street needs a transfer agreement. Wrong inference: "a second utility just doubles capacity." Sharding also adds fan-out queries and distributed commits.

Contrasting case: a utility that builds a second reservoir while the leaking main still runs. More capacity arrives, and so does the same waste.

Every example ran on a real PostgreSQL 17.10 in the pack's own container, with pg_stat_statements preloaded and reset first. No output carries a time.

4.1 ExamHall: the live feed stalls

EXH-1700: at 09:00 the proctor console's live feed stalled for 60 proctors, and the team asked for read replicas. First, pg_stat_statements, after 120 console refreshes:

4.1 after 120 console refreshes, pg_stat_statements by calls:
  calls 4800 · rows 4800 · SELECT status FROM candidates WHERE id = $1
  calls 120 · rows 6000 · SELECT id, candidate_id, kind FROM alerts WHERE proctor_id = $1 ORDER BY id DESC LIMIT $2
  tile lookups per feed query: 40

The status-tile lookup runs once per candidate tile, 40 times per feed query: it dominates by calls (steps 1–2). The next step is fixing that statement, which MR !1900 does (db-n-plus-one owns how), not replicas (step 3).

4.2 ExamHall: ten instances do not fit

EXH-1701: for the live feed, the proctor API grew from 6 to 10 instances, each with a pool of 20. Read the budget from the server, then fill it as an ordinary role:

4.2 PostgreSQL 17.10: SHOW max_connections 150 · superuser_reserved_connections 3 · reserved_connections 0
  budget for the app role: 150 - 3 - 0 = 147 · filled: 147 accepted, then "remaining connection slots are reserved for roles with the SUPERUSER attribute"
  6 instances x pool 20 = 120 -> fits
  10 instances x pool 20 = 200 -> over the budget

The budget is 147, not 150: three slots wait for superusers (step 4). Two hundred is over it, so the connection step's trigger is met. MR !1901 puts a transaction pooler in front (step 5); concepts/db-connection-pooling.md owns what transaction mode changes.

4.3 Proof domain, catalog: what a shard cannot check

The catalog's customers.email is UNIQUE. Two signups use the same email, once on one database and once on two shards routed by customer id:

4.3 one database: 41 accepted · 42 refused 23505
4.3 two shards by id % 2: 41 -> shard_1 accepted · 42 -> shard_0 accepted
4.3 who holds [email protected]? shards asked 2 of 2, rows found 2

Each shard checks only its own rows, so the duplicate got in, and finding one email asks every shard (step 10). Two databases on one server stand in for two servers (model).