DELETEs Are Difficult

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.