What is the best way to implement soft deletion?

sql

soft-deletion

database-views

partitioning

byAlex Kataev·Nov 27, 2024

Soft deletion in SQL is usually achieved by adding a deleted_at TIMESTAMP column to your table. Don't erase the row, but stamp it with the current timestamp when it's deleted. For typical interactions with active records, just ignore those stamped as deleted. Here's how:

-- Alter your table to add the deleted_at TIMESTAMP column:
ALTER TABLE your_table ADD COLUMN deleted_at TIMESTAMP;

-- Lightly ding the record with the timestamp instead of fully smashing it:
UPDATE your_table SET deleted_at = NOW() WHERE id = your_record_id;

-- Feeding time! But we don't eat deleted ones:
SELECT * FROM your_table WHERE deleted_at IS NULL;

This method is effortless and efficient, maintaining row integrity, smoothing the transition of record restoration, and highlighting deletion times.

The `deleted_at` strategy: Advantages and practicalities

Maintenance: Easy audit trails and fewer headaches

Adding a deleted_at column gives you not only control over visibility – you've also just rolled your own audit trail system for your data. The moment of deletion is stamped for posterity, providing you with free metadata. Maintenance becomes smoother, too: queries that already exclude deleted entries reduce the chances of headaches caused by accidental data handling.

Querying made easy: Database Views to the rescue

To streamline your work, use database views to pre-filter deleted records. Here's how you make a view that only includes active records:

-- Creating a VIP club for active records:
CREATE VIEW active_records AS
SELECT * FROM your_table WHERE deleted_at IS NULL;

Now you have a "pure" table with soft-deleted records tucked under the rug. So relax and write some naked-eye queries:

-- Life's a lot easier when you ONLY deal with the VIPs:
SELECT * FROM active_records;

Systematic Agent: Trigger-based updating

Introducing a systematic trigger update system can add a greater level of consistency to your database change tracking. Let the triggers take care of real-time updates to your Audit database - recording all modifications, including those soft deletes while you sip your coffee in peace.

Performance jigsaw: Partitioning for efficient querying

Considering the performance angle is crucial when setting up soft deletion. List partitioning, especially in databases like Oracle, effectively separates active and deleted records into different partitions — The magic trick of "partition pruning," ensures that your queries scan only relevant partitions. In larger datasets, this turbocharges your performance.

Going deeper: Advanced tactics for Soft Deletion

User-oriented Soft Deletion: Dealing with complex scenarios

In an environment with multiple users, soft deletion can be more selective and adherent to user roles. You can choose who gets to see, or restore, those soft-deleted records, adding a thick layer of security to your sensitive data.

Providing robust Metadata with additional columns

While deleted_at handles the "when," adding columns like deleted_by can record "who" did the deletion, and deletion_reason can capture "why" it happened. These enrich your metadata, painting a richer story of your data.

Soft Deletion: The Bulk Update, High Volume challenge

Achieving scalable soft deletion could be challenging in high-volume environments. Using bulk update strategies and carefully managing indexing could mitigate this. Remember, bulky updates could lock tables or, murk up the efficiency of operations, if indexing isn't well planned. Stick to regular index grooming and smart partitioning for a performance that scales.

explain-codes / Sql / What is the best way to implement soft deletion?

Linked

Are soft deletes a good idea?



Finding duplicate values in a SQL table



Delete all rows in a table based on another table



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



Sql DELETE with INNER JOIN

