Sharding
A Socratic walk-through of sharding — reasoned out one step at a time, not lectured.
The question we started with
THE QUESTION #Why does splitting data across machines make some previously trivial questions unanswerable?
Splitting a table across ten machines sounds like a purely physical rearrangement. The rows are the same rows, simply stored in ten places instead of one. Nothing about the data changed, so surely nothing about what you can ask of it changed either.
But something did. Questions that were one line of SQL and a few milliseconds become slow, approximate, or genuinely unanswerable without rebuilding the whole dataset somewhere — and not exotic questions, but things like "how many orders were placed today". It is worth working out exactly which property was lost, because it is a single one and everything else follows from it.
Reasoning it through
REASONING #Start with what a single database quietly provides: every row is reachable from every other at negligible cost, and any set of rows can be examined together in one consistent instant. Both follow from the data sitting in one address space under one transaction manager. Neither is guaranteed by the relational model — they are a property of where the bytes are.
Now split the rows by some key — customer ID, hashed into ten buckets. What became of the two properties?
Reachability has become conditional. If a question mentions the shard key, the system knows which machine to ask and the query is as fast as before on a tenth as much data. If it does not, the system must ask all ten. That is the whole mechanism, and everything painful about sharding is a corollary. Cross-shard queries are not forbidden; they cost ten times the work and take as long as the slowest of ten machines, reliably worse than the average.
The consistent instant has become expensive. Ten machines have ten clocks and ten independent commit sequences, so a query reading all ten reads each at a slightly different moment — a total like "orders placed today" is assembled from ten snapshots that never coexisted. Usually fine; occasionally not, and a genuine cross-shard snapshot requires coordination (a global timestamp authority, two-phase commit, a version-tracking scheme) which is precisely the machinery the split was meant to avoid.
Now joins. What is a join between two tables sharded on different keys — orders by order ID, customers by customer ID? To match them, rows must move across the network to meet. That is a distributed shuffle, and its cost is proportional not to the answer but to the inputs. Hence the practical rule: shard related tables on the same key so that everything one transaction touches lands on one machine. Choosing that key is the single most consequential decision in the design.
Then the constraint that surprises people most: uniqueness. On one machine a unique index on an email address is trivial — there is one place to look. On ten shards keyed by customer ID, two customers sharing an email land on different shards and neither insert can see the other. Global uniqueness now needs either a separate global index (itself a shared, unsharded thing) or the admission that the constraint holds only within a shard. The same applies to auto-incrementing IDs and to any invariant spanning more rows than one shard holds.
So what was lost? Not data, not expressiveness — the global view, the ability to look at everything at once cheaply and consistently. Sharding trades it for capacity growing linearly with machines, and the questions that become hard are exactly those that needed that view: aggregates over everything, joins across keys, constraints spanning the whole set.
One honest note: modern distributed databases do answer these questions — Spanner, CockroachDB and TiDB support cross-shard transactions and global secondary indexes. They have not repealed the constraint, only paid for it in coordination protocols and latency. The cost moved from the application into the engine.
The analogy
THE ANALOGY #Think of a library that outgrows one building and moves its books into ten branches, split by author surname. Fetching a book by a known author is now faster, because each branch is smaller. But "how many books do we hold" requires ten phone calls, and the count is assembled from ten moments rather than one, so it is never exactly right. And "do we own two copies of anything" — once answered instantly by a single catalogue — now needs either a central catalogue nobody wanted to maintain, or the admission that only within-branch duplicates are detectable.
a librarian can walk to another branch and carry a book back, so the split is inconvenient rather than structural, whereas moving rows between shards often costs more than the query itself and is exactly what the split was performed to avoid.
Clarifying the model
THE MODEL #Three refinements.
First, sharding is not replication, and confusing them causes real design errors. Replication puts the same data on several machines: redundancy and read capacity, nothing for write capacity or dataset size. Sharding puts different data on different machines: write capacity and size, nothing for redundancy. Most production systems do both, which is why each shard is usually itself a small replicated cluster.
Second, choosing the shard key is a decision about queries, not data. The right key appears in the largest share of your read and write paths, because those operations stay single-shard. A key chosen for the data's natural structure — geography, say — while the queries are all by user produces a system where nearly every query fans out. It cannot be changed later without rewriting the entire dataset, which is why the decision deserves disproportionate care.
Third, correcting the likeliest misconception: fanning out to every shard is not automatically ruinous, since ten parallel queries returning small results can be quick. The problem is when fan-out is the common case, because then every machine does every query's work and you have a system with the capacity of one machine and the failure modes of ten. What matters is not whether cross-shard queries exist but what fraction of traffic they are.
A picture of it
THE PICTURE #How to readRead the crow's-foot marks as counts. The shard key routes each row to exactly one shard, and that routing is what makes single-shard queries cheap; everything co-located under a shard, including a customer's orders, stays fast. The three relationships reaching across shards are the ones that cost: a global index touches all of them, a fan-out query must ask every one and wait for the slowest, and the local index's label states the constraint plainly — uniqueness holds within a shard and nowhere else. Note the commit_order attribute: each shard has its own, which is why an answer gathered from many is stitched from moments that never coexisted.
What became clearer
WHAT CLEARED #Sharding does not change the data, it changes what is cheaply reachable together. A single database quietly supplies two things — everything reachable from everything, everything visible in one instant — and splitting the rows withdraws both. Every hard consequence follows: aggregates need a fan-out, joins across keys need a shuffle, global uniqueness needs a shared authority, and a cross-shard total is stitched from moments that never coexisted. The shard key decides how much of your workload keeps the old properties, which is why it is chosen before the first row is written and regretted afterwards.
Where to go next
ONWARD #- Why an evenly distributed shard key can still produce one overloaded machine.
- How consistent hashing lets the number of shards change without moving nearly all the data.
Key terms
TERMS #| Term | What it means |
|---|---|
| Shard | one of several machines or databases holding a disjoint subset of a dataset's rows. |
| Shard key | the column whose value determines a row's shard. |
| Fan-out query | a query that must be sent to every shard because it does not constrain the shard key. |
| Entity group | a set of related rows deliberately co-located on one shard so transactions over them stay local. |
| Global secondary index | an index spanning all shards, allowing lookups by a non-shard-key column at the cost of a shared structure to maintain. |
| Two-phase commit | a protocol for committing across several independent machines: correct, but latency-expensive and vulnerable to a stalled coordinator. |
Every term the collection defines is gathered in the glossary.