Databases & SQL

Database Sharding Explained: Horizontal Partitioning for Scale

Sharding solves one problem — a single database server running out of room or throughput — by creating several harder ones. It's usually the last scaling lever you pull, not the first.

Published July 6, 2026

Sharding splits one logical database into multiple physical databases (shards), each holding a subset of the rows, usually running on separate servers. A table with 500 million rows split across 10 shards means each shard only has to index and serve 50 million — smaller working sets, smaller indexes, more total write throughput because writes spread across machines instead of contending for one.

Choosing a shard key

Every row needs a shard key that determines which shard it lives on. For a multi-tenant SaaS product, tenant_id is the obvious choice: it keeps each customer's data together, which also happens to make most queries (which are almost always scoped to one tenant) stay within a single shard. For a consumer social app, user_id is typical. The shard key choice is close to permanent — changing it later means physically moving data between shards, which is exactly the "resharding" problem covered below.

Range sharding

Range sharding assigns contiguous key ranges to each shard: users 1–10M on shard 0, 10M–20M on shard 1, and so on. It's simple to reason about and makes range queries (fetch users 100–200) efficient because they hit one shard. The failure mode is hot spots: if user IDs are assigned sequentially and new signups are the most active users, the newest shard absorbs disproportionate load while old shards sit idle.

Hash sharding

Hash sharding runs the shard key through a hash function and assigns the shard based on the result, typically hash(key) % num_shards:

def shard_for(user_id, num_shards):
    return hash(str(user_id)) % num_shards

shard_for(48213, 8)  # -> deterministic shard index 0-7

This spreads load evenly regardless of how keys were generated, fixing the hot-spot problem, but destroys locality: users 100 and 101 could end up on completely different shards, so a range query across many keys now has to fan out to every shard and merge results. The plain-modulo approach above also has a specific weakness — changing num_shards remaps almost every key to a different shard, which is why production systems generally use consistent hashing instead, so adding a shard only remaps a small fraction of keys.

What gets hard: cross-shard queries and transactions

A query that only needs one shard's data (fetch this user's profile) is unaffected by sharding. A query that spans shards — a global leaderboard, "top 100 products by sales across all tenants" — now needs a fan-out: query every shard, then merge and re-sort the results in application code, which is slower and more complex than a single SQL query with an ORDER BY and LIMIT ever was.

Transactions are worse. A transaction that updates rows on two different shards (transfer money from a user on shard 2 to a user on shard 5) can't rely on a single database's ACID guarantees anymore. You either avoid cross-shard transactions by design (keep related data on the same shard, as with the tenant_id example above), or you implement a distributed transaction protocol like two-phase commit or a saga pattern, both of which add real latency and failure-handling complexity that a single-node database never had to think about. Sharded document stores like MongoDB address this at the platform level, with sharding and chunk migration documented in MongoDB's sharding documentation.

Resharding: the operation everyone underestimates

Adding shards later means moving existing data to rebalance load, live, without downtime and without losing writes that happen mid-migration. This typically involves dual-writing to old and new shard locations during a transition window, backfilling historical data, then cutting reads over once the new layout is verified consistent. It's one of the riskiest operations in a sharded system's lifecycle, which is exactly why picking a shard key that scales well from the start — and choosing hash sharding with consistent hashing over naive range or modulo sharding — matters more than most teams appreciate until they're the ones doing the migration under load.

When to actually shard

Vertical scaling (a bigger server), read replicas, and caching solve most scaling problems more cheaply than sharding, because they don't force you to give up cross-row transactions and simple queries. Shard when write throughput or dataset size has genuinely outgrown what a single well-tuned primary can handle even with replicas and caching in place — not preemptively, because reversing a sharding decision is far more painful than deferring it.