Backend & APIs · Pro
Relational Database With a Query Planner
A SQL database engine with a B+tree storage layer, a write-ahead log, MVCC transactions, and a cost-based optimizer that plans real queries
You build a relational database engine from scratch: a disk-backed B+tree for storage, a write-ahead log for crash recovery, snapshot-isolation transactions via MVCC, and a cost-based query planner that chooses between sequential scans and index seeks. This project is worth doing because it makes every production database decision concrete and intuitive, and because it exercises more systems-programming surface area per hour than almost any other project you can pick.
What you build
- Parses a subset of SQL (SELECT, INSERT, UPDATE, DELETE, CREATE TABLE) into an AST using a hand-written recursive-descent parser
- Stores pages in a buffer pool backed by a B+tree with split and merge operations keeping keys sorted on disk
- Recovers from crashes by replaying a write-ahead log (WAL) with redo and undo phases
- Supports concurrent readers and writers through MVCC: each transaction sees a consistent snapshot without blocking reads
- Plans queries with a cost-based optimizer that estimates row counts from statistics and chooses the lowest-cost physical operator tree
- Executes queries through a volcano-model iterator pipeline (Scan, Filter, NestedLoopJoin, Sort, Aggregate)
- Exposes a simple TCP wire protocol so any SQL client or psql-compatible driver can connect
What it teaches
- How B+trees keep data sorted on disk and why they outperform flat files for range queries
- Buffer pool management: dirty-page tracking, eviction policies, and why flushing order matters for correctness
- Write-ahead logging: the rules that guarantee atomicity and durability and how redo/undo recovery works
- MVCC internals: version chains, snapshot visibility rules, and the tradeoff between read concurrency and space amplification
- Cost-based query optimization: cardinality estimation, operator cost models, and plan enumeration
- The volcano iterator model and how physical operators compose into an execution pipeline
How it works
- 1
SQL Parser
- Raw SQL string
- Tokenizer + recursive-descent parser
- Typed AST output
- 2
Query Planner
- Logical plan from AST
- Statistics lookup
- Cost-based plan selection
- 3
Executor (Volcano)
- Physical operator tree
- Iterator next() calls
- Row-by-row pipeline
- 4
Buffer Pool + B+tree
- Page cache (LRU)
- B+tree read / write
- Disk page I/O
- 5
WAL + MVCC
- Log record before write
- Version visibility check
- Crash recovery on restart
Sign in to open the build guide
Free account. Get the step-by-step build and every resource link.
Take it further
- Add a hash join operator alongside nested-loop join and let the optimizer choose between them based on estimated memory versus CPU cost.
- Implement a secondary index (a second B+tree keyed on a non-primary column) and teach the optimizer to use it when selectivity is high.
- Write a WAL-based replication log reader that streams committed transactions to a read replica over a TCP connection.
