How Database Sharding Works

What is Sharding?

Sharding (also called horizontal partitioning) splits a large dataset into smaller pieces called shards, each stored on a separate database server. Instead of one massive machine holding everything, the data is distributed across a cluster of machines.

Sharding is different from replication — replication copies the same data to multiple nodes for availability, while sharding distributes different data to different nodes for scalability.

Why do we need it?

Problem	How sharding helps
Data too large for one machine	Each shard holds a subset — total capacity = sum of all shards
Write throughput bottleneck	Writes spread across shards — no single node handles all writes
High query latency	Each shard indexes a smaller dataset — queries run faster
Cost	Use many commodity machines instead of one expensive server

Key terminology

• Shard key — the column (or set of columns) used to decide which shard a row belongs to (e.g., user_id, region)
• Shard router — the component that intercepts queries and forwards them to the correct shard
• Hash function — a function that maps a shard key to a shard number (e.g., hash(key) % num_shards)
• Hotspot — a shard that receives disproportionately more traffic than others
• Resharding — the process of re-distributing data when adding or removing shards

How sharding works (simplified)

1. Client sends a query (read or write) with a shard key
2. Router evaluates the shard key → determines target shard
3. Router forwards the query to the target shard only
4. Target shard processes the query
5. Router collects the result and returns it to the client

The critical design decision is: how does the router decide which shard to use? That's what determines the sharding strategy.

Visualization

See how writes get routed to different shards under three common strategies. Toggle between modes to compare distribution, query patterns, and trade-offs:

Applies a hash function to the shard key and uses modulo to assign data to shards. Provides even distribution but makes range queries across shards expensive.

0 / 0

💻Client

│

🔀

Shard Router

Sharding Strategies Compared

Aspect	Hash-Based	Range-Based	Directory-Based
Routing	hash(key) % N → shard number	Key range intervals → shard	Lookup table maps key → shard
Distribution	Even (if hash is uniform)	Uneven if key distribution is skewed	Configurable — any mapping
Range queries	Expensive — must query all shards	Efficient — target one shard	Depends on mapping
Adding shards	Requires resharding most data	Add a new range, move border data	Update directory entries only
Complexity	Low — just hash + modulo	Low — range check	Medium — maintain lookup table
Hotspot risk	Low	High (e.g., recent timestamps)	Depends on mapping design
Use case	User IDs, session tokens	Time-series, sorted data	Geo-distributed, multi-tenant

Choosing a Shard Key

The shard key determines everything about your sharding performance. A bad shard key leads to hotspots, cross-shard queries, and painful resharding.

Good shard key properties

1. High cardinality — many distinct values so data spreads evenly
2. Low frequency — no single value dominates (avoids hotspots)
3. Query-relevant — most queries include the shard key so they hit one shard
4. Immutable — changing the shard key means moving the row to a different shard

Common shard key choices

Shard key	Strategy	Notes
user_id	Hash	Distributes users evenly; queries scoped to one user hit one shard
created_at	Range	Good for time-series; recent data hotspot on the "current" shard
region	Directory	Geo-sharding; each region's data stays in a nearby data center
tenant_id	Hash or Directory	Multi-tenant SaaS; tenant isolation at the shard level

Challenges of Sharding

Cross-shard queries

Queries that don't include the shard key must be sent to all shards and the results merged. This is called a scatter-gather query and is much slower than a targeted query.

Joins across shards

JOINs between tables on different shards are expensive or unsupported. Common workarounds:

• Duplicate small tables to every shard (reference data)
• Application-level joins — query each shard separately, merge in code
• Co-shard related tables — use the same shard key for related tables

Resharding

When you add or remove shards, data must be redistributed. Approaches:

• Consistent hashing — minimizes data movement when adding/removing shards
• Double-write — write to both old and new shard layout during migration
• Online migration — tools like Vitess or MongoDB's balancer move data incrementally

Sharding in Practice

• MongoDB: Native sharding with mongos router. Supports hash and range shard keys. Auto-balancer moves chunks between shards.
• PostgreSQL: No native sharding. Use Citus extension, Pgpool-II, or application-level sharding. Built-in table partitioning (not true sharding — same server).
• MySQL: Use Vitess (used by YouTube) or application-level sharding. MySQL Cluster (NDB) has automatic sharding.
• Redis Cluster: Hash-based sharding with 16384 hash slots. Each node owns a range of slots. Supports resharding without downtime.

When to Shard

Sharding adds significant complexity. Consider sharding only when:

1. You've hit vertical scaling limits — can't add more CPU/RAM to a single machine
2. Read replicas aren't enough — writes are the bottleneck, not reads
3. Your dataset exceeds single-machine storage — data won't fit on one disk
4. You need multi-region data locality — users in different regions need local data

If you're not hitting these limits, start with replication and vertical scaling first. Premature sharding is a common source of unnecessary complexity.

Learn More

Read the full theory in Database > Database Design.