I use soft deletes to maintain insights. For instance I would like to know how many users that has been created in total even if some has been deleted later on. Is this a bad approach? Most of the other comments here seems to use it only to be able to restore deleted entries.
Soft deletion probably isn't worth it
131–140 of 514 posts
Re: Soft deletion probably isn't worth it
#132Re: Soft deletion probably isn't worth it
#133Earlier quoted context omitted.
> Furthermore, the cost of an error is potentially massive. Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers! I'm not sure I buy this argument. It's certainly conceivable for that to happen, but no more so than any other case of "the engineer queried the…
> "the engineer queried the wrong table and thus got incorrect results." The difference is that the path of least resistance, the most obvious method - just query the damn table - is incorrect. Bad design can certainly make a system more error prone.
In the example from this thread the names of the table and view are reasonably clear, so even in a hypothetical project without any external documentation of naming conventions or engineering processes (code review, etc.) the most obvious thing would be to query blah instead of blahwithdeleted.
Of course, this is extremely hypothetical, and in any real project where you're generating a financial report you absolutely must have a detailed understanding of the relation that you're aggregating over. Even if your project has very strict naming conventions for tables, views, etc. you've gotta put in more work than "this short string sounds like a plausible label for the relation I want to aggregate over."
Re: Soft deletion probably isn't worth it
#134> The concept behind soft deletion is to make deletion safer, and reversible
IME, as with “updated_by” and “last_modified_at” columns, it's usually hazy audit requirements, not making deletion reversible, that motivates it.
A proper history store maintained by appropriate triggers solves this, and leaves the referential integrity constraints on the base table intact. (It can also be used for reversibility if you need that.)
Views conceptually would work, but then you get bitten by all the ways that all relations are not equal in real-world RDBMSs.
Re: Soft deletion probably isn't worth it
#135Dumb solution: make soft deletes explicit in your backup system.
Your company has a database backup system right? That system should be configured so that when it runs a backup, it will not remove deleted entries from the previous backup, instead just mark them as "deleted_since" the current backup time.
Idk if any backup system actually support this, if there's some glaring problem (like you can't just overwrite parts of a database backup for some reason), or if most companies just don't have backups because they're too expensive (probably not), but this is the solution I would go with. It works for other sorts of data like file systems as well.
Re: Soft deletion probably isn't worth it
#136Earlier quoted context omitted.
The main problem with views for this use case in practice is that they ossify your schema. Views and matviews are effectively a dependency tree, and many common types of schema evolution become substantially more difficult when the system forces you to wrap your DDL in a series of view drop/recreation steps. This is merely annoying when dealing with regular views because recreating even a large number of views is fas…
As an FYI using a tool like DBT solves this problem. As someone who was not a data engineer I was not familiar, there were tools like this
I have this problem. What is DBT?
Re: Soft deletion probably isn't worth it
#137Earlier quoted context omitted.
Is not there any attempt to improve the soft deletion at the engine/SQL level? I can see it as a possible feature request.
If you're using PostgreSQL, you can implement cascading soft-deletes yourself. The information schema table holds all foreign key relationships, so one can write a generic procedure that cascades through the fkey graph to soft-delete rows in any related tables.
Could someone take a stab at an example of what this would look like? Sounds really interesting.
Re: Soft deletion probably isn't worth it
#138In a previous place I worked, we were programmatically using Box to store files. One day we were presented with a case study in Murphy's Law: a script went awry and deleted everything (10s of thousands of files). There was no clear way to recover these files, they were gone from what we could see. It was a disaster. We got a Box support person on the phone and described what had happened. There was a pause, some mouse clicking and then: "Ok, those files will be back in your account in an hour."
It was 100% our fault. But soft deletes saved us that day. If you're in a situation where you or your customers could benefit from the same, it's wise to not only embrace them but also make sure they work.
Re: Soft deletion probably isn't worth it
#139Earlier quoted context omitted.
How would a view solve the foreign key issue? Are you suggesting coding specific deletion triggers into the view such that appropriate foreign keys are "cascade" deleted when a row in the view is deleted?
because when you are getting a child record through a join (in the view), the parent will never have deleted_at set. Say I have a simple view `select * from foo join bar on foo.foo_id = bar.foo_id where foo.deleted_at is null` I never have to worry about deleting from bar, because I should never grab a child when the parent is 'deleted'.
Assuming that the joins are always joined against the views, not the raw tables. This introduces an opportunity for a mistake.
Re: Soft deletion probably isn't worth it
#140Earlier quoted context omitted.
My experience at a few start-ups has been that account deletion just isn't prioritized. It's not a focus when building an MVP. If the application ever gains traction, everyone is then terrified they'll accidentally delete customer data that they never delete anything. It's a shame. As a user, when I delete my account or data in my account, I want you to permanently delete it, not keep it around and just make inaccess…
If it makes you feel better, the startups that don't have time to delete your data probably don't have a viable disaster recovery plan either. As we learned from the Atlassian snafu, even giant companies with billions in revenue often can't recover from disasters. (I try to test mine every 6 months. I've never had a test go perfectly.)
Good to know. What do you were tests look like?