Database Internals & Query Optimization

By the end, you can

• Trace a SQL query through the parse → optimize → execute → rows pipeline and explain what each stage does.
• Describe how data is stored in pages and why the buffer pool is critical to query performance.
• Contrast row-store and column-store layouts and identify which workload each serves best.
• Explain when a sequential scan outperforms an index scan and why.
• Compare nested-loop, hash, and sort-merge joins by cost formula and best-fit scenario.
• Apply the selectivity formula to estimate cardinality and explain how histograms improve those estimates.
• Identify cardinality errors as the primary cause of bad plans and explain the role of fresh statistics.
• Explain why join ordering is NP-hard and how dynamic programming makes it tractable.
• Describe how WAL provides crash-recovery durability without random writes.
• Explain how MVCC enables readers and writers to proceed concurrently without blocking each other.