The shard key
One column decides where a row lives. often 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 finds just 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 doesn't 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 each shard and the partial results merged — a *scatter-gather*, whose latency is the slowest shard's, not the mean.
So the shard key isn't a schema detail. It's a choice about which queries stay cheap forever and which never will, and it's extremely costly 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 use, or related data is on purpose placed together — sharding orders by customer_id so a customer's orders live beside them.
Global uniqueness. UNIQUE(email) can't be enforced across free servers. The usual answers are a separate lookup service that owns emails, or making ids that are unique by build — 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 adds a coordinator that can fail mid-protocol, or sagas, which are eventually steady with clear compensation. Most teams design so that transactions never span shards.
**AUTO_INCREMENT.** each 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 each 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. Steady hashing places shards on a ring so adding one moves only the keys in its arc, rather than reshuffling all. 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 each part of the use. Before it:
- Index properly. A great many "we need to shard" conversations end at a missing composite index.
- Read replicas, if reads dominate. Cheap, and reversible.
- Caching, for the hot small set.
- Splitting, 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 each row's destination moves" isn't hyperbole, and the number is the reason steady hashing and virtual buckets exist. With the rule hash(key) % N, adding one shard changes the divisor, and a key moves when 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. Steady 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 each 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 mean" is a statement about tail chance, and the math 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 *each* shard was fast. And that chance 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 solid (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 mean shard is fast" is the wrong thing to measure.
Questions people ask about Sharding
What is sharding? Splitting data across multiple database servers, so each holds only part of it. Each part is called a shard.
When should I shard? Only after cheaper options fail: better indexes, read replicas, caching and a bigger machine. Sharding adds a lot of complexity.
What makes a good shard key? One that spreads data and traffic evenly and appears in most queries. A bad key creates a hot shard that takes most of the load.
Why are cross-shard joins hard? The rows live on different servers, so the join has to move data over the network or be done in application code.
What is rebalancing? Moving data between shards when you add or remove servers. Consistent hashing reduces how much data has to move.
Why does the slowest shard set the latency? A query that fans out to every shard must wait for all of them. One slow shard delays the whole answer.
Where it goes wrong with Sharding
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 don't use. each query turns scatter-gather, and you've bought spread-systems problems for no throughput.
given transactions still work. They work within a shard. Across shards they don't, and code written before sharding often assumes or else.
Losing the slowest shard sets the latency. One degraded node makes each fan-out query slow.