Soft Delete

Overview

Soft delete replaces DELETE with an UPDATE that sets a timestamp. The row stays in the table but is hidden from normal reads, which makes undo and audit simple.

How it works

Schema and queries.

ALTER TABLE users ADD COLUMN deleted_at timestamptz;

-- uniqueness only among live rows
CREATE UNIQUE INDEX uniq_live_email ON users (email) WHERE deleted_at IS NULL;

-- "delete"
UPDATE users SET deleted_at = now() WHERE id = 42;

-- default view
CREATE VIEW live_users AS SELECT * FROM users WHERE deleted_at IS NULL;

A nightly job runs a real DELETE for rows older than the retention window.

Alternatives exist. An archive table receives deleted rows through a trigger or application code, keeping the main table lean and its indexes small. Event-sourced systems record a deleted event instead. Temporal tables retain row history automatically. Soft delete is the simplest, but at high churn the archive table approach scales better, because dead rows no longer bloat the hot table and its indexes, and autovacuum or compaction has less to do.

Pitfalls

• Forgetting the filter in a raw query, report or join, which leaks deleted records.
• Unique indexes that include deleted rows and block re-registration.
• Cascading deletes that only soft-delete the parent, leaving children visible.
• Keeping personal data forever and violating erasure obligations.

Key takeaways

• Hide by default through views or scopes, not developer discipline.
• Use partial unique indexes for live rows.
• Define a retention period and a purge job.
• Treat privacy erasure as a hard delete, not a soft one.