You have 12 notes of internals and 11,000 lines of syntax reference. The gap is that none of it has been felt — a query plan you didn’t read, a lock you didn’t hold, a migration you didn’t botch. Every project here produces a measurement.

Difficulty: 🟢 starter (hours–days) · 🟡 intermediate (a week or two) · 🔴 ambitious / portfolio-grade. ⭐ = highest signal.

The ladder

  • 🟢 ⭐ Make a query 100× faster — take a slow query on a table with a million rows, read EXPLAIN ANALYZE, add the right index, and measure. Done when: you can point at the line in the plan that changed from Seq Scan to Index Scan, and explain why your index and not another. Exercises: the optimiser.

  • 🟢 Break it with a missing index — the same exercise inverted: find an N+1 in a real app and fix it. Done when: query count per request drops and you have both numbers.

  • 🟢 Watch isolation levels differ — two psql sessions, one table. Reproduce a dirty read, a non-repeatable read, and a phantom by changing only the isolation level. Done when: you’ve seen all three and can say which level your app actually uses. Exercises: isolation levels.

  • 🟡 Cause a deadlock on purpose, then fix it — two transactions, opposite lock order. Done when: you’ve read the deadlock in the logs and fixed it by ordering acquisitions consistently. Exercises: MVCC.

  • 🟡 A zero-downtime migration — add a NOT NULL column to a large, live table without locking it: expand → backfill in batches → contract. Done when: you ran it against a table under concurrent write load and nothing blocked. This is the single most valuable database skill nobody practises.

  • 🟡 Set up replication and fail over — a primary and a replica, then kill the primary and promote. Done when: you know your actual replication lag and what you lost. Exercises: replication.

  • 🟡 Benchmark B-tree vs LSM — the same write-heavy workload against Postgres and RocksDB/Cassandra. Done when: you can show write amplification differing and explain it from LSM and B-trees.

  • 🔴 Build your own database — the guide: 06-your-own-database. Pager → B-tree → SQL subset → WAL. Done when: kill -9 mid-write and the data survives.

  • 🔴 ⭐ Model something hard, properly — take a real domain (bookings with overlapping constraints, double-entry ledger, versioned documents) and design the schema with correct constraints so the database enforces the invariants, not the application. Done when: you cannot corrupt it from psql with a bad INSERT. Exercises: database design.

If you only do one

The zero-downtime migration. It’s the difference between someone who knows SQL and someone you’d let near production, and it takes a weekend.

↑ Back to Databases