Sharding & Partitioning Strategies
You'll learn to
- -Choose a shard key that avoids hot spots at billion-row scale, and explain the re-sharding cost of a bad choice
- -Design a replication-aware schema and reason about the consistency trade-offs it introduces
Every technique in this course so far has assumed a table can live entirely on one database server. Sharding is what happens when a table grows past what any single server can hold or serve - splitting it across many servers, each owning a subset of the rows.
The Shard Key Is the Single Most Important Decision
A shard key determines which server a given row lives on - typically computed via a hash of a chosen column, or a range partition on it. The entire design lives or dies on this one choice: a bad shard key creates a "hot" shard that receives dramatically more traffic than the others, defeating the entire purpose of sharding in the first place.
-- A billion-row power grid readings table, sharded by grid_sector_id
CREATE TABLE power_readings (
id BIGINT NOT NULL,
grid_sector_id INTEGER NOT NULL, -- the shard key
reading_value DECIMAL(10, 2) NOT NULL,
recorded_at TIMESTAMP NOT NULL,
PRIMARY KEY (grid_sector_id, id) -- shard key is part of the primary key
);
-- Application-level routing: shard_number = hash(grid_sector_id) % num_shards
-- Every query MUST include grid_sector_id to be routed efficiently to one shard -
-- a query without it has to fan out to every shard and merge results.Picking a Shard Key That Avoids Hot Spots
Sharding by a column with low cardinality or skewed distribution (like "power grid region," if one region generates 90% of readings) recreates the exact bottleneck sharding was meant to eliminate - one shard doing nearly all the work. A high-cardinality, evenly-distributed key (a hashed user_id or sensor_id, rather than a coarse category) spreads load far more evenly across shards.
Re-Sharding Is Expensive - Choose Deliberately Upfront
Changing the shard key after the fact means physically moving a large fraction of existing rows to different servers to match the new routing logic - an expensive, high-risk operation on a table already too large for a single server, which is exactly why the shard key deserves the same upfront deliberation as a primary key choice, not a decision to revisit casually later.
Replication: A Different Axis From Sharding
Sharding splits data across servers (each server holds different rows); replication copies the same data across multiple servers (each server holds the same rows), for fault tolerance and read scalability. A single shard is typically itself replicated - one primary handling writes, one or more replicas serving reads - which introduces a consistency question: does a read immediately after a write reliably see that write, or can it briefly see stale data from a replica that has not yet caught up?
CREATE TABLE shard_replicas (
shard_id INTEGER NOT NULL,
replica_host TEXT NOT NULL,
role TEXT NOT NULL CHECK (role IN ('primary', 'replica')),
replication_lag_ms INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (shard_id, replica_host)
);
-- application logic: writes always route to the 'primary' replica_host
-- for a given shard_id; reads may route to any replica, accepting
-- possible staleness up to replication_lag_msNaming the read-after-write consistency trade-off explicitly - "reads from a replica may briefly lag behind the primary" - is worth doing unprompted on this problem; treating replication as a purely transparent, consistency-free scaling trick is a common and costly oversimplification.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design The Power Plant and Agent Smith Tracking schemas in the LLD Lab's Matrix act.