Course · 49 lessons

Database Roadmap.

A course from choosing a database to operating MySQL, PostgreSQL, SQL Server, MongoDB and Redis, with measured indexing, transactions, replication and recovery.

Go from picking the right database to operating six real engines in production. This path is structured into three stages, moving across MySQL, PostgreSQL, SQL Server, MongoDB, and Redis so you see the same problem solved — and sometimes not solved — differently by each one.

Beginner — engine fundamentals

MySQL, SQL Server, MongoDB: EXPLAIN plans and CRUD basics on three very different query engines.
Redis: strings, expiry, hashes, lists, sets, and sorted sets — the in-memory data structures every other Redis lesson builds on.

Intermediate — design, indexing, and correctness

Choosing and designing: a weighted decision framework, transactions compared across engines, and MongoDB schema design (embedding vs referencing).
Indexing & query performance: composite indexes, GIN indexes for JSONB, clustered vs nonclustered indexes, and measured before/after query plans.
Joins, aggregation, and window functions: MySQL join execution plans, SQL Server window functions and recursive CTEs, MongoDB’s aggregation pipeline and its stage-order trap.
Transactions & concurrency: isolation levels, real deadlocks, locking order fixes, and MongoDB and Redis’s different takes on multi-statement atomicity.
Storage engine internals: PostgreSQL’s VACUUM/bloat and WAL durability tradeoffs.
Procedures, streams, and pub/sub: stored procedures and triggers on two engines, MongoDB change streams, and Redis Pub/Sub.
Day-2 operations: MySQL backup/recovery, Redis persistence (RDB vs AOF), replication, and ACL-based access control.

Advanced — scale, performance, and disaster recovery

Scaling & distribution: MongoDB sharding and replica-set failover, Redis clustering, and a real vs. fake sharding comparison across engines.
Performance at scale: measured indexing across engines, MongoDB’s profiler, and Redis Lua scripting/Functions.
Reliability & correctness: ACID vs BASE’s lost-update problem, and the normalized-vs-embedded tradeoff measured in both directions.
Disaster recovery: MySQL replication gaps, PostgreSQL point-in-time recovery, and a full SQL Server backup-drop-restore cycle.

The roadmap, step by step

mysql Step 1 of 49

MySQL EXPLAIN: Reading a Full Table Scan Before You Fix It

A filtered query against 5,000 rows with no index scans every single row — EXPLAIN and EXPLAIN ANALYZE show exactly that, in real numbers, before an index changes the plan entirely.

sqlserver Step 2 of 49

Reading a SQL Server Execution Plan From the Terminal

No SSMS required: SET STATISTICS IO and SET SHOWPLAN_TEXT reveal a Clustered Index Scan reading 40 pages, and the Index Seek that replaces it after one CREATE INDEX -- a real 10x drop in logical reads.

mongodb Step 3 of 49

MongoDB CRUD and the Type-Strict Query Trap

Run a real MongoDB server in Docker, do the four basic operations, then hit the surprise: querying a numeric field with a string never matches — MongoDB doesn't coerce types in queries.

redis Step 4 of 49

Redis in Five Commands: SET, GET, TYPE, and a WRONGTYPE Error

Run a real Redis server in Docker in one command, then hit the error every new user eventually hits: calling GET on a key that isn't a string.

redis Step 5 of 49

Expiry, Atomic Counters, and the -1 vs -2 TTL Trap

TTL returns -1 for a key that never expires and -2 for one that's already gone — mixing those up is the most common expiry bug. Then: INCR's atomic counters, and the error when a string isn't a number.

redis Step 6 of 49

Hashes, Lists, and Sets: Structures That Deduplicate and Enforce Order For You

SADD silently drops duplicates and returns how many were actually new, RPUSH+LPOP gives you a FIFO queue for free, and HINCRBY enforces numeric fields the same way INCR does for strings.

redis Step 7 of 49

Sorted Sets: Building a Leaderboard, and the Ascending-Order Trap

ZRANGE returns lowest score first by default — a real surprise the first time you build a leaderboard and see the worst player listed first. ZREVRANGE, ZRANK, and range queries fix it.

databases Step 8 of 49

Choosing a Database: A Decision Framework With Real Numbers

"It depends" is true and useless. A weighted scoring framework — consistency, query flexibility, write latency, schema flexibility — run against three real scenarios to three different, honest winners.

databases Step 9 of 49

Transactions Across Engines: Who Can Actually Roll Back

PostgreSQL and MongoDB both undo a partially-applied multi-step change when one step fails. Redis's MULTI/EXEC does not — a real queued transaction that keeps its successful command and drops only the failed one.

mongodb Step 10 of 49

Embedding vs Referencing: A 5.9MB Document and a 50x Fetch Time

Embed reviews inside a book document and push 50,000 of them in — the document balloons to 5.9MB and fetching it goes from 2ms to 102ms. Moving to a referenced collection brings the fetch back to 3ms.

mysql Step 11 of 49

Composite Indexes: The Filesort a Single-Column Index Can't Avoid

Filtering by genre and sorting by year: a single-column index still needs a manual sort afterward (2.87ms). A composite index on both columns delivers pre-sorted results — measured 21x faster, and the Sort step vanishes from the plan entirely.

postgresql Step 12 of 49

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN shows what the planner guesses a query will cost; EXPLAIN ANALYZE actually runs it and shows what really happened. Confusing the two means trusting an estimate that may be wrong.

postgresql Step 13 of 49

Indexing Strategy: When an Index Actually Helps

Indexing a low-selectivity column barely moves the needle — 13ms to 13ms — while indexing a high-selectivity one drops a query from 14ms to 1ms. Selectivity, not the presence of an index, is what determines the payoff.

postgresql Step 14 of 49

JSONB Containment Queries Need a GIN Index, Not a B-tree

A B-tree index on a jsonb column compiles, exists, and does absolutely nothing for a containment query — the operator has to match the index type, not just the column.

sqlserver Step 15 of 49

Clustered vs Nonclustered: When an Index Makes a Query Slower

Forcing SQL Server to use a nonclustered index costs 217 logical reads -- worse than the 40-read scan it was supposed to beat -- because every matching row needs a separate key lookup. INCLUDE fixes it at 4 reads.

mongodb Step 16 of 49

explain() Proves It: 200,000 Docs Scanned vs. a Covered Query That Touches Zero

A query against 200,000 documents takes 65ms and scans every single one without an index. One index later it's 2ms. A covered query on top of that touches zero documents at all — explain() proves each claim.

mysql Step 17 of 49

A Missing Join Condition Turns 4,000 Rows Into 8 Million

Forgetting the join condition on an old-style comma join doesn't error — it silently produces a cartesian product, 8,000,000 rows where 4,000 were expected. EXPLAIN's join buffer (hash join) with no ON clause is the tell.

sqlserver Step 18 of 49

Window Functions: Ranking Ties, and Why WHERE Can't See Them

RANK() and DENSE_RANK() handle a three-way tie for first place completely differently from each other and from ROW_NUMBER() -- and none of the three can be filtered directly in WHERE, a real Invalid column name error proves it.

sqlserver Step 19 of 49

Recursive CTEs and the Default MAXRECURSION 100 Limit

A recursive CTE generating a date series past 100 rows fails with Msg 530 by default -- SQL Server caps recursion depth at 100 unless OPTION (MAXRECURSION n) says otherwise.

mongodb Step 20 of 49

The Aggregation Stage-Order Bug: Wrong Results, No Error

$limit before $sort doesn't error — it just silently returns the wrong documents. $match, $group, $sort, $limit, and $lookup, and the one ordering mistake that returns confidently wrong data.

mongodb Step 21 of 49

When the Aggregation Optimizer Can (and Can't) Use Your Index

A $match placed after $group looks like it must scan everything first — but MongoDB's optimizer can rewrite it into a pure index lookup when filtering on the group key itself. Filter on a computed value like a $sum, and that optimization disappears entirely.

mysql Step 22 of 49

Isolation Levels: The Same Transaction Reading Two Different Values

Under MySQL's default REPEATABLE READ, a transaction reads the same value twice even after another session commits a change in between. Switch to READ COMMITTED and the second read sees the new value — a real, measured non-repeatable read.

mysql Step 23 of 49

A Real Deadlock: Error 1213, and the Lock-Order Fix

Two sessions locking the same two rows in opposite order produce a genuine ERROR 1213 deadlock, not a hang — MySQL detects it and kills one transaction immediately. Locking in a consistent order eliminates it entirely, replaced by an ordinary wait.

postgresql Step 24 of 49

A Real Deadlock, and Why Lock Order Fixed It

One error inside a transaction poisons every statement after it until ROLLBACK — and two transactions locking the same two rows in opposite order produces a real 'deadlock detected' error, not a hang.

sqlserver Step 25 of 49

A Real Dirty Read: Seeing a Value That Never Actually Existed

READ UNCOMMITTED reads an uncommitted price change mid-transaction -- then the transaction rolls back, and the value never really existed. The default READ COMMITTED blocks instead, confirmed with a real timeout.

mongodb Step 26 of 49

Multi-Document Transactions: When One Update Isn't Enough

Transfer money between two accounts as a single atomic transaction — then abort one on purpose and prove the balance actually rolls back, not just that the code stopped running.

redis Step 27 of 49

MULTI/EXEC Transactions and the WATCH-Based Optimistic Lock

Queue commands with MULTI, run them atomically with EXEC — then watch a real transaction abort mid-flight when another client changes a WATCHed key first.

postgresql Step 28 of 49

VACUUM Doesn't Shrink Tables — VACUUM FULL Does, at a Cost

Deleting 80% of a table's rows doesn't shrink it by a single byte, and neither does a plain VACUUM. Only VACUUM FULL actually returns space to the OS — by rewriting the whole table under an exclusive lock.

postgresql Step 29 of 49

The WAL Durability Tradeoff: 0.30s vs 0.02s for the Same 200 Inserts

Every committed write has to hit durable storage before COMMIT returns — turning that guarantee off doesn't change what gets written, only when Postgres waits for it, and the difference is measured, not asserted.

mysql Step 30 of 49

Stored Procedures and Triggers: Code That Runs Without Being Asked

A stored procedure enforces a business rule with a real SIGNAL error, and a trigger logs every stock change automatically — even for updates that never call the procedure at all.

sqlserver Step 31 of 49

The Trigger That Silently Dropped a Row

A trigger reading `inserted` into scalar variables works fine for a single-row insert -- and silently audits only one of two rows on a multi-row insert, because a trigger fires once per statement, not once per row.

mongodb Step 32 of 49

Change Streams: Watching a Collection in Real Time (Replica Set Required)

watch() fails outright on a standalone server — change streams need a replica set, even a local one. Once that's running: real insert/update/delete events, filtering by operation type, and resuming after a disconnect.

redis Step 33 of 49

Pub/Sub: Channels, Patterns, and the Message That Never Arrives

Publishing to a channel with no subscribers doesn't queue the message for later — it's just gone. Real fan-out to multiple subscribers, pattern subscriptions, and sharded Pub/Sub follow.

mysql Step 34 of 49

Backup and Recovery: What a Restore Actually Gives Back

A real mysqldump, a real DELETE with no WHERE clause, and a real restore — which undoes the mistake but also erases legitimate work done after the backup, exposing exactly what a full logical restore can't do alone.

redis Step 35 of 49

RDB vs AOF: What Survives a Hard Kill and What Doesn't

A graceful restart saves your data by default — Redis snapshots on SIGTERM. A hard kill (docker kill, a power loss, an OOM kill) gives it no such chance, and only AOF protects against that. Then: the new BACKUP command family.

redis Step 36 of 49

Replication: A Read-Only Replica, and What WAIT Actually Guarantees

Writing directly to a replica fails with READONLY by design — and WAIT doesn't error when it can't reach the number of replicas you asked for, it just tells you how many actually acknowledged in time.

redis Step 37 of 49

ACL: a User Restricted to One Key Pattern and One Command

A default Redis install lets every connection run every command against every key. ACL SETUSER changes that — including a real NOPERM the moment a restricted user reaches outside its allowed keys or commands.

mongodb Step 38 of 49

Sharding: Why 60,000 Fresh Documents Landed on Exactly One Shard

A real 2-shard cluster, a collection sharded from the start — and every one of 60,000 bulk-inserted documents lands on a single shard anyway, because a lone chunk under the size threshold has no reason to split or move.

mongodb Step 39 of 49

Killing the Primary and Measuring the Real Failover Time

docker kill the primary node of a real 3-node replica set and time exactly how long the cluster takes to elect a new one from container logs and MongoDB's own timestamps — 9.01 seconds, closely matching the documented default election timeout.

redis Step 40 of 49

Clustering: Hash Slots, MOVED, and the CROSSSLOT Error

A real 3-node cluster, a real MOVED redirect from writing to the wrong node, and a real CROSSSLOT error the first time a multi-key command touches two different slots — fixed with a hash tag.

databases Step 41 of 49

Scaling Patterns: Real Sharding vs a Table That Only Looks Sharded

MongoDB's sharding puts data on genuinely separate machines; MySQL's PARTITION BY divides a table by the same kind of range key but keeps every partition on one server. Both engines' own distribution-reporting tools also turn out to report the wrong numbers.

databases Step 42 of 49

Indexing Across Engines: One B-Tree Idea, Measured Twice

The same customer_id lookup, on the same shape of 100k+ row table, measured before and after an index in both PostgreSQL and MySQL — two engines, two real query planners, the same order-of-magnitude win.

mongodb Step 43 of 49

The Profiler: Finding a Slow Query Without Knowing to Look for It

explain() answers 'is this specific query slow' — the profiler answers 'which of my queries are slow at all,' logging every operation over a threshold with its real execution stats, including the query plan, with no code changes needed.

redis Step 44 of 49

Lua Scripting, EVALSHA's NOSCRIPT, and Functions That Forget to Declare no-writes

A guessed script hash fails with a real NOSCRIPT error, and a Function callable via FCALL refuses to run through FCALL_RO until it explicitly declares itself read-only — Redis won't infer that for you.

databases Step 45 of 49

ACID vs BASE: the Lost Update Neither Engine Prevents by Default

A naive read-then-write race loses an update in both PostgreSQL and Redis when neither uses locking — the real difference is that Postgres ships a built-in fix (SELECT ... FOR UPDATE) and a raw key-value GET/SET pair has no equivalent.

databases Step 46 of 49

Normalized vs Embedded: a Tradeoff That Points in Two Different Directions

Fetching one customer with their orders costs about the same, indexed, in both a normalized PostgreSQL schema and an embedded MongoDB document. Searching across orders independent of their customer is measured 11x slower embedded — the same schema choice wins and loses depending which direction you query.

mysql Step 47 of 49