To Soft Delete or Not to Soft Delete

8/26/2024

In this blog post I will discuss the pros and cons of soft deleting records in a database versus other approaches you can take. Heat up your pop corn and get your salt ready!

Soft Delete

Soft delete is a technique used to mark a record as deleted without actually deleting it. In many cases, you will something like a IsDeleted column in your table that will be set to true when the record is deleted. This way, the record is not actually deleted from the database, but it is marked as deleted.

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsDeleted { get; set; }
}

As you might imagine in many cases you don't care about "deleted" records in your Web API so often times people will filter out the deleted records likes this:

public async Task<IReadOnlyCollection<User>> GetUsers()
{
    return await _context.Users.Where(u => !u.IsDeleted).ToArrayAsync();
}

Now I choose Entity Framework deliberately because it is a popular ORM and it is easy to work with. As you need to add this Where(u => !u.IsDeleted) to every query you write, it can be a bit cumbersome, so folks resort to Global Query Filters in Entity Framework. This way you can apply the filter globally and you don't have to worry about it in your queries.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().HasQueryFilter(u => !u.IsDeleted);
}

So everytimt you retrieve something from the User table, it will automatically filter out the deleted records. Of course you can override that filter if you want to:

public async Task<IReadOnlyCollection<User>> GetUsersWithDeleted()
{
    return await _context.Users.IgnoreQueryFilters().ToArrayAsync();
}

Popcorn, Grains of Salt and my personal opinion

Now that we have seen how soft delete works, let's give you my opinion. For starters, do I like soft deletes? Often times: No. For many reasons. Let's start why and let's check some alternatives.

Do we need deleted records?

In many cases, you don't need the deleted records. If you don't need them, why keep them in the database? They are just taking up space and making your queries slower. What's not needed, can be deleted. Of course, if you have to keep them, then we have to find different ways of doing so! Often times reporting might want to see deleted entries, but how often are reports generated? Are they part of your daily business? Even if, see "Alternatives" below.

Global Query Filters

Global Query Filters are a blessing and a curse. If you see a specific query, this query will not tell you the whole truth! You have to know that there is a global query filter applied to the query. This can be confusing and can lead to bugs.

Performance

Soft deletes can have a performance impact. If you have a lot of records in your table, the query will have to filter out the deleted records every time you query the table. You might counter this with additional indexes, but this can also have a negative impact on your insert and update performance.

Alternatives

Archive Table or schema

One alternative is to move the deleted records to an archive table or schema. This way you can keep the deleted records, but they are not in the same table as the active records. This way you can keep your active records table clean and fast. You can also create a view that combines the active records with the archived records if you need to see them together in specific circumstances.

Archive Database

Basically like the last approach, but you move the deleted records to a different database. This way you can keep your active records database clean and fast. This can be a bit more cumbersome to set up, but it can be a good solution if you have a lot of deleted records. Especially if you have lots of data, this can be a money saver if your using managed instances on Azure or AWS.

For example Azure offers you an Archive Tier, where even terabytes of data cost way way less than an active SQL database (given that you don't read much data and don't need your archive in seconds available!).

Conclusion

Soft deletes can be a good solution in some cases, but in many cases they are not my preffered way of handling data. Especially if there is no need and/or you are handling a lot of data, you might want to consider other approaches.

Are my EF LINQ to SQL queries safe?

Have you ever asked yourself if your EF LINQ queries are susceptible to SQL injection attacks? Either because you are querying some user data from a text field or directly taking whatever your API hits against the database?

Delete a record without prior loading in Entity Framework

Sometimes you have an Id of an object and want to delete the underlying thing from the database. But it doesn't make sense to load the whole object from the database to memory first. So how can we achieve this quickly?

LiteDB - A .NET embedded NoSQL database

In this article we will have a closer look at LiteDB, a .NET NoSQL Document Store in a single data file. We will discover the advantages of LiteDB and why it is a viable candidate for your next project.

We will also explore what are the differences between a NoSQL and a classical SQL database are and what this has to do with the reminiscent SQL CE or the more modern SQLite database.

Table of Contents