System design deep dive
Database Sharding: deep dive
Sharding splits one logical database across many machines so that storage and write throughput grow with the number of nodes. It is powerful but expensive in complexity, so strong candidates explain both how to shard and why to delay it as long as possible.
How a sharded query works, step by step
- The application receives a request, for example load the orders of user
u_9182. - It extracts the shard key from the request. Here the shard key is
user_id, chosen because nearly every query is scoped to one user. - A routing layer maps the key to a shard: it hashes the key into one of, say, 4,096 logical buckets, then looks up which physical database currently owns that bucket.
- The query runs on that single shard, using ordinary indexes and transactions, because all of that user's rows live together.
- Queries without the shard key (for example all orders placed today) must scatter to every shard and gather the results, which is slow and is usually pushed to an analytics store or search index instead.
The logical bucket layer is the trick that makes resharding tolerable. Because there are many more buckets than machines, adding capacity means moving whole buckets to the new machine and updating the bucket map, rather than rehashing every row with a new modulus.
When to shard (and when not to)
Shard only when a single primary can no longer keep up after you have done the cheaper things: added indexes, moved reads to replicas, cached hot reads, archived cold data, and scaled the machine vertically. A modern server with 64 cores and NVMe storage running Postgres can handle tens of thousands of writes per second and several terabytes of data. Typical triggers are a dataset approaching the limit of one disk, write throughput the primary cannot sustain, or backups and index rebuilds that take too long on a single huge node.
Choosing a shard key
| Candidate key | Good when | Risk |
|---|---|---|
| user_id | Most queries are about one user (orders, settings, messages) | Power users with huge data create heavy shards |
| tenant_id | B2B SaaS where tenants are isolated | One giant customer can outgrow a shard |
| created_at | Time-series data queried by range | All new writes hit the newest shard (write hotspot) |
| hash(order_id) | Even spread of write load is the priority | Fetching a user's orders becomes scatter-gather |
| geo region | Data residency and latency by country | Regions are very different sizes |
A good shard key has high cardinality, spreads load evenly, and matches the dominant query so that most requests touch exactly one shard. If two entities are always queried together, such as a user and their orders, co-locate them by sharding both on user_id.
Resharding without downtime
- Create the new shard and start copying the buckets it will own from a snapshot.
- Stream ongoing changes for those buckets (change data capture) so the copy catches up.
- Briefly pause writes for the moving buckets, confirm the copy is identical, and flip the bucket map.
- Route new traffic to the new shard, then delete the old copies once verified.
Common mistakes in interviews
- Proposing sharding on day one for a system that fits comfortably on one machine with replicas.
- Using
hash(key) % Ndirectly on physical nodes, which forces nearly all data to move when N changes. Use logical buckets or consistent hashing. - Choosing a shard key that makes the main query scatter to every shard.
- Forgetting that auto-increment IDs no longer work globally; you need a distributed ID scheme such as Snowflake-style IDs.
- Ignoring cross-shard transactions, which require sagas or two-phase commit and should be designed away where possible.
Interview questions
›What is the difference between sharding and partitioning?
Partitioning is the general idea of splitting a table into pieces, which can all live on one server (such as Postgres table partitioning by month). Sharding is horizontal partitioning across separate machines, so each piece has its own CPU, memory, and disk. Sharding scales capacity; single-node partitioning mostly helps query pruning and maintenance.
›How do you handle a celebrity user whose data overloads one shard?
Detect such keys and give them special treatment: split their data with a sub-key (such as user_id plus a bucket number), place them on a dedicated shard, or cache their hot reads aggressively. The general shard key stays the same for everyone else.
›How do you generate unique IDs across shards?
Use 64-bit IDs composed of a timestamp, a machine or shard ID, and a per-millisecond sequence, like Twitter Snowflake. They are unique without coordination and roughly time-ordered, which keeps indexes efficient. UUIDs also work but are larger and random, which hurts B-tree locality.
›How do you run a query that needs data from all shards?
The router fans the query out in parallel and merges the results, applying sorting and limits after the merge. Because tail latency is set by the slowest shard, frequent cross-shard queries are better served by a separate denormalized store such as Elasticsearch or a data warehouse fed by change data capture.
›Does sharding replace replication?
No, they solve different problems and are used together. Sharding spreads data and write load across machines, while replication keeps copies of each shard for fault tolerance and read scaling. A typical setup is N shards, each a primary with two replicas.