Roadmaps / Databases / Phase 1
Phase 1 of 4 · Study weeks 1-4
Modelling, indexes and query plans
This phase makes the first checkpoint possible: a schema that enforces the rules and queries you can explain.
Topics every week
- Week 18 h
- The relational model and normal forms
- Keys, constraints and types
- Money and time types
- Idempotency keys
Hand inThe ledger schema with constraints and a test that each constraint rejects bad data
- Week 28 h
- Storage: pages, heap files and the write-ahead log
- How a B-tree index works
- Index types and when each helps
- Visibility and vacuum
Hand inA note that draws one page and one B-tree split, with
pageinspectoutput - Week 38 h
- The planner and
EXPLAIN (ANALYZE, BUFFERS) - Statistics and row estimates
- Join algorithms
- Covering and partial indexes
Hand inThree queries with annotated plans
- The planner and
- Week 48 h
- Designing for the access pattern
- Denormalisation and its cost
- The lost-update design and its fix
- Predicted against measured
Hand inCheckpoint 1: the three-way table, the plans and the seeded design failure
Practice projects
- Practice 1, constraints that bite (Week 1): write a check, a unique and a foreign key constraint. Done when: a test inserts bad data for each and the database rejects it with the expected error.
- Practice 2, the page (Week 2): inspect one table page and one index page with
pageinspect. Done when: you label the header, the item pointers and one tuple and say what changes after an update. - Practice 3, the plan (Week 3): take a slow query and make it fast with one index. Done when: the plan before and after is annotated and the gain is a number.
- Optional, the same schema on MySQL: build the schema in MySQL. Done when: a table lists three differences you met (types, locking, plan output).
GitHub repos to study
Existence and last commit were checked on 22 Nov 2026 by shallow clone. Every repository below had a commit in the last 12 months. Look at the README for archive notices before you rely on one.
| Repo | What to read | Week |
|---|---|---|
| postgres/postgres | src/backend/access/nbtree/README and src/backend/optimizer/README | 2 and 3 |
| sqlite/sqlite | A small database to read end to end: the B-tree and pager code | 2 |
| mysql/mysql-server | The InnoDB storage layer, for the optional comparison | 2 |
Milestone checkpoint
Build Checkpoint 1. Write the schema and the constraint tests first. Load 1 million generated ledger rows and write your timing predictions before you run the heap, B-tree and covering-index queries. Seed the bad design (a balance column updated by two writers), show it losing an update and replace it. Hand in the table with your own numbers, the annotated plans, ADR-1 (the schema and keys) and ADR-2 (the indexes and what each costs on writes).
Check yourself
Answer each question in your head first, then open it to check.
Why does the planner sometimes ignore an index?
If a query reads a large share of the table, a sequential scan is cheaper than many random index lookups, or the statistics are stale and the row estimate is wrong.What does a covering index save?
The query is answered from the index alone, so it skips the table lookups.What does
EXPLAIN ANALYZEshow thatEXPLAINdoes not?It runs the query and shows real times and row counts beside the estimates, so a bad estimate is visible.Why is money stored as an integer or exact decimal?
Floating point numbers cannot represent most decimal fractions exactly, so sums drift.What does an idempotency key protect against?
A client retry after a timeout applying the same request twice.