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

Re: Soft deletion probably isn't worth it

#151

Post by JohnBooty » Tue, Jul 19, 2022, 8:55 PM UTC

I've been a software dev since the 90s and at this point, I've learned to basically do things like audit trails and soft deletion by default, unless there's some reason not to.

Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. It helps the business, it helps you as developer by giving you debug information as well as helping you to cover your ass when you are blamed for some data loss bug that was really user error.

Soft deletion has obvious drawbacks but is usually far less work than implementing equivalent functionality out-of-stream, with verbose logging or some such.

Retrofitting your app and adding soft deletion and audit trails after the fact is usually an order of magnitude more work. Can always add it pre-launch and leave it turned off.

If performance is a concern, this is usually something that can be mitigated. You can e.g. have a reaper job that runs daily and hard-deletes everything that was soft-deleted more than n days ago, or whatever.

Re: Soft deletion probably isn't worth it

#152

Post by banashark » Tue, Jul 19, 2022, 8:56 PM UTC

cpursley wrote: post #136

enepture wrote: post #124

Earlier quoted context omitted.

As an FYI using a tool like DBT solves this problem. As someone who was not a data engineer I was not familiar, there were tools like this

I have this problem. What is DBT?

I believe this: https://docs.getdbt.com/docs/introduction

Re: Soft deletion probably isn't worth it

#153

Post by PaulMest » Tue, Jul 19, 2022, 8:56 PM UTC

enepture wrote: post #124

semiquaver wrote: post #73

Earlier quoted context omitted.

The main problem with views for this use case in practice is that they ossify your schema. Views and matviews are effectively a dependency tree, and many common types of schema evolution become substantially more difficult when the system forces you to wrap your DDL in a series of view drop/recreation steps. This is merely annoying when dealing with regular views because recreating even a large number of views is fas…

As an FYI using a tool like DBT solves this problem. As someone who was not a data engineer I was not familiar, there were tools like this

In case you're not familiar with DBT, it helps with the "transform" in ELT https://docs.getdbt.com/docs/introduction.

Re: Soft deletion probably isn't worth it

#154

Post by pg_1234 » Tue, Jul 19, 2022, 8:57 PM UTC

JohnBooty wrote: post #151

I've been a software dev since the 90s and at this point, I've learned to basically do things like audit trails and soft deletion by default, unless there's some reason not to. Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. It helps the business, it helps you as developer by giving you debug information as well as helping you to c…

This 100%

Re: Soft deletion probably isn't worth it

#155

Post by jtwebman » Tue, Jul 19, 2022, 8:57 PM UTC

The bigger reason to use soft deletes is to keep history. Just because someone does not access doesn't mean we should report on the things they did months ago.

Re: Soft deletion probably isn't worth it

#156

Post by nicoburns » Tue, Jul 19, 2022, 8:57 PM UTC

JohnBooty wrote: post #151

I've been a software dev since the 90s and at this point, I've learned to basically do things like audit trails and soft deletion by default, unless there's some reason not to. Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. It helps the business, it helps you as developer by giving you debug information as well as helping you to c…

+1 on audit trails. And one should always store audit trails in machine readable format. That way you can not only manually inspect what happened, but you can query it too (and reconstruct the entire state as it existed in the past if necessary).

Re: Soft deletion probably isn't worth it

#157

Post by scott_w » Tue, Jul 19, 2022, 8:57 PM UTC

jiggywiggy wrote: post #63

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.

Ha, and then there is the opposite regulation that you have to delete user data.

Not quite. GDPR (and equivalents) have clear escape hatches to allow you to store data if you have good reason (even if the data subject requests its removal).

Invoices, from the article, is a great example. That record must remain unchanged in most financial regulations. I’d wager a customer sending a deletion request for invoices will be met with raucous laughter from the legal and finance teams.

Re: Soft deletion probably isn't worth it

#158

Post by remram » Tue, Jul 19, 2022, 8:58 PM UTC

SoftTalker wrote: post #106

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…

A problem (unless something has changed, my context is Oracle from some time ago) is that NULL values are not indexed. So the "WHERE deleted_at IS NULL" could trigger a full table scan. It can also cause row migration when the NULL value is eventually filled in. Unless you explicitly need the deleted date, it's probably better to use a non-nullable Y/N for this.

It seems Oracle does it although there is a special syntax to opt-in. That seems wild. I am not aware of another DBMS having that limitation though.

Re: Soft deletion probably isn't worth it

#159

Post by evanelias » Tue, Jul 19, 2022, 8:59 PM UTC

tfigment wrote: post #72

vyrotek wrote: post #45

I've found SQL Server Temporal Tables are a good alternative to get the benefits of soft-deletes without some of the drawbacks. https://docs.microsoft.com/en-us/sql/relational-databases/ta...

Mysql also has this now. I've wanted to rewrite out apps to use it but haven't gotten around to it. Postgres has it as an addon but feels like it wouldn't work for us until its first class supported.

MariaDB has this -- called system-versioned tables -- but MySQL actually does not. Although they share a common lineage, MySQL and MariaDB are somewhat distinct databases at this point, with each one having a number of features that the other lacks.

Re: Soft deletion probably isn't worth it

#160

Post by justin_oaks » Tue, Jul 19, 2022, 8:59 PM UTC

For those who are expressing favor with soft deletes, do you default to soft deletes on every table unless you know you won't need them? Or do you only apply them where you know you'll need them?

I think people arguing for and against soft deletes both understand that there are cases where you want to use them and when you don't.