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

Re: Soft deletion probably isn't worth it

#491

Post by nivertech » Thu, Jul 21, 2022, 6:49 AM UTC

cal85 wrote: post #449

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…

> Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. In my experience this happens “rarely”, not “always”. It can happen, and in some ultra-rare cases the impact of not being able to recover some data might be huge (company-ending, even), and engineers are good at worrying about such edge cases. That’s why we habe protective measures…

> That’s why we have protective measures like soft deleting and event sourcing

IMO soft deletion is a hack trying to fix problems in CRUD, which is a hack in itself.

CRUD attempts to model everything in the universe as a collections of mutable items, loosely based on RDBMS/SQL.

Event Sourcing is more realistic: it models everything as a append-only logs of immutable events/facts, which preserves both the historical data and, more importantly, the original intent.

Unlike CRUD, Event Sourcing is technology agnostic.

Re: Soft deletion probably isn't worth it

#492

Post by dcow » Thu, Jul 21, 2022, 9:32 AM UTC

heisjustsosmart wrote: post #490

dcow wrote: post #487

Earlier quoted context omitted.

I am not the person you originally replied to but you (again) commented like I was. My comment was about how your comment erroneously targets the wrong person because you thought you were replying to one person when you were replying to another. You did it again in your previous comment hence my serious question.

It doesn't matter, it was advice for all.

It does, actually, matter. Notably because it causes confusion when trying to understand your point, among other things.

Re: Soft deletion probably isn't worth it

#493

Post by tomnipotent » Thu, Jul 21, 2022, 10:12 AM UTC

klysm wrote: post #462

tomnipotent wrote: post #221

Earlier quoted context omitted.

Only if those partitions are on separate storage, otherwise you have the same number of dead tuples/dirty pages.

Not sure that is true, Postgres can rule our entire partitions and scan less stuff I think?

Doesn't help with writes. More dead tuples can mean more pages scanned for the same amount of data.

Re: Soft deletion probably isn't worth it

#494

Post by NoInkling » Thu, Jul 21, 2022, 3:11 PM UTC

BeefySwain wrote: post #137

smallnamespace wrote: post #109

Earlier quoted context omitted.

If you're using PostgreSQL, you can implement cascading soft-deletes yourself. The information schema table holds all foreign key relationships, so one can write a generic procedure that cascades through the fkey graph to soft-delete rows in any related tables.

Could someone take a stab at an example of what this would look like? Sounds really interesting.

Here's an interesting approach using rules: https://evilmartians.com/chronicles/soft-deletion-with-postg...

Re: Soft deletion probably isn't worth it

#495

Post by tornato7 » Fri, Jul 22, 2022, 12:08 AM UTC

salzig wrote: post #405

jrumbut wrote: post #402

Earlier quoted context omitted.

I've long wished for an RDBMS (or perhaps ORM, but I think it would be extra cool at the database level, see below) that does things that don't hyperscale. So many LOB applications (the part of the iceberg under the water) have modestly sized databases with a relatively consistent number of users. The data is small, but often complex, awkwardly structured, highly relational, etc. The challenges these applications fac…

CockroachDB has "AS OF SYSTEM TIME", which even allows you to backup the database for that moment. https://www.cockroachlabs.com/docs/stable/as-of-system-time....

BigQuery has this too, it’s extremely useful.

Re: Soft deletion probably isn't worth it

#496

Post by tornato7 » Fri, Jul 22, 2022, 12:10 AM UTC

JohnBooty wrote: post #370

gmiller123456 wrote: post #172

Earlier quoted context omitted.

The author uses the "no one ever undeleted anything" as the primary justification. I think this is the part they miss. I've never undeleted a user either, but there have been many times I've gone back to look at something. Either a complaint finally gets around to me as to why the user wanted their account deleted (e.g. feature not working) and it helps to figure out why. Or they're returning and want things set up l…

I think this is the part they miss. I've never undeleted a user either, but there have been many times I've gone back to look at something. Yeah. As far as a user-facing "Undelete" button existing or being used... that's very rare in my experience. What's much more common is a user accidentally deletes some data. They deny they made an error. The developers are blamed. You then have to go on a wild goose chase figuri…

This is something that public or private blockchains could be helpful for. Since everything is built on the hash of what came before, you can’t delete something without leaving a trail.

Re: Soft deletion probably isn't worth it

#497

Post by Falkon1313 » Fri, Jul 22, 2022, 12:36 AM UTC

makeitdouble wrote: post #293

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…

> unless there's some reason not to. Yes. To note, with GDPR there's now legal reasons to do so regarding user personal data. That can be the moment the devs realize they actually can't delete the data, because they soft deleted for so long, many relations are now interlocked and the data model needs to be changed to give a starting point to the deletion cascade. My lesson from that was to at least have one test dele…

Good point. That could be tricky when it crosses the boundaries of schema changes and data migrations.

Re: Soft deletion probably isn't worth it

#498

Post by Falkon1313 » Fri, Jul 22, 2022, 1:14 AM UTC

danielrhodes wrote: post #130

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…

Being unable to effectively use foreign key relationships is definitely a downside of using soft deletes. But it's also worth asking if these types of behaviors, which would also include a feature like triggers, really belongs in a database or whether it's better to have at the application level (or at least at a layer above the data layer). I'd argue that ultimately you probably don't want these things at the DB lev…

Presumably you have a schema defining the tables, the columns, and the types at the least, along with things like unique indexes. So you already have data constraints in your database design. And that's where they belong, to ensure the data integrity, since the database's concern is the data.

If you're doing everything as one big table of entity-attribute-value with generic blobs for the values, then yes you'll have to re-implement all the normal constraints (that the database would handle) in your application and do all your data integrity handling there. And you'll also have to duplicate that logic across every application that accesses that database now and in the future.

Data usually lives longer and has more uses than just one program. So I think it's generally better to put integrity constraints in the database, rather than having to re-implement and duplicate that logic several places.

Re: Soft deletion probably isn't worth it

#499

Post by JohnBooty » Fri, Jul 22, 2022, 1:47 AM UTC

icedchai wrote: post #473

dubswithus wrote: post #465

Earlier quoted context omitted.

I would never suggest archiving unless we're hitting some performance limit. Why generate more busy work? Just leave it there and if need be add more indexes and partitions.

well, they were running into performance issues. This table had something like 100 columns and at least 30 indexes already. This table was fat . Many columns, many rows, mostly unused garbage. And several of these "columns" were actually JSON blobs that probably should've been in their own table. But what do I know. I left!

When we talk about best practices, it's probably not too useful to talk about these sorts of "extremely high technical debt situations."

Once you have an existing tirefire, it's a matter of damage control and "what is the least bad way to accomplish new functionality?" and the answer to that will always be unique for each unique tirefire.

Re: Soft deletion probably isn't worth it

#500

Post by Falkon1313 » Fri, Jul 22, 2022, 2:33 AM UTC

doctor_eval wrote: post #422

dubswithus wrote: post #344

Earlier quoted context omitted.

> Using views, stored procedures and other features lets you implement things like soft delete trivially, without it infecting all your application code. That’s great but some of us actually like to write code. Especially Ruby on Rails where soft delete is a breeze if you don’t overthink it and build something the business doesn’t need.

Well, less code means less bugs, but go nuts. I'm just saying that the database is part of the stack. You wouldn't avoid Ruby features "just in case we stop using Ruby" - so why would we avoid using database features? It's up to you how best to assemble the features from your stack. There is a reason we avoided database features in the 90's, which was to avoid database vendor lock-in. This was almost entirely a finan…

The main reason was indeed vendor lock-in. Databases (like compilers) were hugely expensive back before open source got big.

But also some of the features were a bit flaky back then. I remember views in MySQL always caused problems - if you dumped and restored (ex: staging to dev), the special handling of permissions for views and such ("Access denied; you need the SUPER privilege for this operation") would break stuff. So we just didn't use them.^1

However, I'm still in favor of them, and think it's worth finding workarounds for things like that. They're not as flaky now, and people have found workarounds for the remaining flakiness. Nowadays the fear of using them is just tradition from 30 year old problems.

But now we have one or two generations of developers who were taught "Don't use the database features! 'Best practice' is to use it as a dumb datastore and re-implement them all in your application." But they don't know why, so wouldn't even know that those reasons are no longer applicable.

>^1 There exists a shortcoming with the current implementation of views. If a user is granted the basic privileges necessary to create a view (the CREATE VIEW and SELECT privileges), that user cannot call SHOW CREATE VIEW on that object unless the user is also granted the SHOW VIEW privilege.

>That shortcoming can lead to problems backing up a database with mysqldump, which may fail due to insufficient privileges. This problem is described in Bug #22062.

>The workaround to the problem is for the administrator to manually grant the SHOW VIEW privilege to users who are granted CREATE VIEW, since MySQL doesn't grant it implicitly when views are created.

> -- https://dev.mysql.com/doc/refman/8.0/en/view-restrictions.ht...