Internal Architecture of a Database Engine

Problem Explain the internal architecture of a relational database engine - what the major components are and how a query flows through them.

Be ready to discuss

• Query parser and planner/optimizer: parsing SQL into an AST, rewriting it, and using table statistics and available indices to pick an execution plan by cost.
• Storage engine: on-disk page layout, row formats, heap vs clustered storage, and how tuples are located.
• Index structures: B-Tree/B+Tree for range and point lookups, hash indices, and LSM-trees in write-heavy engines; clustered vs secondary indices.
• Buffer pool / page cache: keeping hot pages in memory, eviction policy (LRU and its variants), and dirty page flushing.
• Transaction manager and concurrency control: two-phase locking vs MVCC, lock granularity, deadlock detection.
• Write-ahead logging (WAL): log-before-data ordering, checkpoints, and crash recovery (redo committed work, undo uncommitted).
• The trade-off that runs through all of it: indices speed reads but add write amplification and space, so index choice is workload-dependent.