Subject
Databases
Modelling, indexing, transactions and scale.
Start at Level 0Interview questions
Foundations
What a database is, the vocabulary of tables and keys, and the running example used throughout.
Level 0
How databases work
What a database management system actually is, the vocabulary of tables, rows and keys, where SQL fits, and the small bookstore database used as a running example for the rest of this roadmap.
10 modules · about 2 weeks
SQL
Querying and changing data, from a single SELECT to views, window functions and recursive queries.
Level 1
SQL fundamentals
The core of SQL: filtering and sorting rows, the relational algebra and calculus behind a query, every kind of join, grouping and aggregation, subqueries, set operations, three-valued logic with NULL, and changing data safely.
9 modules · about 2 weeks
Level 2
Advanced SQL
Views and materialized views, window functions, common table expressions and recursive queries, stored procedures and triggers, integrity constraints, a first look at indexes from the SQL side, and authorization.
7 modules · about 2 weeks
Data modeling and design
Designing a schema before any table exists, and normalizing it once it does.
Level 3
Data modeling and design
Designing a schema before any table exists: entity-relationship modeling, mapping diagrams to tables, functional dependencies, normal forms through 5NF, decomposition, when to denormalize on purpose, and modeling data whose history matters.
9 modules · about 3 weeks
Storage and indexing
What is actually on disk, and the structures that let a query avoid scanning all of it.
Level 4
Storage, the physical layer
What is actually on disk underneath a table: storage media, blocks, file organization, the buffer pool that caches pages in memory, row-oriented versus column-oriented layout, and the real path a write takes to become durable.
6 modules · about 2 weeks
Level 5
Indexing
Why an unindexed query must scan everything, B+-trees and hash indexes, LSM-trees for write-heavy workloads, composite and covering indexes, bloom filters and bitmap indices, when an index actually hurts, and reading an execution plan to check any of it.
8 modules · about 2 weeks
Query processing and optimization
What happens between a SQL statement and a result set, and how to help the optimizer.
Level 6
Query processing and optimization
The stages between a SQL statement and a result set: turning SQL into a query plan, the different join algorithms an optimizer can pick, sorting costs, cost-based optimization and why its estimates can be wrong, writing index-friendly queries, pagination that doesn't slow down on later pages, and materialized views.
7 modules · about 2 weeks
Transactions and concurrency control
Making concurrent changes safe, precisely, not just by hoping nothing collides.
Level 7
Transactions and concurrency control
A precise definition of ACID, when an interleaving of concurrent transactions is actually safe, what each isolation level really prevents, lock-based and timestamp-based protocols, deadlocks, multiversion concurrency control, the anomalies snapshot isolation still allows, serializable snapshot isolation, and picking the right isolation level for a real application.
10 modules · about 3 weeks
Recovery and durability
Surviving a crash, a bad disk and a human mistake.
Level 8
Recovery and durability
The kinds of failure a database has to survive, the write-ahead log that makes crash recovery possible, checkpoints that bound how far back recovery looks, the ARIES algorithm in outline, backups and point-in-time recovery, and keeping a remote standby ready to take over.
6 modules · about 2 weeks
Distributed data
Replication, partitioning and the real difficulty of machines that talk over a network.
Level 9
Replication
The leader-follower replication shape, the concrete glitches asynchronous replication lag causes and how each is fixed, accepting writes in more than one place with multi-leader replication, removing the leader entirely with leaderless replication and quorums, and choosing a topology for a real requirement.
5 modules · about 1 week
Level 10
Partitioning and distributed transactions
Splitting one dataset across many machines, indexing partitioned data, rebalancing as a cluster changes shape, why unreliable networks and clocks make distributed systems genuinely hard, two-phase commit, what consensus actually solves, and the CAP theorem used carefully rather than as a slogan.
7 modules · about 2 weeks
Alternative data models
Document, key-value, graph and column-family stores, and when each actually fits.
Level 11
NoSQL and alternative data models
Document databases, key-value stores, graph databases and column-family stores as four different ways to model the same data, each suited to a different access pattern, and how to pick a model for a real workload rather than a trend.
5 modules · about 1 week
Big data and analytics
A second access pattern, built for asking questions across history instead of serving one request.
Level 12
Big data and analytics
OLTP versus OLAP as two different access patterns that both call themselves queries, data warehousing and the star schema, MapReduce and distributed batch processing, stream processing for events as they arrive, and column-oriented storage built for analytical queries.
5 modules · about 1 week
Practice and interviews
Tuning, choosing, designing and talking through it out loud.
Level 13
Practical performance, operations and interviews
Measuring load and reasoning about scalability, tuning a genuinely slow query end to end, a decision framework for choosing a database for a workload, a full worked database system-design interview case study, and a capstone designing this very platform's own database.
5 modules · about 2 weeks