TL;DR - One primary has three ceilings: write throughput, disk, and the working set that fits in RAM. Sharding splits rows across independent primaries by a shard key, multiplying all three. You pay in routing, resharding and cross-shard queries, so pick the key for the dominant query and pre-split into logical shards on day one.
30-second version: "I shard when one primary is write-bound or the hot data outgrows RAM. I pick a shard key that matches the most common query, usually a hash of the tenant id, so each request touches one shard. I pre-split into about a thousand logical shards mapped to nodes, so growth means moving a few logical shards at a time. Cross-shard reads scatter-gather; cross-shard writes go through a saga or get designed away."
Why One Primary Runs Out
You run an events table on one primary at 10,000 writes per second with 2 KB rows. Three ceilings arrive in order.
Write throughput. Every write costs the primary a WAL append, an fsync, an update per secondary index and a send per replica. Replicas multiply reads; writes stay single-node. A tuned NVMe primary sustains tens of thousands of small writes per second, so 10,000 with four indexes is near the ceiling.
Storage. 10,000 writes/s x 2 KB = 20 MB/s; x 86,400 s per day = 1,728,000 MB, about 1.7 TB per day before indexes. A 90-day window is about 155 TB on one box.
Working set vs RAM. Reads mostly hit the last 30 days: 30 x 1.7 TB = about 52 TB. A 1 TB RAM machine caches about 2 percent, so most reads go to disk and p99 climbs to tens of milliseconds.
Across 32 shards, each takes 312 writes/s, 54 GB a day, and a 1.6 TB hot window that fits in RAM on a 2 TB node.
How Sharding Works
What is a shard? An independent database instance holding a subset of a table's rows, with its own primary, replicas and indexes.
What is a shard key? The column that decides which shard holds a row. A query carrying the key routes to one shard; one lacking it fans out to all. Choose the key the dominant queries already carry.
Hash, Range, or Directory
- -Hash - hash(key) mod shard_count. Even spread; range scans hit every shard.
- -Range - each shard owns a contiguous key range. Cheap range scans; time-ordered keys push every new write onto the newest shard.
- -Directory - a lookup table maps each tenant to a shard. Move a tenant by updating one row; cache the table in every router.
Hot Keys and the Celebrity Key
Hashing balances key counts, and traffic per key still varies: a tenant producing 30 percent of writes pins 30 percent of the load on one shard. Fixes, by effort: cache its hot reads; give it a dedicated shard via a directory override; or append a bucket (tenant_id, 0 to 15) to spread it over 16 shards.
Routing
- -Client library - the shard map lives in the application. Zero extra hops; every service tracks map changes.
- -Proxy - a stateless tier extracts the key and forwards. One place to update the map, about 0.5 to 1 ms extra.
- -Coordinator - a planner node fans out and merges, so cross-shard SQL works transparently; it caps fan-out throughput.
Resharding: Logical Shards, Moved One at a Time
Under hash mod 16, growing to 32 nodes remaps most rows. The fix is indirection.
What is a logical shard? Pre-split the key space into a fixed count, say 1,024, and keep a map from logical shard to physical node. With 16 nodes each hosts 64; growing to 32 means each node hands 32 to a new node. Exactly half the data moves, the minimum for doubling, and the hash stays fixed forever.
Moving one logical shard. 52 TB over 1,024 shards is about 51 GB each; at 200 MB/s that copies in about 255 seconds. Snapshot to the new node, stream the WAL tail until lag is under a second, pause writes briefly, flip the map entry, drop the old copy. One shard at a time, under full traffic.
Cross-Shard Queries, Joins and Transactions
Scatter-gather. A query lacking the shard key hits every shard and the router merges. If each shard answers under 20 ms at p99, the chance all 32 do is 0.99^32 = about 0.72, so 28 percent of fan-out queries wait past a shard's p99. Keep fan-out for admin and analytics.
Joins. Co-locate rows that join: orders and order_items both keyed by customer_id join locally. Across keys, denormalize or keep an asynchronously updated copy keyed differently.
Transactions. Two options for a write spanning two shards:
- -Two-phase commit - prepare, then commit. Two round trips, a durable log write per participant per phase, and locks held throughout; a coordinator crash leaves participants holding locks.
- -Saga - local transactions with compensations: debit A, credit B, refund A if the credit is rejected. Intermediate states are visible; compensations must be idempotent.
Better still: pick the key so the common transaction lives on one shard.
Secondary Indexes: Local or Global
Local index - each shard indexes its own rows. Writes touch one shard; a lookup by email scatters to all.
Global index - the index is sharded by the indexed value, so a lookup is one hop to the index shard and one to the data shard. Each insert becomes a two-shard write, so global indexes usually update asynchronously and read slightly stale.
Operating It
- -Backups - per shard, in parallel: 155 TB becomes 32 jobs of about 5 TB. For a cluster-wide snapshot, recover every shard to one recorded timestamp.
- -Schema changes - shard by shard, expand-then-contract, with the application handling both schemas during the window.
- -Monitoring skew - export writes/s, size and p99 per shard; alert when the largest exceeds 2x the average.
Alternatives Worth Naming
- -Read replicas first - for read-heavy load, replicas plus a cache may carry you for years.
- -Partitioning within one node - monthly partitions give smaller indexes and cheap retention, at far less operational cost.
- -A bigger box - roughly linear cost up to the largest instance, and it keeps single-node transactions.
- -Managed systems - sharding middleware over MySQL-style primaries, a distributed Postgres with a coordinator, or a key-value service that auto-splits partitions. They handle routing and resharding; key choice and hot keys remain yours.
When to Use in Interviews
Reach for sharding when writes, volume or the hot working set exceed one machine, and name which: a social feed, a multi-tenant SaaS, an events pipeline. State the shard key first, name the fan-out queries you accept, and say how you reshard. For a read-heavy system under a few terabytes, say replicas plus a cache.
Summary
- -Three ceilings - writes, disk, RAM; 10k writes/s x 2 KB is 1.7 TB a day.
- -Shard key - matches the dominant query; hash, range or directory.
- -Hot keys - cache, dedicate a shard, or add a bucket suffix.
- -Logical shards - pre-split into 1,024; move 51 GB at a time.
- -Cross-shard - scatter-gather, co-locate, sagas over 2PC.
- -Operations - per-shard backups, rolling schema changes, skew alerts.
Interview Q&A
- -How do you choose the shard key? - The column in the most frequent queries and transactions, with a hash that spreads load.
- -How do you add nodes? - Move logical shards one at a time: snapshot, stream the WAL tail, flip the map entry.
- -How do you run a cross-shard query? - Scatter and merge, off the user-facing path; add a global index for frequent ones.
- -How do you do a cross-shard transaction? - Design it onto one shard; otherwise a saga, or 2PC if you can afford held locks.
- -What breaks first in operations? - Skew. Alert at 2x average per-shard load.
Disclosure: I build Pretense, a local proxy for AI coding tools. When you paste a shard map or migration script into an assistant, it swaps detected secrets, PII and, inside fenced code blocks, code identifiers for stand-in tokens before the request leaves your laptop; identifiers come back byte-exact, secrets stay as stand-ins by design.
From the System Design Laws newsletter.
