System Designer
Back to Reference

Database Sharding

Horizontal partitioning strategies and trade-offs

Not Started
Loading...

Database sharding splits data across multiple servers to achieve horizontal scaling. When a single database can't handle your load, sharding distributes both data and traffic. The challenge lies in choosing the right shard key and managing the complexity of distributed queries and transactions.

The golden rule: shard by the most common access pattern. If users always query their own data, shard by user_id. For multi-tenant apps, shard by tenant_id. Use consistent hashing to minimize resharding pain, and always plan for cross-shard queries—they're inevitable but should be rare.

Sharding Strategies Comparison
Implementation Complexity
2/5Range
5/5Consistent Hash
Data Distribution
2/5Range
5/5Hash
Range Query Performance
2/5Hash
5/5Range
Resharding Ease
2/5Range
4/5Consistent Hash

Horizontal (Range-based)

Split data by ranges of shard key values

Low Complexity
Medium Performance
Pros
  • •Simple to understand
  • •Easy range queries
  • •Predictable data location
Cons
  • •Hotspots possible
  • •Uneven data distribution
  • •Manual rebalancing
Example: User IDs 1-1M → Shard1, 1M-2M → Shard2

Hash-based

Use hash function to determine shard placement

Medium Complexity
High Performance
Pros
  • •Even distribution
  • •Automatic balancing
  • •No hotspots
Cons
  • •Complex range queries
  • •Difficult resharding
  • •Hash function dependency
Example: hash(user_id) % shard_count = target_shard

Directory-based

Lookup service maps keys to shards

High Complexity
Medium Performance
Pros
  • •Flexible routing
  • •Dynamic rebalancing
  • •Query optimization
Cons
  • •Additional complexity
  • •Lookup service SPOF
  • •Extra network hop
Example: Directory service: user_123 → shard_7

Consistent Hashing

Hash ring distributes load with minimal reshuffling

High Complexity
High Performance
Pros
  • •Minimal data movement
  • •Handles failures well
  • •Elastic scaling
Cons
  • •Complex implementation
  • •Uneven loads possible
  • •Virtual nodes needed
Example: Amazon DynamoDB, Cassandra ring topology
Sharding Challenges & Solutions

Sharding introduces complexity. Here are the main challenges and proven approaches to solve them.

Cross-shard Queries

Queries spanning multiple shards are expensive

Solutions:
  • Denormalization: Duplicate data to avoid joins
  • Application-level joins: Fetch and combine in app
  • Read replicas: Aggregate data for reporting
  • Event sourcing: Rebuild views from event stream

Distributed Transactions

ACID guarantees across shards are complex

Solutions:
  • Avoid when possible: Design for single-shard transactions
  • 2PC (Two-Phase Commit): Slow but consistent
  • Saga pattern: Compensating transactions
  • Eventually consistent: Accept temporary inconsistency

Shard Key Selection

Wrong shard key leads to hotspots and poor performance

Solutions:
  • High cardinality: Many unique values
  • Even distribution: Avoid skewed access patterns
  • Query alignment: Match common query patterns
  • Immutable keys: Avoid changing shard placement

Resharding

Adding/removing shards requires data migration

Solutions:
  • Pre-shard: Over-provision initially
  • Virtual shards: Multiple virtual shards per physical
  • Live migration: Move data without downtime
  • Consistent hashing: Minimize data movement
Real-world Sharding Examples

Instagram

400M+ users, 1000s of shards
Approach

User ID-based sharding

PostgreSQL sharded by user_id using Django ORM

Key Lessons
  • •Started simple with range sharding
  • •Migrated to hash-based for better distribution
  • •Pre-allocated shard space for growth

Discord

150M+ users, billions of messages
Approach

Guild-based sharding

Cassandra clusters sharded by guild (server) ID

Key Lessons
  • •Guild-based keeps related data together
  • •Consistent hashing handles node failures
  • •Hot guilds can still cause issues

Uber

100M+ users, global presence
Approach

Geographic + hash sharding

MySQL sharded by city + hash(trip_id)

Key Lessons
  • •Geographic sharding reduces latency
  • •City boundaries can be problematic
  • •Multi-level sharding adds complexity

Pinterest

400M+ users, billions of pins
Approach

Object type-based sharding

HBase/MySQL sharded by object type and ID

Key Lessons
  • •Different object types have different access patterns
  • •Board data kept together for performance
  • •Cross-type queries are expensive
Performance Impact & Metrics

Key performance considerations when implementing database sharding.

Query Performance

single Shard
95%
cross Shard
5%
Impact: 10-100x slower
Mitigation: Design for single-shard queries

Data Movement

range Sharding
High
hash Sharding
Medium
consistent Hash
Low
Mitigation: Use consistent hashing

Hotspot Risk

range Sharding
High
hash Sharding
Low
directory Based
Medium
Mitigation: Monitor shard utilization

Sharding Best Practices

  • • Start with vertical scaling first
  • • Choose shard key carefully - it's hard to change
  • • Design for single-shard queries when possible
  • • Monitor shard utilization and hotspots
  • • Plan for resharding from day one
  • • Consider read replicas before sharding

Common Mistakes

  • • Sharding too early or too late
  • • Using timestamp as shard key (hotspots)
  • • Ignoring cross-shard query performance
  • • Not planning for data migration
  • • Tight coupling between shards
  • • Over-optimizing for edge cases

📝 Test Your Knowledge

6 questions • Progress: 0/6