Sharding (Horizontal Partitioning)

Splitting a large table into smaller pieces across multiple machines — each machine holds a subset of rows.

Sharding vs Partitioning

• Partitioning → splitting data within SAME machine (logical separation)
• Sharding → splitting data across DIFFERENT machines (physical separation)

The Shard Key

The column used to decide which shard a row goes to. Good shard key properties:

• High cardinality (many unique values)
• Evenly distributed (no hotspots)
• Frequently used in queries
• Doesn't change often

Sharding Strategies

1. Range Based Sharding

Shard 1 → user_id 1 to 1,000,000
Shard 2 → user_id 1,000,001 to 2,000,000

• Pros: Simple to implement. Range queries are fast (nearby IDs on same shard).
• Cons: Hotspot problem — new users always go to latest shard.

2. Hash Based Sharding

shard_number = hash(shard_key) % num_shards

• Pros: Even distribution (no hotspots). Simple to compute.
• Cons: Range queries hit ALL shards. Adding new shards → rehashing most data.

3. Consistent Hashing

Place shards AND keys on a virtual ring. Key goes to nearest shard clockwise. Adding new shard → only keys between new shard and its predecessor need to move → only 1/N of data moves. Used by Cassandra, DynamoDB, Discord.

4. Directory Based Sharding

Lookup table maps key → shard. Most flexible routing. Cons: Lookup table = single point of failure. Extra hop for every query.

The Hotspot Problem

Bad shard key → one shard gets all traffic. Example: shard by created_at → ALL new writes go to latest shard. Solution: choose shard key with even distribution, use consistent hashing.

Cross Shard Queries — The Big Problem

SELECT * WHERE age > 25 → must query ALL shards, gather results, merge and sort. Called SCATTER-GATHER. JOINs across shards are extremely expensive — design to avoid them.

When to Shard

Don't shard prematurely! First try: vertical scaling → read replicas → caching → better indexes → query optimization. Only shard when single machine can't hold data or write throughput exceeds single machine (usually 100M+ rows or >10k writes/second).

Sharding vs Replication

	Sharding	Replication
Data	Different data on different nodes	Same data copied to multiple nodes
Improves	Read AND write performance	READ performance and availability
Coverage	Each shard has PARTIAL data	All replicas have ALL data