Accidental DELETE Without a WHERE Clause in Production

An engineer running a cleanup script against the wrong environment deleted 2.3 million user preference records in 4 seconds.

Overview

A cleanup script meant for staging ran against production because of a typo in an environment variable, and hard-deleted 2.3 million rows in 4.1 seconds.

What happened

The task was routine: delete soft-deleted user records older than 90 days from the staging database. The engineer had run the same script dozens of times. This time the DSN variable had a typo, so it fell back to the default, which was the production connection string set in the shell profile.

Why it broke

Each safeguard the engineer relied on was missing or bypassed.

> [!WARNING] No WHERE clause scoped the delete by environment because the engineer trusted the DSN to keep environments apart.

A startup health check caught it: it queried user preferences, found zero rows and reported unhealthy.

Impact

Rows deleted — 2.3 million
Time to delete — 4.1 seconds
RTO target — 4 hours
Actual recovery time — 6 hours 12 minutes

The fix

Recovery combined a 2-hour-old RDS automated snapshot with 4 hours of binary log replay, getting as close to the deletion as possible.

DSN falls back to a production default — Separate credentials for production DML
Destructive script runs directly — Dry run or row-count check first
Hard delete on user_preferences — Soft delete on critical tables
Snapshots plus manual log replay — Point-in-time recovery enabled

Key concepts

RTO (Recovery Time Objective): The maximum acceptable time to restore service after a failure. In this incident, 4 hours was the target; 6+ hours was actual.
RPO (Recovery Point Objective): The maximum acceptable amount of data loss measured in time. An RPO of 2 hours means the recovery point can be up to 2 hours before the incident.
Binary log replay: Replaying MySQL/PostgreSQL WAL or binary logs from a snapshot forward to a specific timestamp, recovering changes made after the snapshot.
Point-in-time recovery (PITR): RDS/Aurora feature that allows restoring a database to any second within the backup retention window using automated backups plus transaction logs.
Soft delete: Marking rows as deleted with a flag and timestamp rather than issuing a DELETE statement, preserving the data and enabling trivial recovery.

When to use it

Discussing database safety controls in production environments
Designing recovery plans for data loss scenarios
Explaining RTO vs RPO tradeoffs in database backup strategy

Interview questions

An engineer accidentally deleted 2 million rows from production. Walk me through the recovery.
What is the difference between RPO and RTO, and how do backup strategies affect them?