Why sharding exists: the single-Postgres ceiling

Otto’s Postgres is running at 95% CPU, the working set has grown past what fits in RAM, and writes are saturating the single primary. Read replicas help for reads, but the write bottleneck is the machine itself. There is no bigger instance left to buy.

The single-Postgres ceiling

A single Postgres instance has hard limits:

• Working set vs RAM: once the hot portion of your data no longer fits in shared_buffers plus OS page cache, every query starts hitting disk. Cache-hit ratio (blks_hit / (blks_hit + blks_read)) dropping below 95–99% is the leading signal.
• Write throughput: a single primary can sustain roughly 10–50k transactions per second depending on hardware. Above that, WAL generation dominates CPU and the primary becomes the bottleneck.
• Storage: practical ceiling around 1–10 TB of working set in RAM on the largest available machines.

Vertical scaling (more RAM, faster NVMe) buys time — often years. The right order is: fix slow queries first, add indexes, add read replicas, vertically scale. Sharding is the last resort, not the first.

What sharding does

Sharding takes one logical dataset (all your orders, all your users) and splits it across N physical Postgres instances. Each row lives on exactly one shard, determined by a shard key — a column or combination of columns (typically tenant_id for B2B SaaS, user_id for B2C).

The win is linear-ish scale-out: 10 shards give roughly 5–8× the throughput (overhead from coordination reduces the theoretical 10×). The cost is that every operational task — backups, migrations, upgrades, VACUUM — must run on every shard.

The hot-shard failure mode

The most important failure mode to design against from day one is the hot shard: one shard receives far more traffic than the others because the shard key was poorly chosen, or because one tenant grew 1000× larger than the median.

Example: a SaaS shards orders by customer_id. An enterprise customer signs and their orders are 5× all other customers combined. Their shard hits 95% CPU while the other shards sit at 10%. The cluster effectively runs at single-shard capacity for the dominant customer.

The fix — moving that tenant to a dedicated shard — is covered in lesson 05. The lesson here is: shard-key selection is the single most consequential design decision in a sharded system, and bad choices are lived with for years.

The library metaphor

Sharding is the difference between one library with a million books (one building, one lookup desk, find anything by walking around) and ten libraries with 100k books each (ten buildings, you need a directory of “which library has this book”). The single library is simpler. The ten-library setup needs a directory (the shard map) and a rule for which building to go to before you set off.

Books that get checked out a million times a year fill one library while the others sit empty — that is the hot-shard failure.