Sharding
Sharding is the point where a database stops being a database and becomes a distributed
system you are now responsible for. Joins stop working. Transactions stop working. Unique
constraints stop working. ORDER BY with LIMIT becomes a merge across every shard.
All of that is worth it eventually. It is almost never worth it as early as people reach for it, which is why the first thing to say in an interview is what you would try before sharding, and why that has run out.
You shard a social app by user id hash. Name one query that used to be one statement and is now genuinely hard.
What you try first
Say these out loud in order. Each one buys real time and none of them costs you joins.
Read replicas, if the load is reads. This is the common case and it takes an afternoon. A cache in front, which for a read heavy workload usually removes more load than a shard would. Vertical scaling, which is unglamorous and buys a lot: a single modern machine handles far more than most people assume. Then archiving cold rows out of the hot tables, because a large fraction of most tables is data nobody queries.
Shard when writes exceed what one primary can take, or when the working set no longer fits in memory on the largest machine you can buy. Those are the two honest triggers.
Candidates who jump straight to sharding are demonstrating that they know the word. Thirty seconds spent on what you would do first, and why it has stopped working here, is worth more than a detailed shard key discussion, because it is the part that separates people who have run a database from people who have read about running one.
The shard key decides everything
Pick the key badly and you get a distributed system with all of the cost and none of the benefit, because one shard takes most of the load. Try a few.
Two lessons from those four. Hashing fixes skew that comes from ordering, and does nothing about skew that is in the data itself. And a key that is meaningful to humans is usually skewed, because humans are unevenly distributed across everything.
Hash, range, or a directory
The trick that makes resharding survivable
Do not map keys to physical shards. Map keys to a large fixed number of logical shards, say 1,024, and map logical shards to physical machines in a small table.
Now growing the cluster is moving logical shards between machines, not rehashing data. The key to logical shard mapping never changes, so no key ever needs recomputing. Going from 8 machines to 16 means moving 512 logical shards, which you can do one at a time, in the background, with the ability to stop halfway. Compare that with rehashing every row while the site is up.
Pick the logical shard count once and pick it high. It is the one number here you cannot change later without the migration you were trying to avoid.
What you lose, and what to do about it
Joins across shards. Denormalise so the data you read together lives together, or do the join in the application, or keep a small reference table replicated to every shard.
Transactions across shards. Design so they are not needed, which usually means choosing the shard key so that things which change together share a shard. Where you genuinely need one, you are in saga and compensation territory, the same shape as the payment rail.
Unique constraints. A unique index only covers one shard. Global uniqueness, on an email address for example, needs a separate table keyed by that value, and that table is either unsharded or sharded by the value itself.
Pagination and sorting. ORDER BY created_at LIMIT 20 has to fetch 20 from every shard
and merge. Workable at eight shards, painful at a thousand, and the reason cursor
pagination and per-shard time ordered ids matter.
Counting. COUNT(*) becomes a scatter gather. Keep a counter, or accept an estimate.
Break it
Interview replay
Trade-offs
| Choice | What you gain | What you pay | Pick it when |
|---|---|---|---|
| Hash sharding | Even distribution with no lookup in the request path and nothing to keep consistent. | Range queries scatter to every shard, and resizing is painful without logical shards. | The default when most queries filter on one entity, which is most transactional workloads. |
| Range sharding | Range scans stay local, and splitting a hot range is a local operation. | Clustered access patterns create hot shards, and the newest range is usually the hottest. | Time series and archives, where you read ranges and rarely write to old ones. |
| Directory | Place any key anywhere, so one oversized tenant is an entry in a table rather than a redesign. | A lookup on the request path, and a service whose outage makes every shard unreachable. | Multi-tenant systems with wildly uneven tenants. Often layered over hashing rather than replacing it. |
| Logical shards over physical | Rebalancing moves shards instead of rehashing rows, so growth is a background operation. | One more indirection, and a shard count you have to choose correctly on day one. | Always, if you are building the sharding layer now. Retrofitting it is the migration itself. |
Checkpoint
1. What is the honest trigger for sharding rather than adding replicas?
2. Why is created_at usually a poor shard key?
3. You hash keys to 1,024 logical shards, then map those to 8 machines. What does the indirection buy?
I would not shard first. Reads scale with replicas and a cache, and archiving cold rows often buys a year, so I would shard when writes exceed one primary or the working set no longer fits in memory. The shard key comes from the highest volume query, usually a hashed user or tenant id, so most reads hit one shard. I would avoid a time based key, because traffic concentrates on recent data and the newest shard becomes permanently hot. I would hash to a large fixed number of logical shards and map those to machines in a small table, so growing the cluster moves shards rather than rehashing rows. And I would keep the ability to override placement for a single key, because hashing spreads load evenly only when the data itself is even, and one enterprise tenant is never even.
