Under the Hood: Soft Deletes vs. Hard Deletes
A Hard Delete executes DELETE FROM users WHERE id = 42;, permanently unlinking tuples and allowing VACUUM to reclaim space. A Soft Delete updates a flag: UPDATE users SET deleted_at = NOW() WHERE id = 42;, preserving historical data for audit compliance and recovery.
The Pitfalls of Soft Deletes
Engineering Solutions for Soft Deletes
1. Partial Unique Indexes
If you have a unique constraint on email, soft-deleted rows prevent a user from re-registering with the same email. Solve this with a Partial Unique Index that enforces uniqueness only on active records:
CREATE UNIQUE INDEX uq_users_active_email ON users(email)
WHERE deleted_at IS NULL;
2. Archival Tables (The True Production Pattern)
Keeping millions of soft-deleted records in your primary transactional table pollutes B-Trees and memory buffers. Instead, execute a true delete on the primary table while archiving the deleted record to an auxiliary users_archive or cold S3 Parquet dataset inside an atomic transaction.
Worked example: the cost of keeping deleted rows
Consider a messages table where 5M messages are created per day and 40% are eventually deleted. After 3 years it has about 5.5B rows, 2.2B of them soft-deleted.
- Every index still contains 2.2B deleted entries. For a 40 GB index, about 16 GB of cache is spent on rows no user can see.
- A query like
WHERE conversation_id = ? ORDER BY created_at DESC LIMIT 50must read past deleted rows. If a conversation is 80% deleted, it reads about 250 index entries to return 50. - The fix is a partial index:
CREATE INDEX ON messages(conversation_id, created_at DESC) WHERE deleted_at IS NULL;This index contains only live rows, so it is 40% smaller and does no wasted reads.
Options compared
| Pattern | Undo / audit | Query complexity | Storage and performance | Compliance |
|---|---|---|---|---|
| Hard delete | None (backups only) | Simplest | Best | Easiest to erase |
deleted_at column | Instant undo | Every query needs a filter; use a view or ORM default scope | Bloat unless partial indexes are used | Must still purge later |
| Archive table in the same transaction | Undo by moving the row back | Main table stays clean | Good; archive can be partitioned or cold | Purge the archive on schedule |
| Event log / audit table (CDC) | Full history of all changes | Main table clean | Log grows, but is append-only and cheap | Must also redact the log |
| Delete after grace period | Undo window of N days, then hard delete | Filter during the window only | Bounded bloat | Good |
Archive-on-delete in one statement (PostgreSQL)
WITH moved AS (DELETE FROM users WHERE id = 42 RETURNING *) INSERT INTO users_archive SELECT *, now() FROM moved;
This is atomic: either the row is in exactly one of the two tables or the transaction fails.
Failure modes
- Unique constraints: the partial unique index
WHERE deleted_at IS NULLworks in Postgres and SQLite. MySQL has no partial indexes, so the usual workaround is a generated column that is NULL when deleted, included in the unique key. - Foreign keys to soft-deleted rows remain valid, so orders can point to a "deleted" customer. Decide explicitly whether that is desired.
- Restores that collide: undeleting a user whose email has since been taken by a new account must be handled.
In practice: Stripe-style APIs return deleted: true objects for a while. Google Workspace and Slack offer a trash with a fixed retention period and then hard delete. Many teams use a Rails discard/paranoia gem or EF Core global query filters to apply the filter automatically.