Database Sizing for System Design: Indexes, Replicas, and When to Shard

The most reflexive wrong answer in system design sounds responsible: “we’ll add an index.” The second most reflexive one sounds ambitious: “we’ll shard.” Both are real tools, and both are routinely proposed for problems they make worse — because the person proposing them has a mental model of the database as a magic box, not as a machine with two very different paths through it.

This is where the loads and the ceilings collide, because in almost every real system the database is where the capacity argument ends — the stateless tiers scale by adding boxes, and the stateful tier is where physics pushes back. By the end you’ll have the write path and the read path as two separate machines in your head, and a four-check procedure that answers “should we shard?” in under a minute.

First, sort your data into three shapes#

Before sizing a database, ask whether the data belongs in one at all. Part 1’s value-decay question sorts everything into three shapes, and each shape has a different home:

This single sort is worth more than any tuning. The classic self-inflicted wound is shoving shape 1 or 2 into shape 3’s database — then heroically scaling a Postgres that never needed to see that data. (My own trading-system replica holds the order book purely in memory: lose the process, refetch a snapshot. The database never hears about it.)

Everything below is about shapes 2 and 3 — data that genuinely must persist. (A quick gloss, since the word does a lot of work here: a columnar store — ClickHouse, Parquet files on S3 — lays each column together on disk instead of each row. That makes it brilliant at appending and scanning millions of rows, and terrible at updating one — which is exactly why it’s the home for history and never for truth.)

Anatomy of one INSERT — why writes cost so much#

Here’s what a single durable row actually buys, in order:

flowchart LR
  A["client<br/>INSERT"] --> B["① append to WAL<br/>(write-ahead log)"]
  B --> C["② fsync<br/>~1 ms, the promise"]
  C --> D["③ update table pages<br/>(the heap)"]
  D --> E["④ update EVERY index<br/>one B-tree insert each"]
  E --> F["⑤ ack to client"]

Step ② you know from Part 2 — the notary stamp, ~1,000/sec naive, batching as the carpool. But step ④ is the one nobody prices in, and it’s where the reflexive “add an index” answer goes wrong.

An index is a librarian’s card catalog. The books on the shelves are the table; the catalog is a separate, sorted structure that lets you find a book without walking every shelf. Magnificent for reading. But now watch a new book arrive: it goes on the shelf, and a card must be filed in the author catalog, and the title catalog, and the subject catalog. Five catalogs — five filings, per book, every time, forever. The catalogs don’t know they’re for reading; they charge their fee on every write.

Two corroborations from production, because this is the claim people doubt. The sharpest one-liner in the index literature: “INSERT is the only operation that cannot benefit from indexing — it has no WHERE clause” (use-the-index-luke) — every index is pure cost to a write. And the cost is measurable at scale: when Notion migrated billions of rows between Postgres fleets, dropping indexes during the copy and rebuilding them afterward cut the job from 3 days to 12 hours — the index tax, timed.

That’s the write path: WAL + fsync + heap + one B-tree per index. Every one of those costs recurs on every row forever, which is why write throughput is the precious resource — and why the entire scaling story splits in two from here.

Scaling reads: the abundant direction#

Reads have a wonderful property writes lack: a copy answers them just as well as the original. That single fact gives you three stacked levers, each an order of magnitude:

• The cache. Put the hot answers in RAM next to the app (Redis, or the app’s own memory). The napkin math is dramatic: at a 95% hit rate, the database sees only 5% of read traffic — a cache in front of 100K reads/sec leaves the database serving 5K. A cache doesn’t shave the load; it decimates it. (Respect the classic trap: when a popular key expires, a thousand requests miss simultaneously and stampede the database — the thundering herd again, Part 1’s synchronization lesson wearing a new hat. Jittering expiries is the entry-level fix; the big shops go further: Facebook’s memcache hands out a lease — one recompute token per key — which cut a measured stampede from 17K database queries/sec to 1.3K, and Instagram caches the in-flight promise so concurrent misses all wait on a single rebuild. The stampede is real enough that a 2010 version of it took Facebook down for 2.5 hours — they had to turn the site off to break the loop.)
• Read replicas. The primary streams its WAL to copies; copies serve reads. Photocopying the card catalog: any copy answers lookups, so read capacity scales by adding copies.
• The caveat that keeps replicas honest: replication lag. The copy is always slightly behind — milliseconds usually, seconds under load. A user who writes and instantly reads from a replica can see their own update missing (post a comment, refresh, comment gone — it reappears seconds later, after support has already been emailed). The standard fix: route a user’s reads to the primary briefly after their own write, or read-your-own-writes from the cache. Mention this unprompted and you sound like you’ve been paged for it. And lag has a meaner sibling worth one sentence: failover with unreplicated writes. When a primary dies mid-stream and a replica is promoted, the writes that hadn’t copied over yet are simply gone — or worse, two primaries briefly both accept writes (split brain). GitHub’s famous 2018 incident started with a 43-second network blip and ended in 24 hours of degraded service cleaning that up.

Scaling writes: the scarce direction#

Now the asymmetry. A copy can answer a read, but a copy cannot accept a write — if two nodes both accepted writes to the same data, they’d diverge and you’d have two truths. So every write must funnel through the one primary, replicas or not:

flowchart TD
  R1[reader] --> C1[(cache)]
  R2[reader] --> C1
  R3[reader] --> RE1[(replica)]
  R4[reader] --> RE2[(replica)]
  W1[writer] --> P[(THE primary<br/>all writes, one node)]
  W2[writer] --> P
  W3[writer] --> P
  P -.WAL stream.-> RE1
  P -.WAL stream.-> RE2

Reads fan out across copies; writes funnel into one door. So the write levers are fewer and dearer, in escalation order:

• Batch (Part 2’s carpool): raw ~5–10K/s → ~50–100K rows/s. The first and cheapest lever, worth 10×.
• Queue as shock absorber: put Kafka or similar in front, so bursts land in the log (100s of MB/s, cheap) and the database drains at its own steady pace. This trades latency for survival — fine for telemetry, unacceptable for “payment confirmed.”
• Trim the write itself: fewer indexes on hot tables, move append-only shapes out to columnar (shape sorting again).
• Only then: shard — split the data across multiple primaries, each owning a slice. More notaries, each with their own book.

Sharding vocabulary, pinned down (these get conflated constantly): partitioning splits a table into pieces on the same node — mainly so old pieces can be dropped instantly (deleting a billion rows is agony; dropping a partition is a metadata change). Sharding splits data across nodes — each shard is its own primary with its own notary, which is what actually multiplies write throughput. (Routing a key to its shard usually uses consistent hashing — a hashing arrangement where adding a shard moves only a small slice of keys, instead of reshuffling nearly all of them the way a plain hash(key) mod N would.) Same idea, one node vs many; only one of them buys you write capacity.

And the tax, because sharding is the lever with the mortgage attached: any query that doesn’t include the shard key becomes scatter-gather — ask every shard, stitch the answers (Figma’s engineers call it “a database-wide game of hide-and-seek”); cross-shard transactions and global uniqueness stop being free; even ID generation breaks, since auto-increment can’t span shards (Instagram’s fix became the standard: 64-bit IDs packing a timestamp + shard number + sequence — with the elegant side effect that sorting by ID sorts by time, saving an entire index). And the shard key is nearly unfixable later — resharding a live system is a migration project, not a config change — so choose one that spreads load evenly and keeps each common query on one shard (user ID for a messaging app; market ID for a trading system).

Choose badly and you get the hot shard: partition by, say, market, and on election night one market takes the whole country’s traffic while its shard-mates idle — a thousand nodes, one of them on fire, aggregate graphs green. This problem is old enough to have a celebrity mascot: Instagram’s co-founder has said he still remembers Justin Bieber’s row ID by heart — 6860189 — because every early scaling fire traced back to it. (Standard fixes: a higher-cardinality key; salting the known-hot key across sub-shards — DDIA’s two-digit random suffix splits one hot key across 100, at the price of reads gathering all 100; and splitting hot counters into sub-counters summed on read, which is exactly how Bieber’s likes got fixed.)

The working set: the cliff nobody graphs#

One more ceiling, and it’s the sneakiest because it isn’t about throughput at all. Your database’s real speed comes from serving hot pages out of RAM — the disk is only the archive. The working set is the slice of data your queries actually touch on a normal day (today’s orders, active users, this week’s ticks) — usually a small fraction of total data.

While the working set fits in RAM, reads are ~100 ns affairs and the database feels supernatural. The day it outgrows RAM, every miss pays the SSD toll — 1,000× slower per Part 2’s ladder — and the degradation is not gradual: p99 latency falls off a cliff within weeks while total data grew only a few percent. The database didn’t slow down; the hot data stopped fitting.

This is why “how big is the database?” is the wrong question, and “how big is the part you touch daily?” is the right one. A 20 TB table with a 50 GB working set runs beautifully on one node. A 500 GB table touched uniformly at random does not.

The decision, assembled: four checks#

Everything above compresses into four checks, in the order a sane engineer reaches for them — writes, reads, working set, total size. Drag the sliders; watch which lever each check demands and when the verdict flips to the knife:

The philosophy the widget encodes: sharding is the last lever, not the impressive one. Batching, caching, replicas, and partitioning each buy an order of magnitude at a tenth of the complexity. The senior answer to “should we shard?” is usually “not yet, and here are the four numbers that say so.”

Two field notes from teams that actually pulled the lever, to keep the checks honest. First, the napkin can’t see every forcing function: Notion and Figma both report that what finally pushed them wasn’t raw size or QPS but Postgres operational walls — VACUUM falling behind, transaction-ID wraparound (Postgres stops accepting writes — Notion called it an “existential threat”), and connection-pool exhaustion. If the widget says “levers suffice” but VACUUM can’t keep up, VACUUM wins. Second, nobody shards by literally splitting N ways: the trick is many logical shards mapped onto few physical boxes — Notion cut 480 logical shards across 32 machines (480 because it divides by nearly everything, so growing to 40 or 48 boxes means moving schemas, never re-splitting them); Instagram ran ~2,000. And the migration itself follows one pattern everywhere: double-write to old and new, backfill history, “dark read” from both and compare, then cut over — Notion’s cutover cost five minutes of downtime for billions of rows.

What to keep within reach#

• Sort data into three shapes first — rebuildable (no DB), append-only (columnar, partitioned), transactional (relational). The cheapest database is the one you don’t run.
• Price the write path fully: WAL + fsync + heap + one B-tree per index. Indexes buy reads with write currency.
• Reads scale with copies — cache (95% hit = 20× off the database), then replicas — but say “replication lag” and “read-your-own-writes” in the same breath.
• Writes funnel into one primary. Levers in order: batch → queue → fewer indexes → shard. Each step costs 10× more complexity than the last.
• Partitioning ≠ sharding: same node vs many nodes; only sharding multiplies write throughput; dropping a partition is the only pleasant way to delete a billion rows.
• Watch the working set, not the total. RAM → SSD is a 1,000× cliff, and it arrives suddenly.
• Shard key = spread load evenly + keep queries local + survive the hot key. It’s nearly unfixable later; spend the design minutes there.

Numbers on a page are knowledge; numbers said out loud under pressure are a skill. The drills are the practice room.