Advanced Track
Sharding & Distributed SQL
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.
What You'll Learn
- ✓Tell sharding apart from partitioning and replication
- ✓Choose a shard key that spreads load and avoids hotspots
- ✓Compute which shard a key lands on with hash() % N
- ✓Compare hash sharding vs range sharding and their trade-offs
- ✓Spot the hard parts: cross-shard joins, transactions, resharding
- ✓Know when to reach for Vitess, Citus, CockroachDB or YugabyteDB
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.
🗄️ Real-world analogy
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.
Three ways to split a database
Vertical partitioning vs horizontal partitioning vs sharding.
-- Three different ways to "split" a database. They are NOT the same thing.
-- 1) VERTICAL PARTITIONING — split COLUMNS, still one server.
-- Move rarely-used / huge columns into their own table.
CREATE TABLE users (id, email, name); -- hot, read constantly
CREATE TABLE user_blobs (id, avatar, bio_long_text); -- cold, read rarely
-- Result: the hot table is smaller, so it caches better. One machine still.
-- 2) HORIZONTAL PARTITIONING (a.k.a. table partitioning) — split ROW
...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.
Replication vs sharding
Same data copied everywhere vs different data split across servers.
-- REPLICATION is a different axis from sharding — don't confuse them.
-- REPLICATION = copy the SAME data to multiple servers.
-- Primary (writes) ──► Replica 1 (reads)
-- └──► Replica 2 (reads)
-- Goal: more READ capacity + high availability (a replica can take over).
-- It does NOT add write capacity or storage — every copy holds ALL the data.
-- SHARDING = split DIFFERENT data across servers.
-- Shard 0 (users 0–999) Shard 1 (users 1000–1999) Shard 2 ...
-- Goal: more W
...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.
Hash sharding vs range sharding
How each rule maps a shard key to a shard, with trade-offs.
-- Two ways to decide which shard a row lives on. Both use a "shard key".
-- A shard key is the column whose value picks the shard, e.g. user_id.
-- HASH SHARDING: shard = hash(shard_key) % number_of_shards
-- Spreads rows evenly, even if the keys are sequential.
-- shard = hash(user_id) % 4 (4 shards: 0, 1, 2, 3)
-- user_id 101 → hash(101)=... % 4 = 1 → Shard 1
-- user_id 102 → hash(102)=... % 4 = 2 → Shard 2
-- user_id 103 → hash(103)=... % 4 = 3 → Shard 3
-- user_id 104 → has
... 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.
Result — hash(user_id) % 4:
| user_id | user_id % 4 | lands on |
|---|---|---|
| 100 | 0 | Shard 0 |
| 101 | 1 | Shard 1 |
| 102 | 2 | Shard 2 |
| 103 | 3 | Shard 3 |
| 104 | 0 | Shard 0 |
| 205 | 1 | Shard 1 |
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.
🎯 Your Turn: hash(key) % N
Fill in the blanks to compute the destination shard.
-- 🎯 YOUR TURN — work out the destination shard by hand (this is pseudo-SQL/maths).
-- Setup: 8 shards, numbered 0–7. Hash sharding: shard = hash(user_id) % 8.
-- For this exercise assume hash(user_id) = user_id (a "perfect" hash) to keep
-- the arithmetic simple.
-- user_id 42 lands on which shard?
-- shard = 42 % ___ -- 👉 fill in the number of shards
-- shard = ___ -- 👉 fill in the remainder of 42 divided by 8
-- ✅ Expected: 42 % 8 = 5, so user_id 42 → Shard 5.
-- (C
...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.
💡 The co-location rule
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.
🎯 Your Turn: choose & justify a shard key
Fill in the column name and the reasoning.
-- 🎯 YOUR TURN — choose a shard key and justify it.
-- Workload: a multi-tenant SaaS. Every company ("tenant") only ever sees its
-- OWN data. 99% of queries are "give me <something> for tenant 7". You almost
-- never query across tenants in the live app.
-- A good shard key keeps data that is queried together ON THE SAME SHARD,
-- and spreads load EVENLY so no single shard is a hotspot.
-- Pick the shard key:
SELECT shard_for('___'); -- 👉 which column? (tenant_id / created_at / status)
-
...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
orderson Shard A toproductson Shard B has no single place to do the work. Common fixes: denormalise (copyproduct_nameintoorders), 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
WHEREmust 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.
Single-shard vs fan-out queries
Filtering on the shard key keeps a query on one server.
-- The trade-off: some queries can no longer touch just one shard.
-- SINGLE-SHARD query (great): the WHERE filters on the shard key, so the
-- router knows EXACTLY which shard to ask.
SELECT * FROM orders WHERE user_id = 42; -- → only Shard (hash(42) % N)
-- CROSS-SHARD / FAN-OUT query (expensive): no shard key in the WHERE, so the
-- router must ask EVERY shard and merge the answers. This is "scatter-gather".
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Step 1 (scatter): send t
...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.
Distributed SQL in practice
CockroachDB: auto-sharding, geo-pinning, follower reads, distributed transactions.
-- CockroachDB / YugabyteDB style: auto-sharded, auto-replicated, PostgreSQL-compatible.
-- A perfectly normal CREATE TABLE — the engine splits & distributes it for you.
CREATE TABLE products (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY, -- UUID: globally unique, no central counter
name VARCHAR(200) NOT NULL,
category VARCHAR(50),
price DECIMAL(10,2),
region VARCHAR(20)
);
-- Rows are auto-split into "ranges" (~512 MB each) spread across nodes.
-- Pin EU
...Common Errors (and the fix)
- Hotspot shard key (timestamp / auto-increment): sharding by
created_ator 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:
statuswith 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
WHEREomits 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
% 4to% 8reshuffles almost every row becausehash(id) % 4 ≠ hash(id) % 8. Fix: use consistent hashing or let Vitess/CockroachDB reshard online.
📘 Quick Reference
| Term | What it means |
|---|---|
| Partitioning | Split one table within a single server |
| Sharding | Split rows across multiple servers (more capacity) |
| Replication | Copy the same data to several servers (reads + safety) |
| Shard key | Column that decides which shard a row lives on |
| hash(key) % N | Hash sharding rule — even spread across N shards |
| Range sharding | Contiguous key ranges per shard — fast range scans |
| Scatter-gather | Fan-out query: ask every shard, merge the results |
| Two-phase commit | Protocol for an all-or-nothing cross-shard transaction |
| Resharding | Adding/removing shards and moving data to rebalance |
| Vitess / Citus | Sharding for MySQL / PostgreSQL you already run |
| CockroachDB / YugabyteDB | Distributed SQL: auto-shard + replicate, PG-compatible |
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.
🎯 Mini-Challenge
Pick a shard key, hash vs range, compute a shard, and name a fan-out query.
-- 🎯 MINI-CHALLENGE: design a sharding plan for a chat app
-- The app stores messages. The #1 query is "load the last 50 messages for
-- conversation X". You have 4 shards (0–3) and expect billions of messages.
--
-- Decide and write down (as SQL comments) FOUR things:
-- 1. The shard key (hint: what does every hot query filter on?)
-- 2. hash or range (hint: do you need even spread, or range scans?)
-- 3. Which shard conversation_id = 1000 lands on, using hash % 4
-- (assume has
...🎉 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
Sign up for free to track which lessons you've completed and get learning reminders.