Components

Components#

Every database engine, from SQLite to Postgres to Cassandra to Elasticsearch, is built from the same handful of internal components. The forms differ; the responsibilities do not. A storage layer holds bytes; a query layer turns text into a plan; an execution layer runs the plan; a transaction layer keeps concurrent work from corrupting state; a durability layer survives crashes; a replication layer survives node loss.

Knowing the component map turns engine-specific knobs into recognisable variants of the same concern. The page that follows walks each component, what it does, how it varies, and what the operator sees when it breaks.

Storage engine#

The bottom of the stack. Lays out rows or documents on disk (or in memory), maintains the file format, and serves typed records up to the layers above. Two dominant designs.

Design	Notes
B-tree (page-based)	Pages of a fixed size (8 KiB Postgres, 16 KiB InnoDB), organised in a balanced tree. Reads are O(log n); writes update pages in place. Postgres, MySQL/InnoDB, SQLite, SQL Server, Oracle.
LSM-tree (log-structured merge)	Writes append to a memtable; full memtables flush to sorted SSTable files; background compaction merges SSTables. Writes are fast; reads check multiple files. Cassandra, RocksDB, LevelDB, ScyllaDB, recent SQLite extensions (libSQL).

Page layout, in either design, separates a small header (transaction id, checksums, free space pointer) from variable- length slots that hold the rows.

┌─ page header ────────┐
│ xmin / xmax / flags  │
│ free space pointer   │
├─ slot directory ─────┤
│ slot[0] → offset     │
│ slot[1] → offset     │
│ ...                  │
├─ free space ─────────┤
├─ row tuple ──────────┤
│ row data             │
└──────────────────────┘

The operator meets storage-engine internals through configuration (page size, fillfactor), through corruption recovery (pg_resetwal, REPAIR TABLE), and through extension authoring.

Indexes#

A separate data structure that maps column values to row locations, so lookups beat full scans. The engine maintains every index on every write; the cost of writes scales with the count.

Index type	When to use
B-tree	General-purpose. Equality, range, ordered scans. The default in every relational engine.
Hash	Equality only; constant-time. Smaller than B-tree on wide keys.
Bitmap	Low-cardinality columns (booleans, enums). One bit per row per distinct value.
GIN (inverted)	Postgres. Many-to-one mappings (full-text terms, JSONB keys, array elements).
GiST / SP-GiST	Postgres. Geometric, range, custom domain (PostGIS, `btree_gist`).
BRIN (block range)	Postgres. Naturally-ordered columns (timestamps). Tiny on disk; coarse on lookup.
HNSW / IVF	Vector / similarity search. Approximate-nearest-neighbour for embeddings.
LSM secondary	Cassandra / Scylla. Secondary indexes implemented as another LSM-tree.

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created ON orders (created_at DESC);
CREATE INDEX idx_logs_payload ON logs USING GIN (payload jsonb_path_ops);
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', body));

The operator picks an index for the query pattern, not the column type. Two queries on the same column can need different indexes.

Query parser#

Turns the query text (SQL, KQL, Cypher, MQL) into an abstract syntax tree the planner can walk. Pure mechanical translation; no decisions about how to execute.

"SELECT id FROM users WHERE age > 18"
     │
     ▼  lex + parse
SelectStmt(
  targets = [ColumnRef(id)],
  from    = [TableRef(users)],
  where   = GreaterThan(ColumnRef(age), IntLiteral(18)),
)

Errors at this stage are syntax errors. Semantic checks (does users.age exist? is the type comparable to int?) happen in a binder pass between the parser and the planner.

Query planner / optimiser#

Picks the cheapest plan for the parsed query. The planner enumerates plan forms (join orders, index choices, scan methods) and scores them with a cost model fed by statistics that the engine periodically samples (ANALYZE).

Cost-model inputs the operator can see.

Statistic	What it estimates
row count per table	how big a scan will be
histogram per column	selectivity of `col = value`, `col BETWEEN …`
distinct values (n_distinct)	join cardinality
correlation	whether physical order matches index order
extended statistics	multi-column dependencies

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > now() - interval '7 days'
GROUP BY u.name;

The plan output is the operator’s main debug tool: see the chosen join algorithm, scan method, estimated vs actual rows, and total cost.

Query executor#

Runs the plan. Two execution models dominate.

Model	Notes
Volcano / iterator	Each operator pulls one row from its child at a time. Simple, composable, low per-row throughput. Postgres, SQLite, MySQL.
Vectorised	Each operator processes a batch (1024 rows) at a time. Cache-friendly, SIMD-friendly. DuckDB, ClickHouse, Snowflake, Velox.
Compiled / JIT	The plan is compiled to native code per query (Postgres LLVM mode, Hyper, Singlestore). Best for long-running queries.

Join algorithms the executor picks from.

Algorithm	When
Nested loop	one side tiny, the other indexed
Hash join	one side fits in memory, equality predicate
Merge join	both sides already sorted on the join key
Index nested loop	outer side small, inner side has matching index

Buffer pool / page cache#

The engine’s in-memory cache of disk pages. Most reads serve from RAM; writes mark pages dirty and the background flusher moves them out. Tunable through one big knob (shared_buffers in Postgres, innodb_buffer_pool_size in MySQL, cache_size PRAGMA in SQLite).

The OS page cache sits below the buffer pool. Most engines do not use O_DIRECT; the database buffer pool and the kernel page cache are both in play, with the engine paying for the indirection.

query → buffer pool → kernel page cache → disk

Cache hit ratio is the headline metric. Postgres’s pg_stat_database exposes it; the operator wants 99%+ on OLTP and accepts ~70% on warehouse scans.

Transaction manager#

The contract the engine keeps with concurrent clients. The four ACID properties name the contract.

Property	Meaning
Atomicity	the transaction commits whole or rolls back whole
Consistency	the transaction leaves data in a valid state per the schema’s invariants
Isolation	concurrent transactions cannot see each other’s partial state (level-dependent)
Durability	committed data survives crash and reboot

Isolation levels weaken atomicity-of-reads to gain concurrency.

Level	What can happen
READ UNCOMMITTED	dirty reads (rare in modern engines)
READ COMMITTED	non-repeatable reads, phantom reads. Postgres default.
REPEATABLE READ	phantom reads (sometimes). MySQL InnoDB default.
SNAPSHOT / SERIALIZABLE	none of the above; sometimes at cost of conflicts that force one transaction to retry

Concurrency control#

How the engine prevents two transactions from corrupting the same data. Two main strategies.

Strategy	Notes
Locking (pessimistic)	readers and writers acquire shared / exclusive locks before access. SQL Server, older MySQL.
MVCC (multi-version)	each write creates a new row version with a transaction id; readers see the version that was committed at the start of their snapshot. Postgres, MySQL/InnoDB, Oracle, CockroachDB.

MVCC eliminates reader-writer blocking but accumulates dead tuples the engine must vacuum / clean up. Postgres’s VACUUM and autovacuum are how that runs.

WAL / durability#

Before a write touches the table page, the engine writes the intent to a write-ahead log. On commit, the WAL record is flushed to disk. On crash, replay the WAL forward from the last checkpoint to recover the table state.

BEGIN
INSERT row 7   ─── WAL: "insert row 7 to page 42" ───┐
UPDATE row 3  ─── WAL: "update row 3 on page 17" ───┤
COMMIT       ─── WAL fsync ─────────────────────────┘
                                  │
                                  ▼
                       committed; data files updated
                       lazily by checkpoint / flusher

The operator meets WAL through:

Replication: most replication streams the WAL byte-by-byte to the replica.
Point-in-time recovery: restore a base backup and replay WAL up to a chosen timestamp.
Disk usage: long-running open transactions hold the WAL back from being recycled; orphaned slots in Postgres are a common operational pain.

Replication#

How many copies the data has, and how they stay in sync.

Mode	Notes
Streaming (physical)	byte-exact WAL shipped to one or more standbys. Read-only standbys; same version. Postgres, MySQL (semi-sync), SQL Server AlwaysOn.
Logical	decode the WAL into logical row changes and apply on the subscriber. Supports cross-version, cross-platform, and selective replication. Postgres `pub/sub`, MySQL binlog row-based.
Multi-leader	every node accepts writes; conflicts resolved by last-writer-wins, vector clocks, or CRDTs. Cassandra, CouchDB, ScyllaDB.
Quorum	read / write to a quorum of N nodes (R + W > N → strong reads). Cassandra, Riak.
Consensus	a single elected leader handles writes; consensus (Raft, Paxos) replicates the WAL. CockroachDB, etcd, FoundationDB, Spanner.

The synchronous / asynchronous knob is independent: a streaming replica can wait for the standby to ack (synchronous_commit = on) or commit locally and ship async.

Backup#

Three forms in roughly increasing operational maturity.

Form	Notes
Logical dump	`pg_dump`, `mysqldump`, `mongodump`. SQL or BSON text; restores anywhere; slow on large data.
Physical snapshot	copy the data directory at a consistent point. Filesystem snapshot, `pg_basebackup`, Percona Xtrabackup. Restore back to a same-version engine.
Continuous (WAL archive)	base backup plus the WAL stream, enabling point-in-time recovery. pgBackRest, WAL-G, Barman.

The operator runs the recovery once before counting it as a backup. Untested backups are wishes.

Catalog#

The system tables the engine reads to know what tables, columns, indexes, types, functions, and permissions exist. The schema of the schema.

-- Postgres: information_schema is the standard surface;
-- pg_catalog.* is the native one.
SELECT table_schema, table_name
FROM   information_schema.tables
WHERE  table_schema NOT IN ('pg_catalog', 'information_schema');

SELECT relname, n_live_tup, n_dead_tup
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC;

Knowing the catalog is how the operator answers “what’s in this database?” without the application schema. Mongo and Cassandra expose comparable system collections / keyspaces.

Where each engine puts the pieces#

A rough alignment table for the five-ish engines the operator meets most.

Component	Postgres	MySQL	SQLite	Cassandra	DuckDB
Storage	heap + B-tree	InnoDB B+tree	B-tree pages	LSM (SSTables)	columnar
Index	B-tree, GIN, GiST, BRIN	B-tree, fulltext	B-tree	B-tree secondary	zone maps
Concurrency	MVCC	MVCC	locking	per-cell timestamps	MVCC
WAL	WAL files	redo + undo log	WAL mode	commit log	WAL
Replication	streaming + logical	binlog + group replication	none	quorum / gossip	none
Executor	volcano / JIT	volcano	virtual machine	per-node	vectorised

References#

Designing Data-Intensive Applications, Kleppmann. The canonical modern survey.
Database Internals, Petrov. Per-component deep dive across systems.
PostgreSQL Internals.
MySQL InnoDB Architecture.
SQLite Architecture.
CMU Database Group lectures, the open course on database internals.
Relational for engine-specific notes.
SQL for the query language layer.