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

The challenges of soft delete

atlas9.dev

Post reply on HN
Print view View original

51–60 of 157 posts

Re: The challenges of soft delete

#51

Post by SchemaLoad »

strken wrote: post #40
SchemaLoad wrote: post #33

Earlier quoted context omitted.

Imo there should be some retention period for moderation but then hard deletion after that. Why would a moderator need to look up a deleted post a year after it was deleted?

"Hi SchemaLoad, I'm Officer John from the Department of Not Letting Children Be Abused. I'm following up on something one of your users posted three years ago. Can you tell me the IP address(es) associated with the following deleted posts: A B C D"

You'd be required to show what you have but you aren't required to store everything forever just in case someone years later asks for it. Would be like showing up to fingerprint the scene 3 years after and being surprised it's too late.

Re: The challenges of soft delete

#52

Post by snuxoll »

gleenn wrote: post #42
eddd-ddde wrote: post #38

Earlier quoted context omitted.

I never got to test this, but I always wanted to explore in postgres using table partitions to store soft deleted items in a different drive as a kind of archived storage. I'm pretty sure it is possible, and it might even yield some performance improvements. That way you wouldn't have to worry about deleted items impacting performance too much.

It's definitely an interesting approach but the problem is now you have to change all your queries and undeleting get more complicated. There are strong trade-offs with almost all the approaches I've heard of.

With partitioning? No you don't. It gets a bit messy if you also want to partition a table by other values (like tenant id or something), since then you probably need to get into using table inheritance instead of the easier declarative partitioning - but either technique just gives you a single effective table to query.

Re: The challenges of soft delete

#53

Post by pixl97 »

tharkun__ wrote: post #36
nine_k wrote: post #18

Earlier quoted context omitted.

> DELETEs are likely fairly rare by volume for many use cases All your other points make sense, given this assumption. I've seen tables where 50%-70% were soft-deleted, and it did affect the performance noticeably. > Undoing is really easy Depends on whether undoing even happens, and whether the act of deletion and undeletion require audit records anyway. In short, there are cases when soft-deletion works well, and i…

Agreed. And if deletes are soft, you likely really just wanted a complete audit history of all updates too (at least that's for the cases I've been part of). And then performance _definitely_ would suffer if you don't have a separate audit/archive table for all of those.

I mean, yes, growth forever doesn't tend to work.

I've seen a number of apps that require audit histories work on a basis where they are archived at a particular time, and that's when the deletes occurred and indexes fully rebuilt. This is typically scheduled during the least busy time of the year as it's rather IO intensive.

Re: The challenges of soft delete

#54

Post by dpark »

3rodents wrote: post #29

Soft deletes are an example of where engineers unintentionally lead product instead of product leading engineering. Soft delete isn’t language used by users so it should not be used by engineers when making product facing decisions. “Delete” “archive” “hide” are the type of actions a user typically wants, each with their own semantics specific to the product. A flag on the row, a separate table, deleting a row, these…

> Soft delete isn’t language used by users so it should not be used by engineers when making product facing decisions.

Users generally don’t even know what a database record is. There is no reason that engineers should limit their discussions of implementation details to terms a user might use.

> “Delete” “archive” “hide” are the type of actions a user typically wants, each with their own semantics specific to the product.

Users might say they want “delete”, but then also “undo”, and suddenly we’re talking about soft delete semantics.

> A flag on the row, a separate table, deleting a row, these are all implementation options that should be led by the product.

None of these are terms an end user would use.

Re: The challenges of soft delete

#55

Post by dpark »

strken wrote: post #37
monkpit wrote: post #32

Earlier quoted context omitted.

Why would implementation details be led by product? “Undo” is an action that the user may want, which would be led by product. Not the implementation in the db.

I believe that was the point. Soft delete isn't a product requirement, it's an implementation detail, so product teams should talk about the user experience using language like "delete" or "archive" or "undo" or "customer support retrieves deleted data".

Sure. Did someone say that the behavior should be described to customers as soft delete, though?

I read a blog about a technical topic aimed at engineers, not customers.

Re: The challenges of soft delete

#56

Post by dpark »

rorylaitila wrote: post #16

Databases store facts. Creating a record = new fact. "Deleting" a record = new fact. But destroying rows from tables = disappeared fact. That is not great for most cases. In rare cases the volume of records may be a technical hurdle; in which case, move facts to another database. The times I've wanted to destroy large volume of facts is approximately zero.

Unless your database is immutable, every changed a record causes a “disappeared fact”.

There are many legitimate reasons to delete data. The decision to retain data forever should not be taken lightly.

Re: The challenges of soft delete

#57

Post by pixl97 »

rorylaitila wrote: post #16

Databases store facts. Creating a record = new fact. "Deleting" a record = new fact. But destroying rows from tables = disappeared fact. That is not great for most cases. In rare cases the volume of records may be a technical hurdle; in which case, move facts to another database. The times I've wanted to destroy large volume of facts is approximately zero.

When you start thinking of data as a potentially toxic asset with a maintenance cost to ensure it doesn't leak and cause an environmental disaster, it becomes more likely that you'd want to get rid of large volumes of facts.

Re: The challenges of soft delete

#58

Post by da_chicken »

LorenPechtel wrote: post #30

The % of records that are deleted is a huge factor. You keep 99%, soft delete 1%, use some sort of deleted flag. While I have not tried it whalesalad's suggestion of a view sounds excellent. You delete 99%, keep 1%, move it!

A view only makes sense if your RDBMS supports indexed views or the query engine is otherwise smart enough to pierce the view definition. Not all of them can do those things.

Re: The challenges of soft delete

#59

Post by Ronsenshi »

elyobo wrote: post #9
whalesalad wrote: post #5

A good solution here (can be) to utilize a view. The underlying table has soft-delete field and the view will hide rows that have been soft deleted. Then the application doesn't need to worry about this concern all over the place.

postgres with rls to hide soft deleted records means that most of the app code doesn't need to know or care about them, still issues reads, writes, deletes to the same source table and as far as the app knows its working

I would also say that most modern ORMs and frameworks also either come with soft delete feature (with automatic filtering on all queries) as part of the package or there are third-party libraries available for ORMs adding this functionality without the hassle of dealing with views (maybe it's me, but I've never had good experience with DB views).

Re: The challenges of soft delete

#60

Post by pessimizer »

zahlman wrote: post #20
ntonozzi wrote: post #19

I've given up on soft delete -- the nail in the coffin for me was my customers' legal requirements that data is fully deleted, not archived. It never worked that well anyways. I never had a successful restore from a large set of soft-deleted rows.

> customers' legal requirements that data is fully deleted Strange. I've only ever heard of legal requirements preventing deletion of things you'd expect could be fully deleted (in case they're needed as evidence at trial or something).

You're thinking of "legal requirements" as requirements that the law insists upon rather than requirements that your legal department insists upon. You often want to delete records unrecoverably as soon as legally possible; it's likely why you wrote your data retention policy.
Post reply on HN