Recovery Case Study — a bad script corrupted 30k recommendations

0The incident

The nightly job recomputes recommendations. Someone "optimized" it to TRUNCATE the table and repopulate — but the repopulate query errored halfway, and because it wasn't wrapped in a transaction, the table was left empty. Worse: the TRUNCATE replicated to every standby in milliseconds, so there was no live copy to fail over to. This is a logical disaster — the kind only a point-in-time backup can undo.

⚠️ Replicas don't save you from a bad script — backups do

RecoEngine had Multi-AZ and read replicas — great for a server dying, useless here. A TRUNCATE or bad UPDATE is a valid command; replication copies it faithfully and instantly to every standby. The only copy that exists at a different point in time — before the script ran — is a backup. That's why continuous backups (base backup + WAL, giving point-in-time recovery) are the backbone of real safety, and replicas are for availability.

The one bad line — and why it was fatal

-- ❌ what the "optimized" job did — TRUNCATE + repopulate, NOT in a transaction
TRUNCATE recommendations;                 -- table now empty
INSERT INTO recommendations SELECT ...;   -- errored partway → 0 rows inserted
-- result: recommendations is EMPTY in prod, and every replica copied the TRUNCATE
-- there is no live copy to fail over to; this needs point-in-time recovery.

1Assess — scope it and find the clean point

Recovery starts with two questions: exactly what is damaged, and exactly when did it start? For a single corrupted table with continuous backups, the answers point to a clean, targeted restore rather than a dramatic failover.

Question	Answer at RecoEngine	Why it matters
What's damaged?	One table (`recommendations`), ~30k rows, emptied	Scoped blast radius → a targeted restore, not a full DB failover
When did it start?	03:00:00 (the cron start; confirmed in logs)	The PITR target is the second before — 02:59:59
Is anything else affected?	No — orders, users, products untouched	Don't roll back the whole DB and lose legitimate writes elsewhere
Recoverable?	Yes — continuous backups cover 02:59:59	PITR window must span the target; retention is what makes this possible

🎯

The key decisionOnly one table is corrupt and the rest of the database has hours of legitimate writes since 03:00. So RecoEngine does not roll production back to 02:59:59 (that would throw away good data everywhere else). Instead it restores a copy to 02:59:59, lifts just the clean recommendations table out of it, and puts it back. Scope the recovery to the damage.

2The recovery — step by step

AStop the bleedingfirst, always▶

Goal: make sure the job can't run again and re-corrupt whatever you restore.

Disable the batch job — pause the cron/scheduler so it can't fire again mid-recovery.
Serve a safe fallback — have the app show popular/default recommendations (or hide the panel) while you recover, so users see something sensible.
Pin the exact time the script started from logs — this is your PITR target minus one second.

BRestore a COPY to just before the script▶

Goal: get a clean copy of the data at 02:59:59 — without touching production.

Point-in-time restore to a NEW instance at 02:59:59. Production keeps running (it has good data everywhere except the one table).
Wait for it to come up and confirm it's a writer/readable, at the right timestamp.

PITR to a scratch instance (RDS shown; self-managed uses recovery_target_time)

$ aws rds restore-db-instance-to-point-in-time \
    --source-db-instance-identifier reco-prod \
    --target-db-instance-identifier reco-restore \
    --restore-time 2026-07-15T02:59:59Z
# self-managed equivalent: restore base backup + set
#   recovery_target_time = '2026-07-15 02:59:59+00'  then replay WAL to it

CValidate the restored table▶

Goal: prove the restored copy is actually clean before you touch prod.

Row count sane? ~30,000 rows, not 0 and not garbage.
Spot-check content — scores in range, no NULLs where there shouldn't be, a few known users have sensible recs.
Confirm the timestamp — the newest row predates 03:00, i.e. you landed before the script.

Validate on the restored copy

$ psql -h reco-restore... -c "SELECT count(*) FROM recommendations;"     # ~30000?
$ psql -h reco-restore... -c "SELECT min(score), max(score) FROM recommendations;"
$ psql -h reco-restore... -c "SELECT max(created_at) FROM recommendations;"  # < 03:00?

DLift the clean table back into production▶

Goal: put the good recommendations back with minimal disruption and a rollback path.

Copy just the table from the restored copy into prod, loading into a new table first (don't overwrite in place).
Swap atomically — rename the (empty) live table aside and the restored one into place in a single transaction, so reads never see a half-loaded table.
Keep the old one briefly for rollback, then re-enable the (fixed) batch job.

Table-level restore + atomic swap

# pull ONLY the recommendations table from the restored copy into prod (staging name)
$ pg_dump -h reco-restore... -t recommendations --data-only reco \
    | psql -h reco-prod... -c "\\copy recommendations_restored FROM STDIN" reco

-- atomic swap in prod: reads never see an empty/half table
BEGIN;
  ALTER TABLE recommendations RENAME TO recommendations_empty;
  ALTER TABLE recommendations_restored RENAME TO recommendations;
COMMIT;
-- verify, then DROP recommendations_empty later; re-enable the fixed job.

3The recovery, visualized

The corruption and the targeted restore, as an ASCII timeline, an entity diagram of the recommendation model, and a sequence diagram of the whole recovery.

PITR timeline — restore a copy to the second before the script

   continuous backup (base backup + WAL)
   ... 02:00 ───────────────────────► 02:59:59 │ 03:00:00  BAD SCRIPT truncates
                                                 ▲
                    recovery_target_time = 02:59:59   ← restore a COPY here
   prod keeps its good data everywhere else (orders, users) since 03:00 —
   so lift ONLY the recommendations table out of the copy and swap it back.
   one corrupted table → targeted restore, NOT a full-database rollback.

Entity diagram — the recommendation model

erDiagram USER ||--o{ RECOMMENDATION : sees PRODUCT ||--o{ RECOMMENDATION : "recommended as" BATCH_RUN ||--o{ RECOMMENDATION : wrote RECOMMENDATION { bigint id bigint user_id bigint product_id float score bigint batch_run_id datetime created_at } BATCH_RUN { bigint id datetime started_at string status "ok or failed" }

Renders with Mermaid. One BATCH_RUN wrote all 30k recommendations — and one bad run wiped them.

Sequence diagram — corruption to recovery

sequenceDiagram autonumber participant Cron as Nightly batch participant DB as PostgreSQL (prod) participant Mon as Monitoring participant Eng as On-call participant Copy as Restored copy (PITR) Cron->>DB: recompute recs (BUG — TRUNCATE, no transaction) DB->>Mon: recommendations served drop to ~0 Mon->>Eng: ALARM — empty recommendations Eng->>Cron: disable the job (stop re-corruption) Eng->>Copy: PITR restore to 02:59:59 (before the script) Copy-->>Eng: clean recommendations table (30k rows) Eng->>Eng: validate row count + scores + timestamp Eng->>DB: load clean table, then atomic swap DB-->>Eng: recommendations restored — panels light up again Note over Eng,DB: scoped to one table — no full failover, minimal disruption

Renders with Mermaid. The full steps are in Section 2.

4Prevention & scorecard

The recovery was clean because backups were continuous and the corruption was scoped. But the incident shouldn't have been possible — the batch job design was the real bug.

The fix — compute into a new table, validate, swap atomically

-- ✅ never TRUNCATE-in-place; build the new data alongside the old, then swap
CREATE TABLE recommendations_new (LIKE recommendations INCLUDING ALL);
INSERT INTO recommendations_new SELECT ... ;      -- fill the NEW table
-- validate BEFORE swapping: is the count sane? any NULLs? scores in range?
BEGIN;
  ALTER TABLE recommendations     RENAME TO recommendations_old;
  ALTER TABLE recommendations_new RENAME TO recommendations;
COMMIT;                                            -- atomic; old kept for rollback
-- the live table is never empty for even a moment, and a bad run can't wipe it.

☑

Batch jobs build into a new table and swap atomically — never TRUNCATE in place.

☑

Bulk writes run in a transaction (all-or-nothing) and validate before commit.

☑

Continuous backups (base + WAL / automated PITR) cover a window longer than "how long until we notice."

☑

The team knows replicas are not a backup — they copy a bad write instantly.

☑

Recovery is scoped to the damage — restore a copy and lift one table, don't roll back the whole DB.

☑

Monitoring alarms on the symptom (recommendations served drops to ~0), so you detect in minutes.

☑

A timed restore drill has proven the PITR path works — before the real incident.

☑

A snapshot before the batch job gives an even faster undo for this specific risk.

The one-line takeaway: a bad script is the most common "disaster," and replicas make it worse, not better — they copy the corruption instantly. Continuous point-in-time backups are what save you, and when only one table is hit you restore a copy and lift that table back rather than rolling the whole database backward. Best of all: design the job so it can't wipe the table in the first place.