sharding-strategy-designDatabase Sharding — Divide & Conquer at Scale
You scaled your reads with Replication, but your Primary DB is drowning in write traffic. When a single machine can no longer hold your entire dataset, it's time to split the database itself across multiple servers.
Sharding Strategies & Architecture
Understand why Replication hits a ceiling, how Sharding breaks through it, and the 3 core partitioning strategies used in production.
1. Why Replication Is Not Enough: The 3 Hard Walls
In the previous module, you mastered Leader-Follower Replication — it scales read throughput beautifully. But Replication hits 3 physical limits that cannot be solved by adding more replicas:
Why it occurs: In replication, all database saves (INSERT/UPDATE) must go to a single Primary server so data stays consistent. As user activity surges, that one machine hits a hard limit on how many simultaneous write requests it can process and save.
Why it occurs: In replication, every replica holds an exact duplicate copy of the entire database. When total data grows past what a single physical machine can store, you run out of drive capacity, and search queries slow down drastically because the database cannot fit its search indexes into fast memory.
Why it occurs: Whenever the Primary writes a new record, it must immediately send that update over the network to every replica. With massive write traffic and many replicas, the Primary spends immense bandwidth and processing power just broadcasting copies, causing replicas to fall behind.
2. The 3 Core Sharding Strategies
Horizontal Partitioning (Sharding) splits rows of a table across multiple database servers. The critical decision is: how do you decide which rows go to which shard?
Example: Users A–M → Shard 1, Users N–Z → Shard 2.
✅ Pros: Simple to implement. Great for range queries (
WHERE created_at BETWEEN ...).❌ Cons: Traffic hotspots — if most usernames start with "S", Shard 2 gets hammered while Shard 1 idles.
shard = hash(user_id) % NExample:
hash(42) % 3 = 1 → Shard 1.✅ Pros: Extremely uniform data distribution. No hotspots.
❌ Cons: Adding/removing shards changes N, reshuffling almost all data. Range queries become impossible across shards.
user_id → shard_location.Example: Lookup says
user 42 → Shard 3.✅ Pros: Maximum flexibility. Can move individual users between shards without disruption.
❌ Cons: Lookup service becomes a single point of failure and a latency bottleneck.
3. Visualizing the Shard Router & Hash-Based Routing
Watch the live query routing below: The Shard Router receives queries, computes hash(key) % 3, and sends each query to the correct shard.
4. The Shard Key Dilemma & The Celebrity Hotspot Problem
Choosing your shard key is the most critical decision in database partitioning. A poor choice causes hotspots, where one server catches fire while the rest sit idle.
Imagine you shard an Instagram-like platform by author_id across 4 database shards. It works great until a mega-celebrity (like Cristiano Ronaldo or Lionel Messi with 100M followers) posts a viral photo.
author_id = 'ronaldo', 100% of those 30M requests slam into Shard 3. Shard 3 crashes, taking down normal users on that shard, while Shards 1, 2, and 4 sit at 1% CPU doing nothing.The routing algorithm is deterministic: hash(author_id) % 4.
- Sharding is designed to distribute different keys evenly across machines.
- However, sharding cannot split millions of queries for the EXACT SAME key.
- Since every like and comment points to the same author, every single query resolves to the exact same shard number. Adding 100 more shards does not help because all 30M queries will still hit Shard 3.
To survive mega-celebrities, production architectures use two core techniques:
The Idea: If writing to one single key chokes a database shard, we mathematically split that one key into 10 random sub-keys. Here is how writes and reads work step-by-step:
post_101), the App Server randomly generates a number between 0 and 9 (the "salt"):post_101_3 → hash('post_101_3') % 4 = Shard 0User B likes post → App creates key:
post_101_7 → hash('post_101_7') % 4 = Shard 2User C likes post → App creates key:
post_101_1 → hash('post_101_1') % 4 = Shard 1SELECT SUM(count) FROM likes WHERE post_id IN ('post_101_0', 'post_101_1', ..., 'post_101_9');1.2M + 1.1M + ... = 12M likes). Because querying 10 shards in parallel takes <5ms, the tiny read cost is 100% worth the massive 10× write scale.For viral posts being viewed by millions simultaneously, place a Redis / CDN edge cache in front of the database. 99.9% of read requests are served directly from RAM in 1ms, completely shielding the database shards from read storms.
user_id). Avoid low-cardinality fields like country (~200 values max).WHERE clause of 90% of queries to avoid expensive cross-shard lookups.5. Sharding Challenges & Operational Pitfalls
Sharding is powerful but introduces significant complexity. Here are the key trade-offs every engineer must understand:
If a query needs data from two different shards (e.g. "find all orders by User 42 AND Product reviews for item 99"), the coordinator must query multiple shards and merge results in memory — extremely expensive.
When a shard fills up, you must redistribute data across new shards. With hash-based sharding, changing N in hash(key) % N moves almost every row. Consistent Hashing solves this (covered in Track 4).
Auto-increment IDs don't work across shards (Shard 1 and Shard 2 would both create id=1). Solutions: UUID, Twitter Snowflake IDs, or a centralized ID generator service.
BEGIN TRANSACTION across shards requires a 2-Phase Commit (2PC) protocol — slow, complex, and a partial failure can leave data in an inconsistent state. Most apps avoid cross-shard transactions entirely.
6. The Full Picture: Sharding + Replication Combined
In real production systems, sharding does NOT replace replication — they combine. Each shard is its own replicated cluster:
7. Interactive Sharding Scenario Quiz
Test your sharding knowledge with real-world scenarios:
country_code. 40% of orders come from the US and 25% from India. Users report that the site is slow during US business hours. What went wrong?country_code has very low cardinality (~200 countries) and extremely uneven distribution (65% of traffic hits 2 shards). A better shard key would be order_id or user_id which distribute uniformly.hash(user_id) % 4. A product manager asks: "Can we add a feature to search all messages containing the word 'meeting' across all users?" What's the architectural concern?hash(song_id) % 3 to shard its catalog. A new viral hit gets 10 million plays in one hour. Is there a problem?8. System Design Interview Comparison Matrix
Sharding strategies quick reference table:
| Dimension | Range-Based | Hash-Based | Directory-Based |
|---|---|---|---|
| Data Distribution | Uneven (depends on key range) | Uniform (hash function guarantees) | Flexible (manually controlled) |
| Range Queries | Efficient (single shard scan) | Expensive (fan-out to all shards) | Depends on mapping design |
| Adding New Shards | Easy (extend the range) | Hard (rehash redistributes most data) | Easy (update lookup table) |
| Hotspot Risk | High (popular ranges overloaded) | Low (unless Celebrity Problem) | Low (can manually rebalance) |
| Complexity | Low | Medium | High (extra lookup service) |
| Used By | MongoDB (range), HBase | Cassandra, DynamoDB, Redis Cluster | Vitess (YouTube), Custom solutions |
Build Your Sharded Architecture
Design a horizontally partitioned database architecture: Route client traffic through an App Server to a Shard Router, then distribute queries to the correct database shards.
Task: Hash-Based Sharded Database Cluster
Drag components to arrange them freely, and click two nodes to connect them.