knaxuslearn · build · ship

Roadmaps / Databases / The milestone project

Read first

The milestone project

Scenario. A payments company keeps its ledger in PostgreSQL. There are 2 million accounts. A transfer moves money from one account to another and writes two ledger rows. Load is 500 transfers a second on average with a peak of 1,500. A few merchant accounts are hot: 10 accounts receive 30 percent of all transfers. Clients retry on timeouts, so the same transfer may arrive twice. Targets: p99 transfer time at or under 50 ms at 500 transfers a second; with 100 concurrent clients hitting the 10 hot accounts, zero violations of the rules (no balance below zero, the sum of all balances unchanged, no transfer applied twice); a daily report over 100 million ledger rows in 10 s or less without slowing transfers; a restore to any minute in the last 24 hours within 15 minutes; a column added to a table of 100 million rows with no lock held for more than 1 second.

PhaseCheckpointDone when
1ModelledThe schema has primary keys, foreign keys, check constraints and a unique idempotency key. Three real queries have EXPLAIN (ANALYZE, BUFFERS) output with the index choice explained. A table compares a heap scan, a B-tree index and a covering index with your predictions beside the measured times. A seeded bad design (a balance stored only as a column that two writers update) is shown to lose an update, then replaced by a design that cannot.
2Correct under concurrencyA transfer runs in one transaction with a chosen isolation level and a row-locking rule. A stress test of 100 clients over 10 hot accounts for 5 minutes ends with every rule intact. A retry with the same idempotency key does not apply twice. A seeded deadlock (two transfers locking accounts in opposite order) is found in the log and fixed with a lock-order rule. A seeded write skew is reproduced, then prevented.
3Fast and scalableThe ledger holds 10 million rows (1 million in the core scope) and meets the p99 target with spread over 3 runs. The slow query found with pg_stat_statements is fixed with a measured gain. The daily report runs on a partitioned table or a replica in 10 s or less. A table compares the right tool for three side workloads (a cache, a document store and an analytics store) with a measured exercise each.
4Recoverable and changeableContinuous archiving is on. Restoring to a chosen minute works, with the time measured against the 15-minute goal. A replica is promoted after the primary is killed, with data loss measured. A column is added with the safe sequence (add nullable, backfill in batches, add the constraint without a long lock), and the longest lock held is reported. A seeded bad migration is caught on a copy first.
WWork version (Staff and Principal)On a real database: pick one risk (a missing restore test, a slow query class or an unsafe migration process), add a gate or a drill, and record the result. Pass: a target agreed in writing beforehand is met, or the number of findings falls at least 5 times over the same database. The ADR is signed by a named stakeholder, scored by someone other than you, and a recheck is booked three months later. Timebox: six weeks alongside Phases 3 and 4.

Evidence to hand in. A repository whose README opens with the problem and the numbers above; one ADR (architecture decision record) per decision, each naming a rejected option; raw EXPLAIN output, stress-test output and a restore log; a one-page design doc; a diagram of the tables and the transfer path. Skeletons: Checkpoint 1 is the three-way table below with your numbers. Checkpoint 2 is the stress report (clients, transfers, rule violations, retries, deadlocks). Checkpoint 4 is the restore log with the timeline and the longest-lock report.

LevelWhat you build and hand in
FresherCore scope: one primary, 1 million rows, a backup and a tested restore, the four checkpoints, ADRs 1 to 4.
SeniorAdds 10 million rows, a replica with a promotion drill, point-in-time recovery, a benchmark in CI and ADRs 5 to 7.
StaffAdds written decisions with numbers: a data platform policy (which store for which workload), a migration playbook and a cost model.
PrincipalAdds a decision on database choice for several teams, with a measured outcome from the work version and a checklist other teams reuse.

Do this at work. A solo lab is not promotion evidence. Take a real database and run Checkpoint W on it. Start with a baseline over a month: the slowest queries, the time since the last tested restore and the number of risky migrations. Make one change. Measure again. A promotion committee accepts the real decision and who made it, the named stakeholder who relied on it, before and after numbers, and a result checked again three months later.

Solo version. With no real database, use a named public dataset or a generated one, restore a published sample database and tune its three slowest queries. Ask a peer or mentor to check your measurements. Generate transfers with a seeded random generator.

Laptop scale. 10 million rows run on a laptop. 100 million rows do not fit comfortably, so measure the report at 10 million and extrapolate with the plan's row estimates, or run 100 million on a cloud machine for one afternoon (state the cost). Nothing in the core scope needs cloud spend.

Honest sizing. A basic ledger takes an experienced engineer a day or two. The checkpoints add concurrency stress, plan analysis, partitioning, point-in-time recovery and migration drills, which is where the hours go. Phase 2 is the heaviest for a fresher (transactions, locks and isolation together), so use the extra fresher week there.

Worked reference: what a "3 Proven" hand-in for Checkpoint 2 looks like

The numbers are illustrative. The shape (a plain read-modify-write loses updates, row locks fix it and cost throughput on hot rows) is what you should expect. Hand in your own measurements.

100 clients, 10 hot accounts, 5 minutesRead then write, default isolationSELECT ... FOR UPDATE, ordered locksSingle atomic UPDATE ... WHERE balance >= amount
Transfers applied61,20054,80058,300
Rule violations (negative balance)1,87400
Sum of balances changed by+4,31000
p99 latency22 ms41 ms27 ms
Deadlocks detected00 (order enforced)0

ADR-3 excerpt: Apply a transfer with conditional single-statement updates inside one transaction, locking the two accounts in id order. The read-then-write version lost 1,874 updates and created money, because two clients read the same balance and both wrote. Locking with FOR UPDATE was correct but held row locks across a network round trip, which raised p99 to 41 ms on hot accounts. The conditional UPDATE checks and changes the balance in one step, so no client holds a lock while it thinks. Rejected: the default isolation with application retries, because it cannot prevent the lost update. Rejected: serialisable isolation for every transfer, until a measurement shows the cost is acceptable for the hot accounts. Revisit if a transfer starts to touch more than two accounts.