Two Ways to Delete
A hard delete removes the row from the table outright. A soft delete leaves the row in place and sets a flag, usually a deleted at timestamp, so the application treats it as gone while the data physically remains.
Why Soft Delete
- Recoverable: an accidental delete can be undone.
- Auditable: you can see what existed and when it left.
- Referential safety: rows referenced elsewhere are not abruptly removed.
The Hidden Costs
- Every query must filter out soft deleted rows, and forgetting the filter is a common bug.
- Unique constraints break because a deleted row still occupies the value.
- Indexes and table size grow with dead rows that are never purged.
Practical Tactics
- Use a partial unique index that ignores deleted rows.
- Provide a view that hides deleted rows so queries stay clean.
- Periodically archive or purge old soft deleted rows.
Key idea
Soft deletes preserve history and allow recovery but force every query and constraint to account for hidden rows.