Design to Scale
Database Sharding
Spread data across several independent databases by a shard key to scale writes and storage beyond one machine — and avoid the classic traps: hot shards, cross-shard queries and painful resharding.
What you will learn
- Explain when sharding is needed and what it costs.
- Choose a shard key and a strategy (range, hash, consistent hashing).
- Recognise hot shards and see why skew defeats range sharding.
- Compare how many keys move when shards are added.
Before you start
Overview
A national library outgrows its building, so it opens branches. Books by authors A–F go to one branch, G–M to another. Each branch is a full library of its own; the catalogue just tells you which branch to visit.
Sharding splits data across several independent databases (shards), each holding a subset of rows chosen by a shard key — for example account_id. A router (in the application, a proxy such as Vitess, or the database itself) sends each query to the right shard. Unlike read replicas, sharding scales writes and storage, because each shard owns different data.
Run it
A overloads one database. B hashes accounts across shards, then adds a fourth shard at t = 20 s: watch Keys moved by the last resize. C splits by account-id ranges, and the busiest accounts all land on shard 1. D uses consistent hashing for the same resize.
Sharding strategies
| Strategy | How a key is placed | Strength | Weakness |
|---|---|---|---|
| Range | Key ranges per shard (ids 0–249 → shard 1) | Range scans stay on one shard; easy to split a range | Skew and sequential keys create hot shards |
| Hash mod N | hash(key) % N | Even spread, simple | Changing N moves most keys |
| Consistent hashing | Next point on a hash ring (with virtual nodes) | Even spread; adding a shard moves ~1/N of keys | More complex; range queries scatter |
| Directory | Lookup table key → shard | Full control, move single tenants | The directory is a critical dependency |
Choosing the shard key
- High cardinality, even access: many distinct values, none dominating. A celebrity account still makes its shard hot — no strategy can split one key.
- Matches your queries: the most frequent queries should include the key, so they hit one shard.
WHERE email = ?on anaccount_id-sharded table must ask every shard. - Keeps transactions local: data changed together (an account and its orders) should share the key.
- Not monotonic for range sharding: auto-increment ids and timestamps send all new writes to the last shard.
Routing with consistent hashing
public final class ShardRing {
private final TreeMap<Long, String> ring = new TreeMap<>();
public ShardRing(List<String> shards, int virtualNodes) {
for (String shard : shards)
for (int v = 0; v < virtualNodes; v++)
ring.put(hash(shard + "#" + v), shard); // each shard owns many small arcs
}
public String shardFor(String key) {
Map.Entry<Long, String> next = ring.ceilingEntry(hash(key));
return (next != null ? next : ring.firstEntry()).getValue(); // wrap around the ring
}
private static long hash(String s) {
return Hashing.murmur3_128().hashString(s, StandardCharsets.UTF_8).asLong();
}
}Trade-offs
| Benefits | Costs | |
|---|---|---|
| Capacity | Writes, storage and memory scale with the number of shards. | One hot key or a bad key choice concentrates load on one shard. |
| Queries | Single-key queries stay fast and isolated. | Cross-shard queries fan out; joins and global sorting move to the application. |
| Operations | A shard failure affects only its slice of users. | Resharding, backups, schema changes and monitoring multiply by N. |
Production considerations
- Start with more logical shards than servers (e.g. 256 logical on 4 physical) so growth means moving whole logical shards, not rehashing.
- Plan cross-shard reporting from day one: stream changes into a warehouse.
- Monitor load per shard, not just in aggregate — the hottest shard sets your limit.
- Rehearse resharding in staging: double writes, backfill, verify, switch, clean up.