Database Engineering · Internals layer

Database Internals

Why does the database behave this way internally? The practical layer teaches how to use databases correctly; this layer sits underneath it and derives every mechanism — pages, B+ trees, the buffer pool, WAL, MVCC, LSM trees, the query planner, replication — from the problem it solves. An interactive systems textbook, not a glossary.

Two layers, one domain

The learner should never feel these are separate products. Every practical lesson carries a “See how this works internally →” link; every internals lesson links back to where you meet it in practice.

Practical layer

How do I build with databases?

Fundamentals SQL Modeling Normalization Indexes Query Plans Transactions Concurrency PostgreSQL Redis NoSQL Vector Scaling Distributed Caching

⇅

Internals layer

Why do databases behave this way?

Storage Records Pages B+ Trees Buffer Management WAL MVCC Recovery LSM Trees Query Engine Distributed Internals

Database Engineering
│
├── SQL · Data Modeling · Indexes · Transactions · Query Optimization
├── PostgreSQL · Redis · NoSQL · Scaling
│
└── Database Internals
    ├── Storage      ├── Pages         ├── Records
    ├── B+ Trees     ├── Buffer Mgmt   ├── WAL
    ├── MVCC         ├── Recovery      ├── LSM Trees
    ├── Query Engine └── Distributed Internals

Start here

Build AtlasDBguided journey

Grow atlas.db from a flat file to a replicated MVCC engine in twelve versions. Each one fixes what the previous could not do.

Internals Playgroundsimulator

B+ tree or LSM, 1K–100M rows, small or large RAM, hash or tree index, read- or write-heavy — watch page accesses, misses, compaction.

⚡ Crash a databasecrash mode

Update, log, commit, then pull the plug at any moment. Does the transaction survive? Watch recovery replay the log.

Follow a read

SELECT … WHERE id = 42, from SQL through the index root, leaf, buffer pool and data page to the record.

Follow a write

UPDATE accounts SET balance = balance − 100: find, load, log, modify, mark dirty, commit, flush later.

Follow the query

A join + group + sort + limit through the real in-browser engine: parser, planner, cost, scans, joins, aggregate, sort.

B+ tree visualizer

Search, insert, delete, split, merge, range scan — with tree height, pages read/written, comparisons and leaves traversed.

Page inspector

Insert, update, delete, compact and fill an 8 KB slotted page and watch the slot directory and free space move.

Descend from a normal question

The defining experience: start where an engineer starts and go all the way down into the machinery.

Why is this query slow?

Slow querypractical
↓Execution planpractical
↓Sequential scaninternals
↓Pagesinternals
↓Storage readsinternals

Why is the indexed query fast?

Index scanpractical
↓B+ treeinternals
↓Internal pagesinternals
↓Leaf pageinternals
↓Buffer poolinternals
↓Recordinternals

What does COMMIT actually do?

COMMITpractical
↓Transactioninternals
↓WALinternals
↓Durabilityinternals
↓Dirty pageinternals
↓Recoveryinternals

Why does DELETE not free space?

DELETEpractical
↓MVCCinternals
↓Dead tuplesinternals
↓VACUUMinternals
↓Slotted pageinternals

How every internals lesson is taught

Never a definition first. The learner understands why a mechanism exists before memorising its name.

Problem↓
Naive solution↓
Why it breaks↓
Better idea↓
Internal mechanism↓
Trade-offs↓
Real database

Example: the B+ tree

ProblemScanning millions of rows is slow.
Naive solutionMaintain searchable metadata: a sorted array of key → row.
Why it breaksThe index is larger than memory; inserting into a sorted array shifts everything.
Better ideaOrganise the index into storage-friendly pages, with a page of separators above.
Internal mechanismA B+ tree: fanout ~200, height 3–4, linked leaves.
Trade-offsEvery write maintains it; splits cost pages; low-selectivity lookups lose to a scan.
Real databasePostgreSQL nbtree, InnoDB clustered index, SQLite B-tree.

Every lesson also offers four depths — Overview, Intermediate, Advanced, Internals — and labels every simulated number as an educational simulation, and every engine-specific section as PostgreSQL or InnoDB rather than “databases”.