Soft Delete vs Hard Delete: What Actually Breaks When You Pick Wrong

A user clicks delete on their account, changes their mind ten minutes later, and asks support to undo it. Whether that’s a five-minute fix or an impossible one was decided months earlier, at schema design time, by whether that table hard deletes or soft deletes. Both are legitimate choices. The mistake is picking one as a blanket default and not noticing where it breaks.

Hard delete: the row is actually gone

Hard delete is the SQL you’d write without thinking about it:

DELETE FROM sessions WHERE id = 'abc123';

The row is gone. Every index shrinks by one entry, every constraint behaves exactly as designed, and every query written against the table sees exactly the rows that currently exist, because there’s no other kind of row to accidentally include. This is the right default for anything where “gone” should genuinely mean gone: expired sessions, one-time verification codes, temporary cache entries, and any data you’re under a legal obligation to actually erase rather than merely hide from the application layer.

The cost is that it’s unforgiving. There’s no built-in undo, no audit trail of what existed before the delete, and if another table has a foreign key pointing at the deleted row without ON DELETE CASCADE or ON DELETE SET NULL explicitly handling it, that reference now points at nothing and the query referencing it breaks.

Soft delete: mark it, don’t remove it

Soft delete keeps the row and flags it instead:

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- "delete"
UPDATE users SET deleted_at = now() WHERE id = 42;

-- every read now needs this filter
SELECT * FROM users WHERE id = 42 AND deleted_at IS NULL;

Nothing is actually removed. Undo is a single UPDATE ... SET deleted_at = NULL. An audit log can show exactly when something was deleted and by whom. Anything referencing the row by foreign key, an order that points at a now-discontinued product, keeps resolving to a real row with real data instead of a broken reference or a null.

That convenience comes with an obligation the schema doesn’t enforce for you: every single query against that table now needs to know about deleted_at, and nothing stops a new report, a new join, or a new admin panel from forgetting the filter and quietly surfacing rows the rest of the application treats as gone.

The constraint problem nobody expects the first time

This is the one that catches teams off guard in production, not in code review. A standard unique constraint has no concept of soft-deleted rows:

-- naive: this index doesn't know deleted_at exists
CREATE UNIQUE INDEX users_email_unique ON users (email);

Soft-delete a user with a@example.com, and that email is still occupying the unique index, attached to a row your application no longer shows anywhere. A new signup with the same email hits a constraint violation the user has no way to understand, since as far as they can tell, that email has never been used.

The fix is a partial index, scoped to only the rows that count:

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

Now uniqueness only applies among active rows. A soft-deleted user’s email frees up for reuse the moment the row is marked deleted, exactly matching what the application actually promises the user. This single index type is the most common thing missing from soft-delete implementations that “worked fine in testing” and then failed the first time a real user deleted an account and someone else tried to sign up with the same email.

Query performance and table growth

Two separate costs. First, every query against a soft-deleted table needs deleted_at IS NULL (or the equivalent) in its WHERE clause and in the index supporting it, usually as a partial index like the one above, or the query planner scans rows it should have excluded from consideration entirely. Second, a soft-delete table never shrinks on its own. Rows accumulate forever unless something separately archives or purges old soft-deleted data on a schedule, which most teams don’t set up until the table is already large enough to be a problem.

	Hard delete	Soft delete
Undo	Not built in	Trivial
Audit trail	None by default	Built in
Foreign key references	Break unless handled explicitly	Keep resolving
Unique constraints	Work as written	Need a partial index scoped to non-deleted rows
Every query	Sees only real rows	Needs an explicit filter, every time
Table growth	Bounded by active data	Unbounded without a separate purge job
Legal “right to erasure”	Satisfies it directly	Does not, on its own

Deciding per table, not per project

Treat this as a decision made once per table, based on what that specific data needs, not a global setting applied to every model in your ORM out of habit. Session tokens, password reset codes, and rate-limit counters should hard delete; nothing benefits from keeping them around, and undo isn’t a meaningful concept for a token. User accounts, orders, and content with downstream references or a genuine “restore this” user expectation are strong candidates for soft delete, paired with the partial unique index from the start, not added after the first collision in production.

One case deserves its own hard rule regardless of the rest of your schema: if a table holds personal data subject to a deletion request under a real privacy regulation, soft delete alone doesn’t satisfy it. Marking a row deleted_at while the underlying data still sits in your database and your backups is not the same as erasing it. That data needs an actual hard-delete path, on a defined schedule, separate from whatever soft-delete convenience the rest of the application relies on for undo and audit history. The same due-diligence habit that catches a missing partial index before it hits production is the one worth applying here too: decide the deletion strategy, and its constraints, at schema design time, not after the first user asks for their account back.