How Would You Shard This Database? Six Decisions, in Order
"We will shard it" is the most common hand-wave in a system design interview. Here is how to actually answer: whether to shard at all, how to choose the shard key, how keys map to shards, how queries find them, what breaks across shards, and how you grow without rehashing everything.
“We will shard the database” is the sentence candidates reach for the moment the numbers get large. It sounds like what big systems do, and it ends the conversation about scale, or so the candidate hopes. Then the interviewer asks “sharded by what?”, and the next three minutes decide whether the candidate has done it or only read the word.
Sharding is not one decision. It is six, taken in order, and the first one is whether to do it at all. This post walks through all six with a running example, the trade-off table for each, the queries that stop working, how to grow without moving every row, and the sentence that answers the question in thirty seconds.
The Question
The direct forms:
- “This table has 20 billion rows and 30,000 writes a second. How would you shard it?”
- “How would you scale the database for this design?”
- “Your single Postgres is at 80 percent CPU (Central Processing Unit). What do you do?”
The follow-ups that turn into it:
- “What is your shard key?”
- “How do you look up a user by email if you sharded by user ID?”
- “One customer is a thousand times bigger than the rest. Now what?”
- “You need to go from 8 shards to 16. Walk me through it.”
- “How do you keep usernames unique across shards?”
What They Are Really Asking
- Do you know when not to shard? Sharding is a one-way door. The interviewer wants to hear the cheaper options first, and a reason the numbers have outgrown them.
- Can you choose a shard key from the access pattern? The key is the whole decision. A candidate who picks it from the query that matters most, and names the queries it breaks, has done this before.
- Do you know what you lose? Joins, transactions, unique constraints, and auto-increment IDs all stop working across shards. Naming them before you are asked is the senior signal. The companion post explains why each one breaks.
- Can you grow without a rewrite?
hash(key) % Nworks until N changes. The interviewer wants to hear how you add capacity without moving every row. - Do you know the operational cost? Every shard is a database with replicas, backups, and migrations. Eight shards is twenty-four instances to run.
The Gotchas
Gotcha 1: Sharding first. Most databases that “need sharding” need an index, a cache, read replicas, or a bigger machine. A single modern server has 64 or more cores and a terabyte of memory. Sharding before you have exhausted those adds permanent complexity to solve a problem you do not have.
Gotcha 2: Choosing the key by cardinality alone. A key with millions of distinct values spreads data evenly, but if your main query does not include it, every request becomes a query to every shard. The key has to match the access pattern, not just the distribution.
Gotcha 3: Sharding by time or by a sequential ID with ranges. All of today’s writes land on the newest shard while the old ones sit idle. You have built a single-node database with extra steps. Hash the key, or put something high-cardinality in front of the time.
Gotcha 4: hash(key) % N over the physical shard count. Going from 8 shards to 9 changes the shard for roughly eight keys in nine. Resharding becomes moving almost the whole data set. Hash into a fixed, large number of logical shards and map those onto machines.
Gotcha 5: Forgetting the queries that do not have the key. “Find user by email” when you sharded by user ID is a query to every shard. Name the secondary access patterns and give each a plan: a global index, a read model, or an accepted scatter-gather for rare admin queries.
Gotcha 6: Assuming joins, transactions, and unique constraints still work. They work within a shard. Across shards, joins become application code, transactions become sagas or two-phase commit, and uniqueness needs its own sharded table.
Gotcha 7: Forgetting the whale. The largest tenant, the celebrity account, the viral product. A hash spreads keys evenly, not load. One key still lives on one shard, so plan for the outlier: a dedicated shard, a split tenant, or a cache in front.
Gotcha 8: Auto-increment IDs per shard. Each shard counts from one and the IDs collide. Use a global ID scheme: Snowflake-style IDs, UUIDs (Universally Unique Identifiers), or ID ranges handed out by a central service.
Gotcha 9: No resharding plan. The day you need more capacity is the worst day to design the migration. Say how a logical shard moves (copy, catch up, brief write pause, cut over) before the interviewer asks.
Gotcha 10: Forgetting that each shard is a full database. Each one needs replicas for failover, backups, monitoring, and every schema migration. Sharding multiplies operations, and that cost is the honest reason most teams use a managed system instead of building their own.
How to Answer
The running example: a profile service. Users, their settings, and their preferences, in one Postgres. It has grown to 2 billion users, 15 TB, and 20,000 writes per second at peak because of a “last active” field and preference syncing. The primary is out of headroom.
Decision 0: Should you shard at all?
Say this first, even when the answer is yes, because it shows you know sharding is the expensive option:
| Symptom | Try first | Why it is cheaper |
|---|---|---|
| Too many reads | Cache, read replicas | Copies need no routing, no shard key, no rebalancing |
| Slow queries | Indexes, query tuning, a bigger machine | One box goes very far |
| Too much data | Archive cold rows, move large unrelated tables to their own database | Splitting by table (vertical partitioning) needs no key |
| Too many writes | Batch writes, move write-heavy data to a store built for it | The “last active” field may not belong in the profile table at all |
Then the threshold: shard when writes or the data set exceed what one primary can hold, and the growth curve says it will not come back.
For the example, the honest first move is to pull “last active” out into a separate key-value store, which removes most of the write load. Assume the interviewer says “and it still does not fit.” Now you shard.
Decision 1: The shard key
The shard key decides which shard a row lives on. Everything else follows from it. A good key has three properties:
- High cardinality and an even spread. Many distinct values, none dominant.
- It is in the main query. The most frequent query carries the key, so it goes to exactly one shard.
- It keeps related data together. Rows read or updated together share the key, so they live on the same shard and joins and transactions among them still work.
The third property is the one candidates miss, because every large table gets split, not just one. With four shards, each shard holds a quarter of users and a quarter of orders. The question is which quarter. Shard users by user_id and orders by order_id, each by its own primary key, and a user’s row lands on one shard while each of their orders lands wherever its own ID hashes. “Show this user’s orders with their name” now needs every shard. Shard orders by its user_id column instead, and the user and all of their orders hash to the same shard, where the join runs as it always did. This is co-location: different tables split along the same key.
Not every table is split. Small reference tables (countries, currencies, categories) are copied whole to every shard so any shard can join to them locally. Large tables that belong to no single entity, such as a product catalog, are sharded by their own key or kept in a separate database, and joins to them happen in application code.
For the profile service, the key is user_id. Millions of distinct values, the main query is “get profile for user X,” and a user’s settings and preferences can carry the same key so they sit on the same shard. What it breaks: lookup by email and lookup by username, which come in Decision 4.
Keys by system, and the ones that look right but are not:
| System | Good key | Tempting but wrong | Why the wrong one fails |
|---|---|---|---|
| Profiles | user_id |
country |
A few countries hold most users |
| URL shortener | short_code |
created_at |
Every new link lands on one shard |
| Chat | conversation_id |
sender_id |
A conversation’s messages scatter across shards and ordering breaks |
| Multi-tenant SaaS (Software as a Service) | tenant_id |
user_id |
Every tenant-wide query becomes scatter-gather |
| Orders | customer_id |
order_date |
Today’s orders all hit one shard |
| Metrics | series_id plus time bucket |
metric_name |
One popular metric overloads one shard |
A compound key is common: (tenant_id, user_id) keeps a tenant together while letting a giant tenant be split by the second part later.
Decision 2: How keys map to shards
| Strategy | How it works | Good at | Bad at |
|---|---|---|---|
| Range | Key ranges per shard: 1 to 1M on shard 1, and so on | Range scans; splitting a hot range | Sequential keys make the newest shard hot |
| Hash modulo N | hash(key) % N |
Even spread, simple | Changing N moves almost every key; range scans hit every shard |
| Consistent hashing | Keys and nodes on a ring; a key belongs to the next node clockwise | Adding a node moves about 1/N of keys | More complex; needs virtual nodes to balance |
| Directory | A lookup table maps each key or range to a shard | Full control; move one tenant anywhere | The directory is a critical dependency and must be cached and replicated |
The practical default is hash into many logical shards, then map logical shards to physical machines:
flowchart LR
K["user_id = 81723"] --> H["hash(user_id) % 1024"]
H --> L["logical shard 377"]
L --> M[("shard map<br/>377 → cluster 3")]
M --> C3[("cluster 3<br/>primary + 2 replicas<br/>logical 256..383")]
Pick a logical count far above any physical count you will ever need, such as 1,024 or 4,096. Start with 8 clusters holding 128 logical shards each. To grow, move whole logical shards to new clusters and update the map. The hash function never changes, so no key is ever rehashed. Vitess, Citus, Cassandra, and DynamoDB all do a version of this.
Range sharding is the right choice when range scans are the main access pattern, such as time-series data keyed by (series_id, time). Systems like Bigtable, HBase, CockroachDB, and Spanner use ranges and split a range automatically when it gets hot or large.
Decision 3: Routing
Something has to turn a key into a connection to the right database.
| Option | How | Trade-off |
|---|---|---|
| Library in the application | The application computes the shard and connects directly | Fastest; every service needs the library and the current shard map |
| Routing proxy | Applications talk to a proxy that parses queries and forwards them (Vitess VTGate, ProxySQL) | Applications barely change; one more network hop |
| The database routes | Distributed SQL (Structured Query Language) such as CockroachDB, Spanner, YugabyteDB, Citus | Simplest for the application; you accept that database’s model and cost |
The shard map itself lives in a small, strongly consistent store (etcd, ZooKeeper, or a replicated table), cached by every router. Moving a shard is a map update, so the map’s version matters: a router with a stale map must get a “wrong shard” error and refresh, not write to the old location.
Decision 4: Queries without the shard key
This is where sharding hurts, and where interviewers push. Three tools:
| Tool | How | Use when | Cost |
|---|---|---|---|
| Scatter-gather | Send the query to every shard, merge the results | Rare queries: admin tools, reports | Latency is the slowest shard; load multiplies by shard count |
| Global secondary index | A separate table email → user_id, sharded by email |
Hot lookups by another unique field | One extra hop; kept in sync asynchronously, so briefly stale |
| Separate read model | Stream changes into a search index or warehouse | Search, filtering, analytics | Eventually consistent; another system to run |
For the profile service: login by email goes through a global index sharded by email. “Search users by name” goes to a search index fed by CDC (Change Data Capture). “Count users per country” goes to the warehouse. None of them touch every shard on the hot path.
The global index has a write-ordering question worth answering out loud. On signup, write the index entry first and the profile second. If the second write fails, an orphaned index entry points at nothing, and a cleanup job removes it. The reverse order leaves a profile no one can log in to.
Decision 5: What breaks across shards
Name all four. Interviewers often ask about one of them, and listing the set shows you know the whole cost. All four break for the same reason: one engine no longer sees every row. What sharding takes away walks through the mechanism for each.
| What breaks | Within one shard | Across shards | What to do |
|---|---|---|---|
| Joins | Work normally | Not available when the matching rows are on different shards, which is what happens when the two tables are sharded by different keys | Shard related tables by the same key (co-location); copy small reference tables to every shard; denormalize the few fields you need; join in application code |
| Transactions | ACID (Atomicity, Consistency, Isolation, Durability) as usual | Two-phase commit (slow, blocking) or none | Choose the key so the transactions that matter stay on one shard; use a saga for the rest |
| Unique constraints | Enforced by the database | Only per shard | A uniqueness table sharded by the unique value (the global index above, with a unique constraint) |
| Auto-increment IDs | Fine | Collide | Snowflake-style IDs (time, machine, sequence), UUIDs, or ranges from a central allocator |
The co-location point is the one that saves designs. If “transfer credits between two users” is a core operation and the users are on different shards, every transfer is a distributed transaction. If credits belong to an account keyed so both sides share a shard, it is a local one. When the core operations cannot be co-located by any key, that is a sign the data model, not the sharding scheme, needs rethinking.
Decision 6: Growth and operations
Moving a logical shard (resharding without downtime):
sequenceDiagram
participant R as Router / shard map
participant Old as Old cluster
participant New as New cluster
Old->>New: 1. bulk copy logical shard 377
Old->>New: 2. stream changes (binlog / WAL) until caught up
R->>Old: 3. pause writes to 377 (seconds)
Old->>New: 4. drain the last changes, verify
R->>R: 5. map: 377 → new cluster, bump version
R->>New: 6. resume writes
Note over Old: 7. delete 377 after a safety window
The write pause is seconds, and only for one logical shard, which is why the logical layer exists.
Hot shards. Monitor load per logical shard, not just per machine. When one goes hot:
| Cause | Fix |
|---|---|
| A hot logical shard among cool ones | Move it to its own machine |
| A whale tenant | Give the tenant a dedicated shard, or split it by a second key part |
| Hot reads on one key | Cache it: in-process cache, CDN (Content Delivery Network), or replicated copies of the key |
| Hot writes on one key | Split the value (sub-counters summed on read) or batch the writes |
Every shard is a database. Each needs a primary and replicas for failover. Copying and sharding combine: 8 shards with 3 nodes each is 24 instances. Schema migrations must run on every shard and tolerate partial failure. Backups are per shard, and a consistent snapshot across all of them at one instant is hard; if you need one, say so early.
Migrating from one database to a sharded one
If the interviewer asks how you get there from a live single database, the shape is the same as a shard move, done once for everything:
- Prepare the data. Make sure every table carries the shard key, and switch ID generation to a global scheme first.
- Route reads and writes through the shard layer while it still points at one database. This proves the application code works with routing before any data moves.
- Copy and stream. Bulk copy into the new shards and tail the old database’s log to keep them current.
- Shadow reads. Read from both, compare, and log differences.
- Cut over shard by shard, with a short write pause per shard.
- Keep the old database read-only for a safety window, then retire it.
Build or buy
Building sharding yourself is rarely the right answer in an interview or in practice. Name the options:
| System | Model | When it fits |
|---|---|---|
| Vitess | Sharded MySQL with a routing proxy | You are on MySQL and need horizontal scale (YouTube, Slack, GitHub run it) |
| Citus | Sharded Postgres as an extension | You are on Postgres and the data has a clear tenant or entity key |
| CockroachDB, Spanner, YugabyteDB | Distributed SQL with automatic range splitting | You want SQL and transactions across shards and accept the latency |
| DynamoDB, Cassandra | Partitioned by design; you choose the partition key | Key-value access patterns at large scale |
The managed systems do not remove the decisions. You still choose the key, still plan for queries without it, and still own the whale.
The worked answer
“Before sharding, I would pull the ‘last active’ writes out of the profile table, since they are most of the write load. Assuming it still does not fit: the shard key is
user_id, because the main access pattern is per user, it spreads evenly, and a user’s settings can share it so their joins stay local. I would hash into 1,024 logical shards on 8 clusters, each a primary with two replicas, so growth is moving logical shards and never rehashing. Login by email uses a global index sharded by email, which also enforces uniqueness. Search goes to a search index fed by CDC. IDs come from a Snowflake-style generator. I would use Vitess rather than build routing myself. The risk I would watch is hot keys from celebrity accounts, so profile reads go through a cache.”
Thirty seconds. Why shard, the key and why, the mapping, the queries that do not fit, what replaced the broken features, operations, and the main risk.
Follow-Up Questions to Expect
- “Why not just use a bigger machine?” Often you should, and say so. Vertical scaling has no routing, no lost joins, and no new failure modes. Shard when the growth curve crosses the largest machine you can buy within the planning horizon, or when one primary’s failover time is no longer acceptable.
- “What if most queries need two different keys?” Pick the key for the hottest or most correctness-critical path, and serve the other through a global index or a second copy of the data sharded the other way. Two copies cost storage and a sync pipeline; that is usually cheaper than scatter-gather on a hot path.
- “How do you handle a tenant that outgrows a shard?” Move it to a dedicated cluster first. If it outgrows that, extend its key with a second part (
tenant_id, user_id) so it can span several shards, accepting that tenant-wide queries for that one tenant become scatter-gather. - “Consistent hashing or logical shards?” Both solve “adding a node should not move everything.” Logical shards with a map are easier to reason about and to move deliberately; consistent hashing suits systems where nodes come and go often, such as caches. Either answer is fine with the reason.
- “How do you do a transaction across two shards?” First, ask whether the key can be chosen so it is not needed. If not, a saga with compensations for business processes, or two-phase commit if the database supports it and the latency is acceptable. Say which, and what the user sees on failure.
- “You need to go from 8 shards to 16. Walk me through it.” With 1,024 logical shards on 8 clusters, each cluster holds 128. Bring up 8 new clusters and move 64 logical shards from each old cluster to a new one, one at a time: copy, stream changes until caught up, pause writes to that logical shard for seconds, update the map, resume. At the end every cluster holds 64. No key is rehashed and no write pause is longer than one logical shard’s cutover. The contrast to say out loud: with plain
hash(key) % 8, switching to% 16moves half of all rows at once. - “How do you keep usernames unique across shards?” A
usernamestable sharded by username, with a unique constraint on it. Two people claiming the same name hash to the same shard, so that shard’s ordinary unique constraint picks the winner, with no cross-shard coordination. Reserve the name first, then create the profile; if the profile write fails, a cleanup job releases the reservation. A rename reserves the new name, updates the profile, then releases the old one. - “How do you rebalance without downtime?” Copy, stream changes until caught up, pause writes to that one logical shard for seconds, update the map, resume. Routers with stale maps get a “wrong shard” error and refresh.
Key Takeaways
- Sharding is a one-way door. Try indexes, caches, replicas, a bigger machine, and moving write-heavy data out first, and say that you did.
- The shard key is the whole decision: high cardinality, present in the main query, and shared by data that is read or written together.
- Never shard by time or by a sequential ID with ranges unless range scans are the point and the system splits hot ranges.
- Hash into many logical shards and map them onto machines, so growth moves shards instead of rehashing keys.
- Every query without the key needs a plan: a global index, a read model, or an accepted scatter-gather for rare queries.
- Joins, transactions, unique constraints, and auto-increment IDs break across shards. Name all four and what replaces each.
- A hash spreads keys, not load. Plan for the whale.
- Each shard is a full database with replicas, backups, and migrations. Prefer a system that does the routing for you.
Further Reading
- What Sharding Takes Away, the companion post: why joins, transactions, unique constraints, and IDs break, and the rule for which constraints survive
- Martin Kleppmann, Designing Data-Intensive Applications, chapter 6 (Partitioning)
- DeCandia et al., “Dynamo: Amazon’s Highly Available Key-value Store” (SOSP 2007), for consistent hashing with virtual nodes in practice
- Karger et al., “Consistent Hashing and Random Trees” (STOC 1997), the original consistent hashing paper
- Vitess documentation, especially the sections on VSchema and resharding
- Herding elephants: lessons learned from sharding Postgres at Notion, a real migration from one database to logical shards on physical hosts
- How Figma’s databases team lived to tell the scale, vertical partitioning first, then horizontal sharding
- Instagram Engineering, “Sharding and IDs at Instagram” (2012), for logical shards in Postgres schemas and time-based global IDs