Database sharding questions show up in system design rounds the moment your design outgrows a single machine. The interviewer wants to see three things: that you can pick a sensible shard key, that you can predict where hotspots will form, and that you know how to move data between shards without taking the system down. Get those three right and you clear the bar. Everything else (replication, consensus, caching) sits on top.
Sharding means splitting one logical dataset across many physical databases so that no single node holds all the rows or serves all the traffic. It is horizontal partitioning across servers. The hard part is almost never "how do I split data," it is "how do I split it so load stays even and the scheme survives growth." That is what this guide focuses on, using table designs you have probably already practiced in SQL rounds.
Why interviewers ask about sharding
Sharding is the canonical "your database is the bottleneck" question. When a design hits millions of writes per second or terabytes that will not fit on one box, vertical scaling (a bigger machine) runs out. The interviewer is checking whether you reach for sharding at the right moment and whether you understand the tradeoffs you inherit the second you do it.
In our mock interviews the most common failure is not a wrong answer, it is jumping to sharding too early or too late. Do not shard a design that comfortably fits on one primary with a couple of read replicas. Say that out loud. Then, when write throughput or dataset size genuinely exceeds a single node, introduce sharding and immediately name the cost: cross-shard queries get expensive, transactions get harder, and you now own a rebalancing problem forever.
If you want to drill the surrounding system design muscles, our practice sets let you rehearse these tradeoffs out loud with an AI interviewer, which is closer to the real thing than reading about them.
Choosing a shard key
The shard key decides which shard a row lives on. It is the single most important decision in the whole design, and once you have written a billion rows it is painful to change. A good shard key has high cardinality, spreads writes evenly, and matches your most common query so you can route to one shard instead of fanning out to all of them.
Think about the SQL tables you already practice on. Take the Followers table from "Find Followers Count," with columns user_id and follower_id. If your dominant query is "get all followers for a user," sharding by user_id keeps every follower list on one shard, so a follower count is a single-shard read. Shard by follower_id instead and the same query fans out across every shard.
Composite keys deserve special attention. In "Find Product Recommendation Pairs," the ProductPurchases table has the unique key (user_id, product_id). If you shard on user_id, all of one user's purchases co-locate, which is great for per-user analytics but bad if one enterprise account buys millions of items. If you shard on the full composite key via a hash, purchases scatter evenly but "everything user X bought" now touches many shards. There is no free lunch: pick the key that serves your read pattern and accept the cost on the others.
A quick decision checklist:
- Route your hottest query to a single shard if you can.
- Prefer high-cardinality keys (user IDs, order IDs) over low-cardinality ones (country, status).
- Avoid keys that concentrate writes, like a monotonically increasing timestamp.
- Confirm the key exists on the rows you write most, so routing is cheap.
Hotspots: the celebrity problem
A hotspot is a shard that gets disproportionate traffic while the others sit idle. The classic version is the celebrity problem. Shard the Followers table by user_id and most users are fine, but the one account with 40 million followers lands entirely on a single shard, and every read and write for that account hammers one node. Even sizing was defeated by one row.
Time-based keys create the same failure in a different shape. Consider "Group Sold Products By The Date" with its sell_date column, or "Article Views I" with view_date. Range-shard on the date and today's shard absorbs every new write while last year's shards go cold. You built a system where one shard is always on fire and the rest are archives.
You have a few standard fixes, and naming them is what earns the point:
- Hash the key so values distribute pseudo-randomly instead of clustering by range or recency.
- Split the hot key by appending a bucket suffix (for example
celebrity_id:0throughcelebrity_id:15) so one entity spreads across several shards, then merge results at read time. - Cache the hot entity in front of the database so most reads never hit the shard at all.
- Give the whale its own shard as a deliberate exception when one tenant dwarfs everyone else.
The interviewer usually will not tell you the celebrity exists. Raising it yourself ("what happens when one author in Article Views has a viral post?") is a strong senior signal.
Range vs hash vs consistent hashing
Most sharding questions reduce to picking a partitioning strategy. Know these three cold and know when each breaks.
| Strategy | How it maps | Strength | Where it hurts |
|---|---|---|---|
| Range | Contiguous key ranges per shard | Fast range scans, easy to reason about | Hotspots on sequential or time keys |
| Hash / modulo | hash(key) % N picks the shard | Even write distribution | Range queries fan out; changing N reshuffles nearly everything |
| Consistent hashing | Keys and nodes on a hash ring | Adding a node moves only a fraction of keys | More moving parts; needs virtual nodes for balance |
Plain modulo sharding has a nasty property that interviewers love to probe: change the shard count from N to N+1 and almost every key hashes to a new home, forcing a near-total data migration. Consistent hashing fixes exactly this. Keys and nodes are placed on a ring, each key belongs to the next node clockwise, and adding or removing a node only relocates the keys between two adjacent points on the ring rather than reshuffling the entire dataset. Virtual nodes (many ring positions per physical node) smooth out the uneven arcs that would otherwise leave some nodes overloaded.
If you are shaky on the hashing mechanics underneath all of this, our Hash Table interview questions are worth a pass, because the same distribution intuition drives both.
Rebalancing without downtime
The follow-up you should expect: "Traffic doubled, add capacity." The naive answer (rehash everything into more shards) implies a giant migration and downtime. The strong answer keeps the system serving reads and writes throughout.
A production-grade migration usually looks like this:
- Pick a strategy that limits movement. Consistent hashing or a logical-shard indirection layer (many small logical shards mapped onto fewer physical nodes) means you move a fraction of data, not all of it.
- Dual-write and backfill. Start copying the affected key range to the new shard while continuing to serve from the old one. Backfill historical rows in the background.
- Verify, then cut over. Compare row counts and checksums, flip the routing layer to the new shard, and keep the old copy briefly as a rollback path.
- Reclaim. Once traffic is stable on the new mapping, delete the migrated rows from the old shard.
The trick that makes this manageable is the routing layer. Applications should never compute shard = hash(key) % N inline. Put a lookup service or a logical-to-physical shard map in between, so rebalancing is a change to that map rather than a redeploy of every client. Pre-splitting into many logical shards up front (say 1024) means "adding a machine" is just reassigning some logical shards, with no rehash at all.
When you present this, state the invariant explicitly: at every step the system serves live traffic, and any single step can be rolled back. That is the property interviewers are actually grading.
How to answer under time pressure
Structure beats trivia here. Walk the interviewer through the same order every time:
- Confirm you actually need to shard (size or throughput exceeds one node).
- Name the access pattern, then choose a shard key that serves it.
- Call out the hotspot risk for that key and your mitigation.
- Pick range, hash, or consistent hashing and justify it.
- Explain how you add capacity without downtime.
If you narrate those five steps, you have covered what almost every sharding question is really asking, even when it is dressed up as "design Twitter's timeline" or "design a URL shortener."
FAQ
What is the difference between sharding and partitioning?
Partitioning is the general idea of splitting a table into pieces; it can happen inside a single database (multiple partitions on one server). Sharding specifically distributes those pieces across multiple servers so you scale beyond one machine's CPU, memory, and disk. All sharding is partitioning, but not all partitioning is sharding.
How do I pick a shard key in an interview?
Start from the query you run most often and choose a high-cardinality key that lets that query hit a single shard. Then stress-test it: ask what happens if one value gets far more traffic than the rest. If a plausible hotspot exists, either hash the key or add a bucket suffix to spread the hot entity.
What is consistent hashing and why does it matter?
Consistent hashing places both keys and nodes on a ring so that adding or removing a node only relocates the keys near that node, not the whole dataset. It matters because plain hash(key) % N sharding remaps almost everything when the shard count changes, making scaling painful. Virtual nodes are added to keep the load balanced across physical machines.
How do you rebalance shards without downtime?
Route through an indirection layer, dual-write to the new shard while backfilling old data, verify with checksums, then flip the routing map and keep the old copy as a rollback path. Pre-splitting into many logical shards up front turns "add a server" into a cheap remapping instead of a full rehash. The key property to state is that live traffic is served at every step.
Do I need to know sharding for coding rounds too?
Rarely in the pure algorithm rounds, but the underlying distribution and hashing intuition overlaps heavily with hash table problems. If sharding comes up at all in a coding context, it is usually as a design discussion attached to a data-heavy problem rather than something you implement from scratch.