Hands-on build 1 · 2 h 36 min · 20 chapters
Postgres Sharding: Complete Hands-On (PgBouncer + Citus)
Load one PostgreSQL until it breaks, shard it yourself with a router you write, then add PgBouncer and Citus. Every number measured live.
Unlock the full build
- All 20 chapters, 2 h 36 min of live code, with chapter jumps
- The 139-page slide deck, and the slides for every chapter
- The whole System Design course: 770 lessons and the AI tutor
- One payment, lifetime access, no subscription
What you build
An orders database for KiranaLoop, a made-up grocery delivery company, on your own laptop in Docker. Then you break it and fix it, four times.
- An orders database for a grocery delivery company, on one PostgreSQL in Docker, loaded until it struggles
- A load test with 64 concurrent checkout servers that reports orders per second and p95 latency every second
- A read replica and monthly partitions, so you see what the cheaper steps fix before you shard
- Four PostgreSQL shards behind a router you write in Python, with a million customers moved across them
- Fixes for the four classic problems: a fan-out dashboard, a hot row in a flash sale, too many connections, and adding a fifth shard
- PgBouncer in front of every shard, then the same system on Citus with live rebalancing
The code, free on GitHub
Every Python file, the Dockerfile and every command from the build. Nothing to install but Docker: every file runs inside a container.
github.com/ronidas39/postgres-sharding-hands-onOpen the repositoryWhat you need
- A laptop with Docker Desktop (Mac, or Windows with WSL 2), about 8 GB of memory for Docker and 10 GB of free disk
- VS Code or any editor
- Basic comfort with a terminal. No Python install: every file runs inside a container
Every chapter
4 chapters free (0, 1, 2, 3), the first 14:44. The other 16 come with the System Design course.
0:00 to 1:18free
0Teaser: what we will build
The whole system in one minute: one PostgreSQL that struggles, four shards behind a router, PgBouncer and Citus.
1:18 to 7:06free
1What sharding is, why and when you need it, and what it costs
Sharding in plain words, the signs that a single database is running out of room, and the price you pay once you split it.
7:06 to 13:11free
2How enterprises run a database
Live orders versus the analytics copy, the write-ahead log and replicas, backups with point-in-time recovery, and pools: how a company runs PostgreSQL before anyone says shard.
13:11 to 14:44freehands-on
3Meet KiranaLoop: build the orders database
The made-up grocery delivery company whose orders database we build, load and split for the rest of the build.
14:44 to 19:12 full buildhands-on
3aBefore you start: Docker Desktop, VS Code, the code
Docker Desktop with enough memory, the editor, and the repository, so every command runs the same on your machine.
19:12 to 24:59 full buildhands-on
3bIs PostgreSQL running? psql and pgAdmin
Start PostgreSQL 17 in a container and look inside it two ways, from the psql prompt and from pgAdmin.
24:59 to 40:06 full buildhands-on
3cThe app container and the first tables
Build the Python app image, create the customers and orders tables, and place the first day of orders.
40:06 to 1:01:14 full buildhands-on
4Measure it: orders per second, p95, the slow second
Sixty-four checkout servers place orders at once while we measure every second. With 8.4 million orders, one second drops to 308.
1:01:14 to 1:12:12 full buildhands-on
5Climb the ladder: a replica, and partitioning by month
The cheaper steps first. What a read replica fixes, what monthly partitions fix, and what neither of them can fix.
1:12:12 to 1:19:29 full buildhands-on
6Choose the shard key from the queries
Read the queries the company actually runs, pick the shard key from them, and measure how evenly it spreads the data.
1:19:29 to 1:21:15 full build
7Four ways to split the data
Hash, range, directory and geography: how each one decides where a row lives, and which reader each one hurts.
1:21:15 to 1:38:42 full buildhands-on
8Four shards and a router
Write the router in Python (md5, then % 4), move a million customers and 8.5 million orders into four shards, and measure again: 32,472 orders per second, 5.1x the one database.
1:38:42 to 1:45:02 full buildhands-on
9The dashboard that asks every shard
A query that needs every shard, and how to bring it from 660 ms down to 6.8 ms.
1:45:02 to 1:56:42 full buildhands-on
10The 9 pm flash sale
Everyone wants the same row at the same second. 50,000 sold and zero oversold, and the reason why.
1:56:42 to 2:05:02 full buildhands-on
11Too many connections, and the pool that fixes it
128 servers, and 28 of them are turned away. Put PgBouncer in front of each shard in transaction mode and all 128 are served.
2:05:02 to 2:14:30 full buildhands-on
12A fifth shard: the belt, the ring road, and buckets
Add a shard and the simple rule moves 80% of customers. A hash ring or logical buckets move about 20%.
2:14:30 to 2:17:19 full build
13What sharding makes hard
A change that touches two shards, ids that are no longer unique, and four of everything to run: load, backups, schema versions, replicas.
2:17:19 to 2:31:40 full buildhands-on
14The enterprise way: Citus, and how others do it
One line per table replaces our router. Distributed tables, co-location, Task Count, and Citus rebalancing onto a new worker in 30 seconds while it keeps running.
2:31:40 to 2:34:34 full build
15Should you shard? The checklist and the interview answer
A checklist for the real decision, and how to answer "how would you shard an orders database?" with real numbers.
2:34:34 to 2:36:15 full buildhands-on
15aClean up your laptop
Remove every container, volume and network the build made.
Interview prep packs
Interview soon? Practise with the interview prep packs
Separate from the course. Real interview questions, each with why it is asked, a full answer, the follow-ups, the common mistakes and a diagram. A PDF you keep.
