knaxuslearn · build · ship

Roadmaps / Databases / Phase 3

Phase 3 of 4 · 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.

Topics every week

  1. Week 98 h
    • Load testing with pgbench and custom scripts
    • pg_stat_statements
    • Connection pooling
    • Memory and checkpoint settings

    Hand inThe p99 report at 500 transfers a second with spread

  2. Week 108 h
    • Partitioning by time
    • Archiving old data
    • Bloat and vacuum tuning
    • Batching writes

    Hand inThe ledger partitioned by month and the effect on the report

  3. Week 118 h
    • Read replicas and lag
    • Caching with Redis and its failure modes
    • Cache invalidation
    • Read-your-writes

    Hand inA cached balance lookup with a stale-read test

  4. Week 128 h
    • When not to use PostgreSQL: documents (MongoDB), wide rows (Cassandra), analytics (ClickHouse, DuckDB)
    • Cost of a second system

    Hand inCheckpoint 3: the p99 report, the report timing and the three-tool table

Practice projects

  • Practice 1, the slow query (Week 9): find the top query by total time with pg_stat_statements. Done when: you fix it, measure it and the total time share drops.
  • Practice 2, the partition (Week 10): partition the ledger by month. Done when: a table compares the daily report before and after and shows partition pruning in the plan.
  • Practice 3, the stale cache (Week 11): cache a balance in Redis. Done when: a test shows a stale read after a transfer, and your invalidation rule fixes it.
  • Optional, the analytics store: load the ledger into ClickHouse or DuckDB. Done when: a table compares the daily report time and storage size with PostgreSQL.

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.

RepoWhat to readWeek
redis/redisThe event loop and persistence code11
ClickHouse/ClickHouseThe MergeTree storage engine docs and code12
duckdb/duckdbThe vectorised execution engine12
apache/cassandraThe storage engine: commit log, memtable and SSTables12
mongodb/mongoThe storage and query documentation12

Milestone checkpoint

Build Checkpoint 3. Write your latency and report-time predictions first. Load the ledger, run the load test three times, find the top query and fix it, partition the table and run the report. Do one small exercise on each of the three side workloads. Hand in the latency report with spread, the report timings, the three-tool table, ADR-5 (partitioning) and ADR-6 (the cache and its invalidation).

Check yourself

Answer each question in your head first, then open it to check.

  1. Why report percentiles and spread, not an average?
    An average hides the slow tail that users feel, and one run can be luck.
  2. When does partitioning help a query, and when does it not?
    It helps when queries filter on the partition key, so whole partitions are skipped, and when old data is dropped as a unit. It does not help queries that touch all partitions.
  3. What can go wrong with a cache in front of a database?
    Stale reads, a thundering herd when a hot key expires and inconsistency if the cache and the database update in different orders.
  4. What does replica lag break?
    Reads from a replica may not see a write you just made, so read-your-writes needs routing to the primary or a wait for the replica to catch up.
  5. When is PostgreSQL the wrong choice?
    When the data is huge and scanned in bulk (a column store fits), when writes are huge and key-based across many nodes (a wide-column store fits) or when you need a cache with tiny latency. Put numbers on the choice.