Database Sharding & Partitioning

Database Sharding — Horizontal Partitioning with Shard Router

When a single database server can no longer handle your data volume or write throughput — even with vertical scaling — you must split your data across multiple servers. This is sharding (also called horizontal partitioning).

[!IMPORTANT] Sharding is a last resort, not a first step. The operational complexity of sharding is enormous. Exhaust all other options first: indexing, query optimization, read replicas, caching, and vertical scaling. Shard when you genuinely must.

Vertical Partitioning vs Horizontal Partitioning (Sharding)

	Vertical Partitioning	Horizontal (Sharding)
Split by	Table / feature	Row / ID range
Complexity	Low	High
Scale limit	Still limited by largest table	Linear with shard count
Cross-join queries	Impossible (different servers)	Complex (scatter-gather)

Sharding Strategies

PYTHON PLAYGROUND

⏳ Loading editor…

The Hotspot Problem

Cross-Shard Queries & Scatter-Gather

The biggest pain point of sharding: queries that need data from multiple shards:

PYTHON PLAYGROUND

⏳ Loading editor…

Quiz

Question 1 of 2

You shard a user database by `user_id % 4` (4 shards). Later you add a 5th shard and change to `user_id % 5`. What percentage of data needs to be migrated?

~20% (only the data for the new shard)

~80% (most records map to different shards with the new modulus)

~50% (half the data moves)

Key Takeaways

✅ Shard last — try read replicas, caching, and vertical scaling first.
✅ Hash sharding gives even distribution but resharding migrates ~80% of data; use consistent hashing to minimize migration.
✅ Range sharding enables efficient range queries but risks hotspots on sequential keys (like timestamps).
✅ Scatter-gather pattern runs cross-shard queries in parallel — latency = slowest shard, not sum of all shards.
✅ Cross-shard JOINs, COUNT, ORDER BY are extremely expensive — design your shard key around your most critical query.

Official Resources

📖 PostgreSQL Table Partitioning — Built-in partitioning
📖 Vitess (YouTube's MySQL sharding) — Battle-tested sharding for MySQL
📖 Discord's database sharding at scale — Real-world case study
📖 Instagram's sharding approach — ID generation + sharding

What's Next?

Sharding handles write scale — but how do you handle read scale and what happens when a database server goes down?
Next: Replication, CAP Theorem & Consistency — Copying data across multiple servers and the fundamental trade-offs involved.