Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Avoiding the soft delete anti-pattern

cultured.systems

Post reply on HN
Print view View original

11–20 of 61 posts

Re: Avoiding the soft delete anti-pattern

#11

Post by mrkeen »

Reefersleep wrote: post #4

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

> Can't get much more explicit than that

If you want to preserve history (not just the special case of deletion) you'd also need to move 'updated' entities as well.

The article isn't just pointing out that a 'deleted' column is a hassle, it's also pointing out it's insufficient for preserving history.

Re: Avoiding the soft delete anti-pattern

#12

Post by wruza »

culturedsystems wrote: post #8
wruza wrote: post #6

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. 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…

"Soft deletion isn’t a blanket rule" That's right, I think it's really "soft deletion as a blanket rule" which is the anti-pattern; soft-deletion is one option which (IMO) is used too often without thinking about specifically what you need to achieve. If soft-deletion is used as a blanket rule, you're more likely to want to try and abstract it away via an ORM or similar, which tends to be fragile (I agree views aren'…

Yeah, I also think that it should be a part of business requirements rather than a purely technological decision that applies everywhere. A developer shouldn’t be asking “do we need soft deletion” in vacuum, because it’s a decision to be made higher up where workflows live.

It all probably stems from a rule that as a developer you must never [force/allow anyone to] lose expensive input or make it hard to recover. So ORM and platform developers try to ensure that no one really deletes anything, as a presumably simplest solution. It’s okayish sometimes, but is a bad responsibilities design really. If data is valuable, then its owner is the most responsible by definition. So the actual responsibility should be moved there, with explicitness and reasonable safety nets where needed. Otherwise a developer has to get defensive on all fronts which comes with additional costs for both them and a user, for reasons not well defined.

Re: Avoiding the soft delete anti-pattern

#13

Post by kthejoker2 »

The main problem I have is the article takes a performance/devlopment lens to soft deletes, and only pays lip service to the objectives you're trading off performance for with soft deletes ... namely data retention / disaster recovery / audit requirements.

* availability / recovery - soft deletes provide the best RPO/RTO in archival / lifecycle planning

* auditability / compliance - much easier to achieve with 1 system than 2 or 3 systems

* security - see above

You certainly can achieve these objectives with CDC / snapshotting / warehousing / archival practices, but the soft delete pattern has its place at the application layer in spite of performance which is only begrudgingly acknowledged in the article.

Re: Avoiding the soft delete anti-pattern

#15

Post by nivertech »

“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.”

— Greenspun's tenth rule of programming

Re: Avoiding the soft delete anti-pattern

#16

Post by afiori »

mrkeen wrote: post #11
Reefersleep wrote: post #4

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

> Can't get much more explicit than that If you want to preserve history (not just the special case of deletion) you'd also need to move 'updated' entities as well. The article isn't just pointing out that a 'deleted' column is a hassle, it's also pointing out it's insufficient for preserving history.

These options are ways to preserve entities not histories.

For example you might not care to record the 20 different names/birthdays a user changed but you might care to remember that the user existed.

Re: Avoiding the soft delete anti-pattern

#17

Post by afiori »

magicalhippo wrote: post #7

We make a B2B application that's installed on-prem for a lot of customers. 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. 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 t…

If the thing you want to do with the deleted data is mostly ad-hoc support queries (that is you are keeping the current workflow, not adding new UI and functionality for pervasive restores) then I feel like moving deleted entries to a new "shadow table" with the same schema (eg subscription might have a soft_deleted_subscription shadow) might work well for you.

I have never implemented this, but I feel like it would work well (including not having to specify a deleted_date IS NULL on every query)

Re: Avoiding the soft delete anti-pattern

#18

Post by magicalhippo »

afiori wrote: post #17
magicalhippo wrote: post #7

We make a B2B application that's installed on-prem for a lot of customers. 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. 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 t…

If the thing you want to do with the deleted data is mostly ad-hoc support queries (that is you are keeping the current workflow, not adding new UI and functionality for pervasive restores) then I feel like moving deleted entries to a new "shadow table" with the same schema (eg subscription might have a soft_deleted_subscription shadow) might work well for you. I have never implemented this, but I feel like it would…

That is a possibility. But all our "soft-delete targets" have at least 2-3 levels of child tables, it's never just one table. So that complicates matters.

For example, it could be the user deletes a customer entry in our system, the customer has contacts, and each contact has multiple contact methods say. There are many other child tables for a customer, like delivery addresses and official id numbers and so on, this was just an example.

And yes, user wants to use this data as before it was deleted. So has to go back into the same tables.

On the bright side, we don't have too many foreign keys to "associated data". For example, orders with that customer id would not change, just point to a non-existing customer. So after restore that all works fine.

Re: Avoiding the soft delete anti-pattern

#19

Post by kylehotchkiss »

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?

Re: Avoiding the soft delete anti-pattern

#20

Post by EdwardDiego »

Sigh, as always in tech, the answer to "is soft delete appropriate" is - "it depends".

Do you want to support reversible deletion in the business logic sense? Soft delete is a trivial way to do this.

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.

Of course at large entity counts, soft delete can impact performance, but that's usually a rather large entity count, and then you can start considering approaches like a delete log or denormalisation.

Afraid of throwing away data you worry you might need later but don'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.

Post reply on HN