knaxuslearn · build · ship

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

  1. 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

  2. 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 pageinspect output

  3. 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

  4. 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.

RepoWhat to readWeek
postgres/postgressrc/backend/access/nbtree/README and src/backend/optimizer/README2 and 3
sqlite/sqliteA small database to read end to end: the B-tree and pager code2
mysql/mysql-serverThe InnoDB storage layer, for the optional comparison2

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.

  1. 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.
  2. What does a covering index save?
    The query is answered from the index alone, so it skips the table lookups.
  3. What does EXPLAIN ANALYZE show that EXPLAIN does not?
    It runs the query and shows real times and row counts beside the estimates, so a bad estimate is visible.
  4. Why is money stored as an integer or exact decimal?
    Floating point numbers cannot represent most decimal fractions exactly, so sums drift.
  5. What does an idempotency key protect against?
    A client retry after a timeout applying the same request twice.