Sharding and partitioning
One database, until it isn't#
A single well-tuned Postgres instance will take you further than most designs assume — comfortably into tens of thousands of reads per second with a replica or two, and terabytes of data on one volume. You do not shard because the data is large. You shard because one of three things ran out:
- Disk. The data no longer fits on one machine.
- Write throughput. One primary cannot absorb the write rate.
- Working set. The rows you actually touch no longer fit in RAM, so queries start hitting disk on every request.
Only the second genuinely forces a partition. The other two have cheaper answers you should reach for first — bigger volumes and archival for disk, more memory or a cache for the working set. An interviewer who hears "we shard" before hearing "we tried a read replica" will ask why, and they are right to.
Replicas add read capacity without changing your write path or your consistency story. They do nothing for writes, because every replica still applies every write. The moment the bottleneck is writes, replicas stop helping and partitioning is the only lever left.
Horizontal, vertical, and what the words mean#
Horizontal partitioning (sharding) splits rows across machines. Users 1–1,000,000 on shard A, the rest on shard B. Every shard has the same schema and a slice of the rows. This is what people mean by sharding.
Vertical partitioning splits columns. The hot, small columns a request needs on every page load go in one table; the large, rarely-read blob goes in another. Often a cheaper win than sharding and frequently overlooked — moving a 2KB bio column out of a table you scan constantly can shrink the working set by an order of magnitude.
Try vertical partitioning and archival before horizontal sharding. They do not introduce cross-shard queries.
Choosing the key is the whole decision#
Everything else about sharding is mechanics. The partition key is the design. It fixes which queries stay cheap, which ones fan out to every shard, and where traffic piles up on a bad day.
| Key | Stays cheap | Goes wrong when |
|---|---|---|
user_id | Everything one user owns lives in one place | One user is a celebrity |
tenant_id | Per-customer isolation, cheap deletes, easy compliance | Tenant sizes differ by 1000× |
created_at | Time-range scans, cheap archival of old partitions | Every write lands on today's shard |
| Hash of PK | Even write distribution, cheap single-key reads | Any query that isn't by that key |
The pattern in that table: every key makes one access pattern cheap and every other one expensive. Choose it from the query you run most, not from the one that is easiest to explain.
A query that includes the partition key goes to exactly one shard. A query that does not becomes a scatter-gather — it hits every shard, waits for all of them, and merges the results. Scatter-gather is bounded by the slowest shard, so its latency is a p99 of your fleet rather than an average, and it gets worse as you add shards. A design where the common read is a scatter-gather has the wrong partition key.
Hot shards, and why they are the expected follow-up#
Even distribution of keys does not mean even distribution of traffic. This is the failure mode interviewers probe for, so raise it before they do.
Celebrity users. Sharding a social product by user_id is correct right up until one account has 40 million followers and its shard serves a hundred times the traffic of any other. Mitigations: replicate the hot partition for reads, cache it aggressively at a layer in front, or special-case those accounts entirely — which is what large social products actually do.
Uneven tenants. In B2B, one enterprise customer can be larger than the next thousand combined. Shard by tenant_id and that customer owns a shard; place the largest tenants on dedicated shards deliberately rather than pretending the hash will save you.
Time-based keys. Sharding by date guarantees a hot shard, because all writes are for today. Sometimes that is an acceptable price for cheap archival — dropping a whole partition is far nicer than deleting a billion rows — but take the trade knowingly.
Resharding is the hard part#
Choosing an initial key is easy. Changing it later, on a live system, is where the real difficulty lives, and it is a common follow-up question.
The naive scheme, shard = hash(key) % N, is what makes it painful. Change N from 3 to 6 and the modulo changes for almost every key: roughly 5 in 6 rows must physically move, while the system stays up.
Two ways out, and they are both worth being able to name:
Consistent hashing. Place shards and keys on a ring; a key belongs to the next shard clockwise. Adding a shard moves only the keys between it and its predecessor — about 1/N of the data instead of nearly all of it. Virtual nodes (many ring positions per physical shard) smooth out the distribution. This is the standard answer wherever nodes join and leave routinely, which is why it belongs to the distributed cache problem rather than to a relational database.
Logical shards. Create many more partitions than machines up front — say 1,024 logical shards spread over 8 physical nodes. Growth means moving whole logical shards between machines, which is a lookup-table change plus a data copy, and never a rehash. This is what most large systems actually do, and it is the more practical answer for a database.
The live migration itself is the same shape either way: dual-write to old and new locations, backfill historical data in the background, verify the two agree, cut reads over, then stop writing to the old location. Each step is independently reversible, which is the property that makes it safe.
Name the constraint that forced the split. Choose a key from the dominant access pattern. Say which query becomes a scatter-gather as a result. Name the hot-shard scenario before you are asked. Then explain how you would add capacity later without a rehash — logical shards or consistent hashing, and why you picked one.
What this page deliberately doesn't cover#
Splitting the data is half the problem. The other half is making each piece survive a machine dying, which means replication — and the moment there is more than one copy of a partition, you owe an answer about what a client can observe after a write. That is the next concept.
Where this shows up
This page is the mechanism on its own. Each problem below bends it to a constraint that page has and this one does not.