Someone deletes a customer record and later needs it back. The obvious fix is to not really delete it — add a deleted_at timestamp, set it instead of removing the row, and filter it out of queries.
The first part works. The second part is the problem, because "filter it out of queries" means every query, forever, written by everyone, including the one written at 6pm on a Friday by someone who did not know the convention.
What changed in 2026
- Retention obligations got stricter. Data deletion requirements made "we never actually delete anything" a liability rather than a safe default.
- Partial indexes became the standard fix for the unique constraint problem, which used to require awkward workarounds.
- Temporal patterns gained traction. Separate history tables and system-versioning became more common as an alternative to flags.
- ORM support improved and stayed leaky. Frameworks handle the common case and reliably miss raw queries, reporting tools, and direct database access.
The problems, concretely
Every query needs the filter. This is the defining cost. Miss it once and deleted data appears somewhere it should not — a report, an export, an API response, an admin screen. The failure is silent and the blast radius depends entirely on where it happened.
Unique constraints stop working as intended. A user deletes their account with email a@example.com, then signs up again with the same address. The unique constraint rejects it, because the deleted row still holds the value. You now have a support ticket and no obvious fix.
Foreign keys become misleading. A child row can reference a soft-deleted parent, and the database sees nothing wrong. Referential integrity is intact by the database's definition and violated by yours.
Indexes and tables grow forever. Deleted rows accumulate indefinitely, occupying space and slowing scans. Without a purge policy, a table of mostly-deleted rows is a table you are paying to scan.
Aggregates go quietly wrong. A COUNT(*) or SUM() missing the filter produces a number that is plausible and wrong, which is worse than an error.
Making it safer
If you are going to soft delete, make the safe path the default rather than relying on everyone remembering.
Use a partial unique index. Define uniqueness only over live rows: unique on (email) WHERE deleted_at IS NULL. Deleted rows no longer block reuse, and the constraint means what you intended — see partial indexes.
Expose a view, not the table. Create a view that filters deleted rows and have application code read from it. Now forgetting the filter is impossible for anything using the view, and access to the raw table becomes a deliberate act.
Add partial indexes for the hot queries. Indexes covering only live rows are smaller and cheaper, since most queries only ever want those.
Purge on a schedule. Soft delete should be a recovery window, not permanent storage. Hard-delete rows after a defined period, which bounds growth and satisfies retention obligations.
Set the column, not a boolean. A timestamp tells you when, which a boolean does not, and costs nothing extra.
When something else is better
| Requirement |
Better fit |
| Undo within a short window |
Soft delete with scheduled purge |
| Full history of changes |
Separate audit or history table |
| Regulatory retention |
Archive table with explicit retention |
| Who changed what, when |
Audit log, not a flag |
| Point-in-time reconstruction |
System-versioned or temporal tables |
The distinction that matters most: soft delete answers "is this row live", not "what happened to it". A deleted_at column tells you a row was deleted at a time. It does not tell you who did it, why, or what the row looked like before an earlier edit.
If what you actually need is an audit trail — and often it is, once someone asks the question properly — a separate history table capturing changes serves that far better, and lets the main table delete rows normally. The main table stays clean, queries stay simple, and the history is queried deliberately when needed.
Common mistakes
- Relying on discipline for the filter. Someone will forget; make it structural.
- Plain unique constraints. Deleted rows block value reuse.
- No purge policy. Unbounded growth and a retention problem.
- Assuming the ORM covers it. Raw queries, reporting tools, and BI connections bypass it.
- Using it as an audit trail. It records that something was deleted, not what happened.
- Cascading confusion. Soft-deleting a parent while children remain live, with nothing enforcing consistency.
- A boolean instead of a timestamp. Loses information for no saving.
FAQ
Is soft delete an anti-pattern?
No, it is a trade with costs people underestimate. For a genuine short undo window it is reasonable. As permanent storage substituting for an audit trail, it usually goes badly.
How do I add it to an existing system safely?
Column, partial unique indexes, then a view — and migrate readers to the view before anything starts writing the flag. Adding the column first and hoping queries get updated is how leaks happen.
What about cascading?
Decide deliberately and enforce it. Soft-deleting a parent while children remain live creates orphans the database considers valid. Either cascade the flag or handle it in queries; do not leave it ambiguous.
Does it interact with row-level security?
Yes, and usefully. A security policy that filters deleted rows enforces it at the database level for every connection, which is stronger than a view anyone can read around.
Where to go next
For the unique constraint fix, read partial indexes. For managing data lifecycle at the storage level instead, table partitioning, and for the version churn deleted rows contribute to, MVCC explained.