markview/Soft Delete vs Hard Delete: Trade-offs Engineers Should Know

Soft Delete vs Hard Delete: Trade-offs Engineers Should Know

"What happens when a user deletes their account?" sounds simple in a design interview, but the answer touches correctness, privacy law and performance.

Hard delete

The row is removed with DELETE. Storage is reclaimed, queries stay simple and the data is really gone, which matters for privacy rights such as erasure requests. Downsides: no undo, foreign keys must cascade or be cleaned up, and audit history disappears unless you keep it elsewhere.

Soft delete

Add a deleted_at timestamp (or is_deleted flag) and filter it out everywhere. Benefits: easy restore ("undo" or a 30-day trash bin), audit trail, and referential integrity stays intact. Costs:

  • Every query must remember the filter. One missed WHERE clause leaks deleted data. ORMs offer default scopes, but raw SQL and analytics jobs bypass them.
  • Unique constraints break: a deleted user still "owns" the email address. Use a partial unique index (unique on email where deleted_at is null).
  • Tables grow forever and indexes bloat; add partial indexes on live rows.
  • It is not deletion for compliance purposes; personal data still exists.

Better middle grounds

  • Archive table: move the row to an archive or history table in the same transaction, then hard delete. The main table stays clean; restore copies it back.
  • Event log: in event-sourced systems, deletion is just another event, and projections drop the record.
  • Scheduled purge: soft delete first for a grace period, then a background job hard deletes and also removes the data from caches, search indexes, backups policy windows and downstream copies.
  • Crypto-shredding: encrypt each user's personal data with a per-user key and delete the key to make all copies unreadable, including those in backups.

Interview tip

Ask what "delete" must guarantee: user undo, audit, or legal erasure. Then propose soft delete with a grace period plus a purge job that cleans every copy, and mention partial unique indexes. That shows you think beyond the single table.

Disclosure: I'm part of the team behind Linkjob AI, an AI interview assistant - https://www.linkjob.ai/