CoursesSystem design from first principles to design review
Puzzle 13 · 14 of 19
Can you recover the right history after a bad release?
Test restoration with real reads and writes.
All puzzles in this course
The problem
A faulty process deletes reservations and the change reaches every replica. What separates successful replication from recovery of the right data?
See it
Scroll sideways to see the full diagram.
The base copy contains receipt 10. Follow the upper line as recorded changes arrive: insert 20, delete 20 and then insert 30.
Step 1 of 4Choose the recovery point immediately before the bad deletion. Predict the two IDs visible at that point.
Step 2 of 4Restore the base and replay to that point in a separate database. IDs 10 and 20 survive. The same process stopped after the deletion would recover only 10.
Step 3 of 4Receipt 30 was valid but later than the chosen point. Verify it independently before replaying it. A new committed write of 40, read through another connection, tests the recovered database.
Step 4 of 4
The building block
Define the acceptable amount of data loss in time, the recovery point objective, and the acceptable restoration time, the recovery time objective. A deletion at 10:07 and a usable base copy from 10:00 leave seven minutes of intervening work to account for. A base backup and archived change log can allow recovery to a point immediately before the mistake.
Restore synthetic reservations into a separate database. Count confirmations, compare identities and make a new reservation. If valid requests arrived after the deletion, rewinding the entire database can also remove them. Reconcile those requests and external payments. Healthy replicas may have copied the original mistake perfectly.
Score a recovery by the clock
The deletion occurs at 10:07. A monitor detects it at 10:10, the operator finishes restoring at 10:18, and validation permits traffic again at 10:24. If interruption began at 10:07, recovery took 17 minutes and missed a 15-minute objective. Measuring only the eight minutes spent restoring would hide the miss.
Suppose the newest consistent state you can actually recover is 10:04. The gap at deletion time is three minutes. That fails a two-minute recovery point objective even if the database restarts instantly. Choose one known reservation from 10:03 and one from 10:06, look them up after restoration, then reconcile any later external charge independently.
Challenge a restore that has the right row count
An independent receipt journal lists the two confirmations that should exist at the recovery point. The restored table also has two rows. Is that enough to reopen reservations? Compare identities and amounts in both directions.
CREATE TABLE journal (id INTEGER PRIMARY KEY, cents INTEGER);
CREATE TABLE restored (id INTEGER PRIMARY KEY, cents INTEGER);
INSERT INTO journal VALUES (10, 500), (20, 900);
INSERT INTO restored VALUES (10, 500), (30, 900);
SELECT id, cents FROM journal
EXCEPT SELECT id, cents FROM restored;
SELECT id, cents FROM restored
EXCEPT SELECT id, cents FROM journal;
The first query returns the missing receipt 20. The second returns unexpected receipt 30. Change 30 to 20 and 900 to 800 in the restored table. Counts and identities now agree, but the two comparisons still reveal a disagreement over the amount. Fix the amount too and both differences disappear.
The comparison assumes the journal is complete for this exact recovery point. A legitimate later receipt belongs to reconciliation against the later timeline. Treating every extra row as disposable can erase a real customer operation.
Try it
A receipt database starts with ID 10. The service commits ID 20, marks a recovery point, accidentally deletes 20, marks a second point, then commits ID 30. Before running the experiment, write down the exact IDs you expect at each point. Which valid receipt could you lose even if you restore perfectly?
On Linux or macOS, use Python 3 with psycopg 3 and the PostgreSQL 17 server tools on your command path. Save the experiment and its helper in one folder. From that folder run both commands as an ordinary user.
python3 restore.py before_delete
python3 restore.py after_delete
Each command creates disposable local clusters, takes a clean base copy, archives subsequent writes and restores to the named point in a separate directory.
Before deletion the restored IDs are 10 and 20. After deletion only 10 survives. The original database contains 10 and 30 at shutdown. Receipt 30 is a valid later commit after both named points. Before reopening service, decide how to reconcile it with an independent receipt record. The lab also writes ID 40 on the recovered timeline and checks it from a fresh connection. Compare its measured time with the full incident timeline below.
Record detection, decision, restore, validation and reopening times. A fifteen-minute interruption budget covers that entire path. For a schema change, first add a compatible field, switch writers and readers, and remove the old field after the compatibility window. Run the old application against the upgraded schema before relying on rollback.
Check yourself
Sources
- PostgreSQL · Continuous Archiving — “restore the database to a previous point in time”
What you now know
A recovered process earns traffic after the restored history and a new write pass verification.
Answer every question above correctly to unlock the next puzzle.
You can test a restore. How do you challenge a design when a reviewer changes the workload itself?
Something wrong or unclear on this page? Tell me: a puzzle that didn't click is a bug in the puzzle.