knaxuslearn · build · ship

Roadmaps / Databases

Foundations · 16 study weeks · 4 phases

Databases Roadmap Beta

You can use a database for years without knowing why a query is slow or why two writes collide. These 16 study weeks take you from using a database to understanding it well enough to model data, keep it correct under concurrent writes, tune it, recover it and choose a different system when the workload calls for one. You build one system, ledgerd, a payments ledger on PostgreSQL, and every phase adds a checkpoint to it. At 8 hours a week the core path is 128 hours and takes 16 weeks. At 12 hours a week it takes about 11 weeks, and at 16 hours a week about 8 weeks. At the end you can show, with numbers, that your ledger never loses or invents money and that you can restore it on a deadline.

This roadmap is in beta and keeps improving over time. If you spot an error, please email your suggestions and corrections to [email protected].

Read first

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

    • 3 repos
    • Milestone checkpoint
    1. Study week 1The relational model and normal forms8 h
    2. Study week 2Storage: pages, heap files and the write-ahead log8 h
    3. Study week 3The planner and EXPLAIN (ANALYZE, BUFFERS)8 h
    4. Study week 4Designing for the access pattern8 h
  2. Phase 2 · Study weeks 5-8

    Transactions and concurrency

    This phase makes the second checkpoint possible: a transfer that stays correct when 100 clients fight over the same rows.

    • 2 repos
    • Milestone checkpoint
    1. Study week 5ACID in practice8 h
    2. Study week 6Row locks and SELECT ... FOR UPDATE8 h
    3. Study week 7Deadlocks and lock ordering8 h
    4. Study week 8Stress testing for invariants8 h
  3. Phase 3 · Study weeks 9-12

    Performance, scale and the right tool

    This phase makes the third checkpoint possible: a ledger that meets its latency target at scale, with a report that does not slow it, and a clear view of when another database is the better choice.

    • 5 repos
    • Milestone checkpoint
    1. Study week 9Load testing with pgbench and custom scripts8 h
    2. Study week 10Partitioning by time8 h
    3. Study week 11Read replicas and lag8 h
    4. Study week 12When not to use PostgreSQL: documents (MongoDB), wide rows (Cassandra), analytics (ClickHouse, DuckDB)8 h
  4. Phase 4 · Study weeks 13-16

    Recovery, replication and change

    This phase makes the last checkpoint possible: a ledger you can restore on a deadline, fail over and change without an outage.

    • 4 repos
    • Milestone checkpoint
    1. Study week 13The write-ahead log8 h
    2. Study week 14Streaming replication8 h
    3. Study week 15Online schema changes8 h
    4. Study week 16Security: roles, least privilege and row-level security8 h

Also in this roadmap