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

Restore to before deletion and reconcile later receipts Original history 10 20 delete 20 30 Receipt IDs Before delete: 10, 20 Restored: 10, 20 Missing later receipt: 30 Fresh write to verify: 40

Scroll sideways to see the full diagram.

  1. 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 4
  2. Choose the recovery point immediately before the bad deletion. Predict the two IDs visible at that point.

    A backup contains 10; then 20 is inserted, deleted, and 30 inserted. Which IDs exist immediately before the deletion?

    Step 2 of 4
  3. Restore 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 4
  4. Receipt 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

What can damage all current replicas logically?

Not quite. Replication propagates unwanted changes too. Re-read that part ↑

Right. Replication propagates unwanted changes too.

What does restoring only the 10:00 copy omit?

Right. Drivers may have completed reservations in that period.

Not quite. Drivers may have completed reservations in that period. Re-read that part ↑

What finishes a recovery drill?

Right. A running process does not establish a correct history.

Not quite. A running process does not establish a correct history. Re-read that part ↑

Sources

What you now know

A recovered process earns traffic after the restored history and a new write pass verification.

You can test a restore. How do you challenge a design when a reviewer changes the workload itself?