Sharding
Splitting one dataset across separate machines, and the queries that stop being possible once you do.
Overview
What it is
Sharding splits a dataset across independent database servers. Each shard holds a subset of the rows and knows nothing about the others.
The distinction from [partitioning](partitioning_in_databases.html) is the machine boundary, and it is the whole difficulty. Partitions share a query planner, a transaction manager and a lock table. Shards share nothing, so anything that needed a global view has to be rebuilt in the application or given up.
Sharding
Worth knowing
Sharding
The last resort of scaling, what it buys, and the four things it takes away.
The shard key
One column decides where a row lives. Usually the key is hashed and the remainder taken modulo the shard count, so a routing rule might be shard = hash(customer_id) % 4.
The query above computes exactly that for a set of customers, and the variants show what different access patterns cost.
The consequence is stark and worth stating as a rule:
A query that names the shard key touches one node. A query that does not touches all of them.
A lookup by customer_id is a single-node request and stays fast as the fleet grows. A report grouped by product_id has to be sent to every shard and the partial results merged — a *scatter-gather*, whose latency is the slowest shard's, not the average.
So the shard key is not a schema detail. It is a decision about which queries stay cheap forever and which never will, and it is extremely expensive to change afterwards.
What you give up
Cross-shard joins. Customers on shard 1, orders on shard 3, and no engine can join them. Either the join moves into the application, or related data is deliberately placed together — sharding orders by customer_id so a customer's orders live beside them.
Global uniqueness. UNIQUE(email) cannot be enforced across independent servers. The usual answers are a separate lookup service that owns emails, or generating ids that are unique by construction — UUIDs, or Snowflake-style ids with a shard number embedded.
Cross-shard transactions. ACID stops at the shard boundary. Spanning shards means two-phase commit, which is slow and introduces a coordinator that can fail mid-protocol, or sagas, which are eventually consistent with explicit compensation. Most teams design so that transactions never span shards.
AUTO_INCREMENT. Every shard would start at 1. Ids must come from elsewhere.
Rebalancing
Adding a shard when the rule is hash(key) % 4 changes it to % 5, and almost every row's destination moves. Migrating the whole dataset while serving traffic is the single worst part of operating a sharded system.
Two designs avoid it. Consistent hashing places shards on a ring so adding one moves only the keys in its arc, rather than reshuffling everything. Virtual buckets hash keys into a fixed large number of buckets — say 1024 — and map buckets to physical shards; adding a shard moves a few buckets and the hash rule never changes. The bucket approach is what most modern systems use, because moving is then a matter of copying a bucket and updating a map.
Do the cheaper things first
Sharding is close to irreversible and touches every part of the application. Before it:
- Index properly. A great many "we need to shard" conversations end at a missing [composite index](composite_and_covering_indexes.html).
- Read replicas, if reads dominate. Cheap, and reversible.
- Caching, for the hot small set.
- Partitioning, if one table is the problem.
- A bigger machine. Unglamorous, and modern hardware goes a very long way.
Shard when writes exceed what one machine can take, or the working set no longer fits in memory anywhere, and not before.
Why naive rebalancing is the nightmare
"Almost every row's destination moves" is not hyperbole, and the number is the reason consistent hashing and virtual buckets exist. With the rule hash(key) % N, adding one shard changes the divisor, and a key moves whenever hash % N differs from hash % (N+1) — which is almost always:
| change | keys that must move |
|---|---|
% 4 → % 5 | ~80% |
% 100 → % 101 | ~99% |
Measured over two hundred thousand random keys, the % 4 to % 5 change relocated 79.8% of them, and it only gets worse as the fleet grows. Consistent hashing and virtual buckets bring that down to roughly 1/(N+1) — about 20% and 1% for the two rows above — because a new shard takes a slice from its neighbours instead of forcing every key to recompute against a new divisor. Moving 1% of the data to add a machine is an afternoon; moving 99% while serving traffic is the project everyone dreads.
The slowest shard sets the latency
"Scatter-gather latency is the slowest shard's, not the average" is a statement about tail probability, and the arithmetic is unforgiving. If each shard answers within its target 99% of the time, a query that must wait for *all* of them is fast only if *every* shard was fast — and that probability falls as the fan-out grows:
| shards in the fan-out | each 99% fast | chance the query hits a slow shard |
|---|---|---|
| 10 | 0.99 | 9.6% |
| 100 | 0.99 | 63.4% |
| 100 | 0.999 | 9.5% |
A hundred-shard query with per-shard 99% latency is slow *most of the time*, because 0.99⁹⁰ is only 0.366. The fan-out has turned a rare per-shard event into the common case, and the only cures are to make each shard far more reliable (the third row) or to avoid the fan-out by putting the shard key in the query. This is why a single degraded node makes an entire sharded tier feel slow, and why "the average shard is fast" is the wrong thing to measure.
Where it goes wrong
A shard key with skew. Sharding by country puts most of the traffic on one node. Check the distribution of real values.
Sharding by a key the queries do not use. Every query becomes scatter-gather, and you have bought distributed-systems problems for no throughput.
Assuming transactions still work. They work within a shard. Across shards they do not, and code written before sharding usually assumes otherwise.
Forgetting the slowest shard sets the latency. One degraded node makes every fan-out query slow.
Check yourself
0 of …Answer without scrolling back up.
What is the difference between partitioning and sharding?
What happens to a query that does not name the shard key?
Why do virtual buckets make rebalancing easier?
Sharding
The distinction from [partitioning](partitioning_in_databases.html) is the machine boundary, and it is the whole difficulty. Partitions share a query planner, a transaction manager and a lock table. Shards share nothing, so anything that needed a global view has to be rebuilt in the application or given up.