Skip to content
LLD Learn/Planet-Scale Schema Design
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Sharding & Partitioning Strategies

4 min read

You'll learn to

  • -Explain sharding as splitting rows across servers by a shard key, and why that key is the key decision
  • -See a hot shard appear from a skewed key, and an even spread from a high-cardinality key
  • -Count how many rows move when the number of shards changes, and tell sharding from replication

Every technique so far has assumed a table fits on one database server. Sharding is what happens when a table outgrows what any single server can store or serve. It is split across many servers, and each server owns a subset of the rows.

An Analogy: Library Branches

A city library system with one building would be overwhelmed. So the city opens branches, and assigns each reader to a branch by a rule, such as the first letter of their surname. The rule is the shard key. If most readers' surnames begin with the same letter, one branch is swamped while the others sit empty, and the whole point of having branches is lost.

The Shard Key Is the Single Most Important Decision

A shard key decides which server a row lives on. Usually the key is hashed and the remainder after dividing by the number of shards picks the server. The entire design lives or dies on this one choice.

A shard key column, and the hashing logic that routes a row
-- 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

A hot shard is one that receives far more traffic than the rest. Sharding by a low-cardinality or skewed column recreates the very bottleneck you were avoiding. Low cardinality means few distinct values. The demo makes it concrete. It routes 10,000 readings to 4 shards, first by region, where the north generates 90 percent of them, and then by sensor id, where 500 sensors report 20 readings each.

Press Run: one shard takes 9,000 of 10,000 rows by region, and an even spread by sensor

Sharding by region put 9,000 rows on a single shard, and left one shard with nothing at all. Sharding by sensor id spread the rows between 2,200 and 2,800 per shard. A high-cardinality, evenly distributed key, such as a hashed sensor or user id, spreads load far better than a coarse category.

Re-Sharding Is Expensive, So Choose Deliberately

The last line of the demo is the warning. Going from 4 to 5 shards with this simple hash-and-remainder rule moved 401 of 500 sensors, about 80 percent of the data, to a different server. On a table already too big for one server, that is a slow and risky operation. (A technique called consistent hashing moves far fewer.) It is why the shard key deserves the same care as a primary key.

Replication: A Different Axis From Sharding

Sharding splits data across servers, so each holds different rows. Replication copies the same data to several servers, so each holds the same rows, for fault tolerance and to share read traffic. A single shard is usually replicated too: one primary takes the writes and replicas serve reads. That raises a consistency question. After a write, does a read always see it, or might it briefly see stale data from a replica that has not caught up?

Modeling a shard's replica topology as data
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_ms

Name the read-after-write trade-off unprompted: "reads from a replica may briefly lag the primary". Treating replication as a free, consistency-free way to scale 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.

Ready to Build This?

Design The Power Plant and Agent Smith Tracking schemas in the LLD Lab's Matrix act.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!