DELETEs Are Difficult

Original Article ↗ Hacker News Discussion ↗

Relational databases make it trivially easy to run destructive commands like `DELETE` without safeguards, yet reliably removing data at scale turns out to be technically and legally complex. Commenters compare strategies such as soft deletes, partitioning, tombstoning, and background vacuuming, and note trade-offs around performance, foreign-key cascades, and operational safety (e.g., batching large deletes to avoid locking and bloat). Data protection rules like GDPR further complicate matters by demanding “real” erasure even though storage systems, backups, and filesystem behavior make complete, immediate deletion practically unattainable.

Danger and UX of DELETE

  • Many argue SQL makes destructive actions too easy, especially DELETE FROM table; without a WHERE.
  • Suggested safeguards: require WHERE, require explicit opt‑in syntax, or client options that forbid unqualified DELETE/UPDATE (e.g., “safe updates” modes).
  • Some rely on habits: always BEGIN and be ready to ROLLBACK, or use CLI extensions that refuse unsafe statements.

Soft Deletes vs Hard Deletes

  • Common pattern: “soft delete” via status or date_deleted columns so data can be restored and later purged.
  • Others note this often fails legal/privacy requirements (e.g., GDPR erasure); sometimes only personally identifiable fields are wiped (“firm delete”) while preserving referential structure.
  • Soft deletes can be more expensive in Postgres because they’re effectively an extra update/insert, while real DELETE is “just” a tuple flag.

Performance, Batching, and MVCC

  • Large DELETEs are expensive; advice is to delete in small batches (e.g., 10k rows per iteration) to avoid locks, bloat, and log thrash.
  • Some ask why databases don’t auto‑batch; responses cite semantics: one big transaction ≠ many smaller ones, and ACID guarantees limit what engines can transparently change.
  • Other strategies: partitioning and dropping old partitions, using TRUNCATE where appropriate, or designing TTL/cleanup jobs that delete gradually.

ACID, Semantics, and Engine Differences

  • Debate over whether row‑tombstoning plus later vacuum/compaction is still ACID‑compliant (consensus: yes, as long as queries don’t see “deleted” rows).
  • Postgres’s MVCC/vacuum model is contrasted with undo‑log approaches (MSSQL, Oracle, MySQL), and LSM‑tree engines where deletes become tombstones compacted later.

Cascades, Keys, and Schema Design

  • ON DELETE/UPDATE CASCADE is useful but seen as “dangerous magic” by some, who prefer RESTRICT and explicit cleanup.
  • Skepticism about updating primary keys; natural keys as PKs cause pain and are generally discouraged in favor of surrogate keys.

GDPR, Backups, and True Erasure

  • Question whether DELETE plus eventual vacuum is GDPR‑compliant; replies say delays are acceptable if documented and reasonably bounded.
  • True physical erasure is hard: filesystems, SSD behavior, and backups mean data can linger. Supervisory authorities differ on how backups must be handled; expectations remain somewhat unclear.