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

161–170 of 514 posts

Re: Soft deletion probably isn't worth it

#161

Post by munk-a »

nfm wrote: post #142
munk-a wrote: post #91

Earlier quoted context omitted.

If we're assuming you're using a view based approach which elides the soft deleted rows automatically then you'll get a lot of these dependent objects correctly updated for free assuming you're pulling them out of the DB with JOINs - SELECT FROM foo JOIN bar (assuming bar is a view into barwithdeleted) will automatically filter out the invalid rows from foo... if you're using this information to populate a CRUD inter…

Yes, but other queries (any aggregate queries that don't join the soft deleted table, any joins to other tables) will now return rows that would have been deleted under hard deletion with cascade.

This is definitely something to watch out for, but in practice (as someone that migrated a system without soft deletes to one that had them) I found that it doesn't tend to come up nearly as much as you might think - usually the table being transitioned to support soft deletes is a relatively core table (since ancillary tables usually aren't worth the complexity to transition) so a lot of your reporting queries will already be pulling in that table. You definitely need to check to make sure you're not missing anything - and sometimes CRUD interfaces will need to be completely revamped to include the table in question - but it's usually not that hard.

Re: Soft deletion probably isn't worth it

#162

Post by coding123 »

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…

I was going to chime in with this. thanks. One issue with views however is that a lot of these features require more and more nuanced knowledge of RDBMSes where these days unless you have a veteran architect, most of the team just knows the various library/tooling that interacts with "a variety of databases" so there is often less effort to go deeper.

Re: Soft deletion probably isn't worth it

#163

Post by mmerlin »

danielrhodes wrote: post #130
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…

Being unable to effectively use foreign key relationships is definitely a downside of using soft deletes. But it's also worth asking if these types of behaviors, which would also include a feature like triggers, really belongs in a database or whether it's better to have at the application level (or at least at a layer above the data layer). I'd argue that ultimately you probably don't want these things at the DB lev…

My perspective is DB level triggers are the absolute very best place to put cascading update/delete logic so it only ever needs to be written once and is consistent regardless of any future frontend clients that might be written in a different language and/or framework than the original codebase.

Right now in $dayjob I am converting an old non-DRY codebase from NoSQL data layer format to proper relational SQL backend.

This old front-end was created by verbosely coding up a relational cascading update/delete system for the NoSQL backend, in numerous places redundantly with subtle differences and inconsistencies, making the code brittle.

My current estimate is some front end functions will be reduced in LOC size by 95% once we use the power of SQL in the backend.

And the backend SQL Triggers+StoredProcedure required to replace these long NoSQL frontend functions doing cascading updates/deletes is only around 10% the size of the replaced front-end code.

And now future new frontends can reuse this without exploding the size of their codebase where complex data operations are required. And no need to reinvent the same data handling algorithm all over again (and risk subtle variation creeping in from the different front-end implementation of data algorithms)

Re: Soft deletion probably isn't worth it

#164

Post by augustl »

JohnBooty wrote: post #151

I've been a software dev since the 90s and at this point, I've learned to basically do things like audit trails and soft deletion by default, unless there's some reason not to. Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. It helps the business, it helps you as developer by giving you debug information as well as helping you to c…

This is why I don't understand why Datomic isn't more popular. Pretty much every system I've worked on never needed to scale past 100s of writes per second due to hard limits on the system (internal backoffice stuff, fundamenally scoped/shardable to defined regions, etc etc). And since Datomic is built with that in mind, you get the trade-off of full history, first class transactions and being able to query for things like "who changes this attribute to its current value, when, and why" is such as super power!

Re: Soft deletion probably isn't worth it

#165

Post by th0ma5 »

augustl wrote: post #164
JohnBooty wrote: post #151

I've been a software dev since the 90s and at this point, I've learned to basically do things like audit trails and soft deletion by default, unless there's some reason not to. Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. It helps the business, it helps you as developer by giving you debug information as well as helping you to c…

This is why I don't understand why Datomic isn't more popular. Pretty much every system I've worked on never needed to scale past 100s of writes per second due to hard limits on the system (internal backoffice stuff, fundamenally scoped/shardable to defined regions, etc etc). And since Datomic is built with that in mind, you get the trade-off of full history, first class transactions and being able to query for thing…

For me it is simply that isn't open source (at least last I checked.)

Re: Soft deletion probably isn't worth it

#166

Post by nicoburns »

tfigment wrote: post #72
vyrotek wrote: post #45

I've found SQL Server Temporal Tables are a good alternative to get the benefits of soft-deletes without some of the drawbacks. https://docs.microsoft.com/en-us/sql/relational-databases/ta...

Mysql also has this now. I've wanted to rewrite out apps to use it but haven't gotten around to it. Postgres has it as an addon but feels like it wouldn't work for us until its first class supported.

I believe first-class support is in development for Postgres. The article I read made it sound like it would probably land in either the next major version, or the one after that.

Re: Soft deletion probably isn't worth it

#167

Post by corrral »

JohnBooty wrote: post #151

I've been a software dev since the 90s and at this point, I've learned to basically do things like audit trails and soft deletion by default, unless there's some reason not to. Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. It helps the business, it helps you as developer by giving you debug information as well as helping you to c…

I'd add tagging, for anything that could conceivably use it, when you're doing DB design. May as well start with support, even if the functionality's initially dormant. Someone will ask for it, directly or indirectly, and it won't take long before they do.

Re: Soft deletion probably isn't worth it

#168

Post by spfzero »

I like the deleted-items-table suggestion the author makes. It's useful though, to think about the cases where you'd want to delete, say a customer with existing invoices. In one situation, you may have made a mistake and want to start over, say an operator creates the customer and order, but then the customer changes their mind. In that situation a hard delete is in order; you want to "undo" the _creation_ of the customer and invoice, and nothing further has happened as far as referencing their key

In other situations though, you may have some reason to treat the customer as if they were deleted, but better to examine the reason for that, and use an attribute more relevant to that reason, such as active/inactive etc. Would be different for different entities of course.

Re: Soft deletion probably isn't worth it

#169

Post by baq »

justin_oaks wrote: post #160

For those who are expressing favor with soft deletes, do you default to soft deletes on every table unless you know you won't need them? Or do you only apply them where you know you'll need them? I think people arguing for and against soft deletes both understand that there are cases where you want to use them and when you don't.

soft delete everywhere by default. true deletes only after retention policy expires, if FK constraints allow it (best if you can drop whole partitions).

Re: Soft deletion probably isn't worth it

#170

Post by rjzzleep »

JohnBooty wrote: post #151

I've been a software dev since the 90s and at this point, I've learned to basically do things like audit trails and soft deletion by default, unless there's some reason not to. Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. It helps the business, it helps you as developer by giving you debug information as well as helping you to c…

In rails you get these things for free. What I don't get is why everyone rolls their own framework with node.js. It's basically 90s PHP all over again.

EDIT: Soft delete is a trivial piece of code when the framework has a well defined transaction system for its ORM. It's not really related to Rails per se. Your statement is extremely disingenuous, while trying to look smart. Audit trails _can_ be(but don't have to be) more complex, especially when the framework uses a lot of stored procedures to handle operations. But other than that these frameworks are specifically designed to REDUCE complexity of such operations, dependency costs - which are huge in node.js, specifically because you can mix and match anything into everything.

Node.js people tend to stitch together XSS solutions, random templating solutions based on their frontend work, even basic salting of auth passwords becomes unpredictable because you have 30 options on minimal auth libraries.

But yes nothing is ever free. If you want to use Rails you still have to learn ruby and the framework and a basic understand of how ActiveRecord builds queries if you want to be writing performant code. And the same applies to Laravel, Django, or whatever of the 50 patchwork node.js solutions you want to base your code on.

Post reply on HN