Databases
Beginner 12 min read Databases

Relational Database Internals

The four machines hiding behind every table

A relational database is a manager for tables: it stores rows, parses and optimizes SQL, coordinates concurrent writers, and safely persists every committed change.

storagequery enginetransactionsWALbuffer cacheconcurrency
Prerequisites: SQL Query Fundamentals
Before we start

What you will learn

  • Name the storage, query, transaction, and durability components
  • Trace one SELECT from parser to returned rows
  • Explain why committed data survives crashes
Introduction

What is this?

Under the hood a relational database is a program that runs four cooperating subsystems: storage (how bytes are laid out on disk), a query engine (parsing, planning, executing SQL), a transaction manager (serializing reads and writes safely), and a WAL-driven durability layer that guarantees committed work is not lost.

Motivation

Why do you need it?

  • Choosing an index or reading an EXPLAIN plan only makes sense once you see how rows are actually read.
  • The WAL answers the question every stakeholder asks: what happens if the machine dies?
  • Knowing the architecture explains why some writes are slow: fsyncs and transaction log order.
Think about it

Mental model

Imagine

A library with a card catalog, reading rooms, and a journal

Think of

A relational database

Indexes are the card catalog, the query engine is the librarian, MVCC is the reading room giving everyone a consistent copy, and the WAL is a ledger a fire-safe vault protects.

Mechanics

How it works

  • Parser turns SQL text into a syntax tree; the planner turns that into a physical plan chosen by cost.
  • Executor walks the plan tree: scans tables or indexes, joins, filters, aggregates, and returns rows.
  • Buffer cache keeps hot pages in memory; dirty pages are flushed to data files by background checkpoints.
  • Transactions write to the WAL first (write-ahead log); the data page is updated later and is crash-safe because the WAL is durable.
  • Concurrency is managed by MVCC: each row carries version visibility info so readers never block writers.

One SELECT through the five subsystems.

SQL text
Parser/planner
Executor
Buffer cache
Returned rows
Hands-on

Examples

The invisible stages behind a query

The invisible stages behind a query
-- You write:
SELECT * FROM orders WHERE status = 'pending';
 
-- The engine actually runs roughly:
-- 1. parse -> SELECT statement AST
-- 2. plan  -> Seq Scan on orders with filter status='pending'
-- 3. exec  -> stream rows from the buffer cache
-- 4. commit/read consistency -> MVCC snapshot decides which rows you see

Your SQL is a contract; the engine is free to choose any physical plan that honors it.

Real world

In production

  • A query spike investigation starts with the executor: is it scanning, or reading from cache?
  • Capacity planning tracks free connections, cache hit ratio, and WAL write rate as the early-warning gauges.
  • Reading the physical plan is how the on-call engineer decides between adding an index and rewriting the query.
Guidelines

Best practices

  • Prefer bounded result sets to keep the executor from buffering huge sorts.
  • Monitor cache hit ratio and WAL write volume before performance degrades.
  • Design schemas so hot tables benefit from the buffer cache instead of fighting it.
Watch out

Common mistakes

  • Assuming an index solves a query that still hits a full seq scan because the predicate is non-sargable.
  • Treating the database as an in-memory cache and scaling it instead of reading the plan.
  • Ignoring write amplification: every UPDATE rewrites a row and its version chain.
When things break

Debugging tips

  • Use an embedded engine (SQLite) to experiment: EXPLAIN shows the same ideas with zero setup.
  • Count I/O with EXPLAIN (ANALYZE, BUFFERS) to see pages touched, not just rows.
  • Check wal activity during a slow write batch to separate disk latency from lock contention.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which subsystem guarantees a committed transaction survives a power loss?

2

MVCC allows readers to see a consistent snapshot without blocking writers.

Go deeper

Official documentation

PostgreSQL System Architecture
docs
SQLite Architecture
docs
MySQL InnoDB Overview
docs