Part 3: Architecture of an RDBMS: Storage Engines & Query Processors

To write high-performance SQL queries, you must understand what happens inside the database engine when you execute a query.

1. The Query Lifecycle Pipeline

[ Client Application (Sends SQL Query) ]
                   │
                   ▼
       [ Parser & Lexer ] ── (Syntax & semantic check)
                   │
                   ▼
      [ Query Optimizer ] ── (Evaluates index costs, statistics)
                   │
                   ▼
     [ Execution Engine ] ── (Executes optimal execution plan)
                   │
                   ▼
       [ Buffer Pool / Cache ] ◄──► [ Storage Engine (Disk / SSD) ]
                   │
                   ▼
       [ Write-Ahead Log (WAL) ] (Guarantees Durability)

2. Core Architectural Components

1. Parser & Compiler

Validates syntax (SELECT * FRMO users fails here) and checks permissions and table schemas.

2. The Cost-Based Query Optimizer (CBO)

The brain of the database. For any query, there are dozens of ways to retrieve the data (full table scan, index scan, hash join, nested loop). The optimizer calculates the mathematical cost based on table statistics and picks the fastest plan.

3. Buffer Pool / Shared Buffers

Reading from disk/SSD is thousands of times slower than RAM. The buffer pool caches database pages (typically 8KB or 16KB blocks) in memory. Reads and writes hit RAM first.

4. Write-Ahead Logging (WAL) / Redo Log

Before any data change is committed to data files on disk, it is sequentially appended to the WAL log. If the server suddenly loses power, the database replays the WAL on restart to guarantee zero data loss.

Key Takeaways

• Databases never touch disk directly on every query; they read and write through in-memory buffer pools.
• The Cost-Based Optimizer determines execution strategy based on table statistics and indexes.
• Write-Ahead Logs (WAL) guarantee durability even during sudden server crashes.

In Part 4, we will learn the 5 Core SQL Sublanguages: DDL, DML, DQL, DCL, and TCL!