Someone ran DELETE without a WHERE clause on an important production table 20 minutes ago. What do you do?

What the interviewer wants to hear

• Stay calm, stop further damage, and communicate
• Replication already copied the delete, so replicas do not help (unless delayed replica)
• Restore a copy to a separate server with PITR to just before the delete
• Extract missing rows and merge them back carefully
• Avoid overwriting newer good data; verify
• Postmortem: permissions, safeguards, delayed replica

Model answer

First I tell the incident channel and stop the damage, for example by pausing jobs that depend on that table. Normal replicas already applied the DELETE, so they do not help, unless we have a delayed replica, which would be the fastest source. Otherwise I restore the latest base backup to a separate server and use point-in-time recovery to replay WAL or binlog up to just before the DELETE. I find the exact time from logs or, in MySQL, by reading the binlog with mysqlbinlog. I do not restore over production, because that would lose 20 minutes of other good writes. Instead I export the deleted rows from the recovered copy and insert them back, checking foreign keys and conflicts. After that, a blameless postmortem: remove direct write access, require reviewed changes, and consider a delayed replica.

More hard Database Administrator questions

• Design a high-availability setup for a critical PostgreSQL database with RPO near zero and RTO under one minute.
• Your single database is reaching its limits. How do you decide whether to shard, and how would you do it?
• How would you upgrade a large production PostgreSQL database to a new major version with minimal downtime?
• The application is timing out and you suspect lock contention in production. Walk through how you diagnose and fix it.
• Design a disaster recovery plan for a critical database that must survive the loss of a whole cloud region.
• How would you migrate a 5 TB on-premises PostgreSQL database to a managed cloud service with less than 15 minutes of downtime?
• A critical 1 TB PostgreSQL table is 60 percent bloat and queries are slowing. How do you fix it without downtime and stop it coming back?
• The business expects traffic to grow ten times over the next 18 months. How do you plan database capacity?
• Your company must meet compliance rules (such as PCI DSS or GDPR) for a database holding personal and card data. What controls do you put in place?