Sharding a Database: The Key You Pick Is the One Decision You Cannot Undo Cheaply

Sharding is what you do after you have run out of the things that are not sharding. Read replicas, a bigger instance, an index that should have existed, moving the two enormous columns into their own table — all of those are reversible in an afternoon, and one of them is usually enough. Sharding is none of those things.

The mechanics are the easy part: put some rows on machine A and some on machine B, and route each query to the right one. The hard part is that the routing function is now baked into your application, your queries, your migrations and your operational runbooks, and changing it later means moving every row while the system is serving traffic.

What you actually give up

Before the key, the price list. Sharding takes away three things you have been using without noticing.

Cross-shard joins. Two tables sharded on different keys cannot be joined by the database. That join moves into your application as two queries and a loop, and it is now your job to make it not be O(n) round trips.

Transactions across shards. A transaction is a property of one database. Spanning two means two-phase commit — with its coordinator, its blocking window, and its ability to leave locks held when the coordinator dies — or it means giving up atomicity and designing for partial failure with compensating actions.

Cheap global queries. SELECT COUNT(*), ORDER BY created_at LIMIT 20, and AUTO_INCREMENT all silently assumed one machine. Counting means asking every shard. Global ordering means fetching a page from every shard and merging in application code. Ids need a generator that is unique without coordination.

None of these are dealbreakers. All of them are work that did not exist before, and the right time to price them is before the migration rather than during it.

The key decides everything at once

Four candidate keys against the same deliberately-skewed customer base, where the biggest tenant is 30% of the rows. Watch both readouts as you switch, because the trade-off is that they move in opposite directions.

Hash the row id and the distribution is perfect. It is also the worst possible key for queries, because every tenant-scoped query becomes a scatter-gather across all eight shards. Your latency is now the slowest of eight responses on every request, and any query with an ORDER BY and a LIMIT has to over-fetch from each shard and merge.

Key by tenant and every tenant query lands on exactly one shard, which is the property you actually wanted. And your largest customer now owns a machine. You cannot split them without splitting the customer, so that shard fails first, and the failure is concentrated on the account you can least afford to have an incident with. This is the single most common sharding regret.

Key by time looks tidy and is the worst of the three. All writes are for now, so the newest shard takes the entire write workload while the other seven are idle disks. Time is an excellent key for pruning — dropping a whole month is one statement — and a terrible one for spreading load.

Composite is where most people end up: split each tenant across a small fixed number of buckets, so the big tenant no longer owns a shard while a query still touches four shards instead of eight. The cost is honest — the bucket count is now a constant embedded in your routing, and changing it is another migration.

There is no key that wins both columns. Pick which one you are optimising, and write down why, because the next person will assume the other one.

Moving rows while the system is up

Eventually a shard is too big, and you have to split it without a maintenance window.

Move the reads early and the figure tells you exactly what a customer sees. The five phases are not ceremony; each one closes one specific hole, and the order is forced.

Dual-write first, so no new write is lost while you work. Then backfill the history in throttled batches — throttled because an unthrottled backfill is a self-inflicted denial-of-service on the shard still serving production. Then verify by comparing counts and checksums, and keep verifying until they agree, because “the backfill script finished” and “the data is correct” are different claims. Only then move reads.

The property that makes this survivable is that the old shard stays complete and stays written to for the entire migration, so every phase before the final one rolls back by changing where reads point. The irreversible step is stopping the dual-write, and it should happen days after the cutover, not minutes.

This is also why the routing table belongs in a lookup service rather than in hash(key) % N in application code. With modulo, adding one shard remaps almost every key at once. With a table mapping virtual buckets to physical shards — a few thousand buckets, assigned to machines — moving load is reassigning a bucket, and the split above becomes a routine operation rather than an event.

The parts nobody mentions

Ids have to be generated without coordination. AUTO_INCREMENT per shard produces colliding ids across shards. UUIDv4 is unique and randomly ordered, which fragments every B-tree it is a primary key of. Use a time-ordered id — Snowflake-style, or UUIDv7 — so ids stay unique, sortable, and index-friendly.

Schema migrations are now N migrations. They will not all succeed, and a shard that fails halfway leaves your fleet on two schemas at once. Every migration has to be backwards-compatible for the duration, which means the expand-migrate-contract pattern is mandatory rather than good practice.

Rebalancing is not something you do once. Tenants grow at different rates, so a distribution that was even last quarter is not now. If moving a bucket between shards is not a routine, low-risk operation, you will avoid doing it, and you will run hot shards instead.

Pagination breaks quietly. LIMIT 20 OFFSET 200 across shards means fetching 220 rows from every shard and discarding almost all of them. Cursor pagination on a sortable key is the fix, and retrofitting it after launch is a public API change.

Reading across shards

Single-shard queries are the whole point, and you will still have queries that are not. Admin screens, reports, “show me every failed order in the last hour” — these have no tenant to route by, and they become scatter-gather: query every shard, merge in the application.

Three things about scatter-gather are worth knowing before you write the first one.

Your latency is the slowest shard, not the average. With eight shards, a query is as slow as the worst of eight responses, so a p99 on one shard becomes roughly a p92 for the query overall. Add shards and this gets worse, not better — the more machines you ask, the likelier one of them is having a bad second.

Partial failure is now a design decision. One shard timing out means you can return seven-eighths of an answer or no answer. Both are defensible; silently returning the seven-eighths as though it were complete is not, and it is the default if nobody thinks about it. Return the result with an explicit note that one shard was unreachable.

Aggregation cannot be pushed down. AVG across shards is not the average of the per-shard averages unless you also carry the counts. COUNT DISTINCT cannot be merged from per-shard counts at all. Sorting with a limit means over-fetching the limit from every shard and merging. Every one of these is a place where the obvious application-side merge is quietly wrong, and the wrongness is small enough to survive review.

The usual escape is to stop doing analytics on the sharded store entirely: replicate into a columnar warehouse and point the reports there. That is one more system, and it is still cheaper than making eight OLTP shards serve a dashboard.

The short version

Do everything else first — replicas, indexes, a bigger box, moving cold columns out. Sharding costs you cross-shard joins, cross-shard transactions and cheap global queries, permanently.

The shard key trades skew against fan-out and you cannot have both. Hashing spreads evenly and scatters every query; keying by tenant keeps queries local and hands your largest customer their own machine to fail; keying by time is a write hotspot dressed as tidiness. A composite key with a fixed bucket count is usually the answer.

Route through a bucket-to-shard lookup table rather than hash(key) % N, so moving load is a reassignment rather than a full remap. And do the migration in the order that keeps the old shard authoritative the whole way — dual-write, backfill, verify, move reads — because the only irreversible step is the one at the end, and there is no reason to take it on the same day.