Database Sharding: Choosing a Shard Key and Surviving Hot Partitions
Sharding is where a lot of system design interviews quietly go wrong. The candidate says "we'll shard the database", the interviewer says "on what key?", and the next two minutes decide the round. This is about having a real answer to that question, and to the three follow-ups that come after it.
Shard first, or scale up first?
Say so before you shard anything. A single Postgres instance on modern hardware handles tens of thousands of reads per second and holds low terabytes comfortably. Read replicas take you further. Sharding costs you cross-shard joins, distributed transactions, and a migration you cannot easily undo.
The honest trigger for sharding is one of:
- Write throughput past what one primary can take — replicas do not help, every write still goes to the leader.
- Data size past what one machine can store or index efficiently.
- Blast radius — you want one customer's outage to take down a quarter of the service rather than all of it.
Saying "I'd scale vertically and add read replicas first, and shard when writes outgrow a single primary" is a stronger opening than reaching for shards immediately.
Picking the shard key
This is the whole decision. Everything else follows from it.
Range partitioning — rows go to shards by ranges of the key (user_id 0–1M on shard 0, and so on). Range scans stay cheap because neighbouring keys live together. The failure is obvious once you see it: ids are handed out in order, so every new signup lands on the last shard. Your newest and most active users all pile onto one machine while shard 0 idles.
Hash partitioning — shard = hash(key) % N. The spread is even and the skew problem disappears. You pay for it by losing range scans: "all orders from last Tuesday" now hits every shard.
Entity-based — shard by the thing most queries are scoped to. For a chat product that is usually the conversation; for a B2B product, the tenant. Queries that stay inside one entity stay on one shard, which is the point.
Geographic — shard by region, for latency and data residency. The skew follows your user population, which is rarely even.
The thing most candidates miss
Hashing fixes range skew. It does not fix hot keys.
A single account with a hundred million followers hashes to exactly one shard, however good the hash function is. There is no hash that splits one key across machines, because the whole contract of a hash is that the same key always lands in the same place. The shard carrying that account melts, and switching hash functions changes nothing.
The fixes are different in kind:
- Composite key. Shard on
hash(user_id + bucket)where bucket is 0–N. One account's traffic now spreads over N shards. Reads must fan out and merge, which is the cost. - Dedicated treatment. Pull the handful of celebrity accounts out and serve them from cache, or give them their own shard.
- Change the access pattern. Twitter's answer to this is not a sharding trick — it is fanning out on read for celebrities and on write for everyone else.
If you can say "hashing solves skew but not hot keys, and here's why they're different problems", you are ahead of most candidates in the room.
The three follow-ups
"How do you query across shards?" You fan out, then merge in the application or a query layer. Say what that costs: latency is now the slowest shard, not the average, and pagination across shards is genuinely awkward — you cannot just LIMIT 20 on each shard and merge, because the global top 20 might all be on one. Either over-fetch and discard, or keep a secondary index keyed differently.
"What about transactions across shards?" A single-shard transaction is ordinary ACID. Across shards you need two-phase commit, which is slow and blocks on a coordinator failure, or a saga, which gives up atomicity and asks you to write compensating actions for every step. Most teams work very hard to keep transactions inside a shard — that is itself an argument for entity-based sharding.
"How do you add a shard?" This is where hash % N bites. Going from 4 shards to 5 changes the modulus for nearly every key, so almost all your data moves. Two standard answers: consistent hashing, which moves only about 1/N of keys, or virtual shards — map keys to 1,024 logical shards up front, then assign logical shards to physical machines. Adding a machine moves a few logical shards and nothing else. The second is simpler to operate and what a lot of production systems actually do.
What to say out loud
A good sharding answer sounds like this: here is the query pattern that matters most, here is the key that keeps that pattern on one shard, here is the skew it risks, here is how I would detect the skew, and here is what adding a shard costs me later. Five sentences, and you have covered what the interviewer wanted to probe.
The weak answer is "shard by user_id". It might even be correct — but it is correct by accident, and the follow-ups will show that.