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

Soft deletion probably isn't worth it

brandur.org

Post reply on HN
Print view View original

81–90 of 514 posts

Re: Soft deletion probably isn't worth it

#81

Post by munk-a »

bjourne wrote: post #57
munk-a wrote: post #24

I just wanted to touch on the fact that eliding soft-deleted rows from queries is really, really easy - this article makes it out to be a constant headache but here's my suggested approach. ALTER TABLE blah ADD COLUMN deleted_at NULL TIMESTAMP; ALTER TABLE blah RENAME TO blahwithdeleted; CREATE VIEW blah (SELECT * FROM blahwithdeleted WHERE deleted_at IS NULL); And thus your entire application just needs to keep SELE…

This is not a solution. It introduces a leaky abstraction which sooner or later will lead to errors. Sure, all code you write will access the view and not the table. But how can you ensure all other code in the organisation uses the view? Perhaps you add some access control to the table so that only authorized users can read directly from it, but that's even more technical overhead. Then you have foreign keys. If you…

I don't really agree with that. Within an organization you have documentation and instruction as tools - but you're also making the dumb approach (SELECT * FROM blah) the correct approach. If a user is writing a query against the DB, has no idea what the layout of the data is, and decides to prefer blahwithdeleted over blah then I'd really question whats going on at your organization - blahwithdeleted is pretty clearly self-documenting and it's likely a lot of your other domain specific tables with be much harder to naively discover your way through.

I, personally, would in no way restrict access to blahwithdeleted, but I have made a pattern of it in our DB, there are about a dozen blahwithdeleted tables - each with a corresponding blah view... I usually get about one question per every two new employees about which table to use which I can answer in less than a minute with a helpful little explanation.

I'd also mention I've not made a specific value statement on soft deletions in a general case since, if there was a clear general case solution we'd just all do that. This is a decision that needs to be made on a per table basis - it's a rather trivial decision in most cases, but it's very specific to the problem at hand.

Re: Soft deletion probably isn't worth it

#82

Post by 5e92cb50239222b »

dafelst wrote: post #20

Views are a simple solution to this problem. Pretty much all moderns RDBMSs support updatable views, so creating views over your tables with a simple WHERE deleted_at IS NULL solves the majority of the author's problems, including (IIRC) foreign key issues, assuming the deletes are done appropriately. I feel like a lot of developers underutilize the capabilities of the massively advanced database engines they code ag…

This is one of those situations where a good ORM can simplify things greatly. For example, with EF Core you can add a global filter which will filter out soft-deleted rows in all queries automatically (unless you add .IgnoreQueryFilters()).

It couples nicely with some hackery which turns removes into soft-deletes. You can remove objects as usual and they get soft-deleted in the database.

I've used this in a few projects and it's fantastic.

https://docs.microsoft.com/en-us/ef/core/querying/filters

https://www.thereformedprogrammer.net/ef-core-in-depth-soft-...

Re: Soft deletion probably isn't worth it

#84

Post by munk-a »

yladiz wrote: post #66
munk-a wrote: post #24

I just wanted to touch on the fact that eliding soft-deleted rows from queries is really, really easy - this article makes it out to be a constant headache but here's my suggested approach. ALTER TABLE blah ADD COLUMN deleted_at NULL TIMESTAMP; ALTER TABLE blah RENAME TO blahwithdeleted; CREATE VIEW blah (SELECT * FROM blahwithdeleted WHERE deleted_at IS NULL); And thus your entire application just needs to keep SELE…

But, assuming you don't really need the data, why make your queries more complex and instead just actually delete the data?

This is a really scenario specific question - sometimes it's needed, sometimes it isn't. At my shop we have customers that will suspend their account but our sales team is pretty damn awesome so usually they end up renewing after going a while without our product - so being able to easily restore a large swath of former customers with all their permissions and preferences intact with a simple click of a button is a huge win compared to having a dev try to piece the data together out of backups that are six months out of date (a little secret... we never did this and just put the obligation on the CS team to manually recreate the records since that cost the company less).

Re: Soft deletion probably isn't worth it

#85

Post by zozbot234 »

dfee wrote: post #25

My experience is that soft-deletes are blunt tools bridging the gap between hard deletes and event sourcing (capturing all the changes against the table, in a replay-worthy stream). Event sourcing is hard – because the engineers responsible for setting it up and managing it aren't generally well skilled in this domain (myself included) and there aren't a wealth of great tools helping engineers find their way into the…

An event store is just a special case of a temporal database. The whole point of temporal databases is to natively support the notion of historical vs. current data.

Re: Soft deletion probably isn't worth it

#86

Post by MonkeyMalarky »

giantg2 wrote: post #3

"The concept behind soft deletion is to make deletion safer, and reversible." That's one part. The other part is that in many industries you have regulatory data retention and audit requirements. This is arguably the most valuable and common reason to perform Logical deletes.

Then there's always the joy of a situation where your client is being sued by one of their clients and now needs your help recovering everything possible from your platform. And you're going to help them because you'd like to keep them as a client rather than let them be sued into oblivion.

Re: Soft deletion probably isn't worth it

#87

Post by TylerE »

yladiz wrote: post #66
munk-a wrote: post #24

I just wanted to touch on the fact that eliding soft-deleted rows from queries is really, really easy - this article makes it out to be a constant headache but here's my suggested approach. ALTER TABLE blah ADD COLUMN deleted_at NULL TIMESTAMP; ALTER TABLE blah RENAME TO blahwithdeleted; CREATE VIEW blah (SELECT * FROM blahwithdeleted WHERE deleted_at IS NULL); And thus your entire application just needs to keep SELE…

But, assuming you don't really need the data, why make your queries more complex and instead just actually delete the data?

Becauase that's a huge assumption. From my experience, it's sorta like the giant box of old cables most techies keep around.

Do you reach into it often? No.

But when you do it absolutely saves the day and makes you a hero.

Re: Soft deletion probably isn't worth it

#88

Post by xwdv »

kukx wrote: post #76
pbardea wrote: post #49

Earlier quoted context omitted.

> assuming the deletes are done appropriately This is one gripe I have with soft-deletion. Since I can no longer rely on ON DELETE CASCADE relationships, I need to re-defined these relationship between objects at the application layer. This gets more and more difficult as relationships between objects increase. If the goal is to keep a history of all records for compliance reasons or "just in case", I tend to prefer…

Is not there any attempt to improve the soft deletion at the engine/SQL level? I can see it as a possible feature request.

Doubt it. It seems like something obvious yet I’ve waited so long for it. Seems like you have to rely on third party plugins.

Re: Soft deletion probably isn't worth it

#89

Post by rodelrod »

dfee wrote: post #25

My experience is that soft-deletes are blunt tools bridging the gap between hard deletes and event sourcing (capturing all the changes against the table, in a replay-worthy stream). Event sourcing is hard – because the engineers responsible for setting it up and managing it aren't generally well skilled in this domain (myself included) and there aren't a wealth of great tools helping engineers find their way into the…

Or you can see it the other way around: soft-deletes are a pragmatic alternative to event sourcing that provides a lot of the value without requiring a team of super-humans and a radical redesign of the existing systems.

Re: Soft deletion probably isn't worth it

#90

Post by jayd16 »

eeperson wrote: post #29
jewayne wrote: post #7

Earlier quoted context omitted.

I would argue that in many cases the concept behind soft deletion is to make deletion permanent. Hard deletes retain no memory of what you wanted to be gone, so any malfunctioning sync process will continuously recreate the deleted record soon after it's deleted. Soft deletes are often the only way to make sure deleted records don't reappear.

Couldn't a malfunctioning sync process undo a soft delete as well?

Null is more ambiguous than an explicit conflict. If there is literally no record, even of the delete action then there's no timestamp for last write wins.
Post reply on HN