Soft Updates

...and Soft Deletes

Published on Saturday, October 10, 2020

Hard Delete - An operation in which data is erased from the database
Soft Delete - An operation in which a flag is used to mark data as unusable, without erasing the data itself from the database.

In many business applications, you are not permitted to delete a record from the database, but still have to find some way to hide it from the user when the data is inactive or no longer needed. For traditional relational databases, the most common way is to add additional columns like IsDeleted (Deleted, Active, Archived, etc.) and use it as a flag when you no longer need a row. If required, it is possible to add metadata like who did the deleting and DateTime.

Soft Update - an operation in which the current state of the data is marked as unusable, and a new state is appended to the database, without erasing the old information itself from the database.

Much less common is a soft update when you need to keep the historical data of data modifications. There are many ways to achieve this. The most common one is an append-only pattern or having a separate table for historical data.

Append-only solutions have many benefits, including data consistency, rollback, and in some cases, performance. The simplest append-only data structure is the log file - every transaction that happens is logged in.

Solution based on historical data is not immutable, but before every change occurs, data is first copied to a new location, and old data is overwritten.