When to shard
Single-node limits: CPU, RAM, disk IOPS, backup window, blast radius. Vertical scale first; shard when the working set or write rate exceeds one machine — or when regulatory isolation requires it.
Sharding = horizontal partitioning: each shard holds a subset of rows, each shard is typically its own database instance.
Shard key selection
The shard key determines which node owns a row — and which queries are local vs scatter-gather.
| Strategy | Routing | Pitfall |
|---|---|---|
| Hash(shard_key) | Even spread | Range queries hit all shards |
| Range(shard_key) | Efficient range scans | Hot last shard (today's data) |
| Directory lookup | Flexible | Lookup service is bottleneck |
Colocation — related rows on same shard (same tenant_id) — keeps joins local.
Cross-shard pain
- JOIN across shards — application-side merge or denormalize
- Global uniqueness — UUIDs or per-shard sequences + offset
- Transactions — 2PC across shards is rare/slow; sagas or per-shard atomicity
- Resharding — moving key ranges while serving traffic (Vitess resharding, Citus rebalance)
Vitess (MySQL sharding middleware)
App → VTGate (query router) → VTTablet per shard → MySQL instance
↑
VSchema (shard key → shard map)
VTGate parses SQL, routes by VSchema, can scatter queries. Used at YouTube scale; hides shard topology from many apps.
Citus (distributed Postgres)
| Table type | Behavior |
|---|---|
| Distributed | Sharded by distribution column |
| Reference | Replicated to all nodes (dimension tables) |
| Local | Single-node only |
Colocated distributed tables share shard key — local joins without network shuffle.
Federation (lighter alternative)
Postgres FDW, MySQL FEDERATED — query remote tables through one connection. Useful for cross-DB reporting, not high-QPS OLTP.
Cross-reference
General partitioning concepts (consistent hashing, rebalancing) → Distributed Systems → Partitioning.
Senior-level signal
"We'll shard later" without choosing a tenant-isolated shard key upfront often forces painful migration. For multi-tenant SaaS, tenant_id as shard key is the default winning pattern.
Where this goes next
Document Databases — when relational sharding isn't the model; flexible documents and different replication topologies.
Further Reading
Hands-On Tasks (Optional)
Low-setup exercises — schema drills, paper walkthroughs, or optional local installs. No autograding; the goal is interview fluency on how data is stored.
- Pick a shard key for a SaaS app20m
Multi-tenant app with `tenants`, `users`, `orders`. Propose a shard key, which tables colocate, and one query that becomes expensive after sharding. No cluster required.