A deleted_at column feels safe — nothing is ever really gone, recovery is one UPDATE away. It also quietly breaks every query you didn’t remember to filter, and that’s the trade you’re actually making.

What soft delete buys you

ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;

-- "delete"
UPDATE orders SET deleted_at = now() WHERE id = $1;

-- recover
UPDATE orders SET deleted_at = NULL WHERE id = $1;

Recovery from user error (“I didn’t mean to delete that”) without restoring from a backup. An audit trail — you know something existed and when it stopped being active. Referential integrity stays intact, since the row is still there for any foreign key pointing at it.

What it costs you

Every single query against that table now needs WHERE deleted_at IS NULL, forever, in every place that touches it. Miss one, and a “deleted” row shows up somewhere it shouldn’t — in a report, in a count, in a join result a user can see.

-- the query you'll forget to update six months from now
SELECT count(*) FROM orders WHERE customer_id = $1;
-- should be:
SELECT count(*) FROM orders WHERE customer_id = $1 AND deleted_at IS NULL;

This isn’t a one-time cost. It’s an ongoing tax on every query, every join, every new feature that touches the table, for the life of the schema. New engineers on the team won’t know the convention exists until they ship the bug that reveals it.

Unique constraints stop working the way you expect

-- this constraint now blocks re-registering an email after a soft delete,
-- because the "deleted" row still holds the unique value
CREATE TABLE users (
  email TEXT UNIQUE NOT NULL,
  deleted_at TIMESTAMPTZ
);

A user deletes their account, then tries to sign up again with the same email — and hits a unique constraint violation, because the old row is still sitting there. Fix requires a partial index, not a plain unique constraint:

CREATE UNIQUE INDEX users_email_unique ON users (email) WHERE deleted_at IS NULL;

This works, but it’s a thing you have to know to do, on every column that needs uniqueness among non-deleted rows.

Where soft delete is actually worth it

• Financial records, where you’re often legally required to retain data rather than actually erase it — “soft delete” here isn’t really about undo, it’s about visibility while retaining the row.
• User-facing “trash” features, like an email client’s trash folder, where recovery is an expected product behavior, not an emergency.
• Anything with an audit or compliance requirement to show what existed, even if it’s no longer active.

Where a real delete (with a backup) is simpler and safer

• Anything without a specific recovery or compliance requirement. If nobody’s asked for “undo delete” as a feature, you’re paying the query tax for a capability nobody uses.
• High-churn tables (sessions, temp tokens, cart items) where soft-deleted rows just accumulate as dead weight nobody queries.

For most of these, a real DELETE plus a database backup with point-in-time recovery gives you the same “can undo a mistake” safety net, without every query in the codebase needing to remember a filter.

The middle ground

Boiled down to one decision:

flowchart TD
    A{"Legally required to retain data,\nor need a user-facing trash?"} -->|Yes| B["Soft delete: deleted_at column"]
    A -->|No| C{"High-churn table\n(sessions, tokens, cart items)?"}
    C -->|Yes| D[Hard delete + backups]
    C -->|No| E["Has anyone actually asked\nfor undo delete?"]
    E -->|Yes| B
    E -->|No| D
  

If you’re not sure, default to hard delete and rely on backups. Add soft delete deliberately, per table, when a specific feature needs it — not as a blanket schema convention applied everywhere “just in case.” The tables that actually need undo are usually a small minority of your schema, not all of it.