Hi Pythonistas!
Your system has:
- multiple servers.
- load balancer.
- Redis cache.
- CDN for static files.
Handling millions of requests.But all roads still lead to one place.The database.
App Server 1 ─┐
App Server 2 ─┼──→ Single Database
App Server 3 ─┘
Cache helps.But cache misses hit the database.Writes always hit the database.Cache doesn't help writes at all.
Your database has limits:
CPU → queries per second
RAM → data in memory
Disk I/O → read/write speed
Connections → simultaneous connections
Storage → total data
One machine. Hard limits.You hit them.What now? Two Approaches
Vertical scaling
- bigger machine.
- Has a ceiling.
- Expensive.
- Single point of failure.
Sharding
- split data across multiple machines.
- No ceiling.
- Each machine handles a subset.
That's what this post is about.
Partitioning vs Sharding
Two terms. Often confused.
Partitioning: Split data within one database instance.
One database server:
├── Partition 1 (users A-M)
└── Partition 2 (users N-Z)
Same machine. Data organized into parts.
Sharding
Split data across multiple database instances.
Shard 1 (users A-M) → Database Server 1
Shard 2 (users N-Z) → Database Server 2
Different machines. Each has a subset.Think of it like:
Partitioning → one bookshelf, books in sections
Sharding → multiple bookshelves in different rooms
Horizontal vs Vertical Partitioning
Horizontal Partitioning: Split by rows.
Partition 1: Partition 2:
id name id name
1 Yacob 3 Nithin
2 Arjun 4 Pranav
Each partition has all columns.Just different rows.This is what most people mean by sharding.
Vertical Partitioning
Split by columns.
Original:
id name email bio settings last_login
Core table (frequently accessed):
id name email last_login
Extended table (rarely accessed):
id bio settings
Frequently accessed columns in one table.Rarely accessed in another.Reduces row size.More rows fit in memory.Better performance for common queries.
Sharding Strategies
How do you decide which shard a record goes to?
Range Based Sharding
Split by value range.
Shard 1: user_id 1 to 1,000,000
Shard 2: user_id 1,000,001 to 2,000,000
Shard 3: user_id 2,000,001 to 3,000,000
Or by date:
Shard 1: orders Jan-Apr
Shard 2: orders May-Aug
Shard 3: orders Sep-Dec
Good:
- Range queries are efficient "Get all orders from January"→ only hits Shard 1
Bad:
- Hotspot problem.
- Most recent date shard gets all new writes.
- Old shards sit idle.
Hash Based Sharding
Hash the shard key.Use result to determine shard.
shard_number = hash(user_id) % number_of_shards
user_id 101 → hash(101) % 3 = 2 → Shard 2
user_id 202 → hash(202) % 3 = 0 → Shard 0
user_id 303 → hash(303) % 3 = 1 → Shard 1
Good:
- Even distribution.
- No hotspots.
Bad:
- Range queries terrible."Get all users created this month" → must query ALL shards→ merge results→ expensive
- Adding a new shard breaks everything.Before: hash(user_id) % 3 After: hash(user_id) % 4→ every record maps to different shard→ massive data migration This is why consistent hashing was invented.Remember from Post 7 (Load Balancing)?Same concept here.Minimal reshuffling when shards added or removed.
Directory Based Sharding
A lookup table maps each record to its shard.
Directory:
user_id 1-1000 → Shard 1
user_id 1001-2000 → Shard 2
user_id 2001-3000 → Shard 3
Before any query:
- check the directory.
- Get the shard.
- Query that shard.
Good:
- Flexible.
- Move data between shards without changing algorithm.
Bad:
- Directory = single point of failure.
- Extra network hop per query.
Geographic Sharding
Shard by user location.
Indian users → Mumbai database
US users → Virginia database
EU users → Frankfurt database
Good:
- Data compliance.
- GDPR requires EU user data stays in EU.
- Lower latency for local users.
Bad:
- Uneven if regions have different user counts.
- Cross-region queries are slow.
Finding the Right Shard Key
The shard key is the most important decision in sharding.
Wrong key = disaster.
Follow this process:
Step 1: Understand Access Patterns First
Before thinking about shard keys understand how your data is accessed.Write down your top 5 queries.The shard key should serve those queries.Not the other way around.
Example Blog platform:
Top queries:
1. Get all posts by user → user_id
2. Get user profile → user_id
3. Get user's followers → user_id
4. Get comments for post → post_id
5. Get post analytics → post_id
3 out of 5 are user-centric.Shard key = user_id.
Step 2 - Find the Primary Entity
Every system has one primary entity.Everything revolves around it.
E-commerce → customer
Social network → user
SaaS product → tenant / company
Messaging app → user / conversation
Ride sharing → trip / city
That primary entity is usually the right shard key.
Step 3: Check Four Properties
Every candidate shard key must pass four checks.
High cardinality:
- user_id → millions of unique values ✅
- country → ~200 values ❌
- status → 3-5 values ❌
- gender → 2-3 values ❌
Even distribution:
- user_id (random) → uniform ✅
- created_at → recent dates get more data ❌
- country → US users dominate ❌
Query alignment:
- Most queries filter by user_id? → shard by user_id ✅
- Most queries filter by date?→ shard by date ✅ (watch hotspots)
- Shard by order_id but query by user_id? → must scan ALL shards ❌
Low update rate:
- user_id → never changes ✅
- email → can change ❌
- username → can change ❌
If shard key changes: record must move to different shard.Expensive. Risky. Complex.
Step 4: Watch for Hotspots
Even a good key can create hotspots.
Celebrity hotspot:
Shard by user_id
Celebrity has 100M followers
→ all activity on her shard
→ overwhelmed
Solution - add randomness:
Spreads one user's data across 10 shards.Or special-case hot keys.Detect them. Cache aggressively. Route differently.
Real Examples
Uber:
Shard by city_id Most operations are city-scoped:
- "Find drivers in Bangalore"
- "Get trips in Bangalore today"
- City is the natural boundary.
WhatsApp:
- Shard by phone_number
- All messages to/from a number on same shard.
- Queries are always user-centric.
Shopify:
- Shard by shop_id
- Every merchant's data on same shard.
- No cross-shard queries for merchant operations.
Problems With Sharding
Sharding solves scale.But creates new problems.
Cross-shard queries
"Find users who signed up this month AND made a purchase" Users on Shard 1. Purchases on Shard 2.
→ query both shards
→ merge in application code
→ expensive
Cross-shard transactions
Transfer $100 from User A (Shard 1) to User B (Shard 2)
Step 1: Deduct from User A
Step 2: Add to User B
Step 2 fails → User A lost $100, User B got nothing
Distributed transactions are hard. We'll cover solutions in Phase 4 - Reliability.
Resharding
Traffic grows.3 shards not enough. Need 6.
Shard 1 data → split to new Shard 1 + Shard 4
Shard 2 data → split to new Shard 2 + Shard 5
Shard 3 data → split to new Shard 3 + Shard 6
Massive migration.System must stay live during it.Extremely complex.Consistent hashing minimizes this.
How Real Systems Solve Cross-Shard Problems
Denormalization
Store related data together on same shard.
Shard by user_id:
→ user profile
→ user's orders
→ user's payments
→ user's reviews
All on same shard. No cross-shard join needed.Tradeoff: data duplication.
Two-phase queries
Query each shard in parallel.Merge in application.
Global tables
Small stable tables copied to every shard.Countries, currencies, categories.No cross-shard join needed.
Distributed transactions (2PC)
For critical operations like payments.Slow. Used sparingly.Phase 4 goes deep on this.
When to Shard
Sharding is complex.Don't do it early.
Step 1: Optimize queries and indexes
Step 2: Vertical scale bigger database
Step 3: Read replicas separate reads and writes
Step 4: Caching reduce database load
Step 5: Only now → consider sharding
Most applications never need sharding.Instagram had millions of users before they sharded.Start simple.
Shard when you genuinely feel the pain.
The most important lesson:
- Don't think about sharding first.
- Think about your queries first.
- What data does my app need?
- How does it need it?
- What's the most natural grouping?
The shard key emerges from the answers.Not the other way around.
Partitioning
Within a Single DatabaseEven without sharding partition within one database.
CREATE TABLE orders (
id BIGINT,
created_at DATE,
amount DECIMAL
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2024_q1
PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE orders_2024_q2
PARTITION OF orders
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');Query Q1 orders → only scans Q1 partition.Not the entire table.Much faster.No code changes.Application still queries orders table.Database handles routing automatically.
Mental Model
Partitioning → split within one database
Sharding → split across multiple databases
Horizontal → split by rows
Vertical → split by columns
Range sharding → by value range, good for range queries
Hash sharding → by hash, even distribution
Directory sharding → lookup table maps key to shard
Geographic → by user location, compliance
Shard key → field used to determine shard
High cardinality → many unique values
Query alignment → key matches most common queries
Hotspot → one shard getting too much traffic
Denormalization → store related data on same shard
Resharding → adding shards, consistent hashing helps
Cross-shard query → query all shards, merge in app
What's Coming Next
Now you know how to scale your data horizontally.But sharding created a new problem.One database gets writes.
Other databases are idle.What if reads could be served by separate machines?
What if your database had multiple copies?
Database Replication.
How to scale reads separately from writes.
And what happens when the primary database goes down.
