Sharding

By the end of this lesson you'll be able to explain how a database is split across many servers, choose a shard key that spreads load evenly instead of creating a hotspot, work out which shard any given row lands on, and recognise when a managed distributed SQL engine is the right call instead of rolling your own. This is how systems serve billions of rows when one machine simply isn't enough.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

People use these three words interchangeably, but they solve different problems. Partitioning splits one table within a single server. Sharding splits rows across multiple servers. Replication copies the same data to several servers. Getting these straight is the whole foundation of the lesson.

Think of your data as paperwork. Partitioning is adding more drawers to one filing cabinet — still one cabinet, just better organised. Sharding is buying many cabinets and putting them in different rooms, each holding a slice of the files. Replication is making photocopies so every room has the same files for safety and faster reading. You shard when one cabinet physically can't hold (or be opened fast enough for) all the paper.

Notice that the first two stay on one machine — they make queries cheaper but they can't add CPU, RAM, or write throughput. Sharding is the only one of the three that adds capacity , because each shard is a separate server doing its own work.

Before you reach for sharding, be sure you actually need it. Replication copies all your data to extra servers so more machines can answer reads and so you survive a server dying. It is far simpler than sharding — but it does nothing for write load or storage, because every copy holds everything . Sharding is what you use when the data itself is too big or too write-heavy for one machine.

Once you've decided to shard, you need a rule that maps each row to a shard. That rule reads one column — the shard key — and decides the destination. There are two classic rules. Hash sharding runs the key through a hash function and takes the remainder modulo the number of shards, giving an even spread. Range sharding assigns contiguous ranges of the key to each shard, which keeps range scans fast but risks a hotspot on whichever shard holds the newest data.

Four shards, hash sharding with shard = hash(user_id) % 4 . To keep the maths readable we pretend hash(user_id) = user_id , so the destination is just user_id % 4 . Trace a couple of rows yourself before reading on.

See how consecutive IDs (100, 101, 102, 103) scatter across all four shards? That's exactly what gives hash sharding its even write spread — and exactly why "give me users 100–200" has to visit every shard.

Eight shards, hash sharding. Work out which shard user_id = 42 lands on using shard = hash(user_id) % 8 (pretend hash(x) = x ). The answer is in the comments — and it deliberately shows you how to double-check your modulo arithmetic.

The shard key is the single most important decision you'll make, and it's painful to change later. A good key does two things at once: it spreads load evenly (so no shard becomes a bottleneck) and it co-locates data that's queried together (so your common queries stay on one shard). A key with too few distinct values — like status with only 'active' / 'inactive' — can't spread across many shards. A key that always increases — like created_at or an auto-increment id under range sharding — sends every new write to the same shard.

Data that's queried together should live on the same shard. In a multi-tenant SaaS, shard by tenant_id so all of one company's rows sit on one server and "show me everything for tenant 7" is a single-shard query. Cross-tenant analytics then runs on a separate analytical copy, not on the live shards.

A multi-tenant SaaS where almost every query is "for one tenant". Choose the shard key and justify why it keeps the hot query single-shard. The expected answer is in the comments.

Sharding buys scale but charges a tax in complexity. Once rows live on different servers, four things that were trivial on one machine get hard:

Almost nobody hand-rolls sharding from scratch any more — the routing, resharding, and distributed transactions are too easy to get wrong. Instead you reach for a system that does it for you. At a high level there are two families:

The example below is real CockroachDB-flavoured SQL. The CREATE TABLE is standard and runs anywhere; the geo-pinning and follower-read lines are CockroachDB-specific and show what these engines hand you for free.

Q: When should I shard versus just adding a read replica?

If your problem is read load and the data still fits on one machine, add a replica — it's a far smaller change. Shard only when write throughput or total size genuinely exceeds a single server. Sharding is the heavier hammer; don't pick it up first.

Q: Hash or range sharding — which is the safe default?

Hash is the safer default because it spreads load evenly and resists hotspots. Choose range only when your dominant queries are "everything between X and Y" on the shard key (e.g. time-series scans) and you've accepted the newest-shard hotspot risk.

Q: Why is changing the number of shards (resharding) such a big deal?

With plain modulo, almost every row's destination changes when N changes — hash(id) % 4 is unrelated to hash(id) % 8 — so you'd move most of your data. Consistent hashing moves far less, and managed engines reshard online without downtime, which is a big reason to use them.

Q: Do I have to give up JOINs and transactions when I shard?

Not entirely, but they get harder. Keep related rows on the same shard (co-locate by shard key) and most joins and transactions stay single-shard and fast. For the rest, denormalise, use replicated reference tables, or pick a distributed SQL engine that handles cross-node joins and ACID for you.

Put it all together — a brief, a blank canvas, and the expected answer in the comments. Reason it out, then check yourself against the solution.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary