What does it mean to "soft delete" a row in a relational database instead of issuing a DELETE statement, and what do you trade away by doing it?
answer
- deleted_at NULL = alive
- UPDATE, not DELETE — cascade never fires
- filter in every query or it leaks
- UNIQUE now collides with dead rows
- flag ≠ erasure
basics
~20 sSoft delete marks a row as gone — usually by setting a deleted_at timestamp — instead of removing it. The row still physically exists, so it can be restored and still satisfies foreign keys, but every query must now filter it out and the table keeps growing.
solid answer
~50 sA **soft delete** replaces `DELETE FROM orders WHERE id = 1` with an `UPDATE` that stamps a marker column — typically `deleted_at TIMESTAMP NULL`, sometimes `is_deleted BOOLEAN` plus `deleted_by`. The row stays in the table; the application treats "deleted" as a state, not an absence. You gain: cheap undo, no broken foreign keys from children that still reference the parent, and the ability to answer "what was here?" without restoring a backup. You pay for it everywhere else. Every read in the system must add `WHERE deleted_at IS NULL`, and one forgotten filter leaks dead data into a screen, an export, or a `COUNT`. Unique constraints now collide with dead rows. `ON DELETE CASCADE` never fires, because nothing was deleted — cascading becomes application logic. Tables and indexes keep growing, and the flag alone does not satisfy a legal erasure request. So it is a deliberate trade: reversibility and referential safety in exchange for permanent query discipline and unbounded growth.
code
sql · 8 linesALTER TABLE customers
ADD COLUMN deleted_at TIMESTAMPTZ NULL,
ADD COLUMN deleted_by BIGINT NULL;
-- instead of: DELETE FROM customers WHERE id = 42;
UPDATE customers
SET deleted_at = now(), deleted_by = 7
WHERE id = 42 AND deleted_at IS NULL;go deeper
Be able to state the mechanic — a flag or timestamp column, an UPDATE instead of a DELETE — and name the two obvious consequences: you can restore the row, and every query has to exclude it.
Add the constraint and referential effects: unique indexes now see dead rows, cascade rules never fire, and the table plus its indexes grow forever.
Frame it as a policy decision with enforcement: how the filter is guaranteed (views, ORM scopes, row-level security), how purge and retention run, and where soft delete is the wrong tool.
Separate the requirements people bundle into "delete": undo, audit, referential safety, and legal erasure. Assign each to the right mechanism instead of making one flag carry all four.
## The two ways to remove a row A **hard delete** is what the database actually means by deletion: the row is removed from the table, its index entries go away, and any foreign key referencing it either blocks the delete, cascades, or nulls the child column, depending on the declared referential action. After a hard delete, the row is gone from the live database — recovering it means restoring a backup or reading a copy you kept somewhere else. A **soft delete** is an application convention layered on top of an ordinary `UPDATE`. You add a marker column — most commonly `deleted_at TIMESTAMP NULL`, where `NULL` means "alive" and a non-null value means "deleted at that moment" — and the code that used to delete now writes the timestamp instead. Nothing is removed. From the engine's point of view the row is a perfectly normal, live row; only your code knows it is supposed to be invisible. ## Why teams choose it - **Undo.** Users delete things by mistake. Flipping a timestamp back to `NULL` is a one-line restore; a backup restore is an incident. - **Referential safety.** If invoices reference a customer, hard-deleting the customer either fails on the foreign key or cascades away real financial records. Soft delete leaves the parent in place so historical children still resolve. - **Context for later questions.** Support and finance frequently ask "what happened to this account?" A row that still exists, with a timestamp on it, answers that instantly. - **Regulated deletion windows.** Some domains require a grace period before data actually disappears; a `deleted_at` plus a purge job expresses that directly. ## What it costs **Every query becomes conditional.** The predicate `deleted_at IS NULL` now belongs on every read of that table — including joins, subqueries, aggregates, reports, batch jobs, and whatever populates your caches and search index. A single omission is a correctness bug that no type system catches, and it usually surfaces as "deleted customers appear in the monthly total". **Constraints stop meaning what they say.** A `UNIQUE (email)` constraint applies to all rows, live or dead, so a user who deletes their account can never sign up again with the same address. Fixing that requires a partial/filtered unique index or a redesign of the key. **Referential actions never fire.** `ON DELETE CASCADE` and `ON DELETE SET NULL` are triggered by an actual delete. A soft delete is an update, so children are untouched and keep pointing at a logically dead parent. Cascading deletion becomes something your application (or a trigger) must reimplement, along with the matching restore semantics. **The table only grows.** Dead rows consume pages, sit in every index, inflate table statistics, and slow scans. On a table where 90% of rows are deleted, the optimizer's estimates and your index choices both get worse unless you index for it — for example by making the hot indexes partial (`WHERE deleted_at IS NULL`) or by putting the flag into the leading columns. **It is not erasure.** "The user asked us to delete their data" and "the user pressed the delete button in the UI" are different requirements. A flag satisfies the second, never the first; erasure needs a real purge or anonymization path. ## Designing the flag Prefer `deleted_at TIMESTAMPTZ NULL` over `is_deleted BOOLEAN`. A timestamp is strictly more informative — it tells you *when*, which drives retention and purge — and `NULL`/non-null works naturally with partial indexes. A boolean with `NOT NULL DEFAULT false` is easier to index in engines without partial indexes, which is the one real argument for it. Many schemas add `deleted_by` (who) and sometimes `deletion_reason` or a batch id so a cascading soft delete can be reversed as a unit. Avoid the variant where deleted rows are `UPDATE`d to blank out columns "to save space" — you now have neither the data nor real space savings, and you have destroyed the audit value that motivated the flag. ## When to hard delete instead Hard delete when the row is genuinely disposable (session rows, expired tokens, transient events), when volume makes retention expensive, when regulation demands removal, or when nothing references the row. A common middle ground is **hard delete plus an archive table**: the row moves to `orders_archive` in the same transaction, so the live table stays small and the live constraints keep their normal meaning, while the data is still recoverable. Another is **time-partitioned retention**, where old data leaves by dropping a partition rather than by deleting rows one at a time. The practical rule: soft delete is a *user-facing state*, not an audit log and not a retention policy. Use it when "deleted" is something the business can undo; use real deletion, archiving, or partition drops when the goal is that the data actually goes away.
- Why is a deleted_at timestamp usually preferred over an is_deleted boolean?The timestamp carries the same information as the boolean (non-null means deleted) plus the moment it happened, which is what retention and purge jobs need to decide when a row may finally be removed. It also pairs naturally with partial indexes on `deleted_at IS NULL`. A boolean only wins in engines with no partial-index support, where a `NOT NULL` column is easier to put into a composite index.
- If soft delete keeps the row, does it satisfy a regulatory "delete my data" request?No. The personal data is still stored and still readable by anyone with database access, so a flag does not constitute erasure. You need a real purge, or anonymization that irreversibly replaces the identifying fields, and it must reach replicas, exports, search indexes and analytics copies. Soft delete can be the first step, with a purge job doing the actual erasure after the grace period.
Soft delete is the recycle bin: the file is still on the disk and still takes space, it is just hidden from the folder view. Hard delete is the shredder.
saying these in an interview costs you the question
- Saying soft delete is always the safer default, without mentioning the query-filter burden or unbounded growth
- Assuming ON DELETE CASCADE still cleans up children after a soft delete
- Believing existing UNIQUE constraints keep working as intended once dead rows linger
- Treating the deleted_at flag as an audit trail or as compliance-grade erasure
- Blanking out the row's columns while keeping it, which loses the data and saves nothing