Part 0 · 2 chapters · ~12 min

Know Your Limits First

The four golden signals for a database, capacity modelling from QPS, working set, IOPS and connections, where a single node actually ends, scaling up before scaling out, and the cost model in dollars per query across managed and self-hosted options.

1

Golden signals and the ladder

signalfor a databasewhere to read it
latencyquery time percentiles by query fingerprint; commit latencypg_stat_statements, performance_schema, APM
trafficqueries and transactions per second, rows read and writtenpg_stat_database, Com_* counters
errorsdeadlocks, serialisation failures, connection refusals, timeoutslogs, pg_stat_database.deadlocks
saturationCPU, IO queue depth, connections used, replication lag, lock waits, cache hit ratioOS metrics, pg_stat_activity wait events
THE SCALING LADDER
climb one rung at a time; each rung is cheaper than the one above
1. measuregolden signals, the slow-query pipeline2. fix queries and schemaindexes, rewrites, summary tables3. scale upbigger instance, faster disks, more RAM4. pool connectionsPgBouncer, ProxySQL, RDS Proxy5. cache and replicate readsRedis, replicas, CQRS6. partition and shardVitess, Citus, app-level
swipe the figure sideways, or tap expand for full screen
1/6
measure
Most "we need to shard" conversations end at the first rung: a few queries cause most of the load. pg_stat_statements or performance_schema digests rank them.
a few queries usually cause most of the loadrank before you redesign
2

Capacity modelling and the single-node ceiling

code
capacity model, a lending app (illustrative numbers)
peak QPS            = daily active users × requests per user per day × queries per request / 86,400 × peak factor
                    = 2,000,000 × 30 × 6 / 86,400 × 4  ≈  16,700 queries/s
working set         = hot rows × average row size × (1 + index overhead)
                    = 40M active accounts and loans × 400 B × 2.5   ≈  40 GB   → fits in RAM on one large node
write IOPS          = writes/s × (WAL + data + index pages) after batching ≈ measure with a load test
connections         = pods × pool size  → keep under the pooler's server pool, not max_connections

Cost model: compare dollars per thousand queries at your p99 target, not instance prices. Managed services (RDS, Cloud SQL, Aurora) cost more per core but include backups, failover and patching; self-hosted is cheaper at scale only if you count the engineers who run it. Aurora and other serverless options also bill per IO, which changes the maths for write-heavy workloads.

WHERE A SINGLE NODE ENDS
rough per-node ceilings for a well-tuned OLTP database on large hardware (orders of magnitude, measure yours)
point reads / s (cached)~100k-1Msimple writes / s~10k-100kworking set in RAMhundreds of GB to a few TBconnections (Postgres, direct)hundreds; thousands via a poolerstorage per nodetens of TB, but restores slow down
swipe the figure sideways, or tap expand for full screen
1/5
reads
Cached point reads on primary keys reach six figures per second per node; the limit is usually CPU and network, not disk.
reads scale far on one node when cachedCPU and network bound