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

11–20 of 109 posts

Re: Easy, alternative soft deletion: `deleted_record_insert`

#11

Post by duskwuff »

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 around, you need some way to figure out who they belong to. And I don't see any way to do that with this schema, short of rehydrating all the rows and following the original foreign key relations.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#12

Post by jxf »

jimmytucson wrote: post #7

My question when people implement a generic feature like this, is why doesn’t the database do it? And many tunes, as is the case with soft delete, it does. For example, Redshift tombstones records and you can choose when to “vacuum” them up (actually delete them). Usually if you’re changing the way a primary function of the database works, like delete, it’s probably not a good move.

> Usually if you’re changing the way a primary function of the database works, like delete, it’s probably not a good move.

This isn't a soft delete -- you can't use the data for undeletion and it's not visible to an application that can see that table (as it would be in the case of a `deleted_at` column). It's only for analytical purposes, as the author says.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#14

Post by YouWhy »

What I like about this construct is the fact that it uses the looser JSON semantics for the typically ad-hoc nature of recovering deleted entries - which does not necessitate the full power of referntial consistency.

What I'm not sure about is how will it will behave with blob fields and other data types with "problematic" serializations.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#15

Post by [deleted] »

jxf wrote: post #12
jimmytucson wrote: post #7

My question when people implement a generic feature like this, is why doesn’t the database do it? And many tunes, as is the case with soft delete, it does. For example, Redshift tombstones records and you can choose when to “vacuum” them up (actually delete them). Usually if you’re changing the way a primary function of the database works, like delete, it’s probably not a good move.

> Usually if you’re changing the way a primary function of the database works, like delete, it’s probably not a good move. This isn't a soft delete -- you can't use the data for undeletion and it's not visible to an application that can see that table (as it would be in the case of a `deleted_at` column). It's only for analytical purposes, as the author says.

[deleted]

Re: Easy, alternative soft deletion: `deleted_record_insert`

#16

Post by brightball »

I’ve been using this exact approach for years and strongly advocate for it.

The deleted_at approach is rife with problems since you have to ensure it’s checked in every single query, including joins where it may be referenced. Getting the record out of the table is critical.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#17

Post by rbetts »

jimmytucson wrote: post #7

My question when people implement a generic feature like this, is why doesn’t the database do it? And many tunes, as is the case with soft delete, it does. For example, Redshift tombstones records and you can choose when to “vacuum” them up (actually delete them). Usually if you’re changing the way a primary function of the database works, like delete, it’s probably not a good move.

re: why doesn't the database do it

They do - almost all databases will offer change data capture integration either via making the write ahead log readable or by suggesting triggers, like the article.

If you wanted to read the deletion journal in another system (maybe because of transaction rate), you might prefer CDC to Triggers. For example: https://www.postgresql.org/docs/current/logical-replication-...

Re: Easy, alternative soft deletion: `deleted_record_insert`

#18

Post by brightball »

no_wizard wrote: post #9
jimmytucson wrote: post #7

My question when people implement a generic feature like this, is why doesn’t the database do it? And many tunes, as is the case with soft delete, it does. For example, Redshift tombstones records and you can choose when to “vacuum” them up (actually delete them). Usually if you’re changing the way a primary function of the database works, like delete, it’s probably not a good move.

To take a stab at it: This is one of those things where business requirements trump the technical implementation details. Prevailing theory is that actual deletes are bad because you can’t do historical analysis, recovery etc on the data. Say a customer stops using a service for a year but comes back: it’s a big win if you can (at least optionally) restore their data, so the theory goes. That’s why tricks like this e…

Another is the support case “Help! I accidentally deleted the wrong thing!” where it saves a huge amount of time compared to loading up a full DB backup.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#19

Post by AlexeyMK »

brightball wrote: post #16

I’ve been using this exact approach for years and strongly advocate for it. The deleted_at approach is rife with problems since you have to ensure it’s checked in every single query, including joins where it may be referenced. Getting the record out of the table is critical.

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.

Re: Easy, alternative soft deletion: `deleted_record_insert`

#20

Post by solidsnack9000 »

With a procedural language and SQL introspection features, you can programmatically generate triggers, functions and backing tables for these kinds of things. For Postgres, this SQL can generate temporal tables backed by JSONB for any table:

https://github.com/solidsnack/macaroon/blob/master/temporal....

It would be pretty easy to change this to perform deletion-with-interning like mentioned in the article.

In an older version, the code actually replicated the schema of the source table in the log table; but it can lead to problems during migrations.

Post reply on HN