Module 4 · Storing Data That Keeps Growing · Lesson 18 of 35
What is database sharding? Splitting data across machines
13 minFree System design course · Beginner to intermediateLesson 4.5By Saket Bhatnagar
Researched and drafted with AI, reviewed and edited by Saket Bhatnagar.
In short
- What is database sharding? It splits one big database across several machines, so each one holds and answers for only part of the data.
- One column you choose, such as the user id, or a pair of columns, decides which machine holds each row.
- Pick that column so the busy traffic spreads too, or one machine melts while the rest sit idle.
Video lesson · coming soon
What is database sharding? Splitting data across machines
Lala, your AI mentor, will walk you through this lesson in a short animated video. Until then, everything is here to read and try.
- 01 Four shards, and one still melts
- 02 PIN codes and post offices
- 03 The router picks one shard
- 04 Pick the shard key
- 05 Four shards to five moves 16 of 20
- 06 Defend your shard key
The two replicas did their job, and “My bookings” is fast again. But the question from lesson 17 still stands: 50,000 bookings a minute, about 830 a second. What happens to the writes?
Zoya picks up a marker in the Baner office. “When we are ten times bigger, which part of the database dies first?” Arjun says reads. “We fixed reads,” she says. “It is the writes, and the disk.”
One big primary can usually take 830 bookings a second today. But each booking also writes a booking row, a payment row and a seat update, all on one machine. The full booking history across 6 states, about 2 TB, keeps growing on its one disk. And if it fails, everything stops.
“A million letters and one post office,” Zoya says. Arjun has a quick fix: one database per match. Zoya asks, “At noon, which database does the Monsoon T20 Cup final live on?” Only one, he sees. So if the data is split, why does one piece still melt?
What is database sharding?
Sharding splits one big database across several machines, and each machine holds a different part of the data. The writes and the disk are shared out.
Each of those machines, with its own slice, has a name. SeatJaldi would start with four.
Here is Arjun’s split by match, spread over four shards, each holding many matches. A router sits in front: a small program that reads each query and picks one shard. Follow the noon traffic.
A router in front of four shards, one running hot
Drag to exploreThe app sends every query to a router, which reads match_id, picks one shard and passes the answer back. Match 42, the final, lands on Shard 3, which runs hot at noon.
Diagram connections:
- App servers to Shard router (Every query)
- Shard router to Shard 1 (Other matches)
- Shard router to Shard 2 (Other matches)
- Shard router to Shard 3 (hot) (Match 42)
- Shard router to Shard 4 (Other matches)
- App servers: Send every database query through the router.
- Shard router: Runs match_id through a hash function and picks one shard.
- Shard 1: Holds other matches. Quiet at noon.
- Shard 2: Holds other matches. Quiet at noon.
- Shard 3 (hot): Holds match 42, the final. Most noon traffic lands here.
- Shard 4: Holds other matches. Quiet at noon.
The one idea to keep: split by a key, then ask where the traffic piles up.
- A food app crawls in one city on match night. That city’s data sits on one shard, and the whole city hits it.
- A flash-sale item’s page is slow while the rest of the shop is quick. All that product’s traffic lands on one shard.
- An order number search is instant; a phone number search pauses. The order number names the shard; the phone number means asking every shard.
How do you choose a shard key?
Choose a shard key that has many different values, spreads the load evenly, and appears in your common queries.
A PIN code works because there are many of them and letters spread across them. Sorting by “letters sent today” would send every new letter to one office. Shard 3 is that buried office at noon.
Why is adding a shard so painful with hash mod N?
With hash mod N, changing N changes the home of almost every key. It is like redrawing every PIN area when one new office opens: most letters change office.
Mod means the remainder after dividing; here shards are numbered from 0. User 812 with 4 shards: 812 / 4 = 203, remainder 0. With 5 shards: 812 / 5 = 162, remainder 2. User 812 moves. Before the table, guess: does key 5 stay or move?
| Key | Shard with 4 | Shard with 5 | Moves? |
|---|---|---|---|
| 1 | 1 | 1 | Stays |
| 2 | 2 | 2 | Stays |
| 3 | 3 | 3 | Stays |
| 4 | 0 | 4 | Moves |
| 5 | 1 | 0 | Moves |
| 6 | 2 | 1 | Moves |
| 7 | 3 | 2 | Moves |
| 8 | 0 | 3 | Moves |
What just happened? Carry on to 20, and only keys 1, 2, 3 and 20 stay: 16 of 20 move, 80%. A key stays only when its remainder matches for 4 and 5, which is 1 key in 5. A router that hashes the key first and then takes mod N moves about the same share.
How many shards does SeatJaldi need?
Count the shards storage needs and the shards writes need, and build for the bigger number.
- A range split gives each shard a run of keys, such as user_id 1 to 1 lakh. Nearby keys stay together, but a key that keeps rising sends every new row to the last shard. Hashing spreads rows evenly, but a range query asks many shards.
- A query without the shard key asks every shard and waits for all. The MongoDB docs call this scatter/gather and warn it can run long.
- Keep rows used together on one shard, such as one user’s bookings. A transaction across shards is slow or hard.
- Moving data later is costly, so many teams start with more slices than machines.
CREATE TABLE bookings (
booking_code text NOT NULL,
user_id bigint NOT NULL,
match_id int NOT NULL
) PARTITION BY HASH (user_id);
CREATE TABLE bookings_p0 PARTITION OF bookings
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- bookings_p1 to p3: the same, with REMAINDER 1, 2 and 3What just happened? This is partitioning inside one PostgreSQL database, the one-machine cousin of sharding. A row goes to the piece whose REMAINDER is left when its user_id’s hash is divided by 4. That hash is not the plain number: user 812 lands in bookings_p2, not p0. Until all four pieces exist, some inserts fail.
Try it yourself
Size for storage
Assume 3 TB of data and 1 TB per node. How many shards?
Size for writes
Assume 1 lakh (100,000) writes a second at a peak and 10,000 a second per node. These are practice numbers, not from the 830 bookings.
Take the bigger answer
Like post offices: a city needs room for every parcel and counters for the daily rush. Build for whichever needs more.
Count the copies
Keep 3 copies of each shard, a primary and two replicas. How many nodes?
Check your work
Storage: 3 / 1 = 3 shards. Writes: 100,000 / 10,000 = 10 shards. Take 10: 10 x 3 = 30 nodes. If you got 3 shards or 9 nodes, you sized for storage only.
So why does one piece still melt? Arjun splits by match, and at noon only one match is on sale. Every request for match 42 goes to one post office in its festival rush. Splitting spreads the data; only the right key spreads the load.
Zoya shards booking history by user_id over four shards. The writes and the growing 2 TB now split four ways, and each “My bookings” read touches one shard. For the final’s seats, she redraws the busy area into smaller ones: match plus stand.
Next year SeatJaldi will need a fifth shard, and hash mod N would move 16 keys in 20. Is there a way to add a fifth shard without moving almost everything?
Check yourself
Check yourself
SeatJaldi shards the seats table by match_id. What is true at noon on the final’s sale?
Pick an answer to see why it is right or wrong.
Check yourself
Going from 4 shards to 5 with hash mod N, about how much data changes shard?
Pick an answer to see why it is right or wrong.
Check yourself
Which shard key keeps “My bookings” on one shard and spreads the load?
Pick an answer to see why it is right or wrong.
Remember these three
- Split by a key, then ask where the hot spot is.
- A good shard key has many values, spreads the load and is in your common queries.
- With hash mod N, going from 4 shards to 5 moves about 4 keys in 5, so plan for growth early.
Questions people ask
- What is database sharding?
- Database sharding splits one big database across several machines, called shards. A shard key decides where each row lives, so each shard serves only part of the data.
- How do you choose a shard key?
- Choose a key with many values that spreads the load and appears in your common queries. Avoid keys that keep growing, like dates, or that have few values.
- What is the difference between partitioning and sharding?
- Partitioning splits one large set of data into smaller parts, often inside one database. Sharding puts the parts on different machines, with a router in front that sends each query to the right one.
- What is a hot spot in a database?
- A hot spot is one shard or key that gets far more traffic than the rest. It becomes the slow point holding everything up while the others sit idle, like one big match on one shard.