Implementing soft delete with minimal impact on performance and code

Question

There are some similar questions on the topic, but they are not really helping me.

I want to implement a soft delete feature like on StackOverflow, where items are not really deleted, but just hidden. I am using a SQL database. Here are 3 options:

• Add a is_deleted boolean field.

  • Advantages: Simple.
  • Disadvantages: No date record. Forces me to add a is_deleted = 0 in every query.

• Add a deleted_date date field. This is set to NULL if it's not deleted.

  • Advantages: Has date.
  • Disadvantages: Still cluttering my queries.

For both of the above

• It will also impact performance because there are all these useless rows. They still have to be maintained in indexes. Also an index on the deleted column won't help when fetching non-deleted (the majority) of the rows. Full table scan is needed.

Another option is to create a separate table to hold deleted items:

• Advantages: Improved performance when querying non-deleted rows. No need to add conditions to my queries on non-deleted rows. Easier on index maintenance.
• Disadvantages: Complexity: Requires data migration for both deletion and undeletion. Need for new tables. Referential integrity is harder to handle.

Is there a better option?

Answer 1

In my opinion, the best way forward, when thinking about scaling and eventual table/database sizes is your third option - a separate table for deleted items. Such a table can eventually be moved to a different database to support scaling.

I believe you have listed the three most common options. As you have seen, each has advantages and disadvantages. Personally, I like taking the longer view on things.