Soft Delete vs Hard Delete: What Is the Difference and When Do You Use Each?
Compare soft delete and hard delete patterns in databases, their trade-offs, and when to use each in an interview-ready answer.
Expected Interview Answer
A hard delete physically removes a row from the database with a DELETE statement, while a soft delete keeps the row in place but flags it as inactive using a column like deleted_at or is_deleted, so it can be filtered out of normal queries without losing the underlying data.
Hard deletes free storage immediately and simplify queries since there is nothing to filter, but the data is genuinely gone, breaking any process that needs history, audit trails, or the ability to undo a mistaken delete. Soft deletes preserve referential integrity for related records, allow recovery, and support auditing, at the cost of every query needing a WHERE deleted_at IS NULL filter (often hidden behind a view or ORM default scope) and indexes needing to account for the extra filter to stay efficient. The choice usually comes down to whether the domain has a compliance, audit, or undo requirement — if so, soft delete; if storage, simplicity, and genuine erasure (like GDPR right-to-be-forgotten requests) matter more, hard delete.
- Soft delete preserves history and supports undo/recovery
- Soft delete keeps foreign-key relationships intact for related rows
- Hard delete reclaims storage and keeps queries simple
- Hard delete satisfies true erasure requirements like right-to-be-forgotten
AI Mentor Explanation
When a player is removed from a squad list mid-season, a well-run cricket board does not shred their entire playing record — it marks the player as “inactive” in the roster system while keeping every match statistic intact for the historical record. Only if a player's data must be fully erased, say due to a legal request, does the board actually purge every trace. This is exactly soft delete versus hard delete: flagging a row inactive preserves history, while physically removing the row is reserved for genuine, permanent erasure.
Step-by-Step Explanation
Step 1
Add a deletion-marker column
Add a nullable deleted_at TIMESTAMP (or boolean is_deleted) column to the table to support soft delete.
Step 2
Update writes to flag, not remove
A "delete" operation becomes UPDATE ... SET deleted_at = now() instead of DELETE FROM.
Step 3
Filter deleted rows from normal queries
Every SELECT that should hide deleted rows adds WHERE deleted_at IS NULL, often via a view or default ORM scope.
Step 4
Reserve hard delete for genuine erasure
Use an actual DELETE statement only for compliance-driven purges or expired retention windows, after the soft-delete grace period ends.
What Interviewer Expects
- Clear distinction between physically removing a row and flagging it inactive
- Awareness of the query-filtering overhead soft delete introduces
- Discussion of compliance or audit scenarios that push toward one pattern or the other
- Mention of the impact on foreign keys and referential integrity
Common Mistakes
- Forgetting to add the deleted_at filter to every query, leaking "deleted" rows into normal results
- Using soft delete everywhere without considering true erasure requirements like GDPR
- Not indexing on the deletion-marker column, causing full scans on large tables
- Assuming soft delete alone satisfies legal right-to-be-forgotten obligations
Best Answer (HR Friendly)
“A hard delete physically removes a row from the database, so the data is genuinely gone. A soft delete instead sets a flag or timestamp on the row marking it as deleted, while the row itself stays in the table, which lets you recover it, audit it, or keep related records consistent. I would choose soft delete when history, undo, or compliance auditing matters, and hard delete when I need the data to actually disappear, like satisfying a legal erasure request.”
Code Example
-- Soft delete: mark the row inactive, keep the data
ALTER TABLE customers ADD COLUMN deleted_at TIMESTAMPTZ;
UPDATE customers
SET deleted_at = now()
WHERE customer_id = 42;
-- Normal queries must filter it out
SELECT * FROM customers WHERE deleted_at IS NULL;
-- Hard delete: permanently remove the row
DELETE FROM customers WHERE customer_id = 42;
-- The row and all its data are gone; this cannot be undone.Follow-up Questions
- How do you handle unique constraints on a table that uses soft delete?
- How would you implement soft delete cleanly without littering every query with a filter?
- How does soft delete interact with cascading foreign-key deletes?
- How would you support both soft delete for normal use and hard delete for GDPR erasure requests?
MCQ Practice
1. What is the main mechanism behind a soft delete?
Soft delete keeps the row physically present and uses a column like deleted_at or is_deleted to exclude it from normal queries.
2. Which scenario most strongly favors a hard delete over a soft delete?
True erasure requirements cannot be satisfied by merely flagging a row inactive; the data must actually be removed.
3. What is a common operational cost of using soft delete broadly?
Because soft-deleted rows still exist in the table, every relevant query needs to filter them out to avoid showing "deleted" data.
Flash Cards
What is a hard delete? — Physically removing a row from the table with a DELETE statement; the data cannot be recovered.
What is a soft delete? — Flagging a row as inactive with a column like deleted_at while keeping it in the table.
When is soft delete preferred? — When audit history, undo capability, or referential integrity for related rows matters.
When is hard delete required? — When true, permanent erasure is needed, such as for legal right-to-be-forgotten compliance.