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

Easy, alternative soft deletion: `deleted_record_insert`

brandur.org

Post reply on HN
Print view View original

31–40 of 109 posts

Re: Easy, alternative soft deletion: `deleted_record_insert`

#31

Post by pmontra »

nonethewiser wrote: post #22

> I’ve spent the time migrating our code away from deleted_at, and we’re now at the point where it’s only left on a couple core tables where we want to retain deleted records for an exceptionally long time for debugging purposes. Sounds like the problem is not soft deleting, but applying soft deleting to _everything_ without thought. Then he goes on to suggest an alternative that is even more complicated. Just includ…

Actually this is what they are defending against: "dozens of bugs and countless hours of debugging time as people accidentally omit deleted_at IS NULL from production and analytical queries."

Those queries could be raw SQL or from different ORMs and applications, maybe written without a full understanding of the database.

However the claim is not substantiated: how many bugs of that type did they had before?

Re: Easy, alternative soft deletion: `deleted_record_insert`

#32

Post by djur »

nonethewiser wrote: post #22

> I’ve spent the time migrating our code away from deleted_at, and we’re now at the point where it’s only left on a couple core tables where we want to retain deleted records for an exceptionally long time for debugging purposes. Sounds like the problem is not soft deleting, but applying soft deleting to _everything_ without thought. Then he goes on to suggest an alternative that is even more complicated. Just includ…

This is addressed in the previous article:

> Some ORMs or ORM plugins make this easier by automatically chaining the extra deleted_at clause onto every query (see acts_as_paranoid for example), but just because it’s hidden doesn’t necessarily make things better. If an operator ever queries the database directly they’re even more likely to forget deleted_at because normally the ORM does the work for them.

I ran into this exact issue last month. Very common for places to have a general "soft delete unless you have a good reason not to" policy, and very common for people to forget about the deletion flag when writing joins by hand or doing reporting.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#34

Post by jl6 »

This is another solution to such a common problem that one might be surprised at there being no solution baked into the SQL standard. Instead, we have some vendor specific features like automatic audit tables and time travel, and a huge array of bespoked techniques like in the article: everything from adding a deleted_at column through to re-architecting your system around event-sourcing.

Why such diversity of solutions? I believe this is because the problem of adding time dimensions to your data model is heavily dependent on exactly what you intend to do with your data. Enabling theoretical audits is a very different requirement than enabling admins to rollback changes to data, which is a very different requirement to enabling users to undelete their data - and then there’s a performance angle to layer on top: your design will depend on whether you need your INSERTs/UPDATEs to be fast or your SELECTs.

It’s definitely not a case of one of these approaches being definitively better than another. You can’t shortcut talking to your users/clients/stakeholders about how the whole system is intended to work.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#36

Post by sroussey »

djur wrote: post #32
nonethewiser wrote: post #22

> I’ve spent the time migrating our code away from deleted_at, and we’re now at the point where it’s only left on a couple core tables where we want to retain deleted records for an exceptionally long time for debugging purposes. Sounds like the problem is not soft deleting, but applying soft deleting to _everything_ without thought. Then he goes on to suggest an alternative that is even more complicated. Just includ…

This is addressed in the previous article: > Some ORMs or ORM plugins make this easier by automatically chaining the extra deleted_at clause onto every query (see acts_as_paranoid for example), but just because it’s hidden doesn’t necessarily make things better. If an operator ever queries the database directly they’re even more likely to forget deleted_at because normally the ORM does the work for them. I ran into t…

True, but in that case I wonder the reason why you or anyone else has such direct access to such data. ETL would eliminate such things for a data lake before anyone would run ad-hoc queries (that could and likely do) contains PII and other privileged data.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#37

Post by yen223 »

TylerE wrote: post #23
AlexeyMK wrote: post #19

Earlier quoted context omitted.

You can get around the "check in every transaction" problem with an ORM, but now you're (more) coupled to your ORM which you will occasionally inevitable need to circumvent for something or other. And now you've made it (more of a) leaky abstraction.

Bwttwr yet, encode the logic in a view and run your queries against the view. DRY

In postgres you cannot make a foreign-key reference to a view, which effectively means you can't prevent another table from referencing a soft-deleted record.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#38

Post by djur »

sroussey wrote: post #36
djur wrote: post #32

Earlier quoted context omitted.

This is addressed in the previous article: > Some ORMs or ORM plugins make this easier by automatically chaining the extra deleted_at clause onto every query (see acts_as_paranoid for example), but just because it’s hidden doesn’t necessarily make things better. If an operator ever queries the database directly they’re even more likely to forget deleted_at because normally the ORM does the work for them. I ran into t…

True, but in that case I wonder the reason why you or anyone else has such direct access to such data. ETL would eliminate such things for a data lake before anyone would run ad-hoc queries (that could and likely do) contains PII and other privileged data.

Well, for one thing, there are countless software systems worked on by hundreds of thousands of developers which will never include a data lake or any ETL more complex than "a programmer writes a script to generate a CSV and upload it/email it somewhere".

Re: Easy, alternative soft deletion: `deleted_record_insert`

#39

Post by username1407 »

duskwuff wrote: post #11

There's at least one factor which I don't see consideration for here. Under GDPR, CCPA/CCRA, and a number of other privacy laws, if you're going to retain data related to people you need to provide those people some way of retrieving that data or requesting that it be erased. Putting the data into a "deleted_record" table doesn't remove that obligation. So, if you're going to have a bunch of "deleted" records hanging…

Can't you use json operators [1] for this?

-- Permanently delete message sent by jon

DELETE from deleted_record where table_name = 'messages' and data->>'sender' = 'jon';

[1] https://www.postgresql.org/docs/15/functions-json.html

Re: Easy, alternative soft deletion: `deleted_record_insert`

#40

Post by duskwuff »

username1407 wrote: post #39
duskwuff wrote: post #11

There's at least one factor which I don't see consideration for here. Under GDPR, CCPA/CCRA, and a number of other privacy laws, if you're going to retain data related to people you need to provide those people some way of retrieving that data or requesting that it be erased. Putting the data into a "deleted_record" table doesn't remove that obligation. So, if you're going to have a bunch of "deleted" records hanging…

Can't you use json operators [1] for this? -- Permanently delete message sent by jon DELETE from deleted_record where table_name = 'messages' and data->>'sender' = 'jon'; [1] https://www.postgresql.org/docs/15/functions-json.html

That assumes that all of a user's data is a single foreign key relationship away from the user, which often isn't the case.

It also requires you to scan the entire table and decode JSON for every row you've ever deleted, which may become prohibitively expensive for large databases.

Post reply on HN