Soft deletes are a schema decision that breaks every query you write afterwards

A customer deletes their account, changes their mind a week later, and tries to sign up again with the same email address. Registration fails with a duplicate key violation on a row that, as far as the product is concerned, does not exist.

Support cannot reproduce it because their test accounts have never been deleted. The engineer who picks it up finds the row, sees deleted_at populated, and understands the problem in about four seconds. Fixing it properly takes considerably longer, because the unique index is one symptom of a decision that has already spread through the schema.

Somebody added a deleted_at TIMESTAMP NULL column two years ago. It was a small change. That is the thing about soft deletes: the change is always small, and the consequences are structural.

The constraint you broke without touching it

A unique index does not know about your application’s opinion on what deleted means. It sees a row with an email in it, and it enforces uniqueness over that email.

CREATE UNIQUE INDEX users_email_key ON users (email);

Once a soft-deleted row exists, that index is holding the address hostage forever.

The Postgres fix is a partial index, and it is genuinely elegant:

DROP INDEX users_email_key;

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

Uniqueness now applies to live rows and ignores deleted ones. The index is also smaller, because it only contains live rows, which is a small bonus on a table where most rows are eventually deleted.

MySQL has no partial indexes, which turns this into a modelling problem rather than an indexing one. The common workaround exploits the fact that NULL values do not collide in a unique index:

ALTER TABLE users
    ADD COLUMN email_live VARCHAR(255)
        GENERATED ALWAYS AS (IF(deleted_at IS NULL, email, NULL)) STORED,
    ADD UNIQUE KEY users_email_live (email_live);

It works. It also means the schema now carries a derived column whose only purpose is to compensate for a constraint the database cannot express, and every engineer who encounters it has to be told why.

Every index gets a passenger

The unique constraint is the loud failure. The quiet one is that all your other indexes are now slightly wrong.

An index on (customer_id, created_at) was built for a query that no longer exists, because the query is now WHERE customer_id = ? AND deleted_at IS NULL ORDER BY created_at. The index can still seek on customer_id, then it has to filter out deleted rows, then sort what remains.

On a table where five percent of rows are deleted, nobody notices. On a table where a background job soft-deletes expired records and nothing ever removes them, the ratio inverts over a couple of years and the index is mostly pointing at rows that will always be discarded.

Partial indexes fix this too, and this is where they earn their keep:

CREATE INDEX idx_orders_customer_live
    ON orders (customer_id, created_at)
    WHERE deleted_at IS NULL;

Now the index only contains live rows. It is smaller, it stays in cache better, and the planner does not have to filter anything out. The catch is that the planner will only use it when it can prove the query predicate implies the index predicate, so the query must contain deleted_at IS NULL literally. Write WHERE deleted_at IS NULL and it matches. Write WHERE COALESCE(deleted_at, 'infinity') > now() and it does not, which is the same sargability trap that keeps planners off indexes generally.

Referential integrity quietly becomes your problem

This is the consequence I think is genuinely underrated, because it moves a guarantee out of the database without announcing it.

A foreign key from order_items.order_id to orders.id guarantees that every line item points at a real order. Soft delete an order and the constraint is perfectly satisfied. The row is still there. The line items still reference it. The database is happy.

Your application now believes those line items belong to nothing. Nothing in the schema expresses that. Whether the invariant holds depends entirely on whether every code path that reads order_items remembers to join back to orders and check the flag.

flowchart TB
    subgraph hard["Hard delete"]
        O1["orders row deleted"] --> FK1["FK enforces:<br/>cascade or restrict"]
        FK1 --> SAFE["Invariant held by the database"]
    end

    subgraph soft["Soft delete"]
        O2["orders.deleted_at set"] --> FK2["FK sees a live row.<br/>Nothing fires."]
        FK2 --> APP["Invariant held by<br/>whoever remembers to filter"]
    end

    style SAFE stroke:#4ade80,stroke-width:3px,color:#fff
    style APP stroke:#ef4444,stroke-width:3px,color:#fff

Cascade behaviour goes with it. ON DELETE CASCADE does nothing for a soft delete, so cleaning up dependent rows becomes application logic that has to be written once per relationship and kept in sync as the schema grows.

The default scope that hides the bug

ORMs offer to solve the filtering problem for you. Rails has default_scope, Hibernate has @Where, and both will silently append the predicate to every query against the entity.

@Entity
@Where(clause = "deleted_at IS NULL")
public class Order { }

This is convenient and I have a hard time recommending it, because the filter becomes invisible at exactly the moments you need to see it.

An engineer writing a query cannot tell from the code that a predicate is being added. A native query bypasses the annotation entirely, so half your data access has the filter and half does not, and which half is which depends on how each repository method happens to be implemented. Admin tooling that legitimately needs to see deleted rows has to fight the framework to turn the scope off. And a JOIN to another entity applies that entity’s scope too, which produces query behaviour that is correct, surprising, and extremely hard to reason about from the calling code.

The invisibility is the problem. A predicate that everyone must remember is bad. A predicate that nobody can see is worse, because it removes the possibility of remembering.

What the requirement usually actually is

Almost every soft delete I have looked at closely was solving one of three different problems that got collapsed into one column.

If the requirement is reversibility, a user restoring from trash within thirty days, then a soft delete is the right tool and a scheduled job should hard delete anything past the window. The table stays bounded, the flag has a defined lifetime, and the behaviour matches what the product promised.

If the requirement is audit, knowing that something was deleted and by whom, then a flag on the live row is a poor fit. It records that a deletion happened and nothing about who did it, when, or what the row looked like beforehand. An append-only audit table answers all of that and keeps the operational table clean.

If the requirement is reporting, keeping data for analytics, then an archive table is the honest version. Move the row out of the operational table into orders_archive on delete. The live table shrinks, every query against it is correct by default with no predicate at all, and the analytics team queries the archive explicitly because they know it exists.

There is a fourth case worth naming: when deletion is not really deletion but a state transition. An order that is cancelled is not deleted, it is cancelled, and modelling it as status = 'cancelled' gives you a state machine you can reason about instead of a boolean that discards the reason.

If you are keeping it

Assuming a soft delete is genuinely correct for the case, the things that make it survivable:

Make the constraint partial, so uniqueness applies to live rows only. This is the failure users actually hit.

Make the hot indexes partial too, and write the predicate literally so the planner can match them.

Give the flag a retention policy and a job that enforces it. An unbounded soft delete column is a table growth problem disguised as a data model, and it interacts badly with pagination that counts rows the user will never see.

Expose the filtering in the query rather than hiding it in a framework annotation, so the next engineer can see what is happening without knowing which ORM feature is in play.

And write down, somewhere findable, which tables use it. The most expensive soft delete bugs I have seen were not in the service that owns the table. They were in a reporting query, or a data pipeline, or a second service reading the same database, written by someone who had no reason to know that a column called deleted_at was load bearing.