Skip to content
Velloreth
Technology 4 min read

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

Sharding versus replication

Replication copies the same data to several servers.

Sharding splits the data across servers.

ReplicationSharding
Data per serverall of ita subset
Scalesreadswrites and storage
Failoveryesper shard
Complexitymoderatehigh

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:

OptionScales
An indexa slow query
A cachea repeated read
A read replicaread traffic
Archivingthe table size
Object storagea large blob
A bigger servereverything, 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:

  1. Add the shard key to every table
  2. Route the reads by the key, with a fallback
  3. Route the writes by the key
  4. Backfill the shards
  5. Verify the counts
  6. 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.

For the related topics, see database-indexes-explained and database-normalization. For the broader context, cap-theorem-explained and transactions-and-acid.

Frequently asked questions

What is database sharding?
It is splitting one logical table across several database servers, where each server holds a subset of the rows. A shard key decides which row goes where. It is used when a single server cannot hold the data or handle the write load, and it is the last step after indexes, caching and read replicas.
What is a good shard key?
One with high cardinality, an even distribution, and a match to the common query. 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 it concentrates the writes on the newest shard.
What is the difference between sharding and replication?
Replication copies the same data to several servers, which scales reads and provides a failover. Sharding splits the data across servers, which scales writes and storage. They are often combined: each shard is replicated.
What is consistent hashing?
It is a scheme where a key and a node are both mapped onto a ring, and a key belongs to the next node clockwise. Adding a node moves only the keys between it and its predecessor, rather than rehashing every key. It is the standard way to add a shard without a full rebalance.
What is a cross-shard query?
A query that must read from more than one shard, such as a count of all users or a join across two shard keys. It is slow, because it fans out and merges. The design goal is to keep the common queries within one shard.
When should I shard?
When a single server is genuinely the limit, after the cheaper options are exhausted. Add an index, add a cache, add a read replica, archive the old rows, and move a large blob to object storage. Sharding adds a routing layer, a rebalancing problem and a cross-shard query problem, so it is the last resort.