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.
| Phase | Checkpoint | Done when |
|---|---|---|
| 1 | Modelled | The 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. |
| 2 | Correct under concurrency | A 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. |
| 3 | Fast and scalable | The 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. |
| 4 | Recoverable and changeable | Continuous 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. |
| W | Work 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.
| Level | What you build and hand in |
|---|---|
| Fresher | Core scope: one primary, 1 million rows, a backup and a tested restore, the four checkpoints, ADRs 1 to 4. |
| Senior | Adds 10 million rows, a replica with a promotion drill, point-in-time recovery, a benchmark in CI and ADRs 5 to 7. |
| Staff | Adds written decisions with numbers: a data platform policy (which store for which workload), a migration playbook and a cost model. |
| Principal | Adds 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 minutes | Read then write, default isolation | SELECT ... FOR UPDATE, ordered locks | Single atomic UPDATE ... WHERE balance >= amount |
|---|---|---|---|
| Transfers applied | 61,200 | 54,800 | 58,300 |
| Rule violations (negative balance) | 1,874 | 0 | 0 |
| Sum of balances changed by | +4,310 | 0 | 0 |
| p99 latency | 22 ms | 41 ms | 27 ms |
| Deadlocks detected | 0 | 0 (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.