Database Sharding: Partitioning Strategies, Hot Keys, and Rebalancing

Every database on a single machine eventually meets a ceiling it cannot raise. Writes queue behind one machine’s commit path, the working set stops fitting in memory, or the dataset stops fitting on the volume. Database replication fixes none of those ceilings; every replica performs every write and stores every byte, so copies multiply reads and nothing else. When the bottleneck is writes or storage, the mechanism is splitting: sharding.

Database sharding is splitting one logical dataset across multiple machines, where each machine holds a disjoint subset of the data and is the only home for its keys; the mechanism that scales write throughput and storage past a single node.

Why databases get sharded

The ceilings arrive in a familiar order. First, the read ceiling: the working set outgrows memory and every query starts touching disk, a problem indexing and caching still own at this stage. Second, the write ceiling (the commit path of one machine, its log, its locks, its disk flushes) saturates, and no amount of read tuning helps. Third, the storage ceiling: the dataset outgrows any volume a single machine can mount. Vertical scaling raises all three at a rising cost and then stops; the point at which growth must become horizontal scaling.

Replication and sharding are the two horizontal mechanisms, and they are complements, not substitutes. Replication copies the same data to more machines and multiplies read capacity and failure tolerance. Sharding puts different data on different machines and multiplies write capacity and storage. Production systems almost always run both, which one a workload needs first is a decision this article deliberately does not make; sharding vs replication owns that comparison.

One placement fact does the quiet work underneath everything below: when a fleet holds disjoint key ranges, adding or removing machines decides where keys live, and the scheme that moves the fewest keys wins. That scheme is consistent hashing, and its ring is the prerequisite this article borrows whenever hash placement appears.

Sharding strategies

Every sharding scheme answers one question: given a key, which machine holds the row? Four strategies cover the field.

Range sharding

Each shard owns a contiguous range of the key space: user IDs 1 through 9,999 on one machine, 10,000 through 19,999 on the next. Range sharding keeps related rows together, which makes range scans efficient and gives shards human meaning: a region, a tenant tier, a date window. Its weakness is unevenness. Writes for a given key land on exactly one shard, so a hot key range bloats one machine while the rest idle, and monotonically increasing keys make it structural: auto-increment IDs and timestamps send every insert to the shard that owns the current end of the range.

Hash sharding

Hash sharding applies a hash function to the key and uses the result for placement, even distribution by construction, because a good hash turns any key pattern into uniform noise. The price is geometry: two rows with adjacent keys land in unrelated places, so range scans become fleet-wide fan-outs. When placement rides a consistent-hashing ring, membership changes move only the affected arc (about a 1/N share of keys) instead of the near-total reshuffle a modulo scheme produces.

Directory sharding

A lookup service maps every key or key range to its shard. Any placement is expressible, and rebalancing is a directory edit rather than a data-movement formula. The costs are the directory itself (one more system with its own availability and consistency to operate) and the extra hop on every routing decision. The pattern suits fleets where placement rules are irregular or change for business reasons.

Geographic sharding

Geographic sharding places data by where it belongs: European users on European machines, both for latency and for data-residency law. It is usually range sharding with a policy attached, and the boundary (which region owns which users) is a compliance document as much as a placement rule.

Hash vs range partitioning

The two dominant strategies reduce to one trade, because directory sharding is usually one of them wrapped in a lookup table and geographic sharding is range sharding with a passport:

MongoDB’s sharded clusters default to range-based keys and offer hashed keys as the alternative; Google Spanner splits ranges automatically as they grow. The choice is about the workload’s shape: if the dominant queries scan ranges or group an entity’s rows together, range placement pays for its unevenness with locality; if the dominant pressure is write volume, hash placement pays for its opacity with evenness.

Choosing a shard key

The shard key is the most consequential decision in the design, because it cannot be changed after data lands without moving all of it, a reshard of everything. A good key has high cardinality, spreads writes evenly, and matches the dominant access pattern: every frequent query should carry the shard key, so the query routes to exactly one shard. Multi-tenant systems shard by tenant, per-user data shards by user, and compound keys, such as (user_id, created_at), combine the evenness of the first component with the ordering of the second. The test is unforgiving: a query that arrives without the shard key is a query to every shard.

Hot keys and hot shards

A hot shard is one receiving traffic far out of proportion to its share of the data. The celebrity problem is the canonical form: one famous account’s posts and profile live on exactly one shard, and while the fleet holds a million users, the traffic for that one user can saturate it. Monotonic keys cause the same condition structurally; the shard that owns “now” takes every insert. Hash placement prevents the structural version and does nothing about the celebrity: the key still lives somewhere, and there is still exactly one of it.

The fixes trade convenience for spread. Key salting appends a small random component; one logical key becomes several physical ones, at the cost of a fan-out read to reassemble it. Compound keys spread a heavy user’s rows across time while keeping them locatable. And the cheapest fix often sits one tier up: hot keys are a distributed cache problem too, and a cache fleet that absorbs a celebrity’s reads keeps the database from ever seeing the spike.

Detection is a metrics discipline: per-shard request rate, write rate, and queue depth, watched as a distribution; the shard that drifts from the pack is the incident before the incident.

Cross-shard queries

Sharding buys cheap queries for exactly one predicate, the shard key. Everything else fans out. A query for “all orders in the last hour” that does not carry the shard key is a scatter-gather: send the query to every shard, merge the partial results, and only then sort or limit. The fan-out’s latency is the slowest shard’s; a tail-latency problem, because the request inherits the worst response in the fleet, the failure mode tail latency exists to describe.

Secondary indexes

An index on a non-shard-key column comes in two forms. A local index lives inside each shard and answers only for its own rows: a query by that column fans out to every shard, each returning its partial matches. A global secondary index is itself a sharded dataset; a mapping from the indexed column to the shard holding the row, which turns the fan-out into a two-hop lookup but gives the index its own sharding, replication, and consistency to operate. The mechanics of indexing itself belong to database indexing; the decision here is only where the index lives.

Aggregations and joins

Aggregations degrade gracefully: a fleet-wide COUNT or SUM becomes partial aggregation per shard plus a merge, at the cost of the fan-out. Joins do not; two tables joined on a column that routes them to different shards stop sharing a node, and the join happens in application code. The standard answers are denormalization (store the joined shape, write more, read once) and key co-location (shard both tables by the join key). Transactions across shards are the hard boundary: the local ACID guarantee ends where the node ends, and what replaces it (two-phase commit and its blocking behavior) is the subject of distributed transactions.

Resharding

Resharding is moving the boundaries: adding shards, splitting a shard that grew past its machine, or repairing a shard key that turned out wrong. The naive version rehashes every key against the new fleet size and moves almost all of it; the modulo-placement trap that consistent hashing exists to avoid.

The placement scheme determines the bill. On a consistent-hashing ring, adding a node moves only the arc it receives, about a 1/N share of the keyspace, with no formula change anywhere else. Range systems migrate in pieces: MongoDB’s balancer moves chunks between shards in the background, a few at a time, until the fleet is level again. Directory systems rebalance by editing the map and letting the data follow.

Custom shard layers use the dual-write plus backfill pattern: write new records to old and new placement simultaneously, backfill history in the background, verify counts, then cut reads over. It is online and proven (it is how large MySQL fleets (Vitess and its descendants) grew) but it is operationally the heaviest routine task a sharded system performs, and the drift window between the two placements is a consistency risk to monitor, not just wait out.

The planning answer is to shard ahead: more, smaller shards than the data needs, so growth is repointing rather than re-splitting. Virtual shards on few machines give the same elasticity; logical boundaries stay stable while physical placement moves.

Common mistakes

• Sharding to fix a read problem. Most slow-query tickets die to indexing or caching, and sharding a read-bound system adds fan-out to the queries that were slow in the first place. Exhaust indexes and caching first.
• A shard key the queries don’t carry. Hashing by primary key while every query filters by tenant makes every query a scatter-gather. The key must match the access pattern, not the schema’s ID column.
• Monotonic keys. Timestamps and auto-increment IDs concentrate every insert on the shard that owns the top of the range, the structural hot shard.
• Discovering cross-shard joins in production. The ORM’s lazy query that touches three shards per page is a design decision made by accident.
• Resharding without verification. Dual-write drift, backfill gaps, and a cut-over without count checks turn a migration into a restore.
• Shards without replicas. An unreplicated shard is a data-loss region waiting for a disk. Every shard needs copies, the topic of database replication.

FAQ

What is database sharding?

Splitting one logical dataset across multiple machines, where each machine holds a disjoint subset and is the only home for its keys. It is the mechanism that scales write throughput and storage past a single node; replication (the complement) scales reads and failure tolerance instead.

What are the main sharding strategies?

Range sharding assigns contiguous key ranges per shard, preserving locality and range scans; hash sharding places keys by hash for even write distribution; directory sharding routes through a lookup service for maximum flexibility; geographic sharding places by region for latency and residency law.

How do you choose a shard key?

Pick the highest-cardinality column your frequent queries already carry (tenant for multi-tenant systems, user for per-user data) optionally compounded with a second column for ordering. The key must spread writes evenly and route the dominant queries to exactly one shard; changing it later means moving everything.

What is a hot shard?

A shard receiving traffic far out of proportion to its share of the data, caused by a single heavy key (the celebrity problem) or by monotonic keys concentrating all inserts on the shard owning the current end of the range. Salting, compound keys, and a cache tier that absorbs the reads are the standard responses.

Why are cross-shard queries expensive?

Any query without the shard key fans out to every shard, and its latency becomes the slowest shard’s response. Secondary indexes can be local (forcing the fan-out) or global, which trades it for a second sharded dataset to operate.

What is resharding?

Moving the boundaries between shards: adding machines, splitting grown shards, or repairing a bad key. Consistent hashing limits movement to the affected arc; range systems migrate chunk by chunk; custom layers dual-write and backfill. Done online, it is the heaviest routine operation a sharded fleet performs.

Does sharding improve availability?

Not by itself. A lost shard takes its slice of the data offline with it; sharding scales capacity, and replicas are what keep a shard’s data available. The two mechanisms stack: every shard gets its own copies.

Where this cluster goes

This article anchors the Databases & Data Scaling cluster, together with its sibling pillar. The rest of the cluster:

• database replication, the sibling pillar: copies of every shard, the read scale and the durability.
• sharding vs replication, the decision article between the two mechanisms.
• database indexing, the read lever to exhaust before splitting anything.
• SQL vs NoSQL, data models that make sharding easier or harder by design.
• blob and object storage, when the data outgrows the database and becomes files.
• search engines in system design, when search needs its own sharded system.
• read/write separation; routing reads across the replicas each shard keeps.
• data partitioning: the umbrella concept, its vertical and horizontal forms, and the partitioning-vs-sharding vocabulary.
• Elasticsearch architecture, a search engine built from sharding and replication as first-class design.

Related articles

• Next read: sharding vs replication; the decision this article sets up: which mechanism your workload needs first, and why the answer is usually both.
• consistent hashing; the placement scheme hash sharding leans on, and the reason resharding can move 1/N instead of everything.
• database replication; the complement: copies of each shard, and the lag that comes with them.
• distributed transactions, what happens to atomicity once a transaction’s rows stop sharing a node.
• database indexing, the cheaper lever to exhaust before splitting a dataset.
• vertical vs horizontal scaling; the growth decision that sharding is the sharpest answer to.