Soft delete vs hard delete: trade-offs

Capsule overview

"Delete that record" sounds trivial. In a real app, it isn't. Hard delete removes the row physically: you free up space, you simplify the model, you satisfy retention regulations. Soft delete marks the row as deleted but keeps it: you get recovery, an audit trail, and you can undo accidents. Each option has an invisible cost that only shows up in production, when it's already too late to change the decision.

In this capsule you don't learn to implement either one (that comes in 03). You learn to decide which one fits your case. You're going to see the real costs of each option (space, performance, compliance, operational complexity), a decision tree you can apply in any code review, and the cases where soft delete is NOT the right answer and choosing it accrues technical debt.

The goal is that by the end you can defend your decision to a senior colleague with concrete criteria, not with "it's what everyone does." The wrong pattern applied out of inertia is the most expensive technical debt to reverse in a SaaS codebase.

The framing: "deleting" isn't an operation, it's a modeling decision

When a user clicks the "delete task" button, what looks like an atomic event hides several decisions:

1. Should the row physically disappear or stay marked?
2. If it stays marked, for how long? Forever? Until a job cleans it up?
3. Can the user undo the deletion? How long afterward?
4. Does history (the audit log, reports, analytics) count this row or exclude it?
5. Should other rows pointing at this one (foreign keys) be deleted, marked, or left orphaned?
6. Does compliance regulate retention (you must keep it X years) or deletion (you must remove it on demand)?

Hard delete answers "1: it disappears, 2: it never existed, 3: no, 4: they don't appear, 5: cascade, 6: you satisfy deletion but you lose retention." Soft delete answers "1: marked, 2: forever by default, 3: yes, 4: depends on the query, 5: depends on the model, 6: you satisfy retention but you violate deletion."

Neither answer is universally correct. The question is: for your domain, which column weighs more?

Mental model: deleting as a commitment, not an action

Think of deletion like a bank's safe deposit box. Hard delete is smashing the box with a sledgehammer: there's no way to recover what was inside, but the box stops taking up space in the vault. Soft delete is locking the box and putting a "do not open" label on it: the contents are still there, they take up space, someone with the key can open it, but the customer sees it as "deleted."

Every bank customer wants something different. Some want the guarantee that their contents disappear when they close the account (GDPR). Others want to be able to ask for the contents back six months later (recovery). Some are under regulation that requires them to keep it for seven years (tax compliance).

Your API is the bank. The question isn't "how do I delete?", it's "what kind of bank do you want to be for your customers?".

What you gain with each option

Hard delete (DELETE FROM tasks WHERE id = $1)

Real advantages:

• Space: the row frees up space in the table and in every index that included it. After the next VACUUM, that space is reused. No accumulation of long-term "garbage."
• Query simplicity: every query is SELECT * FROM tasks WHERE .... There are no extra filters to remember. There's nothing to automate. What's there is alive.
• Stable long-term performance: the table only grows with active data. Indexes stay dense. There's no degradation from accumulation.
• GDPR right-to-be-forgotten is satisfied by default: if the data doesn't exist, you don't have to prove it was deleted.
• Foreign keys with ON DELETE CASCADE are deterministic: the cascade removes dependencies and that's that.

Real costs:

• Loss of information: if the user regrets it, there's no going back. "Restore from backup" sounds fine until you remember that a backup has hours of latency and isn't selective (you restore the whole DB or nothing).
• A broken audit trail: you can't answer "what happened to task #1234?". The row isn't there. The log says it was deleted on March 15, but the content went with it.
• Foreign keys have to be thought through: if comments.task_id references tasks.id and you delete the task, the cascade removes the comments. If you do NOT use cascade, you get an FK violation error. If you use ON DELETE SET NULL, you're left with orphaned comments. Each case requires an explicit decision.
• Impossible to audit deletions: "who deleted this task?" — no log says so unless you built a parallel audit log.

Soft delete (UPDATE tasks SET deleted_at = NOW() WHERE id = $1)

Real advantages:

• Trivial recovery: UPDATE tasks SET deleted_at = NULL WHERE id = $1 and the row is alive again. Undo costs nothing to implement.
• Preserved audit trail: the row is still there with its content. You can answer "what did task #1234 say before it was deleted?" by reading the row directly.
• Foreign keys stay valid: related records aren't orphaned. A comment on a deleted task still points at an existing row.
• Historical metrics work: "how many tasks did this user create in March?" counts the deleted ones too, without losing historical visibility.
• Retention compliance is satisfied: if the regulation says "keep the data for seven years," soft delete keeps it.

Real costs:

• Accumulated space: deleted rows keep taking up room in the table and indexes. On tables with a high delete ratio (>50%), the bloat can triple the size. VACUUM doesn't reclaim that space (you have to do VACUUM FULL, which takes an exclusive lock).
• Degraded performance if unmanaged: without a partial index, every query with WHERE deleted_at IS NULL walks deleted rows it then discards. It's a disguised N+1. You'll see it measured in capsule 03.
• Risk of the forgotten filter: a new endpoint written without remembering the filter returns deleted records to customers. A semantic bug, hard to detect in QA, easy to propagate.
• GDPR right-to-be-forgotten is NOT satisfied: the personal data is still there. Marking deleted_at doesn't satisfy "delete my data." You need to combine soft delete + anonymization + deferred hard delete.
• Cascade isn't trivial: if you delete a user, do their tasks get deleted? Do they get marked too? Are they left orphaned referencing a "deleted" user? There's no single answer; each relationship needs an explicit decision.
• JOINs require attention: if you do JOIN users ON tasks.author_id = users.id, do you also filter users.deleted_at IS NULL? If not, you show tasks from deleted users. If you do, legitimate tasks from deleted users disappear.

Worked case: the same operation with each option

Let's see what PostgreSQL does in each case. The table:

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  author_id BIGINT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at TIMESTAMPTZ NULL
);

Hard delete

BEGIN;
DELETE FROM tasks WHERE id = 1234;
-- DELETE 1
COMMIT;

What happens internally:

1. PostgreSQL marks the row as "deleted" in its MVCC (visibility map). The row is still physically in the table.
2. The row stops being visible to new transactions.
3. The foreign keys (comments.task_id for example) execute their policy: CASCADE deletes dependencies, RESTRICT fails if there are dependencies, SET NULL marks them as NULL.
4. On the next VACUUM, the physical space is reused for new inserts.

If after the COMMIT someone wants to recover it: they need to go to the backup. Good luck.

Soft delete

BEGIN;
UPDATE tasks SET deleted_at = NOW() WHERE id = 1234;
-- UPDATE 1
COMMIT;

What happens internally:

1. PostgreSQL creates a new version of the row with deleted_at = NOW() (MVCC).
2. The old version is marked as "not visible to new transactions." It's still physically in the table until the next VACUUM.
3. The foreign keys trigger nothing: the row still exists from the schema's point of view.
4. Any new query has to filter WHERE deleted_at IS NULL so as not to see it.

If after the COMMIT someone wants to recover it:

UPDATE tasks SET deleted_at = NULL WHERE id = 1234;

Done. The row is visible again.

What changes in subsequent queries

Hard delete:

SELECT id, title FROM tasks WHERE author_id = 42 ORDER BY created_at DESC LIMIT 50;
-- Returns only live tasks (the deleted ones no longer exist)

Soft delete:

-- You forgot the filter: it returns deleted tasks too
SELECT id, title FROM tasks WHERE author_id = 42 ORDER BY created_at DESC LIMIT 50;

-- Correct filter: it returns only live tasks
SELECT id, title FROM tasks
WHERE author_id = 42 AND deleted_at IS NULL
ORDER BY created_at DESC LIMIT 50;

That difference is exactly the most expensive semantic bug of the pattern. With hard delete it's impossible to forget the filter because there is no filter. With soft delete, forgetting it is a mistake waiting in every new query in the codebase. That's why the module invests an entire capsule (04) in how to automate the filter.

Decision tree: which one to use?

Does regulation require you to delete on demand (GDPR, CCPA, HIPAA in some cases)?
├─ YES → you need hard delete, or soft delete + anonymization + deferred hard delete.
│        (soft delete alone does NOT satisfy it)
└─ NO → next question

Do users need to undo the deletion within some horizon (minutes, days, months)?
├─ YES → soft delete (the option that gives trivial recovery)
└─ NO → next question

Do the audit trail / historical reports / analytics need to see deleted records?
├─ YES → soft delete (preserves history with no additional ETL)
└─ NO → next question

Can the table grow past 100M rows with a delete ratio >50%?
├─ YES → soft delete stops scaling; consider archive tables (capsule 07)
└─ NO → next question

Is the complexity of maintaining the automatic filter in every query acceptable for the team?
├─ YES → soft delete (with the module's automatic mechanism)
└─ NO → hard delete (simpler, fewer things to forget)

Summary: soft delete wins when the domain values recovery + audit + historical metrics, and regulation allows it. Hard delete wins when simplicity outweighs undo, or regulation demands it.

Cases where the answer is clear

Soft delete, no hesitation:

• Tasks, projects, documents editable by the user (the TaskFlow case).
• Comments the user might regret.
• Products in a catalog (a discontinued product can come back).
• Cancelled subscriptions that can be reactivated.

Hard delete, no hesitation:

• Expired session tokens (there's no point recovering them).
• Cache entries (derived data, regenerable).
• Personal data of users exercising GDPR right-to-be-forgotten.
• Telemetry events older than the retention window.
• Log tables with a natural TTL (errors, requests, raw metrics).

Ambiguous cases that depend on the product:

• Chat messages (can the user "delete for everyone"? is there an internal audit trail?).
• Payments / transactions (audit usually wins but compliance rules).
• User accounts (typically hybrid: mark as deactivated, then hard delete with a cron).

The special case of GDPR (and why soft delete isn't enough)

GDPR Article 17 (right-to-be-forgotten) says a user can ask for their personal data to be deleted. Soft delete doesn't satisfy this: the data is still there, it's just marked.

The standard solution in SaaS is a three-step pattern:

1. Immediate soft delete: UPDATE users SET deleted_at = NOW() WHERE id = $1. The account stops being visible.
2. Synchronous anonymization: overwrite personal fields with generic values. UPDATE users SET email = 'deleted-' || id || '@anonymized', name = 'Deleted User', phone = NULL WHERE id = $1. The personal data disappears, but the row stays with a valid FK so historical joins don't break.
3. Deferred hard delete: a nightly job runs DELETE FROM users WHERE deleted_at < NOW() - INTERVAL '30 days' after a grace period. The foreign keys must have ON DELETE SET NULL for the fields that should stay available, or CASCADE for the ones that go with the user.

This pattern satisfies GDPR (the personal data is gone after 30 days) and allows recovery during the grace window. It's the only case where soft delete and hard delete coexist in the same table.

Deeper coverage of compliance (what GDPR considers "personal data," exceptions, deletion logs for audits) is outside this guide's scope. If you work with regulated data, consult your legal team before choosing a strategy.

Why does this decision matter in real work?

1. Modeling decisions are the most expensive to reverse. Changing from hard to soft delete after the fact means adding a column, doing a backfill (all past deletions are lost, so it only applies going forward), modifying every query, automating the filter, and redoing tests. Changing from soft to hard means losing history that some other part of the system (reports, audit) probably already consumes. Neither is trivial.

2. The invisible costs show up late. Soft delete without a partial index is invisible in QA with 100 rows. It shows up in production when you hit 1M and the endpoint goes from 5ms to 800ms. Hard delete without recovery is invisible until a customer deletes something by accident and asks to restore it.

3. Code reviews are going to ask you this. When you propose a DELETE /tasks/{id} endpoint in a PR, a senior reviewer is going to ask "hard or soft? why?". Having the answer ready with criteria (not preference) is senior level.

4. The wrong pattern scales badly. Soft delete applied to a telemetry events table with 200M rows a month is immediate debt: every month you accumulate more deleted rows than live ones. Hard delete applied to user-editable tasks generates support tickets. Knowing how to choose the right pattern from day 1 saves you months of migration later.

Traps and common mistakes

Mistake 1 (conceptual): "soft delete is always safer"

Symptom: the team applies soft delete to everything by default, "just in case."

Why it's wrong: soft delete has costs (space, performance, complexity, GDPR risk) that accumulate in every table where it's applied unnecessarily. A session_tokens table with soft delete accumulates expired tokens forever, the indexes bloat and auth queries slow down, all to preserve information nobody is ever going to query.

How to tell: ask yourself "is anyone ever going to want to recover an expired session_token?". If the answer is no, soft delete is unjustified overhead.

How to fix it: an explicit decision per table, not a default. Document the rationale in the model (a comment in the ORM or the schema).

Mistake 2 (conceptual): "soft delete satisfies GDPR because the data 'disappears' from the API"

Symptom: the team believes they're compliant with soft delete because the customer doesn't see their data after deleting the account.

Why it's wrong: GDPR doesn't care what the customer sees, it cares about what your DB stores. If the regulator audits and the personal data is still there, you aren't compliant. Even though the customer can no longer see it, your backups, internal reports, and administrators can.

How to tell: look at what data a "soft-deleted" row holds. Is there PII (name, email, phone, address)? If so, soft delete alone isn't enough.

How to fix it: the soft delete + anonymization + deferred hard delete pattern described above.

Mistake 3 (practical): the silent cascade with ON DELETE CASCADE and soft delete

Symptom: your model has comments.task_id REFERENCES tasks(id) ON DELETE CASCADE. You decide to switch to soft delete: now it's UPDATE tasks SET deleted_at = NOW(). The comments are still there. But the day some job runs a real hard delete (for compliance, for GC, whatever), the comments go with the cascade — and if they had their own soft delete, you lose them without a deletion mark.

Why it happens: cascade lives at the schema level, not the application level. Transitioning to soft delete at the app level doesn't change the schema's behavior when someone does run a DELETE.

How to tell: list the FKs with cascade in your schema (information_schema.referential_constraints). Any table with soft delete + an FK with cascade is a candidate for inconsistency.

How to fix it: decide the policy explicitly. It's usually changing CASCADE to RESTRICT or NO ACTION, and handling the cascade at the application level. Capsule 06 goes deeper.

Mistake 4 (edge case): hard delete on referenced rows with no FK handler

Symptom: you run DELETE FROM users WHERE id = $1 and get ERROR: update or delete on table "users" violates foreign key constraint "tasks_author_id_fkey" on table "tasks". The operation fails in production and returns a 500 to the user.

Why it happens: the FK has no cascade policy and there are tasks pointing at the user. PostgreSQL protects you from leaving orphaned tasks, but the error goes out to the user.

How to tell: try the DELETE in a test environment against a row with dependencies. If it blows up, your app doesn't handle the case.

How to fix it: decide the policy at the schema level (CASCADE, SET NULL, RESTRICT) and handle the error in the app where it applies. In SaaS, the answer is usually "don't allow hard delete if there are dependencies; use soft delete or ask the user to remove the dependencies first."

Exercises

Exercise 1: classify each table

For each case, decide between hard delete, soft delete, or hybrid (soft + deferred hard delete), and justify it:

a) A password_reset_tokens table with a 1-hour TTL. Each token is used once.

b) An invoices table in a billing app. Tax regulation requires keeping invoices for 7 years. Users can "void" an invoice.

c) A users table in a B2B SaaS. Users can cancel their account. GDPR applies.

d) An audit_events table that records every action in the system. Volume of 10M events per month.

e) A cart_items table for a shopping cart. Users add and remove items constantly.

Exercise 2: identify the invisible cost

You're shown this model in a code review:

class SessionToken(Base):
    __tablename__ = "session_tokens"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    token_hash: Mapped[str]
    expires_at: Mapped[datetime]
    deleted_at: Mapped[datetime | None] = mapped_column(default=None)  # soft delete

    __table_args__ = (
        Index("idx_session_token_hash", "token_hash"),
        Index("idx_session_user", "user_id"),
    )

What problems do you spot? The app has 100k active users, each generating ~5 tokens a day (login from mobile + web), a 7-day TTL, and a high churn ratio.

Exercise 3: redesign the GDPR pattern

Your B2B SaaS app has this table:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  full_name TEXT NOT NULL,
  phone TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

A customer exercises GDPR right-to-be-forgotten. Design the three-step flow (soft delete + anonymization + deferred hard delete). Define which columns to touch, at what point, and what happens with the foreign keys (assume tasks.author_id REFERENCES users.id).

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ NULL;
ALTER TABLE users ADD COLUMN anonymized_at TIMESTAMPTZ NULL;

-- Partial index for active-user queries
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

-- tasks.author_id must keep pointing at a valid row after the hard delete.
-- If the product rule is "historical tasks keep a reference to the deleted user",
-- use SET NULL:
ALTER TABLE tasks
  DROP CONSTRAINT tasks_author_id_fkey,
  ADD CONSTRAINT tasks_author_id_fkey
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL;

-- Alternative: if the tasks should be deleted with the user, use CASCADE.
-- Every FK requires an explicit decision.

async def delete_user_gdpr(session: AsyncSession, user_id: int) -> None:
    """
    Soft delete + synchronous anonymization.
    The hard delete is run by a nightly job after the grace period.
    """
    now = datetime.now(timezone.utc)
    await session.execute(
        update(User)
        .where(User.id == user_id)
        .values(
            email=f"deleted-{user_id}@anonymized.local",
            full_name="Deleted User",
            phone=None,
            deleted_at=now,
            anonymized_at=now,
        )
    )
    await session.commit()

async def hard_delete_expired_users(session: AsyncSession) -> int:
    """
    Physically removes users with deleted_at > 30 days.
    FKs with SET NULL leave orphaned tasks with author_id NULL.
    FKs with CASCADE delete the tasks in cascade.
    """
    result = await session.execute(
        delete(User).where(
            User.deleted_at < datetime.now(timezone.utc) - timedelta(days=30)
        )
    )
    await session.commit()
    return result.rowcount

Exercise 4: defend the decision in a code review

A junior colleague opens a PR with:

@app.delete("/sessions/{session_id}")
async def delete_session(session_id: int, db: AsyncSession = Depends(get_session)):
    await db.execute(
        update(SessionToken)
        .where(SessionToken.id == session_id)
        .values(deleted_at=datetime.now(timezone.utc))
    )
    await db.commit()
    return {"status": "deleted"}

Justify in a review comment why they should switch to hard delete, citing concrete criteria (not personal opinion). Your message has to convince the junior and the tech lead who approved the PR.

Exercise 5: detect the silent cascade

In your DB you have:

CREATE TABLE projects (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  deleted_at TIMESTAMPTZ NULL
);

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  deleted_at TIMESTAMPTZ NULL
);

Your app uses soft delete on both tables. What potential problem exists? How do you verify it and how do you fix it?

-- List foreign keys with CASCADE on tables that have deleted_at
SELECT
  tc.table_name AS child_table,
  kcu.column_name AS child_column,
  ccu.table_name AS parent_table,
  rc.delete_rule
FROM information_schema.referential_constraints rc
JOIN information_schema.table_constraints tc
  ON rc.constraint_name = tc.constraint_name
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON rc.unique_constraint_name = ccu.constraint_name
WHERE rc.delete_rule = 'CASCADE'
  AND tc.table_name IN (
    SELECT table_name FROM information_schema.columns
    WHERE column_name = 'deleted_at'
  );

-- Option 1: RESTRICT (the most conservative)
ALTER TABLE tasks
  DROP CONSTRAINT tasks_project_id_fkey,
  ADD CONSTRAINT tasks_project_id_fkey
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE RESTRICT;

Summary and next step

In this capsule you learned:

• Deleting is a modeling decision, not an atomic operation. Every deletion implicitly answers 6 questions (visibility, retention, undo, metrics, FK, compliance).
• Hard delete wins on simplicity and GDPR compliance; soft delete wins on recovery, audit, and historical metrics. Neither pattern is universal.
• The decision tree takes you to the right answer based on regulation, undo, metrics, scale, and operational complexity.
• Hybrid patterns exist and are usually the answer for personal data under GDPR: soft delete + anonymization + deferred hard delete.
• The silent cascade is the most common trap when introducing soft delete over a schema with existing CASCADE foreign keys. Audit the FKs when introducing soft delete.

Before moving on you should be able to:

• Decide between hard and soft delete for a new table with concrete criteria (not preference).
• Identify when a team is applying soft delete out of inertia where it adds no value.
• Design the hybrid pattern (soft + anonymization + deferred hard delete) for a table with PII under GDPR.
• Audit foreign keys with CASCADE on tables with deleted_at to detect inconsistencies.

Next capsule — Implementing deleted_at in PostgreSQL. You're going to land the pattern in real code: a schema with deleted_at TIMESTAMPTZ, a partial index WHERE deleted_at IS NULL, before-and-after measurements with EXPLAIN ANALYZE. The capsule is 100% pure PostgreSQL; SQLAlchemy arrives in 04.

Resources

1. Cultured Systems — "Avoiding the soft delete anti-pattern" — the strongest case against soft delete by default. Recommended reading before applying the pattern to a new table.
2. Brandur Leach — "Soft deletion probably isn't worth it" — a real case from Stripe. Why soft delete stops being sustainable as the table scales and how to migrate to archive tables.
3. Heroku Engineering — "Why Soft Deletion is Evil" — the classic counterargument. Useful for understanding the opposite case.
4. Milan Jovanovic — "Implementing the Soft Delete Pattern" — a discussion of automatic mechanisms in other stacks; conceptually applicable.
5. GDPR Article 17 — Right to erasure — the official text. Required reading if your app handles data from European residents.
6. PostgreSQL Documentation — Foreign Keys — the reference for the CASCADE, RESTRICT, SET NULL, and NO ACTION policies.
7. Sequin — "PostgreSQL Soft Deletes: Implementation Strategies" — a comparison of schema-level strategies, complementing this capsule.

Module 2 — SQL Patterns for Production APIs Guide

Next capsule: Implementing deleted_at in PostgreSQL.