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

141–150 of 157 posts

Re: The challenges of soft delete

#141

Post by [deleted] »

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…

[deleted]

Re: The challenges of soft delete

#142

Post by lazide »

KronisLV wrote: post #136
pif wrote: post #124

Earlier quoted context omitted.

> I think we largely need support for "soft deletes" to be baked into SQL I think web and GUI programmers must stop expeting the database to contain the data already selected and formatted for their nice page.

> I think web and GUI programmers must stop expeting the database to contain the data already selected and formatted for their nice page. So a widespread, common and valid practice shouldn't be made better supported and instead should rely on awkward hacks like "deleted_at" where sooner or later people or ORMs will forget about those semantics and will select the wrong thing? I don't think I agree. I also don't think…

What way of making it better supported wouldn’t require custom semantics that people would forget and then select the wrong thing.

Re: The challenges of soft delete

#143

Post by lazide »

tucnak wrote: post #117
jackfranklyn wrote: post #115

[flagged]

I'm struggling to see your point. CREATE VIEW not only helps, yes, indeed it's oftentimes exactly all you need. If you have multiple access patterns, like having to "actually query deleted records" sometimes, somewhere, at some point, someone would have to maintain invariants on these access patterns. This is not rocket science. The heart of the matter is that SWE's cannot handle schema/basic SQL to save their lives,…

It’s just a lot of overhead (in every way) if you’re just trying to store some rows and columns.

Re: The challenges of soft delete

#144

Post by infamia »

postexitus wrote: post #98
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…

Soft deletes in banking are just a Band-Aid to the much bigger problem of auditability. You may keep the original record by soft deleting it, but if you don't take care of amends, you will still lose auditability. The correct way is to use EventSourcing, with each change to an otherwise immutable state being recorded as an Event, including a Delete (both of an Event and the Object). This is even more problematic from…

> The correct way is to use EventSourcing, with each change to an otherwise immutable state being recorded as an Event, including a Delete (both of an Event and the Object).

Another great (and older) approach is adding temporal information do your traditional database, which gives immutability without the eventual consistency headaches that normally comes with event sourcing. Temporal SQL has their own set of challenges of course, but you get to keep 30+ years of relational DB tooling which is a boon. Event sourcing is great, but we shouldn't forget about other tools in our toolbelt as well!

Re: The challenges of soft delete

#145

Post by KronisLV »

lazide wrote: post #142
KronisLV wrote: post #136

Earlier quoted context omitted.

> I think web and GUI programmers must stop expeting the database to contain the data already selected and formatted for their nice page. So a widespread, common and valid practice shouldn't be made better supported and instead should rely on awkward hacks like "deleted_at" where sooner or later people or ORMs will forget about those semantics and will select the wrong thing? I don't think I agree. I also don't think…

What way of making it better supported wouldn’t require custom semantics that people would forget and then select the wrong thing.

> custom semantics

Making those custom semantics (enabled at per-schema/per-table level) take over what was already there previously: DELETE doing soft-deletes by default and SELECT only selecting the records that aren't soft deleted, for example.

Then making the unintended behavior (for 90% of normal operational cases) require special commands, be it a new keyword like DELETE HARD or SELECT ALL, or query hints (special comments like /*+DELETE_HARD*/).

Maybe some day I'll find a database that's simple and hackable enough to build it for my own amusement.

Re: The challenges of soft delete

#146

Post by arter45 »

dpark wrote: post #54
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 pr…

End users do all kinds of stuff, but as a developer you're supposed to gather (even elicit, at times!) requirements from users or stakeholders who act as proxies for actual users.

Store something so you can read it in a year or even after a blackout is a user requirement, which leads to persistence.

And if this is a user requirement, deleting ("un-storing") is a user requirement too.

"I want to delete something but I also want to recover it" is another requirement.

Of course,you could also have regulatory requirements pointing to hard-deleting or not hard-deleting anything, but this also holds for a lot of other issues (think UX - accessibility can be constrained by regulations, but you also want users to somehow have a general idea of the user experience).

Re: The challenges of soft delete

#148

Post by tomnipotent »

snuxoll wrote: post #85
tomnipotent wrote: post #66

Earlier quoted context omitted.

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.

That’s not at all what the child to me was saying in even a generous reading. But HOT updates are a thing, too.

What do you think they were saying? I don't see any other way to read it.

HOT updates write to the same tuple page and can avoid updating indexes, but it's still a write followed by marking the old tuple for deletion.

Re: The challenges of soft delete

#149

Post by snuxoll »

tomnipotent wrote: post #148
snuxoll wrote: post #85

Earlier quoted context omitted.

That’s not at all what the child to me was saying in even a generous reading. But HOT updates are a thing, too.

What do you think they were saying? I don't see any other way to read it. HOT updates write to the same tuple page and can avoid updating indexes, but it's still a write followed by marking the old tuple for deletion.

> Pg moves the data between positions on update?

I assume they typo'd "partitions" as "positions", and thus the GP comment was the correct reply.

Re: The challenges of soft delete

#150

Post by strken »

direwolf20 wrote: post #92
strken wrote: post #40

Earlier quoted context omitted.

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

Think of the children! We can't have privacy because children might be abused if we have privacy!

This argument applies equally to anything else that needs digital forensics, like SBF's personal banking history, or which user deployed a crypto-miner to some random staging server back in 2023.
Post reply on HN