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.

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.

What You'll Learn

1. Sharding vs Partitioning vs Replication

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.

2. Replication Is a Different Axis

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.

3. Hash vs Range Sharding

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.

Diagram: where does each user_id land?

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.

Your Turn: compute the 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.

4. Choosing a Good Shard Key

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.

Your Turn: pick the shard key

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.

5. The Hard Parts: Joins, Transactions, Fan-Out & Resharding

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:

• Cross-shard joins — joining orders on Shard A to products on Shard B has no single place to do the work. Common fixes: denormalise (copy product_name into orders ), keep small reference tables replicated on every shard, or join in your application code.
• Cross-shard (distributed) transactions — "debit Shard A, credit Shard B" must be all-or-nothing. This needs two-phase commit : phase 1 every shard says "I can commit", phase 2 the coordinator says "commit"; if anyone fails phase 1, everyone rolls back. It's slower and more fragile than a local transaction.
• Fan-out queries (scatter-gather) — a query with no shard key in its WHERE must ask every shard and merge the results, so it gets slower as you add shards.
• Resharding — going from 4 shards to 8 breaks plain modulo, because hash(id) % 4 ≠ hash(id) % 8 , so most rows must move. Consistent hashing minimises how much data moves; managed engines automate the whole migration.

6. Distributed SQL Engines (So You Don't Build This Yourself)

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:

• Sharding middleware / extensions sit on top of a database you already know. Vitess fronts MySQL (it powers YouTube, Slack and GitHub) and routes your normal SQL to the right shard, including online resharding. Citus is a PostgreSQL extension that turns a cluster of Postgres nodes into one sharded database.
• Distributed SQL databases are built from the ground up to be sharded and replicated. CockroachDB and YugabyteDB are PostgreSQL-compatible: they automatically split data into ranges, spread those ranges across nodes, replicate each for safety, and run ACID transactions across nodes for you. You mostly write plain SQL and let the engine place the data.

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.

Common Errors (and the fix)

• Hotspot shard key (timestamp / auto-increment): sharding by created_at or a sequential id under range sharding sends every new write to one shard while the rest sit idle. Fix: shard on a high-cardinality, well-distributed key (e.g. user_id / tenant_id ), or hash the key so new rows scatter evenly.
• Low-cardinality shard key: status with two or three values can't spread across many shards — you get a handful of giant shards. Fix: pick a key with many distinct values.
• Cross-shard transaction surprises: a single BEGIN; ...; COMMIT; that touches two shards can partially apply if you didn't set up two-phase commit. Fix: keep a transaction's rows on one shard (co-locate by shard key), or use an engine that does 2PC for you.
• Accidental fan-out: a query whose WHERE omits the shard key quietly hits every shard and gets slower as you scale. Fix: include the shard key in hot-path filters; route reporting to an analytics replica.
• Resharding the naive way: changing from % 4 to % 8 reshuffles almost every row because hash(id) % 4 ≠ hash(id) % 8 . Fix: use consistent hashing or let Vitess/CockroachDB reshard online.

📘 Quick Reference

Term

What it means

Frequently Asked Questions

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.

Mini-Challenge: Shard a Chat App

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.

🎉 Lesson Complete

• ✅ Partitioning splits within one server; sharding splits rows across servers; replication copies the same data
• ✅ A good shard key spreads load evenly and co-locates data that's queried together
• ✅ Hash sharding ( hash(key) % N ) spreads evenly; range sharding keeps range scans fast but risks hotspots
• ✅ Cross-shard joins, distributed transactions, fan-out queries, and resharding are the real costs of sharding
• ✅ Vitess and Citus shard databases you already run; CockroachDB and YugabyteDB are distributed SQL out of the box
• ✅ Next: Materialized Views — pre-compute expensive results so reads stay fast

Practice quiz

Continue this course

• Previous: Partitioning
• Next: Materialized Views

Related lessons

• Indexes
• WHERE clause