Sharding Strategies

Purpose-splits stop helping once ten billion uniform rows hammer one primary

Federation gave users, products, and orders each their own database with its own buffer pool and durability setting, and the 800 GB walk-through showed the small database's hit rate climbing from 60% to over 95%. That split works because the data differs by purpose. Replicas, which are live copies of the whole dataset, bought safety and read speed, but every copy still holds everything. When the dataset passes what one machine can store, with ten billion uniform order rows and climbing, and writes hammer a single primary, which is the one machine allowed to accept edits, adding more copies does not help, because copies do not split write load or storage.

The naive fix is an ever-bigger primary, and it fails on two ceilings at once. A second tempting fix is federating further, splitting orders into recent-orders and old-orders databases, and it fails on the join the business never stops asking: every revenue report spans both sides, so each query becomes two fetches plus an in-memory merge, and the newest side regrows into the same hotspot within quarters. Storage hits the largest practical disk and backup window, while write throughput hits the single machine's ability to fsync, which means flushing edits to durable disk. Beyond that point each doubling of data needs a doubling of one machine rather than adding ordinary machines.

The way out is cutting the dataset into pieces and dealing each piece to a different machine, keeping the same tables and schema but different rows on each. Each piece is called a shard, and each shard is a full database minding its own slice. Think of a post office that sorts mail by zip code so each branch handles only its own region: one analogy for the whole idea, where the shard key is the zip code that decides which branch a letter belongs to.

Ten million users dealt across three machines with no overlap

The routing precision is the whole game. Ask for one user and exactly one machine should wake up, which requires knowing which slice holds that user before asking. Get the mapping wrong and every request fans out to all three machines, multiplying cost by the shard count.

A request for user 7,000,000 computes its slice first and contacts only shard C. That pre-computed destination is what separates sharding from broadcasting.

Three routing strategies decide which slice owns each row

A shard key, which is the column whose value decides placement, and a mapping strategy together determine routing. Hash strategies spread evenly but complicate growth, range strategies keep order together but invite hotspots, and directory strategies stay flexible at the cost of running a critical lookup service.

Growing from ten shards to eleven moves everything unless hashing is consistent

Plain hash sharding has a painful property: adding one machine changes the divisor, so almost every row belongs somewhere new and the team migrates the whole dataset to add one box. Consistent hashing, which arranges shards and keys on a ring so each key belongs to the next shard clockwise, fixes the blast radius. A newcomer takes over only the keys between itself and its neighbor, so roughly 1/N of the data moves instead of all of it, where N is the new shard count. Virtual shards, which are many small ring segments per machine, smooth the distribution further so the newcomer siphons evenly from several neighbors.

Slicing charges routing logic, hotspots, and lost cross-shard guarantees

The bill is permanent. Queries joining users with orders now span machines, so the application fetches both sides and joins in memory, paying extra round trips and memory. The application learns geography, meaning every query first decides where to go, and that routing logic lives in the codebase forever. Foreign keys, which are database-enforced links between tables, and multi-shard transactions stop working, so atomicity across shards becomes application sagas with compensating actions. None of this is a reason to avoid sharding past the single-box ceiling, but it is the price quoted up front.

Size shards so each one stays movable in hours, not days

Walk the capacity math: ten billion rows at 500 bytes each need 10,000,000,000 times 500 bytes, which is 5,000,000,000,000 bytes or 5 TB before indexes, which no single box holds comfortably with replicas and headroom. Split across 16 shards and each holds about 625 million rows and roughly 300 GB, which is a beefy but ordinary machine with room for indexes and a replica. The operating rule is that each shard stays small enough that a rebalance, backup, or failover finishes in minutes to low hours. Key cardinality, which is the number of distinct key values, decides feasibility: a user identifier with tens of millions of values spreads evenly, while a country code with 200 values guarantees overloaded shards no matter how clever the hash.

Even perfect hashing cannot save a shard holding a celebrity

Hashing spreads rows evenly but not load. One wildly popular account joins shard B and suddenly 40% of reads hit one machine while the others idle, which is the classic hot shard, and no hash function prevents it because the skew lives in popularity rather than key distribution. Large chat and photo systems converge on the same mitigations: split the hot key further by appending a bucket suffix so one logical celebrity spreads across several physical shards, cache the hot rows aggressively so reads never reach the shard, and isolate the loudest tenant on a dedicated shard. The sibling meltdown is the scatter-gather query, which fans out to every shard because it lacks the shard key: one slow shard sets the latency for all, so any hot query that cannot include the shard key should be rethought, either by denormalizing, which means copying the needed field into each row, so it can, or by admitting sharding is the wrong tool for that query.

Sharding deals uniform rows across machines by key, with consistent hashing moving roughly 1/N of the data per added box and celebrity keys still melting single shards. Keys route rows, but rows are not the only sharing problem: one codebase serving thousands of customer organizations must never mix up whose rows are whose, and a forgotten tenant filter returns the world instead of an error. Sharing hardware deliberately while the database itself enforces the labels is the multi-tenant contract.