The challenges of soft delete

Original Article ↗ Hacker News Discussion ↗

Approaches to implementing soft delete

  • Move deleted rows to a separate collection/table (popular in Mongo and RDBMS via triggers or CDC), keeping “live” tables slim and fast.
  • Use table partitioning (e.g., by a deleted flag or time) so “deleted” partitions can be placed on cheaper storage and excluded transparently.
  • Use views (or RLS) to hide soft-deleted rows; the app queries the view as if it were the table. Some dislike views due to past bad experiences; others see them as exactly what’s needed.
  • Maintain separate archive/history tables via triggers or CDC (append-only), treating updates as new versions or “soft deletes” of old state.
  • For offline/replicated systems, treat deletion as just another update, then run a garbage collector to hard-delete after a retention window.

Soft delete vs audit/history

  • Several argue soft delete is a poor fit for true audit/compliance; event sourcing or dedicated audit tables/history logs are more reliable and expressive.
  • Others use “immutable rows + new row per change” patterns to preserve full history and make “as-of” queries possible.
  • Some domains (banking, insurance) already extensively use append-only or bitemporal patterns (valid_from/valid_to) rather than ad-hoc soft delete.

Performance, complexity, and schema drift

  • Query complexity is a recurring concern: missing a “deleted” filter in one of many joins leads to “ghost data.”
  • When deleted rows reach 50–70% of a table, performance can degrade; partitioning, warehousing, or archival is then recommended.
  • Schema drift in archived/soft-deleted records is tricky; many report that large-scale restores are rare and often fail, so the cost of keeping old schemas in sync may not be worth it.

Regulatory and risk considerations

  • Privacy laws (GDPR, CCPA, anti-retention rules) often require hard deletion or strict retention limits; soft delete alone is insufficient and may be risky.
  • Other regulations and business rules require long-term retention, pushing towards soft delete or archives; many systems end up needing both soft delete and true erasure mechanisms.

Product semantics and usage patterns

  • “Soft delete” is seen as an implementation detail; product language should be “delete,” “archive,” “hide,” “close,” “undo,” etc., each with clear semantics.
  • Some teams conclude that backups + point-in-time restore plus hard deletes are simpler, because actual “undelete” needs are infrequent.