Database Engineering Roadmap

What a database is for — durability, concurrency and access paths — and what the engine does with a query between the text you send and the rows you get back. One lesson, What a Database Actually Is, that gives every later stage its vocabulary: pages, indexes, transactions, the log.

Before moving on: Explain why an application keeps its data in a database rather than in files, and name the three problems the engine solves for you.

• What a Database Actually IsNot startedLearningPracticingMastered

Reading data. The logical evaluation order of a SELECT, the three-valued logic of NULL, filtering and aggregation, every kind of join, then subqueries, CTEs and window functions. Everything after this stage is expressed in queries, so the queries have to come first.

Before moving on: Write a query with a join, a GROUP BY with a HAVING filter and a window function from memory, and predict which rows a LEFT JOIN keeps when the match is NULL.

• SELECT, FROM, WHERE, ORDER BY, LIMITNot startedLearningPracticingMastered
• Aggregation: COUNT, SUM, AVG, GROUP BY, HAVINGNot startedLearningPracticingMastered
• Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELFNot startedLearningPracticingMastered
• Subqueries, CTEs, EXISTS, UNION, CASENot startedLearningPracticingMastered
• Window FunctionsNot startedLearningPracticingMastered

Turning requirements into tables. Start from the access patterns, then entities, relationships and keys, then remove update anomalies with normalization — and learn when to put duplication back on purpose. Comes after SQL because a schema is only good for the queries it has to answer.

Before moving on: Take a short requirements brief, draw the tables with primary and foreign keys, normalize to 3NF, and say which access pattern would justify denormalizing.

• From Requirements to TablesNot startedLearningPracticingMastered
• Relationships, Keys and ConstraintsNot startedLearningPracticingMastered
• Normalization: 1NF to BCNFNot startedLearningPracticingMastered
• Denormalization on PurposeNot startedLearningPracticingMastered

Why a query is slow, how a B-tree fixes it, composite indexes and the leftmost-prefix rule, the other index types, and the decision to add one at all. Needs SQL to know what an index has to serve and Modeling to know which columns a query filters on.

Before moving on: Given a WHERE and an ORDER BY, write the composite index that serves both, and say what that index costs on every write.

• Why Is This Query Slow? IndexesNot startedLearningPracticingMastered
• Composite Indexes and the Leftmost-Prefix RuleNot startedLearningPracticingMastered
• Index Types: B-tree, Hash, Partial, Expression, Covering, Full-TextNot startedLearningPracticingMastered
• Should I Add an Index?Not startedLearningPracticingMastered

Parser, planner and executor, then how to read EXPLAIN ANALYZE and find the node that is actually slow. Comes right after Indexes because the plan is where you see whether the index you added was used.

Before moving on: Read an EXPLAIN ANALYZE plan, point at the bottleneck node, and say whether an index, a rewrite or fresh statistics is the fix.

• How a Query Executes: Planner and ExecutorNot startedLearningPracticingMastered
• Reading EXPLAIN ANALYZENot startedLearningPracticingMastered
• Query Optimization: Finding the Actual BottleneckNot startedLearningPracticingMastered

ACID as four separate guarantees, then what goes wrong when transactions overlap: lost updates, dirty and non-repeatable reads, phantoms, write skew. Isolation levels as the menu of fixes, MVCC as the mechanism most engines use, and locks and deadlocks when MVCC is not enough.

Before moving on: Name the anomaly a given interleaving produces, choose the weakest isolation level that prevents it, and explain why a deadlock happened from the order two transactions took their locks.

• Transactions and ACIDNot startedLearningPracticingMastered
• Concurrency AnomaliesNot startedLearningPracticingMastered
• Isolation LevelsNot startedLearningPracticingMastered
• MVCC: Multi-Version Concurrency ControlNot startedLearningPracticingMastered
• Locks and DeadlocksNot startedLearningPracticingMastered

The concrete implementation of everything so far: types and tables, JSONB and full-text search, extensions, partitioning, VACUUM and connection management. Sits after Execution and Transactions because its operations only make sense once you know what plans and MVCC are.

Before moving on: Pick the right PostgreSQL type for a column, query a JSONB document with an index behind it, and explain what VACUUM cleans up and why it must run.

• PostgreSQL: Tables, Types and the CatalogNot startedLearningPracticingMastered
• JSONB, Full-Text Search and ExtensionsNot startedLearningPracticingMastered
• PostgreSQL in Production: Connections, VACUUM, Partitioning, ReplicationNot startedLearningPracticingMastered

Redis as data structures rather than "a cache", the caching patterns and their hazards (stale reads, stampedes, invalidation), then the non-relational models — document, wide-column, graph — and the access patterns that make one of them the right choice. Needs Modeling to compare against and Transactions to know what consistency you give up.

Before moving on: Choose between cache-aside and write-through for a given read/write mix, and justify a document or wide-column model over tables from the access patterns alone.

• Redis: Data Structures, Not a CacheNot startedLearningPracticingMastered
• Caching PatternsNot startedLearningPracticingMastered
• Cache Invalidation, Stampedes and Hot KeysNot startedLearningPracticingMastered
• SQL vs NoSQL: Choosing a Data ModelNot startedLearningPracticingMastered
• Document Databases: Embed or ReferenceNot startedLearningPracticingMastered
• Wide-Column, Graph, Search and Time-SeriesNot startedLearningPracticingMastered

Embeddings, similarity measures, approximate nearest-neighbour indexes such as HNSW, metadata filtering and hybrid search — the storage layer under retrieval-augmented generation. An ANN index is still an index, so this comes after Indexes and after choosing a data model.

Before moving on: Explain why exact nearest-neighbour search does not scale, what HNSW trades for speed, and when a pgvector column beats a separate vector store.

• Vector Search: Embeddings, Similarity and ANNNot startedLearningPracticingMastered

The scaling ladder in order — pooling, read replicas, caching, partitioning, sharding — then replication, and distributed consistency without the slogans: quorums, consensus, what a partition really costs. Last of the practical stages because every rung assumes the ones before it.

Before moving on: Say which rung of the scaling ladder a given symptom calls for, explain replication lag to a teammate, and state what a quorum read does and does not guarantee.

• Scaling from One User to MillionsNot startedLearningPracticingMastered
• Replication and Read ScalingNot startedLearningPracticingMastered
• Partitioning and ShardingNot startedLearningPracticingMastered
• Distributed Consistency: CAP, Quorums, ConsensusNot startedLearningPracticingMastered

The start of the Build-AtlasDB journey (V0–V3). Bytes, records, page files, slotted pages: how a table physically exists on disk and why the page is the unit of everything the engine does. Only Foundations is needed; this is where the internals layer begins.

Before moving on: Lay out a row of mixed-width columns as bytes, explain what a slotted page buys over fixed slots, and say why a read costs whole pages rather than rows.

• How Is Database Data Physically Stored?Not startedLearningPracticingMastered
• Records on DiskNot startedLearningPracticingMastered
• Pages: The Unit of EverythingNot startedLearningPracticingMastered
• Slotted PagesNot startedLearningPracticingMastered

Derive the index from the sequential scan, then build the page-oriented B+ tree, watch splits and merges, and see why fanout beats Big-O on disk. Hash indexes close the stage (V4–V5). Needs the practical Indexes stage for what an index must answer and Storage for what a page is.

Before moving on: Explain why a B+ tree of a billion keys is three or four pages deep, and why a hash index cannot serve a range query.

• Sequential Scan, Page by PageNot startedLearningPracticingMastered
• The Index, Derived from First PrinciplesNot startedLearningPracticingMastered
• B+ Tree Internals: Pages, Splits, MergesNot startedLearningPracticingMastered
• Why B+ Trees: Fanout, Not Big-ONot startedLearningPracticingMastered
• Hash Index InternalsNot startedLearningPracticingMastered

The buffer pool and its replacement policies, one read and one write followed end to end, then the write-ahead log, checkpoints and the restart sequence after a crash (V6–V7). Needs Storage for the pages being cached and Transactions for what COMMIT promises.

Before moving on: Trace a write from UPDATE to durable on disk, and explain why the log record must reach disk before the dirty page does.

• The Buffer PoolNot startedLearningPracticingMastered
• Buffer Replacement: LRU, Clock and Scan ResistanceNot startedLearningPracticingMastered
• Follow a Read Through the EngineNot startedLearningPracticingMastered
• Follow a Write Through the EngineNot startedLearningPracticingMastered
• Write-Ahead LoggingNot startedLearningPracticingMastered
• Crash RecoveryNot startedLearningPracticingMastered

Transactions as mechanisms: lock tables, waits-for graphs and deadlock detection, version chains, snapshots, dead tuples, and the same workload run under three isolation levels (V8–V9). Builds on the practical Transactions stage and on the WAL, which is what makes a version durable.

Before moving on: Walk an UPDATE through MVCC — which version is written, which snapshot sees it, when it becomes dead — and find the cycle in a waits-for graph.

• A Transaction, Inside the EngineNot startedLearningPracticingMastered
• Concurrency Control: Schedules and SerializabilityNot startedLearningPracticingMastered
• The Lock ManagerNot startedLearningPracticingMastered
• Deadlock Detection: The Waits-For GraphNot startedLearningPracticingMastered
• MVCC Internals: Version Chains and SnapshotsNot startedLearningPracticingMastered
• UPDATE, DELETE and Dead TuplesNot startedLearningPracticingMastered
• Isolation Levels: The Mechanism Behind EachNot startedLearningPracticingMastered

Parser, AST, planner, cost model and join algorithms, then one SQL statement followed from text to result through the real in-browser engine (V10). Needs the practical Execution stage for reading plans and B+ Trees for the access paths the planner chooses between.

Before moving on: Explain why the planner picks a hash join over a nested loop for a given pair of table sizes, and what a wrong row estimate does to that choice.

• Inside the Query EngineNot startedLearningPracticingMastered
• Tokens, Parse Tree, ASTNot startedLearningPracticingMastered
• The Planner: Enumerating Ways to AnswerNot startedLearningPracticingMastered
• Cost-Based OptimizationNot startedLearningPracticingMastered
• Join Algorithms: Nested Loop, Hash, MergeNot startedLearningPracticingMastered
• Follow the QueryNot startedLearningPracticingMastered

The write-optimised alternative to the B+ tree: memtables, SSTables, bloom filters, compaction and the three amplifications, closing with an honest B+ tree vs LSM comparison. Needs B+ Trees to compare against and the WAL, which an LSM engine reuses for its memtable.

Before moving on: Explain why an LSM write is cheap and a read is not, what compaction is paying for, and choose B+ tree or LSM from a workload description.

• LSM Trees: Why Some Engines Favour WritesNot startedLearningPracticingMastered
• SSTables: The Immutable Sorted FileNot startedLearningPracticingMastered
• Bloom Filters: Skipping Files That Cannot Contain the KeyNot startedLearningPracticingMastered
• Compaction: The Merge That Pays for Cheap WritesNot startedLearningPracticingMastered
• Write, Read and Space AmplificationNot startedLearningPracticingMastered
• Storage Engine Comparison: B+ Tree vs LSM TreeNot startedLearningPracticingMastered

The general mechanisms as two real engines implement them: heap tuples, xmin/xmax, shared buffers and VACUUM in PostgreSQL versus clustered primary keys, redo and undo logs in InnoDB, and why their physical layouts change query cost. Needs the practical PostgreSQL stage and MVCC internals.

Before moving on: Explain why a secondary-index lookup costs two tree walks in InnoDB and one heap fetch in PostgreSQL, and what each engine does with an old row version.

• PostgreSQL Internals: Heap, Tuples, Shared Buffers, WAL, VACUUMNot startedLearningPracticingMastered
• InnoDB Internals: Clustered Index, Buffer Pool, Redo, Undo, LocksNot startedLearningPracticingMastered
• Physical Layouts Compared: Heap + Secondary Index vs Clustered IndexNot startedLearningPracticingMastered

How changes actually propagate — the replication stream, partition functions, quorums, a failure simulator you can break — then "why is it slow" one layer down: page reads, buffer misses, scan choice, index maintenance, WAL and compaction, with the central playground to turn the knobs (V11). Needs the practical Distributed stage plus the buffer pool, WAL and LSM mechanisms the knobs control.

Before moving on: Predict what a replica sees after a leader failover, and attribute a slow query to page reads, buffer misses, index maintenance, WAL or compaction using the playground.

• Replication Internals: WAL Shipping, LSNs, Lag and FailoverNot startedLearningPracticingMastered
• Partitioning Internals: Key → Partition Function → NodeNot startedLearningPracticingMastered
• Distributed Fundamentals: Partial Failure, Quorums, Consensus, RecoveryNot startedLearningPracticingMastered
• Performance Internals: Why the Slow Node Is SlowNot startedLearningPracticingMastered
• The Database Internals PlaygroundNot startedLearningPracticingMastered