Avoiding the soft delete anti-pattern

It’s a great article exploring the idea, but the premise and arguments leading to it are somewhat weak, imo. First, views aren’t “fragile”. I may be wrong here, but it feels like TA tries to squeeze that along with some abstract-ORM issues.<p>Second, “anti-pattern” is a very technical rating of this phenomenon. Business logic and its databases may contain data that may, may not, or can never be viewed as deletable, at the same time (and in the same table). Soft deletion isn’t a blanket rule. For example, you hard-delete generated records, cause they are a cached projection of higher level data. You may hard-delete drafts like you trash your notes, but you never hard-delete a signed contract&#x2F;declaration.

The main problem I have is the article takes a performance&#x2F;devlopment lens to soft deletes, and only pays lip service to the objectives you&#x27;re trading off performance for with soft deletes ... namely data retention &#x2F; disaster recovery &#x2F; audit requirements.<p>* availability &#x2F; recovery - soft deletes provide the best RPO&#x2F;RTO in archival &#x2F; lifecycle planning<p>* auditability &#x2F; compliance - much easier to achieve with 1 system than 2 or 3 systems<p>* security - see above<p>You certainly can achieve these objectives with CDC &#x2F; snapshotting &#x2F; warehousing &#x2F; archival practices, but the soft delete pattern has its place at the application layer <i>in spite of performance</i> which is only begrudgingly acknowledged in the article.

How about a separate, schema-wise identical &quot;deleted_x&quot; table that you &quot;move&quot; deleted entities to? Can&#x27;t get much more explicit than that, and still enables whatever joins you&#x27;d like on historical deleted data.

So we call approaches &#x27;anti-patterns&#x27; now if they aren&#x27;t universally suitable?

Sigh, as always in tech, the answer to &quot;is soft delete appropriate&quot; is - &quot;it depends&quot;.<p>Do you want to support reversible deletion in the business logic sense? Soft delete is a trivial way to do this.<p>Do you want to support business logic deletion in a normalised schema while retaining other records that relate to that entity for auditing requirements? Probably worth looking into soft delete first.<p>Of course at large entity counts, soft delete can impact performance, but that&#x27;s usually a rather large entity count, and then you can start considering approaches like a delete log or denormalisation.<p>Afraid of throwing away data you worry you might need later but don&#x27;t have an existing use case for right now? There are better ways to data hoard, and you should regularly analyse how often that hoarded data is actually accessed before your data lake turns into a data swamp.

I am surprised the article didn’t mention the obvious fix soft deletes potential performance issues - have a job run regularly that archives soft deleted data older than X units of time.<p>This allows for undoing a soft delete and gets rid of soft deleted rows eventually.

“Soft-Delete pattern (deleted_at column) or any other pattern adding $event_at column to a DB table, contains an ad hoc, informally-specified, bug-ridden, slow implementation of half of Event Sourcing.”<p>— Greenspun&#x27;s tenth rule of programming

Soft deletion is not an anti-pattern. In real software you need to have possibility to delete items but they still need to be exist in historical items because of analytics, historical data integrity etc.<p>Soft delete is the only way to make this possible without horrible kludges.

No one ever got fired for implementing soft delete.

I remember how easy it used to be to drop an entire firestore collection with one click. Yes, when deleting your production data is one click away (the delete button was right next to the filter button!) it’s very natural to be afraid. Thankfully Google has improved a lot of these interfaces with a deletion confirmation prompt but can you see where the fear originates?

Isn&#x27;t soft-delete just a variant of having a lifecycle? The article tries to distinguish it by saying that the lifecycle pattern is implemented at the app-layer instead of the database layer, but isn&#x27;t their criticism of soft-delete that the app-layer has to deal with it?<p>Maybe a better recommendation is to give guidelines for implementing soft-delete?

The choices are:<p>A. Move deleted data to (an)other table(s): users, deleted_users<p>B. Read from a scope, view, or materialized view but update a raw table: deleted bool or deleted_at datetime<p>C. Sprinkle conditionals everywhere live data is desired: deleted bool or deleted_at datetime<p>There is no one &quot;the way&quot; for all use-cases.

For some reasons I was involved a lot with databases in the past 5 years (more than usual) and I don&#x27;t remember to meet soft-delete implemented anywhere, but lifecycle is used almost everywhere. I think that soft delete may be an indicator of bad design.

Just add on delete trigger to store whole row as json in generic archive table and move on.

We make a B2B application that&#x27;s installed on-prem for a lot of customers.<p>We do hard deletes on most things, mainly due to legacy reasons, and almost every week we get a request to restore data that a user deleted but later realized they needed.<p>And quite often the user realizes this after a week or more, at which point the only option is for the user to ask their IT to restore the DB from backup so we can extract the data from there.<p>So adding soft deletes to the tables the users commonly do mistaken deletes from is something we&#x27;re planning on doing.<p>I don&#x27;t see the alternatives given in the article would work for us. For example few of our customers even have a data warehouse. Our current DB doesn&#x27;t support temporal tables, though we are migrating to MSSQL which does, so that might be an option soon. Though unclear how well it works with 3-4 levels of child tables which would also need to be temporal, especially since we need to do hard deletes due to GDPR etc and we have customers who work 24&#x2F;7 so regular downtime is not tolerated. And users will have active locks against these key tables, not sure how that&#x27;ll work out with the schema changes needed for hard deletes.

But what about data overwrites? This is basically the same as deleting data, since information will be destroyed. Using soft delete is a somewhat naive solution if there is no mechanism for restoring overwritten data.

The lengths people will go to, to avoid using an ORM!

The article is mostly nonsense.