Database Engineering Roadmap

What a database is for — durability, concurrency, access paths — and what the engine does with a query.

• What a Database Actually IsNot startedLearningPracticingMastered

Reading data: evaluation order, NULL, filtering, aggregation, every join, subqueries and window functions.

• 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 access patterns into tables, relationships and keys — then removing anomalies with normalization.

• 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, and the decision to add one.

• 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

The planner and executor, reading EXPLAIN ANALYZE, and finding the actual bottleneck.

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

ACID, the anomalies concurrent transactions cause, isolation levels, MVCC, and locking.

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

The concrete implementation: types, JSONB, full-text, extensions, and production operations.

• 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, the caching patterns and their hazards, and choosing a non-relational model when one fits.

• 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, approximate nearest neighbour, and where the storage for AI retrieval lives.

• Vector Search: Embeddings, Similarity and ANNNot startedLearningPracticingMastered

The scaling ladder, replication, partitioning and sharding, and distributed consistency without the slogans.

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

Why the page is the unit of everything: bytes, records, page files, slotted pages. Start of the Build-AtlasDB journey (V0–V3).

• 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 the page-oriented B+ tree, its fanout argument, and hash indexes (V4–V5).

• 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, the write-ahead log and crash recovery (V6–V7).

• 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

Lock tables, waits-for graphs, version chains, snapshots, dead tuples and isolation levels as mechanisms (V8–V9).

• 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, planner, cost model, join algorithms, and one query followed through the real in-browser engine (V10).

• 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 engine: memtables, SSTables, bloom filters, compaction, the three amplifications, and B+ tree vs LSM.

• 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, and why their physical layouts change query cost.

• 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

The replication stream, partition functions, the failure simulator, and “why is it slow” one layer down with the central playground (V11).

• 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