Database Sharding

Analogy categoryAnalogies

Sharding (horizontal partitioning) splits a dataset across multiple independent database nodes, each holding a disjoint subset of the rows, so that no single machine needs to store or serve the entire dataset. Replication copies the same data everywhere; sharding instead divides the data. This is what allows a system to scale storage and write throughput beyond what any single machine can handle, but it comes at real cost: queries that span multiple shards (joins, aggregations, transactions across shard boundaries) become significantly harder and slower.

🏏

Cricket analogy: Sharding is like splitting a massive multi-decade scorebook across separate archive rooms by decade, so no single room holds every match ever played — but a query like 'compare a bowler's stats across three different decades' now requires visiting multiple rooms and combining results manually.

Choosing a shard key

The shard key (or partition key) determines which shard a given row lives on, and choosing it well is the single most important sharding decision. A good shard key distributes both data volume and query load evenly across shards — for example, sharding a multi-tenant SaaS database by tenant_id keeps each tenant's data together (fast for tenant-scoped queries) and spreads tenants roughly evenly if tenant sizes are comparable. A poor shard key creates a 'hot shard': if you shard a social app by signup date and a viral event drives huge traffic to recently-created accounts, one shard absorbs disproportionate load while others sit idle.

🏏

Cricket analogy: The shard key is like choosing to organize records by team — a good choice like team_id keeps each franchise's matches together for fast team-scoped queries and spreads load evenly across roughly equal-sized franchises, but choosing 'match date' could overload one shard when a World Cup drives huge traffic to recent matches.

Sharding strategies

Range-based sharding assigns contiguous key ranges to each shard (e.g., user IDs 1–1M on shard 1, 1M–2M on shard 2), which supports efficient range queries but risks hotspots if writes cluster at one end of the range (e.g., monotonically increasing IDs always hitting the newest shard). Hash-based sharding applies a hash function to the shard key to pick a shard, which distributes load much more evenly but destroys the ability to do efficient range scans across the original key order. Directory-based sharding keeps an explicit lookup table mapping keys to shards, offering maximum flexibility for rebalancing at the cost of an extra lookup and a potential single point of failure in the directory service itself.

🏏

Cricket analogy: Range-based sharding is like archiving scorecards by match-number ranges (matches 1-1000 in room A), good for browsing chronological ranges but risking recent matches all landing in the newest room; hash-based scatters matches evenly by a hash of match ID, losing chronological browsing; directory-based keeps an explicit lookup table, flexible but a single point of failure.

Resharding and rebalancing

As data grows or access patterns shift, shards need to be split or rebalanced, and naive hash-based sharding (hash(key) % N) is a trap here: changing N (the shard count) remaps nearly every key to a different shard, requiring a massive, disruptive data migration. This is precisely the problem consistent hashing was designed to solve, minimizing the fraction of keys that must move when shards are added or removed.

🏏

Cricket analogy: When a league expands and shard count changes, naive hash%N reassignment (like recalculating every player's practice net from scratch) would remap nearly every player to a different net; this is exactly the problem consistent hashing solves, minimizing how many players actually need to move.

python

# Naive modulo sharding: fragile when shard count changes
def shard_for_key_naive(key: str, num_shards: int) -> int:
    return hash(key) % num_shards

# Adding a shard (num_shards: 4 -> 5) remaps ~80% of keys, forcing
# a massive re-migration of data between shards.

# A directory-based approach avoids this by decoupling key -> shard
# mapping from a formula, letting you move individual key ranges:
shard_directory = {
    "tenant_1": "shard_A",
    "tenant_2": "shard_A",
    "tenant_3": "shard_B",
}
def shard_for_key_directory(key: str) -> str:
    return shard_directory[key]  # O(1) lookup, independently rebalanceable

Instagram's early architecture sharded PostgreSQL by user ID using a custom ID-generation scheme that embedded the shard number directly into each generated ID, so any service could compute which shard held a given piece of data without a separate directory lookup.

Sharding is often reached for too early. It adds substantial operational and query complexity (no more simple cross-shard joins or single-node transactions), so teams should exhaust vertical scaling, indexing, caching, and read replicas before sharding, unless write throughput or dataset size genuinely exceeds a single node's capacity.

Sharding partitions data horizontally across nodes so no single machine stores or serves the whole dataset.
The shard key choice is critical: a poor choice creates hot shards with uneven load.
Range-based sharding supports efficient range queries but risks hotspots at the range boundaries.
Hash-based sharding distributes load evenly but loses efficient range scans over the original key order.
Naive modulo sharding remaps most keys when the shard count changes; consistent hashing minimizes this churn.
Sharding adds real complexity (no simple cross-shard joins/transactions) and should be a late, not first, scaling resort.

Practice what you learned

1. What is the fundamental difference between sharding and replication?

2. What is a 'hot shard' and what typically causes it?

3. Why is naive modulo sharding (hash(key) % N) problematic when adding a new shard?

4. What capability does range-based sharding preserve that hash-based sharding typically loses?

5. According to common system design guidance, when should a team reach for sharding?

Was this page helpful?

Database Replication

Consistent Hashing

Related Study Notes in Software Engineering

Browse all study notes

Software Engineering

Microservices Study Notes

Software Architecture · 30 topics

Software Engineering

Testing & TDD Study Notes

Software Testing · 30 topics

Software Engineering

Design Patterns Study Notes

Software Design · 30 topics

Software Engineering

Software Engineering Study Notes

Python · 40 topics

Software Engineering

Git & Version Control Study Notes

Bash · 40 topics

Topics covered

#Architecture#SystemDesignStudyNotes#SoftwareEngineering#DatabaseSharding#Database#Sharding#Choosing#Shard#SQL#StudyNotes#SkillVeris

Database Sharding