A maintenance script deletes rows it should not have touched, and nobody notices for six hours. Walk me through the recovery.
6 min readUpdated 2026-08-15Target archetype: Big Tech, Enterprise Captive, Product Startup
0
Answer
Short answer
Replication propagates the deletion, so recovery depends on point-in-time restore from a base backup plus the write-ahead log, and the hard part is not the restore but extracting one tenant's rows from a shared store while the rest of the system keeps serving traffic.
Was this answer helpful?
Likely follow-ups
→How do you find the exact moment to stop replaying the log when you only know the deletion happened some time overnight?
→Your backup restore has never been run against a full-size dataset. What is the first number you would want, and how would you get it?
→How would you make this class of mistake impossible for a maintenance script rather than merely recoverable?
→The deleted rows are referenced by rows in three other tables that were then updated. What does restoring only the deleted table give you?
The first move is to say what the failure actually is, because it decides which mechanism applies. This is not a hardware fault, and every redundancy mechanism aimed at hardware faults is useless against it. Three replicas received the deletion promptly and correctly. A synchronous standby has it. A multi-region cluster has it in every region. Replication protects you from losing a machine; it does exactly nothing when the authoritative write is the problem, because its job is to reproduce authoritative writes faithfully.
That distinction — durability, meaning the data survives a machine dying, against recoverability, meaning you can get a previous state back — is the whole subject. A store can be perfectly durable and functionally unrecoverable, and interviewers ask this question because that combination is extremely common and rarely discovered until it matters.
The public example worth knowing is Atlassian's incident in April 2022, where a maintenance script ran with the wrong deletion mode and removed around 400 customer sites, and restoring them took up to two weeks for some of those customers. Nothing had failed in the storage layer: the data was replicated and backed up. What was missing was any path to restore one tenant quickly out of a store shared by many. Expect the interviewer to push towards how long a restore takes and who is able to trigger one, rather than where the second copy lives.
What the six hours costs you
Stop writes to the affected scope before doing anything else, and be explicit about scope, because stopping the whole platform to protect one table is a decision, not a default. Every minute of continued traffic writes rows whose correctness depends on data that is no longer there, and every one of those rows is work you will have to reconcile by hand later.
Then compute two numbers out loud. The recovery point is how much data you are prepared to lose, and it follows from your backup arrangement rather than from a policy document. Nightly snapshots alone give a recovery point of up to twenty-four hours: restoring last night's snapshot returns the deleted rows and discards every legitimate write since. That is almost never acceptable, which is why continuous archiving of the write-ahead log exists — with the log shipped continuously, the recovery point drops to the archival interval, typically seconds to a minute.
The recovery time is how long it takes to be serving again, and it is dominated by things people forget to count: transferring the base backup from object storage, replaying however many hours of log accumulated since it was taken, rebuilding indexes if the restore path does so, warming caches, and reconnecting applications. Log replay is the term that surprises people, because a snapshot taken twenty hours ago means twenty hours of writes to reapply, and replay is single-threaded in many engines and slower than the original writes were. This is the argument for taking base backups more often than your retention policy strictly requires: it does not change your recovery point at all, and it cuts your recovery time substantially.
Point-in-time restore, and choosing the instant
The mechanism is to restore the most recent base backup taken before the incident, then replay archived write-ahead log records forward and stop at a chosen target. Engines expose that target as a timestamp, or better, as a specific transaction identifier or a named restore point.
Loading diagram…
The step worth pointing at is the last one, because everything before it is mechanical and everything after it is judgement.
A timestamp is a poor target when you do not know precisely when the deletion ran, and overshooting by a few seconds means replaying from the base backup again, which is expensive at this size. So the practical technique is to restore to a spare cluster rather than over the live one, replay to a conservative target, inspect whether the rows are present, and adjust. Doing this on a separate cluster is also what keeps the option of aborting: the live system, however damaged, is still serving reads that are correct for everything the script did not touch, and overwriting it with a restore that turns out to be wrong converts a partial data-loss incident into a full outage.
Restoring a subset is a different problem entirely
Here is the part that separates an answer from a rehearsed procedure. What you now have is a full copy of the database as it stood before the deletion. What you need is a few million rows out of it, put back into a live database that has had six hours of unrelated legitimate writes since.
You cannot simply promote the restored copy, because that discards those six hours. So you extract, and extraction runs into the constraints the schema imposes. Restoring a row whose primary key has since been reused by a new insert conflicts. Restoring a child row whose parent was legitimately deleted afterwards violates a foreign key. Restoring rows into a table that other tables have since been updated against gives you a consistent table inside an inconsistent database, which can be worse than the missing rows, because the application now reads a state that no sequence of legitimate operations could have produced. Work out which tables are implicated as a unit and restore that unit together, and expect to write a reconciliation script rather than a single insert.
The multi-tenant version of this is harder again, and it is the shape most SaaS platforms have. When one tenant's rows are interleaved with four thousand others in shared tables, restoring that tenant means a full-cluster restore to a side cluster followed by a filtered export and a careful merge — hours of work per tenant, most of it not parallelisable, which is precisely how a restore turns into two weeks. The design conclusion is that a per-tenant restore is a capability you build deliberately, before you need it: a tenant identifier on every row so a filtered export is a query rather than an archaeology project, or an export pipeline that produces per-tenant extracts alongside the cluster backup.
Protecting the backups from the credentials that caused this
Close on the thing that turns a recoverable incident into an unrecoverable one. If the identity that ran the destructive script can also delete or overwrite backups, then a script with a wider blast radius, or an intruder with those credentials, removes your recovery path along with your data. The controls are unglamorous and each is worth naming: backups in a separate account or project with a separate trust boundary, write-once retention so an object cannot be deleted before its retention expires even by an administrator, and alerting on deletion of backup objects as a security event rather than an operational one.
And the corollary is the question you should expect: when was the restore last exercised end to end, against a dataset the size of production, with the time recorded? An untested restore procedure is a plan, not a capability, and the number that matters is one you measured rather than one you estimated.
Replication defends against hardware and copies your mistakes faithfully, so what you are being graded on is whether you can name a recovery point and a recovery time derived from an actual backup schedule — and whether you have a way to lift one tenant's rows out of a shared store without restoring the whole thing first.