Why soft deletes are more complicated than they seem

Deleting data seems like one of the simplest operations in an application. A user clicks "Delete", the row disappears from the database, and that's it.

In practice, many applications choose another approach: soft delete. Instead of actually deleting the row, it is kept in the database and simply marked as deleted, often with a deleted_at column or an is_deleted boolean.

On paper, the idea is appealing. It allows data to be restored, keeps a history, prevents accidental deletions, and avoids ending up with references to objects that no longer exist.

The problem is that a simple additional field often ends up complicating the entire application. Let's go through the different issues.

A principle that is actually very simple

A typical table could look like this:

SQL

users

-----

id

email

name

deleted_at

When a user is deleted, we no longer use a DELETE, but simply:

SQL

UPDATE users

SET deleted_at = NOW()

WHERE id = 42;

To restore the data, you just set deleted_at back to NULL. Put like that, it's hard to see where the problem is.

Every query now has to think about deleted data

This is often the first difficulty. Before, a query was simply:

SQL

SELECT * FROM users;

With soft deletes, it almost always becomes:

SQL

SELECT *

FROM users

WHERE deleted_at IS NULL;

This filter has to be added everywhere: lists, searches, statistics, joins, exports… And you only need to forget one query for a deleted user to reappear in the application. The hardest part isn't actually writing the condition. It's remembering that it's needed. I've already forgotten one myself. Incomprehensible for the user, who suddenly found themselves looking at data that was supposed to no longer exist...

Uniqueness constraints become less obvious

Let's imagine that the email address has to be unique. If Alice deletes her account, can she create another one with the same address?

With a standard unique constraint, the answer is no: the row still exists! You then have to choose between several behaviors:

• prevent any reuse;
• automatically restore the old account;
• allow a new user with the same address.

Depending on the decision, the database structure can become much more complex. Some databases such as PostgreSQL, for example, allow partial unique indexes, which are very useful for keeping the uniqueness constraint only on active data (as well as limiting the size of the indexes). Others require different solutions. A simple deletion then becomes a real modeling problem.

Relationships don't really disappear anymore

Foreign keys continue to point to rows that still exist. Take an order linked to a user. If that user is logically deleted, should their name still be displayed in the history? Should all their orders be hidden? Or should we only prevent new ones?

The answer depends entirely on the business requirements. Soft delete avoids breaking relationships, but it also forces you to define the behavior of all related data. A query that needs to display history may sometimes have to include deleted data, while another must absolutely exclude it.

Performance can also change

At first, a table mostly contains active data. A few years later, it may contain several million rows, a large part of which have been logically deleted.

The table and some indexes then grow with data that is almost never used. Queries can also become harder to optimize, especially when deleted data always has to be filtered out. Partial indexes can limit part of the problem when they are available.

The funny paradox is that we chose soft delete so we wouldn't lose anything, but we sometimes end up paying for that choice on every read.

Restoring data isn't always straightforward

We often imagine that restoring data simply means setting deleted_at back to NULL.

In reality, a lot of things may have changed in the meantime. The email address may now be used by another account. Related data may have been permanently deleted. Some business rules may also have changed.

Restoring the data can then become a potentially much more complex operation than a simple UPDATE.

Legal requirements make things even more complicated

Keeping deleted data isn't always compatible with what users expect. In some cases, a user simply wants to hide their account. In others, they request the actual deletion of their personal data.

These are two different needs.

Soft delete works very well for the first one, but not necessarily for the second. Many applications therefore combine two mechanisms: an immediate logical deletion, followed by a physical deletion after a retention period or when it is actually necessary.

Soft delete remains a trade-off

Soft delete isn't a bad practice. On the contrary, it is often extremely useful when data needs to be restored or a history needs to be kept.

But it shouldn't be seen as just a deleted_at field. It changes queries, indexes, uniqueness constraints, relationships between data, and sometimes even the application's business rules.

As with many technical choices, the real question isn't "Can we use soft deletes?", but rather "Is their complexity justified by the need to keep this data?". It's up to you to make the right choice 🙂