Sharding vs Partitioning vs Replication Lab (Interactive)
Allocate shards, replicas, and partitions against a live read/write/storage demand profile and see who fixes what. Prove by arithmetic which pillar resolves which bottleneck: replication scales reads, partitioning prunes scans, sharding alone scales writes.
Replication vs Partitioning vs Sharding Allocator
Compose the 3 scaling pillars to satisfy a workload demand — each pillar fixes a different bottleneck.
need 12,000/s / have 10,000/s
only SHARDING scales this
need 30,000/s / have 20,000/s
REPLICAS scale this linearly
need 3 TB / have 2 TB
replication adds ZERO capacity
Physical servers
1
each shard = 1 primary + 0 replicas
Data per node
100%
100% = replication duplicate, 1/N = shard subset
Time-range scan
100% of table
partition pruning 1x faster
Native SQL JOINs
YES
complexity score 0/5
Notice the failure pattern: adding replicas never fixes the write or storage bar, and partitioning never raises the single-node CPU ceiling. Production architectures stack all three — a sharded cluster where each shard is partitioned by month and backed by two read replicas.
How It Works Under the Hood
Engineers conflate three distinct mechanisms. Replication copies the full dataset to follower nodes, scaling reads and providing failover but adding zero write throughput or storage headroom. Table partitioning slices one huge table into sub-files on a single server, enabling partition pruning and O(1) DROP TABLE eviction while remaining bound by one machine. Sharding distributes disjoint row subsets across independent servers, the only pillar that linearly scales writes and aggregate storage, and it taxes away cross-shard joins.
Core Architectural Principles
- Replicas each store 100% of data: read capacity grows N-fold, usable storage stays flat.
- Partition pruning reads 1/Pth of pages for time-filtered queries on the same node.
- Shards store 1/Nth of rows each but prohibit native cross-shard SQL joins and foreign keys.
Define all three in one breath before choosing: duplication for reads, local sub-tables for pruning, distributed subsets for writes. Interviewers probe confusion here, so explicitly say replicas do not scale writes and partitioning does not escape single-node limits. Close with the production combo: sharded cluster, each shard partitioned by month with two read replicas.
Combining all three pillars maximizes headroom but stacks their complexity: routing, pruning plans, and failover orchestration together.