Database sharding is a core scaling technique you’ll need to explain in nearly any large-scale system design interview. The question usually appears as a follow-up: “Your database is a bottleneck — a single Postgres instance can’t handle this write throughput. How do you scale it?”
Strategy
Start with what sharding solves and when it’s actually necessary. Interviewers respect candidates who know that sharding is a last resort — it adds significant operational complexity. Before sharding, you should have:
- Indexed correctly — most “slow database” problems are missing index problems
- Added read replicas — offload read traffic to replicas; only writes go to primary
- Added caching — Redis or Memcached in front of the DB for hot reads
- Vertically scaled — bigger instance (more RAM, faster SSD, more CPU)
Once write throughput or data volume exceeds what a single node can handle — then you shard.
What is Sharding?
Sharding (also called horizontal partitioning) splits your data across multiple database servers called shards. Each shard holds a subset of the data and operates independently. Together, the shards hold the complete dataset.
Contrast this with vertical partitioning, which splits a table by columns (e.g., putting large BLOB columns on a separate server). Sharding splits by rows.
Without sharding:
┌─────────────────────┐
│ users table │
│ 100M rows │ ← single Postgres instance, bottleneck
└─────────────────────┘
With sharding (4 shards):
┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ Shard 0 │ │ Shard 1 │ │ Shard 2 │ │ Shard 3 │
│ user_id │ │ user_id │ │ user_id │ │ user_id │
│ 0–24M rows │ │ 25–49M rows │ │ 50–74M rows │ │ 75–99M rows │
└──────────────┘ └──────────────┘ └──────────────┘ └──────────────┘Sharding Strategies
1. Range-Based Sharding
Assign rows to shards based on ranges of the shard key.
Example: shard users by user_id:
- Shard 0: user_id 0–9,999,999
- Shard 1: user_id 10,000,000–19,999,999
- Shard 2: user_id 20,000,000+
Pros: Simple to reason about. Range queries (“get all users created this month”) stay on one shard.
Cons: Hotspots. New users always land on the last shard. If your user_ids are monotonically increasing (common with auto-increment PKs), shard 2 gets all the new writes while shards 0 and 1 idle. This is a classic hotspot problem: one shard is hot, the others idle. (The celebrity problem is a different failure mode: a single hot key, like one account with tens of millions of followers, overwhelms its shard no matter how evenly the keys are distributed.)
2. Hash-Based Sharding
Apply a hash function to the shard key, then mod by the number of shards.
shard_index = hash(user_id) % num_shardsPros: Uniform distribution. No hotspots (assuming a good hash function). Easy to implement.
Cons: Adding or removing shards requires rehashing — nearly every row moves to a different shard. This is the resharding problem (see below). Range queries hit every shard since adjacent keys hash to random shards.
3. Consistent Hashing
Uses a hash ring (see the Consistent Hashing post) to minimize key movement when shards are added or removed. Only ~1/n of keys need to move when adding the nth shard.
This is what Cassandra, DynamoDB, and many distributed databases use internally. For a product that needs to add capacity regularly without migrations, consistent hashing is the right answer.
4. Directory-Based Sharding
Maintain a lookup table (directory) that maps each key (or range of keys) to a specific shard.
┌─────────────────────────────┐
│ Shard Directory (in Redis) │
│ user_id 1–1000 → Shard 0 │
│ user_id 1001–5000 → Shard 1│
│ user_id 5001–... → Shard 2 │
└─────────────────────────────┘Pros: Maximum flexibility. You can rebalance individual ranges without touching everything else. Can route VIP customers to dedicated high-performance shards.
Cons: The directory itself is a single point of failure and must be highly available. Every read/write requires a directory lookup (extra latency hop). Directory must stay consistent.
Choosing a Shard Key
The shard key choice determines everything. A bad shard key dooms your system:
Good shard key properties:
- High cardinality — enough distinct values to spread load across all shards
- Low frequency imbalance — no single value that accounts for a huge fraction of traffic (user_id is better than country_code)
- Co-locate related data — queries that need multiple rows should find those rows on the same shard. For a messaging app, shard by
conversation_idso all messages in a conversation stay together
Common shard key mistakes:
- Sharding by timestamp: new writes always go to the latest shard (hotspot)
- Sharding by a low-cardinality field like
status(active/inactive): only 2 distinct values, can’t spread across 10 shards - Sharding by a field you frequently query with range conditions, when using hash sharding
Challenges You Must Know
Resharding
When a shard fills up or gets too hot, you need to split it. This requires migrating data from the overloaded shard to a new one while keeping the system live. Usually done with a double-write period: write to both old and new shard, gradually migrate reads, then cut over. Painful. Consistent hashing minimizes (but doesn’t eliminate) this.
Cross-Shard Queries
Queries that join data from multiple shards require a scatter-gather: fan out to all shards, collect results, merge in the application layer.
// Example: "find all users who signed up in the last 7 days"
// With hash sharding on user_id, this query hits every shard:
results = []
for shard in all_shards:
results += shard.query("SELECT * FROM users WHERE created_at > NOW() - 7 DAYS")
return merge_and_sort(results)This is slow and operationally expensive. It’s why sharding decisions must be made based on your most common query patterns.
Distributed Transactions
Transactions that touch multiple shards can’t use a single database’s ACID guarantees. You need either:
- Two-phase commit (2PC) — slow, complex, still used in traditional sharded RDBMS setups
- Saga pattern — sequence of local transactions with compensating transactions for rollback
- Redesign to avoid cross-shard transactions — often the best answer
Hotspots (the Celebrity Problem)
Even with hash sharding, some keys get dramatically more traffic than others. A tweet from Elon Musk, a Reddit post going viral — the shard holding that row gets hammered. Solutions: application-level caching (Redis) in front of the hot row, read replicas per shard, or special-casing high-traffic keys with dedicated infrastructure.
Sharding vs. Other Scaling Approaches
| Approach | Write Scale | Read Scale | Complexity |
|---|---|---|---|
| Single node | ✗ | ✗ | Low |
| Read replicas | ✗ | ✓ | Low |
| Caching layer | ~✓ (offload) | ✓ | Medium |
| Vertical scaling | Limited | Limited | Low |
| Sharding | ✓ | ✓ | High |
Summary
Database sharding splits data across multiple servers so that no single node becomes the bottleneck. Hash-based sharding gives uniform distribution but makes resharding painful. Range-based sharding enables efficient range queries but risks hotspots. Consistent hashing is the production standard for systems that need to scale nodes up and down frequently. Whatever strategy you choose, pick your shard key carefully — it determines your query patterns, your hotspot risk, and your operational burden for years to come.
Related System Design Topics
Sharding is one of several scaling tools — these topics come up together:
- Consistent Hashing — the standard algorithm for distributing keys across shards with minimal remapping when nodes are added or removed.
- CAP Theorem — sharded systems must choose a position on the consistency-availability spectrum, especially for cross-shard operations.
- Caching Strategies — adding a cache layer is almost always the right first scaling step before committing to the complexity of sharding.
- Load Balancing — a shard router (directory node) is a form of application-layer load balancing across database shards.
Also see: API Design (REST vs GraphQL vs gRPC) and SQL vs NoSQL — the remaining two system design foundations.
See also: Design a Distributed ID Generator (Snowflake) — how 64-bit time-sortable IDs encode shard routing information in their bit layout.
Keep sharpening your system design:
