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

41–50 of 157 posts

Re: The challenges of soft delete

#41

Post by IgorPartola »

I have a love/hate relationship with soft deleted. There are cases where it’s not really a delete but rather a historical fact. For example, let’s say I have a table which stores an employee’s current hourly rate. They are hired at say $15/hour, then go to $17 six months later, then to $20/hour three months later. All of these three things are true and I want to be able to query which rate the employee had on a specific date even after their rate had changed. When I have a starts_on and an ends_on dates and the latter is nullable, with some data consistency logic I can create a linear history of compensation and can query historical and current data the same exact way. I also get

But this is such a huge PITA because you constantly have to mind if any given object has this setup or not and what if related objects have different start/end dates? And something like a scheduled raise for next year to $22/hour can get funny if I then try to insert that just for July it will be $24/hour (this would take my single record for next year and split it into two and then you gotta figure out which gets the original ID and which is the new row.

Another alternative to this is a pattern where you store the current state and separately you store mutations. So you have a compensation table and a compensation_mutations table which says how to evolve a specific row in a compensation table and when. The mutations for anything in the future can be deleted but the past ones cannot which lets you reconstruct who did what, when, and why. But this also has drawbacks. One of them is that you can’t query historical data the same way as current data. You also have to somehow apply these mutations (cron job? DB trigger?)

And of course there are database extensions that allow soft deletes but I have never tried them for vague portability reasons (as if anyone ever moved off Postgres).

Re: The challenges of soft delete

#42

Post by gleenn »

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.

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.

Re: The challenges of soft delete

#43

Post by gleenn »

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…

If you're implementing immutable DB semantics maybe you should consider Datomic or alternatives because then you get that for free, for everything, and you also get time travel which is an amazing feature on top. It lets you be able to see the full, coherent state of the DB at any moment!

Re: The challenges of soft delete

#44

Post by antonvs »

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…

It depends on the product. Google Cloud Storage has a soft delete feature in its product, for example: https://docs.cloud.google.com/storage/docs/soft-delete

Re: The challenges of soft delete

#45

Post by ziml77 »

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.

I prefer audit tables. Soft deletes don't capture updates, audit tables do (you could make every update a delete and insert in a soft delete table, but that adds a lot of bloat to the table)

Re: The challenges of soft delete

#46

Post by antonvs »

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"

“Hi Officer John, that data is deleted and is no longer possible to access.”

Unless there’s a regulatory requirement (which there currently isn’t in any jurisdiction I’ve heard of), that’s a perfectly acceptable response.

Re: The challenges of soft delete

#47

Post by rawgabbit »

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 have worked with databases my entire career. I hate triggers with a passion. The issue is no one “owns” or has the authority to keep triggers clean. Eventually triggers become a dumping ground for all sorts of nasty slow code.

I usually tell people to stop treating databases like firebase and wax on/wax off records and fields willy nilly. You need to treat the database as the store of your business process. And your business processes demand retention of all requests. You need to keep the request to soft delete a record. You need to keep a request to undelete a record.

Too much crap in the database, you need to create a field saying this record will be archived off by this date. On that date, you move that record off into another table or file that is only accessible to admins. And yes, you need to keep a record of that archival as well. Too much gunk in your request logs? Well then you need to create an archive process for that as well.

These principles are nothing new. They are in line with “Generally Accepted Record Keeping Principles” which are US oriented. Other countries have similar standards.

Re: The challenges of soft delete

#48

Post by Terr_ »

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

Yeah: You don't "delete" a bank account, you close it, and you don't "undo", you reopen it, etc. The processes have conditions, audit rules, attached information, side-effects, etc. In some cases the same entity can't be restored, and you have to instead create a successor.

"Undo" may work as shorthand for "whatever the best reversing actions happen to be", but as any system grows it stops being simple.

Re: The challenges of soft delete

#49

Post by hnthrow0287345 »

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.

Re: The challenges of soft delete

#50

Post by da_chicken »

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…

> I've seen tables where 50%-70% were soft-deleted, and it did affect the performance noticeably.

At that point you should probably investigate partitioning or data warehousing.

Post reply on HN