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

Re: Soft deletion probably isn't worth it

#341

Post by dubswithus » Wed, Jul 20, 2022, 1:30 AM UTC

rodw wrote: post #275

madisp wrote: post #229

Earlier quoted context omitted.

I'd argue that the simple `deleted_at IS NULL` check is not broken - unless your product / domain specifically allows and requires scheduled future deletions adding such logic can easily introduce bugs. For example, you could to get the comparison flipped by accident, and if it's only in one place out of many that bug could go unnoticed for a while.

If any non-null value for deleted_at indicates a logical delete, it seems like TIMESTAMP is the wrong data type for that column. I mean I guess I can imagine a scenario where you want to know _when_ a record was logically deleted in addition to a flag that says is_deleted, but maybe that should be two different columns. Or at the very least, `deleted_at` is a misleading name in that case. Interpreting `deleted_at` as…

Every Rails developer knows what “deleted_at” means. I’m absolutely repulsed at the thought of turning the users table into a view. What’s next? No code and we only use triggers?

Re: Soft deletion probably isn't worth it

#342

Post by pradn » Wed, Jul 20, 2022, 1:32 AM UTC

pradn wrote: post #191

danielrhodes wrote: post #138

In a previous place I worked, we were programmatically using Box to store files. One day we were presented with a case study in Murphy's Law: a script went awry and deleted everything (10s of thousands of files). There was no clear way to recover these files, they were gone from what we could see. It was a disaster. We got a Box support person on the phone and described what had happened. There was a pause, some mous…

The author agrees with you in principle. All the author is arguing against is the use of "deleted" bool column to indicate deletion. His solution of moving deleted objects to their own column gives you the ability to un-delete, just as before. Only now, your queries and indexes are simpler and you get to use foreign keys and other useful futures.

moving deleted objects to their own table* (not column)

Re: Soft deletion probably isn't worth it

#343

Post by WesleyJohnson » Wed, Jul 20, 2022, 1:32 AM 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

Thank you for this. I had no idea this existed either and it may solve some points for us on MySQL where we've really been yearning for materialized views... which I'm now reading my be a foot gun.

Re: Soft deletion probably isn't worth it

#344

Post by dubswithus » Wed, Jul 20, 2022, 1:33 AM UTC

doctor_eval wrote: post #260

rodw wrote: post #205

Earlier quoted context omitted.

Seriously. That "Downsides: Code leakage" point is nonsensical. ``` CREATE OR REPLACE VIEW active_customer AS SELECT * FROM customer WHERE deleted_at IS NULL OR deleted_at There, I fixed it. Just use `active_customer` instead of `customer ... deleted_at IS NULL`. In fact, since the deleted_at column is a timestamp, the original "leakage" query: ``` SELECT * FROM customer WHERE id = @id AND deleted_at IS NULL; ``` is…

> the database is part of the application 100% this. If you accept that the database is part of the application, you give yourself permission to use the full feature set of the database, and life becomes a lot simpler. Using views, stored procedures and other features lets you implement things like soft delete trivially, without it infecting all your application code. In my entire career I've changed backend database…

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

Re: Soft deletion probably isn't worth it

#345

Post by dubswithus » Wed, Jul 20, 2022, 1:36 AM UTC

robertlagrant wrote: post #240

efsavage wrote: post #193

Earlier quoted context omitted.

Also if people know that deletion is reversible, they're more likely to actually do it, which can keep things generally tidier. I don't actually like using a "deleted" column, my standard table has a status column, and deleted is one of those states, along with active/pending/suspended/etc, as the needs dictate. This way I get soft deletes for basically free both in the schema, but also in the queries (which would ge…

That still has the same issues. You have to remember to set every linked table's records to the same state, or remember to query every linked table through the table that has the lifecycle column on it.

There are some Rails gems that take care of this by scoping associations (that also have the deleted_at column).

Re: Soft deletion probably isn't worth it

#346

Post by mixmastamyk » Wed, Jul 20, 2022, 1:39 AM UTC

pradn wrote: post #342

pradn wrote: post #191

Earlier quoted context omitted.

The author agrees with you in principle. All the author is arguing against is the use of "deleted" bool column to indicate deletion. His solution of moving deleted objects to their own column gives you the ability to un-delete, just as before. Only now, your queries and indexes are simpler and you get to use foreign keys and other useful futures.

moving deleted objects to their own table* (not column)

Also timestamp column.

Re: Soft deletion probably isn't worth it

#347

Post by mst » Wed, Jul 20, 2022, 1:44 AM UTC

Soft delete has always caused me more trouble than it was worth.

Keeping a deleted recrords table via app code or triggers has always been more trouble than it took to build.

Re: Soft deletion probably isn't worth it

#348

Post by dubswithus » Wed, Jul 20, 2022, 1:44 AM UTC

Gurgler wrote: post #330

There's a very legitimate case that I've seen made for soft-deletion in several different situations: foreign keys related to "created-by" columns. Hard-deleting a user who created an object that remains in use after they're gone would trigger referential integrity complaints on those columns. Without being able to reference a "deactivated" user's primary key in such a situation, you'd have to come up with some count…

There are Rails gems that can handle this in various ways.

But the easiest way is to deactivate the user account (is_active boolean) and continue to reference the user in internal records.

Re: Soft deletion probably isn't worth it

#349

Post by mixmastamyk » Wed, Jul 20, 2022, 1:47 AM UTC

decebalus1 wrote: post #323

scott_w wrote: post #147

The example the author gives is… frankly awful. I can’t think of a single case where you’d want to remove the invoices of a customer you delete. Ever. In fact, the opposite is more likely to be a big problem, accidentally cascading your delete to your financial records! Using a soft delete, your invoices won’t “disappear” because your app WILL have a view for looking at just the invoices. Source: I built a bookkeepin…

> I can’t think of a single case where you’d want to remove the invoices of a customer you delete. Ever. CCPA will require you to delete the invoices. And I would love for all platforms to support deleting everything, including invoices, considering some things are illegal in other places and if there's proof of you buying said illegal thing, you can get in serious trouble (think gay dating apps in the UAE). But I do…

You don’t say why (last paragraph).

Re: Soft deletion probably isn't worth it

#350

Post by EdwardDiego » Wed, Jul 20, 2022, 1:52 AM UTC

ikiris wrote: post #326

smolder wrote: post #243

Earlier quoted context omitted.

I wonder what percentage of companies who even appear to comply with deletion requests actually do full deletion in practice. I suspect it's small, knowing how many things are coded to fake-delete for convenience. Businesses also tend to keep cold data backups around. (Maybe backups are exempted? I don't know.) There might even be cases where ostensibly deleted data can still be recovered from a disk, if they haven't…

The really hard part is dealing with the data still in backups. It leads to having to do crazy things like individual keyed encryption per user, escrow hilarity, etc.

I do a lot of work with Kafka, GPDR removal requests for data that's still stored in a Kafka topic is so much fun.