One reason we encourage keeping soft-deleted records at least for a while is synchronizing data across systems. We want to propagate deletions downstream. At some point when all downstream consumers have caught up, we can purge the soft-deleted records.
Soft deletion probably isn't worth it
121–130 of 514 posts
Re: Soft deletion probably isn't worth it
#122Views 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…
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…
To avoid an outage, have you tried fronting the matview with an additional view to allow hot-swapping?
Re: Soft deletion probably isn't worth it
#123The assumption seems to be that the undelete operation is performed by the vendor's support staff, rather than the end user. I've been involved in the implementation/ maintenance of systems with soft delete that was entirely for that purpose - it allowed the user to delete/undelete at will. In our case it also meant certain uniqueness constraints were kept in place effectively reserving things like email addresses or business registration numbers that couldn't be reused until a hard delete was issued.
Arguably it's more like an "is active" flag in such a case, but it's debatable what the distinction is.
Re: Soft deletion probably isn't worth it
#124Views 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…
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
Re: Soft deletion probably isn't worth it
#125Earlier quoted context omitted.
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…
> 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.
Re: Soft deletion probably isn't worth it
#126Views 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…
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…
it is very dangerous to have dependency on materialized view - it is a poor architectural decision from DBA to do that.
if you want view depending on mat view - materialize it yourself in a table, and refresh it yourself controllably.
Re: Soft deletion probably isn't worth it
#127"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.
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…
I've also seen businesses retain "deleted" data in order to support legitimate data analysis work in the future. And it actually can help significantly. Maybe scrubbing PII from deleted accounts is a good idea, but those deleted accounts are perfectly good data points, especially in smaller/newer companies with lower-volume data streams.
Re: Soft deletion probably isn't worth it
#128We use soft-deletes extensively at our startup. Here's a couple reasons: - Feature creep. "Sometimes our users accidentally hit the delete button, or change their minds a minute later. We want to give them a way to undo the deletion." Or "I know we said last quarter that we users want to delete stuff, but they also want to see a list of everything they've deleted in the past." Soft-deletes handle feature-creep a lot…
It seems that it is too easy to delete things in your system. Rather than solving it with reversible soft deletes I would suggest to improve the UX. I don’t agree that it simplifies foreign key management, it is most often the opposite from my point of view.
Re: Soft deletion probably isn't worth it
#129My 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…
Just want to add that the downsides as identified in the article make little sense. Deleting a customers invoices should be a very rare thing. I can't imagine any accountant or auditor is going to be happy with an IT guy deciding when to delete invoices.
If accidentally writing the wrong query is a problem, then writing the wrong query is your problem.
Re: Soft deletion probably isn't worth it
#130Views 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…
> 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 level because you get into a situation where you're sharing business logic between two (or more places).