Lesson 19 · At scale

Sharding

When one table outgrows one server, sharding splits its rows across several servers, called shards. Follow six orders as a simple rule, customer_id % 3, sends each row to one shard; watch a query with the shard key go to one shard while a query without it must ask them all; then see what a hot key and a new shard cost.

12 min10 steps5 questionsAdvanced

Fig 19 · Interactive

Loading figure…

or ← →

Check yourself

5 quick questions

Pick an answer to see why. Your score saves with your progress.

01What does sharding split across servers?

Each shard holds some of the rows, with the same columns. Full copies of the table are replication, which shares out reads but not writes.

02The rule is customer_id % 3. Which shard holds the orders of customer 11?

11 divided by 3 is 3 remainder 2, so 11 % 3 = 2: shard 2. Every row with customer_id 11 lands there.

03The table is sharded by customer_id. Which query can the router send to just one shard?

Only the query that names the shard key lets the router run the rule (7 % 3 = 1) and pick one shard. The others must ask every shard and combine the parts.

04The three shards report partial sums of 60, 80 and 60. What does the router return for SELECT sum(total) FROM orders?

Each shard sums only its own rows; the router adds the parts, 60 + 80 + 60 = 200, which is the same answer one server would give.

05With the rule key % N, the shop goes from 3 shards to 4. Why is that expensive?

Changing N changes most remainders. Real systems split the data into many small pieces first (or use consistent hashing), so adding a server moves only some whole pieces.

Key terms

Glossary

Sharding: Splitting one table's rows across several database servers, so each server holds and serves only some of them.
Shard: One of those servers (or the slice of rows it holds). Every shard has the same columns but different rows.
Shard key: The column the rule reads to pick a row's shard, such as customer_id. A good one spreads rows evenly and appears in most queries.
Router: The part that knows the rule and where every shard is, and sends each query to the right shard or shards. It can be a proxy or a library in the app.
Modulo (%): The remainder after a division: 7 % 3 = 1. A simple rule to turn a key into a shard number.
Scatter-gather: Sending a query to every shard and combining their partial answers, used when the query does not name the shard key.
Hot spot: A shard that gets far more work than the others, for example because one key is very busy.
Rebalancing: Moving data between shards when shards are added or removed, so each holds a fair share again.

Fine print

Notes and sources

To keep the arithmetic checkable, the figure uses customer_id % 3 directly. Real systems hash the key first, so neighbouring keys such as 7 and 8 land far apart; PostgreSQL’s hash partitioning takes the hash value, then the remainder.
With a plain "key % N" rule, changing N moves most rows: here 5 of 6. Systems such as Citus, Vitess and Cassandra split the data into many more pieces than servers (or use consistent hashing), so adding a server moves only some whole pieces.
PostgreSQL itself partitions a table on one server. Its partitions can be foreign tables on other servers (postgres_fdw), and extensions such as Citus spread the shards over many servers and route queries for you.
Sharding and replication work together: each shard is usually a small primary with its own replicas, so a shard can fail over without losing its rows.
Some things get harder once rows live on different servers: a join or a unique check across shards, or a transaction that changes rows on two shards, needs the servers to coordinate.
The Python example uses three in-memory SQLite databases as three shards. Run it and it prints [(1, 7, 30)] then [60, 80, 60] 200.

Quiz Unlocks after the last step.