Explain Codes LogoExplain Codes Logo

Are soft deletes a good idea?

sql
data-integrity
database-performance
best-practices
Anton ShumikhinbyAnton Shumikhin·Nov 30, 2024
⚡TLDR

Soft deletes — marking records as deleted without physically removing them — can be a lifeline in terms of data recovery, audit trail maintenance, and relation preservation. However, they do invite the woes of performance degradation and complex query formulation:

/* This isn't Thanos 'snap', it's a soft dusting */
ALTER TABLE your_table ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

Flag to delete (but not actually delete delete):

/* Record, you've been evicted from DataLand. Kindly pack your tuple. */
UPDATE your_table SET is_deleted = TRUE WHERE id = your_id;

Ignore the metaphorically 'deleted' records:

/* Hey, SQL: Can we pretend that records marked deleted don't exist? */
SELECT * FROM your_table WHERE is_deleted = FALSE;

Ensure you weigh these considerations against your needs for data safety and retrievability.

Avoiding common traps with soft deletes

Less is more: Encapsulate with views and functions

Bypass having to include WHERE is_deleted = FALSE each time by creating a view or function that inherently excludes deleted records:

/* View through the rose-tinted glasses, where deleted records are a thing of past */
CREATE VIEW active_records AS SELECT * FROM your_table WHERE is_deleted = FALSE;

Method to the madness: Two-step deletion

Prevent oopsie-daisy data losses and make deletion a two-step dance — soft delete followed by a hard delete after due review.

Anatomy of performance: Archive data

Archiving aged-off records, rather than keeping all in the main table, can keep the database performance in the green:

/* Into the cold storage you go, old chap! */
INSERT INTO archive_table SELECT * FROM your_table WHERE is_deleted = TRUE;

The data integrity hypotenuse

Fend off foreign key constraints with well-managed cascades on soft delete operations.

Deep dive into soft delete considerations

Say 'no' to boolean

Steer clear of is_deleted; use deleted_date instead. It not only marks absence but also tells when.

Law and order: Know your obligations

Ensure your data treatment complies with all legal policies, like GDPR, which mandates "right to be forgotten".

Spring cleaning: Scrub your database

Removing logically deleted records from time to time keeps your database neat and sprightly.

Plan for the marathon, not the sprint

Consider long-term implications of soft deletes on the database. Bigger databases might make queries sluggish.

Find the sweet spot: Balance data recovery and integrity

Strike a balance between restoration ease and overall data accuracy–don't sacrifice one for the other.

explain-codes / Sql / Are soft deletes a good idea?
Linked

What is the best way to implement soft deletion?

Best way to delete millions of rows by ID

Delete data from all tables in MYSQL

Delete SQL rows where IDs do not have a match from another table

How to delete multiple rows in SQL where id = (x to y)

Delete all rows in a table based on another table

Delete from two tables in one query

Avoiding common traps with soft deletesDeep dive into soft delete considerations
Linked

What is the best way to implement soft deletion?

Best way to delete millions of rows by ID

Delete data from all tables in MYSQL

Delete SQL rows where IDs do not have a match from another table

How to delete multiple rows in SQL where id = (x to y)

Delete all rows in a table based on another table

Delete from two tables in one query