---
topic: databases
author: Crashtech Editorial
published: Mar 30, 2026 · read: 5 min
---

The Hidden Work Behind Every Database Request

What happens inside a relational database engine between issuing a SQL query and receiving rows: parsing, optimization, locks, and WAL logging.

– –

To application developers, executing a database query looks deceptively simple: a client sends a SQL string over a TCP socket, and an array of JSON-like rows arrives milliseconds later.

Underneath that straightforward abstraction lies an astonishing sequence of computational stages. A modern relational database engine (such as PostgreSQL, MySQL, or CockroachDB) executes a sophisticated distributed pipeline encompassing lexical parsing, AST construction, catalog resolution, cost-based query optimization, lock acquisition, buffer pool caching, and write-ahead log durability.

Here is the exact journey of a database request through the core subsystems of a relational database engine.

Step 1: Network Transport and Connection Handling

When an application issues a query, the payload begins as raw bytes governed by a wire protocol (e.g., PostgreSQL frontend/backend protocol v3.0):

+-----------------------------------------------------------------------+
|                    THE QUERY EXECUTION PIPELINE                       |
+-----------------------------------------------------------------------+
|  [ Client Application ]                                               |
|         | Wire Protocol Packets (TCP Socket)                          |
|         v                                                             |
|  [ 1. Transport & Auth ] ---> SSL Handshake & User Privileges         |
|         |                                                             |
|         v                                                             |
|  [ 2. Parser & Lexer ]   ---> Tokenization & Abstract Syntax Tree     |
|         |                                                             |
|         v                                                             |
|  [ 3. Catalog Resolver ] ---> Schema Validation & Type Checking       |
|         |                                                             |
|         v                                                             |
|  [ 4. Query Rewriter ]   ---> View Inlining & Constant Folding        |
|         |                                                             |
|         v                                                             |
|  [ 5. Cost-Based Optimizer ] -> Plan Enumeration & Index Selection    |
|         |                                                             |
|         v                                                             |
|  [ 6. Execution Engine ] ---> Volcano Iterator Tree (open/next/close) |
|        /               \                                              |
|       v                 v                                             |
| [ Buffer Pool ]      [ Lock Manager ]                                 |
|       |                                                               |
|       v                                                               |
| [ 7. WAL Engine & Disk ]                                              |
+-----------------------------------------------------------------------+
  1. TLS Decryption and Packet Framing: The server validates packet headers, decrypts the TLS stream, and parses message types (such as Simple Query ('Q') or Extended Query Protocol ('P', 'B', 'E')).
  2. Session State Verification: The engine verifies that the connection has not timed out, checks transaction isolation state, and ensures the authenticated role holds permission to access the target database.

Step 2: Lexical Analysis, Parsing, and AST Generation

The database engine treats the raw SQL string as input to a formal grammar compiler (often generated via Flex and Bison):

  • Lexer: Scans characters and outputs a stream of lexical tokens (SELECT, identifier: users, operator: =, constant: 42).
  • Parser: Validates grammatical structure against standard SQL syntax rules and builds an in-memory Abstract Syntax Tree (AST).
  • Semantic Analysis / Catalog Resolution: The engine queries its system catalogs (pg_class, pg_attribute, information_schema) to answer critical questions:
    • Does the table users actually exist?
    • Does the column created_at belong to users or a joined table?
    • Do the column data types match the query operators?

If any table is missing or a type mismatch occurs, execution aborts immediately with a syntactic or semantic error.

Step 3: Query Rewriting and Semantic Transformations

Before optimization begins, the database rewrites queries according to schema rules:

  • View Expansion: Views referenced in the query are expanded inline into subqueries.
  • Constant Folding: Expressions like WHERE created_at > NOW() - INTERVAL '7 days' or WHERE 1 = 1 AND active = true are simplified to eliminate redundant branch evaluation.
  • Predicate Pushdown: Filter conditions are pushed down closer to the physical table scan nodes to avoid materializing unnecessary tuples early in the plan.

Step 4: The Cost-Based Optimizer (CBO)

The query optimizer is the mathematical crown jewel of the database. For a single query involving multiple joins, there may be thousands of mathematically valid execution plans. The optimizer’s job is to predict which plan requires the fewest CPU instructions and disk I/O operations.

+---------------------------------------------------------------------+
|                      OPTIMIZER ESTIMATION LOGIC                     |
+---------------------------------------------------------------------+
| Candidate Plan               | Est. Disk I/O | Est. CPU Cost | Pick |
+------------------------------+---------------+---------------+------+
| Plan A: Sequential Table Scan| 12,400 pages  | 420 ms        | No   |
| Plan B: B-Tree Index Scan    | 18 pages      | 2.1 ms        | YES  |
| Plan C: Hash Join + Bitmap   | 145 pages     | 14.8 ms       | No   |
+------------------------------+---------------+---------------+------+

The optimizer relies on column statistics gathered by background workers (e.g., ANALYZE):

  • Equi-depth Histograms: Estimating the distribution of values across columns.
  • Most Common Values (MCV): Exact frequencies of heavily skewed values.
  • Null Fraction & Average Width: Estimating memory buffer requirements for result sets.

The optimizer outputs an executable physical plan: an interconnected graph of execution nodes (e.g., IndexScan, NestedLoop, HashJoin, Aggregate).

Step 5: The Execution Engine and Volcano Iterator Model

Most modern database engines employ the classic Volcano Iterator model. Every node in the execution plan implements three primary methods:

  • open(): Allocates memory buffers, builds hash tables, or initializes index cursors.
  • next(): Yields the next available tuple up the tree.
  • close(): Frees resources and closes file descriptors.
// Conceptual Volcano Iterator Interface
typedef struct PlanState {
    TupleTableSlot* (*ExecProcNode)(struct PlanState *node);
} PlanState;

Because child nodes yield tuples one by one to parent nodes, queries can stream results over the wire before the entire dataset is materialized in memory, preserving buffer cache space.

Step 6: Buffer Pool, Lock Acquisition, and WAL Durability

When an iterator requests tuple #4821, the request descends into the storage manager:

1. Lock Manager Acquisition

Before reading or writing data pages, the query must acquire appropriate locks. For read queries, it requests shared access locks (AccessShareLock) to prevent concurrent schema alterations (DDL). For updates, it acquires exclusive row-level locks within the transaction manager.

2. The Buffer Pool Cache Check

The engine checks whether the 8KB disk page holding the requested tuple resides in memory:

  • Cache Hit: The page is already pinned in the shared buffer pool; the engine reads the row directly from RAM.
  • Cache Miss: The engine issues a synchronous or asynchronous read syscall to disk or NVMe flash storage, evicting an unpinned page using an LRU or 2Q replacement algorithm.

3. Write-Ahead Logging (WAL) for Writes

If the query modifies data (INSERT, UPDATE, DELETE), ACID durability mandates that changes cannot simply sit in memory.

Before modifying the dirty page in the buffer pool:

  1. The engine constructs a binary Write-Ahead Log (WAL) record describing the byte-level delta.
  2. The WAL record is flushed to persistent disk via fsync().
  3. Only once the WAL flush succeeds is the in-memory page marked dirty. If power fails immediately afterward, database recovery replays the WAL log to restore absolute consistency.
+-----------------------------------------------------------------------+
|                       DURABILITY PROTOCOL (WAL)                       |
+-----------------------------------------------------------------------+
| [ UPDATE Statement ]                                                  |
|        |                                                              |
|        v                                                              |
| 1. Construct WAL Delta Record                                         |
|        |                                                              |
|        v                                                              |
| 2. fsync() WAL Record to Persistent Disk (Strict Durability Barrier)  |
|        |                                                              |
|        v (Only after fsync succeeds)                                  |
| 3. Mutate 8KB Data Page in Shared Memory Buffer Pool (Mark "Dirty")   |
|        |                                                              |
|        v (Asynchronous Background Writer)                             |
| 4. Flush Dirty Page to Database File (Table Storage)                  |
+-----------------------------------------------------------------------+

Step 7: Network Serialization and Client Delivery

Finally, the Volcano iterator passes the produced tuple up to the connection backend. The backend encodes the row into the wire protocol’s binary format, fills TCP transmit socket buffers, and sends packets back across the network to the application.

Understanding this subterranean machinery separates engineers who write blind SQL queries from architects who design resilient, predictable high-performance database systems.

Advertisement

/* Comments */