Database Sharding Basics: Splitting Data Across Servers
Sharding splits a table across servers when one is not enough. Learn the shard key, the routing, the rebalancing and the cost.
By Velloreth
Sharding versus replication
Replication copies the same data to several servers.
Sharding splits the data across servers.
| Replication | Sharding | |
|---|---|---|
| Data per server | all of it | a subset |
| Scales | reads | writes and storage |
| Failover | yes | per shard |
| Complexity | moderate | high |
They are combined. Each shard is usually replicated.
The shard key
The shard key decides which row goes where.
The properties of a good key:
- High cardinality — many distinct values
- Even distribution — no hot shard
- Query alignment — the common query filters on it
A user id is a common choice for a multi-tenant application, because most queries filter by user.
A timestamp is a poor choice, because every new row goes to the newest shard, which becomes a hot spot.
A country code is a poor choice if one country is most of the traffic.
A hash of the key distributes evenly, at the cost of losing a range query.
The routing
The application must know which shard holds a key.
A lookup table. A directory maps a key range to a shard.
A hash function. The key is hashed and the result selects the shard.
const shard = hash(userId) % shardCount;
A routing layer. A proxy such as Vitess or Citus routes the query.
The trade-off: a lookup table is flexible and adds a lookup, and a hash is fast and rigid.
Adding a shard
The naive approach rehashes every key.
// Before: 4 shards
shard = hash(key) % 4
// After: 5 shards — almost every key moves
shard = hash(key) % 5
Almost every key moves, which is a full rebalance.
Consistent hashing moves only the keys between the new node and its predecessor.
The ring: a key and a node are both mapped onto a ring, and a key belongs to the next node clockwise.
Virtual nodes improve the balance, because a few physical nodes on the ring can be uneven.
A directory-based approach moves only the ranges that are reassigned, which is the most controllable.
The hard parts
A cross-shard query. A count of all users, or a join across two shard keys, must fan out and merge.
A cross-shard transaction. A transaction that touches two shards needs a two-phase commit or a saga, which is slower and more complex.
A unique constraint across shards. A unique email must be enforced by a separate lookup or by making the email the shard key.
A rebalance. Moving a shard while it serves traffic requires a copy, a catch-up and a switch.
An operational cost. More servers, more backups, more monitoring, more failure modes.
The alternatives
Before sharding:
| Option | Scales |
|---|---|
| An index | a slow query |
| A cache | a repeated read |
| A read replica | read traffic |
| Archiving | the table size |
| Object storage | a large blob |
| A bigger server | everything, up to a point |
A single modern server holds a very large table and handles a high write rate. Sharding is often reached for before it is needed.
The patterns
Shard by tenant. A multi-tenant application shards by the tenant id, which keeps each tenant’s data together.
Shard by user. A social application shards by the user id, and a query for one user’s data is one shard.
Shard by geography. A latency-driven choice, with a data-residency benefit.
A directory service. A separate service maps a key to a shard, which allows a rebalance without an application change.
The migration
A sharding migration is a project, not a change.
The steps:
- Add the shard key to every table
- Route the reads by the key, with a fallback
- Route the writes by the key
- Backfill the shards
- Verify the counts
- Remove the fallback
A dual-write period is common, where a write goes to both the old and the new location.
The verification is the step that is skipped and the one that matters.
Common mistakes
Sharding before the cheaper options. A large cost for a problem an index would solve.
A low-cardinality shard key. A hot shard.
A timestamp shard key. Every write goes to the newest shard.
A cross-shard query on the hot path. A slow page.
A cross-shard transaction. A saga where a transaction would do.
A naive rehash to add a shard. A full rebalance.
No rebalance plan. The shards drift out of balance.
No verification after the migration. A silent data loss.
A unique constraint that is not enforced. A duplicate across shards.
A quotable summary
Sharding splits one logical table across servers, where a shard key decides which row goes where, and it scales writes and storage where replication scales reads. A good shard key has high cardinality, an even distribution and a match to the common query, and consistent hashing adds a shard without rehashing every key. A cross-shard query is slow and a cross-shard transaction is hard, so shard last, after indexes, caching, a read replica and archiving.
Related reading
For the related topics, see database-indexes-explained and database-normalization. For the broader context, cap-theorem-explained and transactions-and-acid.