Jonas Notes

Layers of a Database

2 min read

0. Database Driver (External, Not Part of the DB Server)

  • Purpose: Acts as a translator between the application and the database server.

  • Responsibilities:

    • Implements the wire protocol (handshake, packet formatting, authentication).
    • Handles type conversions (e.g., DATETIME → LocalDateTime).
    • Relies on the OS networking stack for low-level byte transmission.

  • Important:

    • The driver is not part of the database server itself.
    • It only exists to connect applications to the DB server.

Examples:

  • MySQL Connector/J (JDBC)
  • MariaDB Java Client
  • ODBC drivers

1. Query Engine (Inside the DB Server)

The query engine is responsible for interpreting and executing SQL. It has four main sublayers:

  1. Parser

    • Converts raw SQL text → Abstract Syntax Tree (AST).
    • Checks syntax validity.

  2. Optimizer

    • Chooses efficient strategies (index use, join order, predicate pushdown).
    • Produces alternative plans and picks the “best” one based on cost estimates.

  3. Planner

    • Converts the logical plan into a concrete physical plan.
    • Assigns actual operators (e.g., index scan, nested loop join).
    • What you see when you run EXPLAIN.

  4. Executor

    • Runs the physical plan step by step.
    • Produces tuples (rows) by calling iterators/operators (e.g., IndexScan, HashJoin).
    • Sends results back to the client.

2. Storage Engine (Inside the DB Server)

The storage engine manages persistence and retrieval.

  • Responsibilities:

    • File formats, page structures, and indexes (B+Trees, LSM trees, columnar segments).
    • Concurrency control (MVCC, locks).
    • Durability and recovery (WAL / redo logs).
    • Physical layout on disk.

  • Examples:

    • MySQL: InnoDB, MyISAM
    • PostgreSQL: Native heap storage
    • ClickHouse: MergeTree family
    • StarRocks: Rowset + Segment

🗂️ Flow Overview

Application
   │
   ▼
Driver (external to DB server)
   │
   ▼
+---------------------- Database Server ----------------------+
|  +-------------------- Query Engine ---------------------+  |
|  | Parser → Optimizer → Planner → Executor              |  |
|  +------------------------------------------------------+  |
|                          │                                |
|                          ▼                                |
|                    Storage Engine                         |
+-----------------------------------------------------------+

✅ Summary:

  • The driver exists only to connect apps to the DB server, not part of the server itself.

  • Inside the DB server, there are two main layers:

    1. Query Engine (with Parser, Optimiser, Planner, Executor).
    2. Storage Engine (handles persistence, concurrency, recovery).