SQL & Relational Engine Concepts
20 in-depth architectural topics from B+ Tree leaf traversals to MVCC tuple headers and Cost-Based Optimizers.
Relational Model & SQL Query Execution Lifecycle
The journey of a SQL query through the database engine: Parser (AST) -> Query Rewriter -> Cost-Based Optimizer -> Execution Engine -> Buffer Pool / Storage Engine.
B+ Tree Indexes & Leaf Page Traversals
B+ Trees are balanced multi-way search trees used by PostgreSQL, MySQL (InnoDB), and SQLite to provide O(log N) point lookups and efficient sequential range scans.
Clustered vs Secondary Indexes (Heap Tables vs Index-Organized)
In MySQL InnoDB, the Clustered Index (Primary Key) stores the entire row payload directly inside leaf nodes. Secondary indexes store the Primary Key value, requiring a secondary lookup (Bookmark Lookup).
Composite Indexes & The Leftmost Prefix Rule
A composite index on (A, B, C) can satisfy queries on (A), (A, B), and (A, B, C), but CANNOT be used efficiently for queries filtering only on (B) or (C).
EXPLAIN ANALYZE & Query Execution Plans
EXPLAIN displays the optimizer estimated execution plan; EXPLAIN ANALYZE actually runs the query, reporting real wall-clock timing, row counts, and buffer page cache hits.
SQL Joins & Execution Algorithms (Nested Loop, Hash, Merge)
The query optimizer selects between three physical join strategies: Nested Loop Join (small/indexed), Hash Join (large unsorted equi-joins), and Merge Join (pre-sorted streams).
ACID Properties & Write-Ahead Logging (WAL)
ACID guarantees database reliability: Atomicity (All or Nothing), Consistency (Constraints preserved), Isolation (Concurrent transactions do not interfere), and Durability (Committed data survives crashes via WAL).
Transaction Isolation Levels & Concurrency Anomalies
SQL standards define 4 isolation levels to prevent concurrency anomalies: Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
Multi-Version Concurrency Control (MVCC) & Vacuuming
MVCC allows readers not to block writers and writers not to block readers by storing multiple immutable versions of row tuples with creation (xmin) and deletion (xmax) transaction IDs.
Window Functions (ROW_NUMBER, RANK, OVER PARTITION)
Window functions perform calculations across a set of table rows related to the current row without collapsing them into a single row like GROUP BY does.