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
- Week 98 h
- Load testing with
pgbenchand custom scripts pg_stat_statements- Connection pooling
- Memory and checkpoint settings
Hand inThe p99 report at 500 transfers a second with spread
- Load testing with
- 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
- 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
- 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.
| Repo | What to read | Week |
|---|---|---|
| redis/redis | The event loop and persistence code | 11 |
| ClickHouse/ClickHouse | The MergeTree storage engine docs and code | 12 |
| duckdb/duckdb | The vectorised execution engine | 12 |
| apache/cassandra | The storage engine: commit log, memtable and SSTables | 12 |
| mongodb/mongo | The storage and query documentation | 12 |
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.
Why report percentiles and spread, not an average?
An average hides the slow tail that users feel, and one run can be luck.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.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.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.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.