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

61–70 of 157 posts

Re: The challenges of soft delete

#61

Post by ozim »

MaxGabriel wrote: post #13

This might stem from the domain I work in (banking), but I have the opposite take. Soft delete pros to me: * It's obvious from the schema: If there's a `deleted_at` column, I know how to query the table correctly (vs thinking rows aren't DELETEd, or knowing where to look in another table) * One way to do things: Analytics queries, admin pages, it all can look at the same set of data, vs having separate handling for h…

DELETEs are likely fairly rare by volume for many use cases

I think one of our problems is getting users to delete stuff they don’t need anymore.

Re: The challenges of soft delete

#62

Post by edmundsauto »

snuxoll wrote: post #52
gleenn wrote: post #42

Earlier quoted context omitted.

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.

Pg moves the data between positions on update?

Re: The challenges of soft delete

#63

Post by Centigonal »

hnthrow0287345 wrote: post #49

Maybe I'm shooting for the moon, but I'd like soft delete to be some kind of built-in database feature. It would be nice to enable it on a table then choose some built-in strategies on how it's handled. Soft-delete is a common enough ask that it's probably worth putting the best CS/database minds to developing some OOTB feature.

Many data warehousing paradigms (e.g. Iceberg, Delta Lake, BigQuery) offer built-in "time travel," sometimes combined with scheduled table backups. That said, a lot of the teams I've worked with who want soft-delete also have other requirements that necessitate taking a custom approach (usually plain ol' SCD) instead of using the platform-native implementation.

Re: The challenges of soft delete

#64

Post by bandrami »

edmundsauto wrote: post #62
snuxoll wrote: post #52

Earlier quoted context omitted.

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.

Pg moves the data between positions on update?

If you are updating the parent table and the partition key is correctly defined, then an update that puts a row in a different partition is translated into a delete on the original child table and an insert on the new child table, since v11 IIRC. But this can lead to some weird results if you're using multiple inheritance so, well, don't.

Re: The challenges of soft delete

#65

Post by yxhuvud »

salomonk_mur wrote: post #39
talesmm14 wrote: post #11

I've worked at companies where soft delete was implemented everywhere, even in irrelevant internal systems... I think it's a cultural thing! I still remember a college professor scolding me on an extension project because I hadn't implemented soft delete... in his words, "In the business world, data is never deleted!!"

But... It's true. Deleting data completely is an easy way to gimp and lobotomize your future analysis. Storage is cheap. Never delete data.

Deleting data is also a very easy way to not get GDPR compliance issues. Data is a cost and a risk, and should be minimised to what is actually relevant. Storage is the least part of the cost.

Re: The challenges of soft delete

#66

Post by tomnipotent »

bandrami wrote: post #64
edmundsauto wrote: post #62

Earlier quoted context omitted.

Pg moves the data between positions on update?

If you are updating the parent table and the partition key is correctly defined, then an update that puts a row in a different partition is translated into a delete on the original child table and an insert on the new child table, since v11 IIRC. But this can lead to some weird results if you're using multiple inheritance so, well, don't.

I believe they were just pointing out that Postgres doesn't do in-place updates, so every update (with or without partitions) is a write followed by marking the previous tuple deleted so it can get vacuumed.

Re: The challenges of soft delete

#68

Post by paulddraper »

nine_k wrote: post #18
MaxGabriel wrote: post #13

This might stem from the domain I work in (banking), but I have the opposite take. Soft delete pros to me: * It's obvious from the schema: If there's a `deleted_at` column, I know how to query the table correctly (vs thinking rows aren't DELETEd, or knowing where to look in another table) * One way to do things: Analytics queries, admin pages, it all can look at the same set of data, vs having separate handling for h…

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

50-70% as the worst case isn't even necessarily that bad.

(Again, a lot is O(log n) right?)

Re: The challenges of soft delete

#69

Post by paulddraper »

eddd-ddde wrote: post #38
MaxGabriel wrote: post #13

This might stem from the domain I work in (banking), but I have the opposite take. Soft delete pros to me: * It's obvious from the schema: If there's a `deleted_at` column, I know how to query the table correctly (vs thinking rows aren't DELETEd, or knowing where to look in another table) * One way to do things: Analytics queries, admin pages, it all can look at the same set of data, vs having separate handling for h…

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.

IDK if the different drive is necessary, but yes partitioning on a deleted field would work.

Memory >>>>> Disk in importance.

Re: The challenges of soft delete

#70

Post by lelanthran »

iterateoften wrote: post #34

I used to be pretty adamant about implementing soft delete for core business objects. However after 15 years I prefer to just back up regularly, have point in time restores and then just delete normally. The amount of times I have “undeleted” something are few and far between.

> I used to be pretty adamant about implementing soft delete for core business objects.

> However after 15 years I prefer to just back up regularly, have point in time restores and then just delete normally.

> The amount of times I have “undeleted” something are few and far between.

Similar take from me. Soft deletes sorta makes sense if you have a very simply schema, but the biggest problem I have is that a soft delete leads to broken-ness - some other table now has a reference to a record in the target table that is not supposed to be visible. IOW, DB referential integrity is out the window because we can now have references to records that should not exist!

My preferred way (for now, anyway) is to copy the record to a new audit table and nuke it in the target table in a single transaction. If the delete fails we can at least log the fact somewhere that some FK somewhere is preventing a deletion.

With soft deletes, all sorts of logic rules and constraints are broken.

Post reply on HN