skip to content

A bad script deleted 200 rows from one table an hour ago, but the rest of the database has taken an hour of valid writes since. How do you get those rows back, and what does that imply about how you size your recovery window?

level: principalimportance: should knowfreq 38%

answer

  1. PITR granularity = whole instance
  2. side restore, extract rows, merge logically
  3. keys, FK order, sequences, post-incident edits
  4. cadence -> replay time; retention -> window
  5. cheaper: soft delete, history tables, change capture

basics

~20 s

Point-in-time recovery rolls back the whole instance, so do not roll production back. Restore a copy to a scratch instance targeted just before the script, extract the 200 rows logically, and merge them into live data. This costs a full restore, so backup cadence, archive retention and spare restore capacity define what is feasible.

solid answer

~60 s

Rolling production back an hour would discard everyone else's valid hour of work, so the answer is a **side restore**. Provision a scratch instance, restore the newest base backup taken before the deletion, replay archived log to a target just before the script, and open it read-only. Export the 200 rows, then merge them into the live database - reconciling keys and sequence values, respecting foreign-key order, and deciding what to do about rows that were legitimately changed after the deletion. The cost is the whole restore: base-backup transfer plus replay of every log record since it was taken, on hardware and storage you must have available. That is what makes this a design question rather than a procedure question. Backup **cadence** determines replay length and therefore restore time; archive **retention** determines how far back the window reaches at all; spare capacity determines whether you can stand up a copy without disturbing production. For this failure class I would also invest upstream: pre-change restore markers, restricted write access, soft deletes or history tables for high-value entities, and logical change capture that lets you extract deleted rows without a full restore.

code

sql · 8 lines
sql
-- rows exported from the read-only restored copy land in staging
INSERT INTO orders (id, customer_id, status, total, created_at)
SELECT s.id, s.customer_id, s.status, s.total, s.created_at
FROM staging_recovered_orders s
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.id = s.id);

-- afterwards, realign the identifier generator
SELECT MAX(id) FROM orders;

go deeper

for a junior

Know that point-in-time recovery restores the whole database, so recovering a few rows means restoring a copy elsewhere and copying the rows back.

for a middle

Describe the side-restore procedure end to end and name the obvious merge hazards - duplicate keys, foreign-key order, sequence values.

for a senior

Add the operational cost model: restore time driven by backup cadence and replay volume, retention driven by the window, and the need for spare capacity and an isolated, deletion-protected archive.

for a principal

Frame PITR as the general-purpose backstop and argue for narrower instruments - soft deletes, history tables, logical change capture, restore markers, restricted write access - while sizing the window against detection latency and reconciling downstream data copies.

## The constraint that drives everything Point-in-time recovery operates on the **whole database instance**. It replays a physical log; it has no notion of 'replay only the changes to this one table'. So there is no such thing as rolling one table back in place. Every real answer to single-object recovery is a variation on: build a second copy of the database as it was, then move data from that copy into the live one **logically**. ## The side-restore procedure 1. Provision a scratch instance with enough storage for the data plus the fetched log segments. Isolate it from production traffic and from automatic failover management. 2. Restore the newest base backup taken before the deletion. 3. Replay archived log to a target just before the destructive statement - a log position if you can find it, otherwise a marker or a narrowed timestamp - and pause rather than promote. 4. Open read-only and confirm the 200 rows are present with their expected values. 5. Export just those rows. 6. Merge into production. This is the delicate part: - **Keys**: if the primary key is generated by a sequence, re-inserting old identifier values may collide with values issued since, and re-inserting without them breaks references. - **Referencing rows**: insert in dependency order; verify that parents still exist and that the child rows' foreign keys still resolve. - **Concurrent change**: some of the restored rows may have been legitimately updated after the deletion elsewhere in the system, or the application may have already compensated. Blind reinsertion can resurrect data the business deliberately changed. - **Derived state**: caches, search indexes, materialized aggregates, downstream warehouse copies and event streams also lost or recorded the deletion. Reinstating rows in the database does not reinstate them elsewhere. 7. Verify with counts and spot checks, then dispose of the scratch instance and its copy of production data under the same access controls as production. ## What this implies for design **Backup cadence sets restore duration.** Recovery time is roughly the time to place the base backup plus the time to replay the log written since. Daily base backups on a busy system can mean many hours of replay; more frequent base backups, incremental backups, or a delayed standby that is continuously kept a fixed interval behind all shorten that. The choice is a straight cost trade: storage and backup load versus recovery time. **Retention sets the window.** You can only recover to a point covered by a base backup **and** an unbroken chain of archived log from it. Retention policy must therefore be expressed as 'we can recover to any point in the last N days', and log expiry must never cut into the chain that belongs to a backup you still count on. The window should be sized against how long the organisation typically takes to *notice* logical damage, which is often far longer than people assume - a corrupting bug can run for a week before anyone sees it. **Spare capacity is part of the plan.** A recovery strategy that requires hardware, storage and network bandwidth you do not have is a plan on paper. Standing up a copy of the largest database must be possible without taking production resources. **Isolation of the archive.** Archived log and base backups must not share a failure domain with the primary, and must be protected against deletion - including by an attacker or a runaway automation - since the archive is the only thing that makes the window real. ## Cheaper answers for this failure class A full restore for 200 rows is an expensive instrument. Design choices that avoid it: - **Soft deletes or history tables** for high-value entities, so an accidental delete is a flag change or a row in a history table rather than a lost row. - **Logical change capture**: a stream of row-level change events retained for days lets you reconstruct deleted rows by reading the events, with no restore at all. - **Periodic logical exports** of small, critical reference tables, which are trivial to restore selectively. - **Pre-change restore markers** and reviewed migrations, so the recovery target is exact when a restore really is needed. - **Access control**: interactive write access on production restricted, destructive statements delivered only through reviewed migrations, guards that reject unqualified DELETE and UPDATE. ## The judgement to articulate PITR is the general-purpose instrument for logical damage: it can recover anything, at the cost of a full restore and instance-level granularity. Cheaper, narrower instruments cover the common cases faster. A mature design keeps PITR as the backstop, sizes its window against detection latency rather than against convenience, and adds the narrow instruments where the data is valuable enough to justify them.

  • Why not simply run point-in-time recovery on production and roll the whole database back an hour?
    Because recovery is instance-wide: every transaction committed in that hour by every other part of the system would be discarded. For a 200-row loss that trade is almost never acceptable. The proportionate answer is a side restore that lets you take back only the affected rows while production keeps its valid work.
  • How would you decide how far back the recovery window should reach?
    Size it against how long logical damage typically goes unnoticed, not against convenience. Silent corruption from a buggy release or a slow-running script can be discovered days later, so a window that only covers 24 hours leaves that class unrecoverable. Then price it: the window costs archive storage and retained base backups, and its far end is only real if the log chain is genuinely contiguous and periodically proven.
  • What makes merging recovered rows back into a live table risky?
    Identity and semantics. Re-inserting old key values can collide with identifiers issued since, sequence generators may need realigning, foreign-key order must be respected, and some rows may have been legitimately changed or intentionally removed after the incident. Derived systems - caches, search indexes, warehouses and event consumers - also processed the deletion and will not be corrected by the database insert alone.

saying these in an interview costs you the question

  • Proposing to roll production back an hour to recover 200 rows
  • Assuming point-in-time recovery can target a single table
  • Blind reinsertion of exported rows without reconciling keys, sequences and later changes
  • Sizing the recovery window by convenience rather than by how long damage goes undetected
  • Forgetting that caches, search indexes and downstream consumers also need correcting
  • Planning a restore that requires spare hardware and storage the team does not actually have

context

resolving links…