Which column do we split on?
Of all the decisions in a sharded database, one is expensive to take back: the column that decides where each row lives. A chat service for online communities is about to make it. Its messages table takes 20,000 new messages a second at the evening peak. Read replicas already took the history reads, but every replica applies every write, so the primary is still running out of write capacity (why copies add only read capacity: Replication & read replicas).
So the table has to leave the single machine. Sharding splits a dataset across several nodes so that each shard, a slice of the rows held by one node, takes the writes for that slice alone: three even shards, a third of the writes and a third of the data each. Which slice a row belongs to is decided by one value in it, the partition key. A query that gives one value of the key goes to one shard; a query that does not usually has to ask all of them. Because every query from then on has to know about the split, sharding comes after a bigger machine, read replicas and caching have run out. (Many databases call each slice a partition; below, a partition means a smaller unit that a shard holds and moves as a whole, as small as the rows of one key.)
Three candidates for the key are on the whiteboard: created_at, message_id and channel_id. The product's most frequent query, by far, decides between them: "show the latest 50 messages in this channel".
Splitting by time puts every write in one place
The first idea is range partitioning: each shard owns a contiguous range of the key. By created_at, one shard holds January to April, the next May to August, the last September onwards. Range scans are cheap, because neighbouring keys sit together: everything from last Tuesday is on one shard, unless a range boundary falls inside it.
Each shard owns a contiguous time range, so a range scan usually stays on one shard, but every new message goes to Shard C. Hashing the key spreads new writes across all shards.
The diagram also shows the problem. Every new message lands on the shard that owns "now": Shard C takes all 20,000 writes a second while A and B serve old history, and the cluster has the write capacity of one machine. Any steadily growing key does this under range partitioning, an auto-increment message_id included, and so does a key that clusters the busiest rows, such as splitting users by signup date when the newest users are the most active.
Hashing spreads writes and scatters neighbours
Hash partitioning runs the key through a hash function and splits the hash values instead. Consecutive message_ids produce unrelated hashes, so new messages spread evenly whatever order they arrive in. The price mirrors the range scheme: neighbouring keys are scattered, so a range query has to ask every shard. Hash message_id, and the latest 50 messages of one channel are spread over all three shards; the most frequent query now waits for all of them.
How a hash value maps to a shard matters more than it looks: the obvious hash(key) % 3 sends about three keys in four to a different shard the day a fourth shard joins. Placement that moves only the new shard's share has its own article, Consistent hashing.
Keep together what is read together
The third candidate wins. Hash partitioning by channel_id keeps every message of a channel on one shard, where an index on (channel_id, created_at) keeps them in time order, so "latest 50 in this channel" is one sorted read from one shard. Writes still spread, because millions of channels hash across all the shards.
Choose the key from the queries, not from the schema; the entity everything hangs off, here the channel, is usually the answer. Splitting also breaks the auto-increment counter, which now runs on three machines, each counting from the same start, and can hand out the same message_id three times; for IDs minted without a single counter, see Unique ID generation.
One channel louder than a shard
Hashing spreads keys, not traffic. On the night of a cup final, a football community's official channel peaks at 6,000 messages a second, against the 2,500 each of the service's eight shards, by then, was sized for. Every one of those messages carries the same channel_id, so all of them hash to one shard. No key choice splits one hot key's traffic: a key lives on one shard, so all of its writes do too.
Two remedies change what counts as a key. Split the hot key with a suffix, channel_id plus a small bucket number, so its writes spread over several shards and each read gathers the buckets back; worth it only for keys known to be hot. Or isolate the hot tenant: give that community a shard of its own, sized for the final. When the hot key is a single counter rather than a stream of rows, Counting at scale goes through the split in detail, and a hot key in a cache is a job for Hot keys & cache stampedes.
Size skews too: a ten-year-old channel becomes one enormous partition that cannot be moved in pieces. A time bucket in the key, (channel_id, month), caps every partition at one month of one channel, and a history read touches one partition for each month it spans.
The queries that don't name the channel
"Find every message user 81 wrote this week" has no channel_id, so the router asks every shard and merges the results, the shape of Scatter-Gather: the slowest shard sets its latency, and its cost grows with the shard count, so a query that is fine at three shards hurts at thirty. Routine cross-shard joins have the same shape and age the same way.
An index on author_id helps in one of two ways. Kept on each shard, it speeds up every shard's answer, but the query still asks them all; partitioned by author, it sends the query to one place, while each new message updates a second shard, often asynchronously, so a fresh message can be missing for a moment. Keeping such an index in step with the data is what Index Table is about.
Writes hit the same wall. Moving a thread to a channel on another shard is a delete on one machine and an insert on another; a crash in between leaves it in both channels or in neither. Making that all-or-nothing takes a protocol across shards, such as Two-phase commit, with its price: extra round trips, and participants left waiting if the coordinator fails. The cheaper fix is usually the key: data that must change together should share a partition key.
Adding the ninth shard
Without a rebalancing story, adding a shard means a manual data migration, often of most of the rows. With one, what moves is a unit smaller than a shard, a range or one of many partitions created up front; which units go where is the placement function's call, and Consistent hashing compares those, fixed slots included. The move itself usually follows one outline. The new shard backfills the partition while the old one keeps serving, and new writes reach it too, by dual-writing or by replaying the old shard's log; until cutover, clients read and write the old shard only. At cutover, writes pause for a moment (some systems forward them instead), the routing map flips, and from then on only the new shard is read, with stale routers redirected; where that map lives and how routers learn it changed is Shard routing and the shard map. The old copy goes last. Copying competes with live traffic, so rebalance before a shard is full, not when it is.
Before you draw a single shard
The Sharding challenge asks, in eight nodes, for what the chat service had to settle. Decide the key first and write it on the router; every shard's label follows from it, and so does the one request that should touch a single shard. Mark where a cup final would land and how that shard gets split. Then show the thing people blur: a Leader–Follower Replication node behind a shard is a copy of that slice, not another slice. Distributed Key-Value Store goes further, with a hash ring, replicas for every key and nodes joining under traffic.