Soft Delete vs Hard Delete in a Database - What They Are and When Each One Is Wrong
Soft delete vs hard delete in a database is the choice between marking a row as gone and actually removing it. A hard delete issues DELETE FROM ... and the row stops existing; a soft delete sets a column like deleted_at = now() and leaves the row in the table, relying on every future query to filter it out.
The problem soft delete exists to solve is that deletion is usually irreversible and deletion is usually a mistake. Someone removes the wrong customer, an integration fires a bad request, a support agent needs to know what an order looked like before it vanished. Restoring one row from a nightly backup means restoring the whole database somewhere and hand-picking, which is hours of work for something that should take a second.
The cost is that you have not removed anything. You have added a piece of state that every query, every unique constraint, every foreign key, and every new engineer on the team has to know about — forever. Most of the pain people attribute to soft delete comes from adopting it as a default rather than as a deliberate answer to a specific requirement.
| Concept | Soft delete vs hard delete |
|---|---|
| Where it sits | Data modelling - sits in your schema and in every query that touches the table |
| In one sentence | Hard delete removes the row; soft delete keeps the row and marks it invisible, which moves the cost from recovery into every query you will ever write against that table. |
The 60-Second Answer
Hard delete removes the row. Storage is reclaimed, constraints keep working, and the data is gone unless you restore a backup.
Soft delete sets a marker column (deleted_at, is_deleted, a status enum) and the row stays. Undo becomes a one-line update and history stays queryable.
The one problem soft delete solves: recoverable, auditable removal without a restore operation.
The one cost it adds: every read path must filter, and the filter is invisible — nothing fails loudly when you forget it, you just leak deleted records into a screen, a report, or an export.
If you only need "undo" for a few minutes and nobody will ever ask what the record used to contain, a confirmation dialog plus a hard delete is the correct design.
How It Actually Works
The mechanism, step by step
- 1. A hard delete removes the row and the engine reclaims the space, eventually
- 2. A soft delete is an UPDATE that changes visibility, not existence
- 3. Every read path now carries an invisible predicate
- 4. Unique constraints stop meaning what you think they mean
- 5. Foreign keys keep pointing at rows the product considers gone
- 6. Rows accumulate and the table pays for it in indexes and plans
- 7. Soft delete without a purge job is a retention violation waiting to happen
- 8. Soft delete is not an audit log, and using it as one loses the data you wanted
1. A hard delete removes the row and the engine reclaims the space, eventually
What DELETE actually does
DELETE FROM orders WHERE id = 42 removes the row from the table's live data and from every index that contains it. In MVCC engines the row version is not physically erased at commit time — it becomes invisible to new transactions and a background vacuum or purge process reclaims the space later, once no open transaction can still see it.
The practical consequence is that a hard delete is cheap to decide and possibly expensive to execute: index maintenance, cascade evaluation, trigger firing, and replication of the change all happen at delete time.
⚠️ In production — Bulk hard deletes are where this bites. Deleting millions of rows in one statement holds a long transaction, bloats the write-ahead log, blocks the reclaim process for the whole table, and can stall replicas. Delete in batches with a bounded key range and a commit between batches.
2. A soft delete is an UPDATE that changes visibility, not existence
The marker column and its shape
A soft delete writes a marker: UPDATE orders SET deleted_at = now() WHERE id = 42. The row still occupies space, still appears in indexes, still satisfies foreign keys pointing at it.
Prefer a nullable timestamp over a boolean. deleted_at TIMESTAMPTZ NULL gives you the same yes/no information as is_deleted BOOLEAN plus when, which is what you actually need when someone asks why a record disappeared last Tuesday.
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ NULL;
CREATE INDEX orders_active_idx ON orders (customer_id) WHERE deleted_at IS NULL;
⚠️ In production — A boolean flag with a DEFAULT false NOT NULL looks tidier and is worse. You will eventually need the deletion time for support, for retention, and for the purge job, and backfilling it is impossible because the information is gone.
3. Every read path now carries an invisible predicate
Where the real cost lives
Once a table is soft-deletable, SELECT * FROM orders is a bug. Every query, every join, every ad-hoc analytics script, and every report must add AND deleted_at IS NULL — including joins where the deleted row is on the other side, which is the case people forget.
The usual mitigations are a database view that encodes the filter (orders_active) and pointing the application at the view, or a default scope in the ORM. Both work; neither covers the analyst who connects with a SQL client.
⚠️ In production — Forgetting the filter fails silently. There is no error, no crash, no failing test unless you wrote one specifically for it — just a deleted customer reappearing in an export six months later. This single property is the strongest argument against making soft delete the default for every table.
4. Unique constraints stop meaning what you think they mean
The constraint collision
If users.email is UNIQUE and a user is soft-deleted, that email is still taken. The person cannot sign up again, and the error they see is "email already in use" for an account that, as far as the product is concerned, does not exist.
The fixes are all tradeoffs. A partial unique index scoped to live rows is the cleanest where the engine supports it. Otherwise you include the deletion marker in the key, which permits only one deleted row per email unless you also mangle the stored value on delete.
CREATE UNIQUE INDEX users_email_live ON users (email) WHERE deleted_at IS NULL;
⚠️ In production — This one usually ships to production before anyone notices, because it only surfaces on the delete-then-recreate path that no test covers. Go through every unique constraint on the table at design time, not after the support ticket.
5. Foreign keys keep pointing at rows the product considers gone
Referential integrity goes advisory
The database enforces that a child row's parent exists. It has no idea the parent is soft-deleted. So ON DELETE CASCADE never fires, ON DELETE RESTRICT never protects you, and a live order can reference a deleted customer without violating a single constraint.
The deletion rules that the engine used to enforce for you now live in application code, where they are one forgotten call site away from being wrong.
⚠️ In production — Decide explicitly whether soft-deleting a parent cascades to children. Both answers are defensible — cascading matches user expectations, not cascading preserves detail for audit — but an undecided answer means the behaviour differs per code path, and you will find out from inconsistent data.
6. Rows accumulate and the table pays for it in indexes and plans
Storage and query planning drift
Soft-deleted rows never leave. On a table where deletion is rare this is irrelevant. On a table that churns — sessions, queue items, notifications, imported records — the dead fraction grows without bound, and index scans, table statistics, and cached plans all degrade with it.
Partial indexes on WHERE deleted_at IS NULL keep the hot index small, which helps reads. They do nothing about the table itself growing, or about backups and replicas carrying data nobody will ever read.
⚠️ In production — The degradation is gradual and shows up as "the app got slower this year" rather than as an incident, so it rarely gets diagnosed correctly. If deleted rows will ever outnumber live ones, you needed a purge job or an archive table from day one.
7. Soft delete without a purge job is a retention violation waiting to happen
The part everyone skips
If a user asks you to delete their account and you set deleted_at, you have not deleted their data. Under most privacy regimes that distinction matters, and "it is filtered out in the UI" is not a defence.
The workable pattern is two-stage: soft delete gives a recovery window, then a scheduled job hard-deletes or anonymises rows past that window. The window is a product decision — long enough for a human to notice the mistake, short enough that you are not indefinitely holding data you promised to remove.
DELETE FROM orders
WHERE deleted_at < now() - INTERVAL '30 days'
LIMIT 1000;
⚠️ In production — The purge job is the part that never gets built, because soft delete feels finished the moment the UI stops showing the row. Write the purge job in the same change as the marker column, or you have shipped an unbounded data-retention liability with a recovery feature bolted on top.
8. Soft delete is not an audit log, and using it as one loses the data you wanted
Knowing the difference
A soft delete records that a row is gone and, if you used a timestamp, when. It does not record who did it, why, what the row contained before an earlier edit, or what else changed in the same transaction.
If the actual requirement is "prove what happened to this record", you want an append-only history table or change-data-capture into an event store. Those answer questions soft delete cannot, and they let the main table hard-delete cleanly.
⚠️ In production — Teams often adopt soft delete because someone said "we need an audit trail", then discover a year later that all they can reconstruct is which rows were deleted — not who deleted them or what they looked like at the time. Pin down which question you actually need answered before you pick the mechanism.
## Where You Meet It
- A B2B SaaS admin panel where an org admin removes a teammate. The account must stop working immediately, but the teammate's comments, assigned tickets, and approval history have to keep rendering with a name attached. Soft delete on
userswith a live-scoped unique index on email, plus display logic for a deactivated user, is exactly the right shape here — the row is referenced from too many places to remove. - A project management tool with a trash can and a thirty-day recovery window. Deleting a project sets
deleted_at, the project disappears from the sidebar, and a nightly job hard-deletes anything past thirty days. The soft delete is explicitly a UI feature with a defined expiry, not a permanent schema property, which is why it stays manageable. - An e-commerce catalogue where a product is discontinued. Orders reference the product row for price and description at time of purchase, so the row cannot be removed without breaking historical order display. Note that this is not really deletion — it is a lifecycle state, and modelling it as
status = 'discontinued'is clearer than adeleted_atthat means something different from deletion everywhere else. - A high-churn events or sessions table where rows are consumed and discarded within hours. Hard delete in batches, or partition by time and drop whole partitions. Soft delete here buys nothing anyone will use and leaves you with a table that is 95% dead rows.
- A financial ledger where nothing is ever deleted at all. Corrections are reversing entries appended to the table. Neither soft nor hard delete applies — the requirement rules out mutation entirely, and reaching for
deleted_athere would hide the correction rather than record it.
When You Actually Need It
- Users can trigger the deletion themselves and undo is a stated product feature — a trash can, a restore button, an "are you sure" that must be reversible after the fact.
- The row is referenced by records you must keep. Orders point at customers, comments point at authors, invoices point at line items. If removing the parent would blank out history someone still needs to read, keep the row.
- Support or compliance staff are regularly asked "what happened to this record?" and the current answer involves someone restoring a backup to a scratch server.
- Deletion on this table is rare relative to its size, so dead rows will never become a meaningful fraction of it — a
customerstable, not asessionstable. - Your regulator or contract requires a retention period before removal, so the row legally cannot be hard-deleted on request anyway. Soft delete plus a scheduled purge implements that directly.
When You Don't
- High-churn tables where rows are transient. Sessions, job queues, event buffers, webhook deliveries, cache entries. Hard delete in bounded batches, or time-partition the table and drop old partitions — dropping a partition is a metadata operation and beats deleting rows by any measure.
- Deletion is genuinely rare and recovery from backup is acceptable. A small internal tool where someone deletes a record twice a year, and a restore would take twenty minutes: that is a fine SLA. Adding soft delete costs you a filter on every query forever to save twenty minutes twice a year. Take the backup.
- The requirement is "we need an audit trail." Soft delete tells you a row is gone, not who removed it or what it contained. Use an append-only history table or change-data-capture, and let the main table hard-delete.
- Right-to-erasure requests. A soft delete does not erase anything. Hard-delete or irreversibly anonymise the personal fields. Keeping a flagged row and calling it deleted is the worst outcome: you carry the compliance exposure and get no recovery benefit anyone will accept.
- Every table, by default, because the ORM makes it one line. This is the most common way teams end up here, and it is the most expensive. Add the marker to the specific tables with a stated recovery requirement. A blanket policy means every join in the system is one forgotten predicate away from leaking data, and nothing will tell you when it does.
- When the real concept is a lifecycle state. Archived, cancelled, discontinued, and suspended are not deletion — they have their own rules about who can see them and what happens next. Model them as an explicit status column. Overloading
deleted_atto mean five different things is how the filter ends up wrong in the cases that matter.
What People Get Wrong
"Soft delete is the safe default — you can always hard-delete later."
The direction that is easy is the other one. Removing soft delete later means auditing every query, every join, and every report in the codebase for a predicate whose absence produces no error. Adding soft delete later to a specific table is a migration plus a backfill. Default to hard delete and add the marker where a requirement demands it.
"Soft delete gives us an audit trail."
It gives you one bit of history — this row is no longer active — and a timestamp if you chose one. It records nothing about who acted, why, or what the row previously contained. If the requirement is traceability, you need history rows or a change log, and those work fine alongside hard delete.
"Deleted rows do not cost anything because we filter them out."
They occupy pages, sit in indexes, skew the statistics the planner uses, and travel in every backup and replica. On a low-churn table this is negligible. On a table where deletions outpace live rows, the filter is the cheap part and the accumulation is what actually degrades you.
"A hard delete means the data is really gone."
Not immediately, and not everywhere. MVCC engines leave the old row version until a background process reclaims it, the write-ahead log still contains the change, replicas apply it on their own schedule, and your backups hold the row for their entire retention period. For erasure requirements, deletion is a process across your whole data estate, not one statement.
Why It Was Built This Way
SQL's DELETE was designed against a model where the database is the current state of the world. A row exists or it does not, constraints are enforced against what exists, and the storage engine is free to reclaim anything unreachable. That model is why constraints, cascades, and query planning are as simple and fast as they are — the engine never has to ask whether a row counts.
Soft delete is an application-level patch on top of that model. It does not extend the engine's notion of existence; it invents a second notion the engine cannot see. Everything that hurts about soft delete follows directly from that gap. Unique constraints break because uniqueness is defined over rows that exist, and the soft-deleted row still does. Foreign keys stop protecting you because referential integrity is defined over rows that exist. The filter is invisible because the engine has no concept to enforce it against. You did not configure a feature; you opted out of one the engine was providing.
What was traded away is the engine's guarantee, and what was bought is recovery without a restore. That is often a good trade — but only for a table where someone will actually use the recovery. The failure mode is not that soft delete is wrong; it is that it gets applied globally when the requirement was local. Databases that treat time as a first-class dimension — temporal tables, system-versioned rows, immutable event logs — close the gap properly by making "what did this look like then" a query the engine understands. If your requirement is really about history rather than undo, those are the designs to look at before reaching for a nullable timestamp on every table.
Related Briefs
What to Read Next
- temporal tables and system-versioned data in SQL
- change data capture for audit logging
- table partitioning for time-series data retention
Hard delete is the default because it keeps the database's guarantees intact — constraints, cascades, and storage reclamation all work without your help. Soft delete buys recoverable removal in exchange for a predicate that every future query must remember and no error will ever remind it of. Add it per table, with a stated recovery window and the purge job written in the same change, and it stays a feature. Add it everywhere as a policy and it becomes a permanent tax on a codebase that mostly never collects the benefit.
댓글
댓글 쓰기