A privacy regulation requires you to erase an individual's personal data on request, but your system soft-deletes everything. How do you actually satisfy such an erasure request, and what does a purge process have to cover?

## Soft delete is not erasure Setting `deleted_at` hides a row from your application. The name, email address and payment details are still in the table, still in every index, still in every replica and backup, and still visible to anyone with database access or a leaked dump. Under privacy regimes that grant a right to erasure, that is not deletion. Treating the flag as compliance is the single most common mistake in this area. So the schema has to support two distinct operations: 1. **Account closure / user-initiated delete** — a reversible application state. Soft delete with a grace period is the right tool, because users change their minds and because you often need the record for the winding-down of the relationship. 2. **Erasure** — the data must genuinely stop existing. This is irreversible by definition. A useful pattern is to make closure the trigger and erasure a scheduled follow-up: `deleted_at` starts a retention clock, and a purge job erases rows whose clock has expired unless a legal hold applies. ## The three erasure techniques **Hard delete.** The cleanest: the rows go away. Works when the record has no independent legal reason to exist (profile, preferences, device tokens, session history). Requires deleting in foreign-key-safe order, or leaning on declared `ON DELETE CASCADE`. **Anonymization / pseudonymization.** Often you cannot delete the row: an invoice must survive for tax law, a moderation decision must survive for safety, an aggregate must stay consistent. The answer is to strip the row of the fields that identify a person — overwrite `email`, `full_name`, `phone`, `ip_address` with fixed tombstones or a one-way surrogate — while retaining the non-identifying skeleton. Two cautions: it must be *irreversible* (a hash of an email is reversible by brute force over a known address space, so a hash alone is not anonymization), and you must consider re-identification via the remaining columns (a rare postcode plus a birth date can identify someone even without a name). **Crypto-shredding.** Encrypt each subject's personal fields with a per-subject key held in a key store; erasure destroys the key, making every copy of the ciphertext — including ones inside append-only event logs, immutable archives and old backups — permanently unreadable. This is the practical answer for architectures where physically rewriting history is impossible. It costs key-management complexity and makes those fields unsearchable. ## What a purge job must do **Batch it.** `DELETE FROM events WHERE user_id = 42` against ten million rows takes long locks, generates a huge amount of write-ahead log and replication lag, and can blow up the undo/rollback segment. Delete in bounded chunks (a few thousand rows) with a commit between batches, ideally driven by an indexed key range. **Order it.** Children before parents, unless real cascade actions are declared. In a soft-delete schema the cascade was never exercised, so the constraint graph may be incomplete — verify it rather than assuming. **Make it idempotent and resumable.** It will be interrupted. Re-running must be safe and must pick up where it stopped, so drive it from a queue of erasure requests with a per-request status rather than from a single long statement. **Audit the erasure, not the erased data.** Regulators expect proof that you complied. Record subject id, request time, completion time, and which stores were covered — never a copy of the erased values. **Follow the data everywhere.** The relational table is the beginning: physical and logical replicas, read replicas used by reporting, materialized views, caches, search indexes, the analytics warehouse and its downstream marts, object storage (uploaded documents, avatars), message queues and event streams, application logs and metrics with user identifiers, third-party processors you sent data to, and CSV exports someone emailed. An erasure design that stops at the primary database is not an erasure design. This is why teams inventory personal-data locations up front; you cannot erase from a store you did not know existed. **Backups are the standard exception.** Surgically editing backups is impractical and dangerous. The accepted approach is a documented, bounded backup retention window plus a rule that any restore re-applies outstanding erasure requests. Say this explicitly in an interview — it shows you have done it rather than theorized it. ## Storage reality after a purge In multi-version engines a `DELETE` marks row versions dead rather than freeing space; background cleanup (vacuum, purge threads) reclaims it, and index space often does not shrink until a rebuild. Deleting hundreds of millions of rows can leave the table as large as before and slower than before. Where the data is naturally time-ordered, **dropping a partition** is drastically cheaper than deleting rows — a metadata operation that frees the space instantly, with no bloat. Designing retention around partitioning is the difference between a purge that runs in seconds and one that runs for days. ## The conflicts you should name Erasure fights with statutory retention (financial records), with legal holds (litigation), with fraud prevention (you may need a hashed identifier to keep a banned actor out), and with derived analytics that were already aggregated. The engineering job is to surface these conflicts and implement whatever the policy decides — typically: erase identifying columns, retain the minimum legally-required skeleton, and keep an irreversible one-way token where continuity is genuinely required.