Database sharding — splitting data across machines

Replication buys you more read capacity from copies of the same data. Sharding is the different, harder move — splitting the data itself across machines so no single one holds all of it.

Advanced

4 min read

The problem sharding solves that replication can't

A read replica is a full copy of the database — it helps when you have more reads than one machine can serve, but every replica still has to store the entire dataset, and every write still has to happen on the primary. Once the data itself is too large for one machine, or writes alone exceed what one machine can handle, replication stops helping — you need to split the data itself across multiple machines, so each one holds only a fraction of it. That's sharding: horizontal partitioning of rows across separate database instances, each called a shard.

The central decision: the shard key

Every row needs a rule that deterministically decides which shard it lives on — the shard key (also called a partition key). Pick user_id, and every row belonging to a given user lands on the same shard, consistently, every time:

shard_index = hash(user_id) % number_of_shards

The shard key is the single most consequential decision in a sharded design, because it determines which queries stay fast (anything filtered by the shard key hits exactly one shard) and which become expensive (anything that doesn't hits every shard and merges the results) — and it's very hard to change after the data already exists, since changing it means physically moving data between machines.

Why the wrong shard key creates a hot shard

shard_index = hash(country) % number_of_shards

If most users are in one country, that shard takes most of the traffic while others sit nearly idle — a hot shard, and it defeats the entire point of sharding, which is spreading load evenly. A good shard key distributes both data volume and query traffic roughly evenly across shards; user_id or a similar high-cardinality, evenly-distributed field is a common choice specifically because no single value dominates the distribution the way country or status would.

What gets expensive: cross-shard queries

-- fast: user_id is the shard key, hits exactly one shard
SELECT * FROM orders WHERE user_id = 42;
 
-- expensive: no shard key in the filter, must query every shard and merge
SELECT * FROM orders WHERE product_id = 7 ORDER BY created_at DESC LIMIT 10;

A query that doesn't filter by the shard key has to fan out to every shard, wait for all of them to respond, and merge the results in the application layer — turning one query into N, bounded by the slowest shard. This is the real cost of sharding: queries that used to be a single-machine JOIN now sometimes require application-level coordination across machines, and JOINs across two tables that live on different shards generally aren't possible as a single database query at all.

The other real cost: cross-shard transactions

A transaction that needs to atomically update rows on two different shards can't rely on a single database's normal ACID transaction guarantee, since no single database instance sees both rows. This is a genuinely hard distributed-systems problem — solved with patterns like two-phase commit or a saga (a sequence of local transactions with compensating actions if a later step fails), both meaningfully more complex than a normal transaction, and both reasons schema and shard-key design try hard to keep related data that needs to change together on the same shard.

Resharding: the operational cost of getting the key wrong

Adding more shards later means moving data between machines to rebalance — every row's shard assignment can change if you naively reshard with hash(key) % N and change N, since the modulo result shifts for nearly every key. Consistent hashing exists specifically to minimize this: it maps both shards and keys onto a ring, so adding or removing one shard only requires moving the keys that fall in the affected section of the ring, not nearly all of them.