Soft deletion probably isn't worth it • Hacker News Board

Re: Soft deletion probably isn't worth it

#91

Post by munk-a » Tue, Jul 19, 2022, 7:43 PM UTC

pbardea wrote: post #49

dafelst wrote: post #20

Views are a simple solution to this problem. Pretty much all moderns RDBMSs support updatable views, so creating views over your tables with a simple WHERE deleted_at IS NULL solves the majority of the author's problems, including (IIRC) foreign key issues, assuming the deletes are done appropriately. I feel like a lot of developers underutilize the capabilities of the massively advanced database engines they code ag…

> assuming the deletes are done appropriately This is one gripe I have with soft-deletion. Since I can no longer rely on ON DELETE CASCADE relationships, I need to re-defined these relationship between objects at the application layer. This gets more and more difficult as relationships between objects increase. If the goal is to keep a history of all records for compliance reasons or "just in case", I tend to prefer…

If we're assuming you're using a view based approach which elides the soft deleted rows automatically then you'll get a lot of these dependent objects correctly updated for free assuming you're pulling them out of the DB with JOINs - SELECT FROM foo JOIN bar (assuming bar is a view into barwithdeleted) will automatically filter out the invalid rows from foo... if you're using this information to populate a CRUD interface it's likely you'll be JOINing bar already to get some metadata for display (like maybe bar.name instead of the surrogate bar.id key you use for joining).

Re: Soft deletion probably isn't worth it

#92

Post by TylerE » Tue, Jul 19, 2022, 7:44 PM UTC

tehbeard wrote: post #67

> Instead, we rolled forward by creating a new app, and helping them copy environment and data from the deleted app to it. So even where soft deletion was theoretically most useful, we still didn’t use it I don't get this statement. You wouldn't have had the env or data without soft delete? You did use it! I would say, soft delete isn't a tick the box and done solution as many ORMs make it. You need to consider the d…

> mysql interprets null as not clashing with other nulls

Which is correct per SQL. Null is NaN, not zero (or negative infinity).

Re: Soft deletion probably isn't worth it

#93

Post by simcop2387 » Tue, Jul 19, 2022, 7:47 PM UTC

kukx wrote: post #76

pbardea wrote: post #49

Earlier quoted context omitted.

> assuming the deletes are done appropriately This is one gripe I have with soft-deletion. Since I can no longer rely on ON DELETE CASCADE relationships, I need to re-defined these relationship between objects at the application layer. This gets more and more difficult as relationships between objects increase. If the goal is to keep a history of all records for compliance reasons or "just in case", I tend to prefer…

Is not there any attempt to improve the soft deletion at the engine/SQL level? I can see it as a possible feature request.

There's the idea of temporal tables, https://pgxn.org/dist/temporal_tables/

It's not a standard (I think) but it'd let you do a cascading delete and then be able to go and look at the old objects as they were at time of deletion too.

You'd need to do things very differently to show a list of deleted objects though.

Re: Soft deletion probably isn't worth it

#94

Post by munk-a » Tue, Jul 19, 2022, 7:47 PM UTC

znpy wrote: post #60

GartzenDeHaes wrote: post #23

Personally I like no delete designs, which give you a full audit history of changes. This is similar to generally accepted accounting principles. https://en.wikipedia.org/wiki/Generally_Accepted_Accounting_...

Your taste in database design is probably nor gdpr compliant, i hope you don’t work in the eu.

It's surprising but the EU tends to be one of the most stringent regions to work in both when it comes to totally permanently deleting things and when it comes to never ever deleting things - as with anything like this where there is a debate (rather than a settled best practice) there are some times when soft deletion is appropriate and necessary (i.e. to adhere to log retention requirements common in the EU) and some times when it's unnecessary... and the occasional fun time when it's both necessary to support soft deletes and hard deletes - when logs need to be retained for auditing purposes but also when some users can force a hard delete (leading to that data either being purged or moved to an archive storage if it's still needed for auditing purposes).

The world is almost never as simple as it seems.

Re: Soft deletion probably isn't worth it

#95

Post by nirvdrum » Tue, Jul 19, 2022, 7:49 PM UTC

giantg2 wrote: post #3

"The concept behind soft deletion is to make deletion safer, and reversible." That's one part. The other part is that in many industries you have regulatory data retention and audit requirements. This is arguably the most valuable and common reason to perform Logical deletes.

My experience at a few start-ups has been that account deletion just isn't prioritized. It's not a focus when building an MVP. If the application ever gains traction, everyone is then terrified they'll accidentally delete customer data that they never delete anything. It's a shame. As a user, when I delete my account or data in my account, I want you to permanently delete it, not keep it around and just make inaccessible to me.

Re: Soft deletion probably isn't worth it

#96

Post by yladiz » Tue, Jul 19, 2022, 7:50 PM UTC

munk-a wrote: post #84

yladiz wrote: post #66

Earlier quoted context omitted.

But, assuming you don't really need the data, why make your queries more complex and instead just actually delete the data?

This is a really scenario specific question - sometimes it's needed, sometimes it isn't. At my shop we have customers that will suspend their account but our sales team is pretty damn awesome so usually they end up renewing after going a while without our product - so being able to easily restore a large swath of former customers with all their permissions and preferences intact with a simple click of a button is a h…

If I was a tech lead and also wanted to advocate for hard deletion, I would ask the question for this scenario: "What's the cost of keeping all this data unnecessarily, modifying most queries to filter for deleted data, and dealing with other various consequences of soft deletion, and how does this cost compare with the cost of building a bespoke tool to restore deleted data at a customer's request within a certain time frame and compare with the benefit of a customer being able to restore their data at the 'click of a button'?".

Having dealt with systems that have hundreds of millions of records or more, many of which reference deleted data and are therefore useless, I lean towards hard deletion more and more and on the off chance that deleted data really needs to be recovered you build a separate system/infrastructure to support that, rather than building your _entire_ system around the small likelihood you really need to restore it.

Re: Soft deletion probably isn't worth it

#97

Post by croes » Tue, Jul 19, 2022, 7:50 PM UTC

bjourne wrote: post #57

munk-a wrote: post #24

I just wanted to touch on the fact that eliding soft-deleted rows from queries is really, really easy - this article makes it out to be a constant headache but here's my suggested approach. ALTER TABLE blah ADD COLUMN deleted_at NULL TIMESTAMP; ALTER TABLE blah RENAME TO blahwithdeleted; CREATE VIEW blah (SELECT * FROM blahwithdeleted WHERE deleted_at IS NULL); And thus your entire application just needs to keep SELE…

This is not a solution. It introduces a leaky abstraction which sooner or later will lead to errors. Sure, all code you write will access the view and not the table. But how can you ensure all other code in the organisation uses the view? Perhaps you add some access control to the table so that only authorized users can read directly from it, but that's even more technical overhead. Then you have foreign keys. If you…

If you mention the costs of reporting error you should consider the costs of wrongful hard delete too.

And reports are developed and tested before they are used for crucial purposes.

Re: Soft deletion probably isn't worth it

#98

Post by Terr_ » Tue, Jul 19, 2022, 7:50 PM UTC

dfee wrote: post #25

My experience is that soft-deletes are blunt tools bridging the gap between hard deletes and event sourcing (capturing all the changes against the table, in a replay-worthy stream). Event sourcing is hard – because the engineers responsible for setting it up and managing it aren't generally well skilled in this domain (myself included) and there aren't a wealth of great tools helping engineers find their way into the…

> My experience is that soft-deletes are blunt tools bridging the gap between hard deletes and event sourcing

Agreed, sometimes it makes business-sense to implement it, but in the big picture it's still kludgy and not-ideal.

While full-on event-sourcing isn't always the answer, once business-rules prevent you from un-deleting anything there's not much point of having all those dead-rows interspersed in your regular tables.

Re: Soft deletion probably isn't worth it

#99

Post by adrianmsmith » Tue, Jul 19, 2022, 7:51 PM UTC

> Instead of keeping deleted data in the same tables from which it was deleted from, there can be a new relation specifically for storing all deleted data

The disadvantage of this is that if you ever do want to access this "deleted" data, e.g. in admin or compliance tools, you now have to do it in two different ways, one way for the main data and a different way in case the data has been "deleted".

The article asserts you'll never need to "undelete" the data. So they're presenting a solution with that assumption, fair enough. Without that assumption, however, moving the data back from an archive table becomes a pain, and if there are any unique constraints e.g. on username or email address, you'll have a problem if you've moved the data out of the main table and another user has used that username or email address.

Re: Soft deletion probably isn't worth it

#100

Post by jermaustin1 » Tue, Jul 19, 2022, 7:52 PM UTC

chrisshroba wrote: post #75

dafelst wrote: post #20

Views are a simple solution to this problem. Pretty much all moderns RDBMSs support updatable views, so creating views over your tables with a simple WHERE deleted_at IS NULL solves the majority of the author's problems, including (IIRC) foreign key issues, assuming the deletes are done appropriately. I feel like a lot of developers underutilize the capabilities of the massively advanced database engines they code ag…

How would a view solve the foreign key issue? Are you suggesting coding specific deletion triggers into the view such that appropriate foreign keys are "cascade" deleted when a row in the view is deleted?

because when you are getting a child record through a join (in the view), the parent will never have deleted_at set.

Say I have a simple view `select * from foo join bar on foo.foo_id = bar.foo_id where foo.deleted_at is null`

I never have to worry about deleting from bar, because I should never grab a child when the parent is 'deleted'.