Soft deletion probably isn't worth it • Hacker News Board

Re: Soft deletion probably isn't worth it

#141

Post by Volundr » Tue, Jul 19, 2022, 8:45 PM UTC

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…

I'm less likely to use triggers, but I'll say I pretty much always want proper foreign key relationships set up in the database. Unique and other constraints too. In principal I might agree with you that it's an application level concern, but being able to setup these kind of invariants and trust that they will be enforced even in the face of bugs in my code (and there will be bugs in my code) is just too powerful to let go of in the name of purity. I'd much rather let a bit of business logic creep between multiple layers that discover that I have a whole bunch of orphaned records and no sensible way to reconcile them.

Re: Soft deletion probably isn't worth it

#142

Post by nfm » Tue, Jul 19, 2022, 8:46 PM UTC

munk-a wrote: post #91

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…

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.

Re: Soft deletion probably isn't worth it

#143

Post by wvenable » Tue, Jul 19, 2022, 8:47 PM UTC

pbardea wrote: post #49

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…

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

Often you don't have to rely on ON DELETE CASCADE relationships. Because you are never deleting anything, you will never have any orphaned records. If you don't want to see say Invoices for a deleted Customer then that's just another filter feature.

Mostly I use soft-delete because for auditing requirements we pretty much can't remove anything but also because nothing ever truly goes away. If we have an Invoice or Order then, from our perspective, we must have those forever even if the corresponding client is deleted and can never place another one.

Re: Soft deletion probably isn't worth it

#144

Post by scifibestfi » Tue, Jul 19, 2022, 8:47 PM UTC

> When I worked at Heroku, we used soft deletion. When I worked at Stripe, we used soft deletion. At my job right now, we use soft deletion.

> As far as I’m aware, never once, in ten plus years, did anyone at any of these places ever actually use soft deletion to undelete something.

That's wild. So it seems the idea of needing undelete is largely an unfounded fear.

Re: Soft deletion probably isn't worth it

#145

Post by pierrebai » Tue, Jul 19, 2022, 8:50 PM UTC

The author claims pruning soft-deleted entries requires a complex query, but hard-deleting an entry would have required the same complexity. So it's really not an argument.

Re: Soft deletion probably isn't worth it

#146

Post by teej » Tue, Jul 19, 2022, 8:50 PM UTC

enepture wrote: post #124

semiquaver wrote: post #73

Earlier 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

dbt is great, but I'm not sure it's appropriate to manage objects in a production transactional database. It's designed for analytical, columnar databases.

Re: Soft deletion probably isn't worth it

#147

Post by scott_w » Tue, Jul 19, 2022, 8:52 PM UTC

The example the author gives is… frankly awful.

I can’t think of a single case where you’d want to remove the invoices of a customer you delete. Ever. In fact, the opposite is more likely to be a big problem, accidentally cascading your delete to your financial records!

Using a soft delete, your invoices won’t “disappear” because your app WILL have a view for looking at just the invoices.

Source: I built a bookkeeping system and soft deletes is a necessary feature.

Re: Soft deletion probably isn't worth it

#148

Post by outworlder » Tue, Jul 19, 2022, 8:52 PM UTC

I wish Datomic was made open-source (with maybe some features available as an 'enterprise' offering) so that we could actually have a decent alternative for this 'soft-delete' problem.

Re: Soft deletion probably isn't worth it

#149

Post by ibejoeb » Tue, Jul 19, 2022, 8:53 PM UTC

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…

> developers underutilize the capabilities of the massively advanced database engines

So true. There are so many amazing, powerful features in all of the major players.

Also: updatable views are amazing. With query rewriting (whatever you vendor calls it) you can affect some truly material changes to the system without any changes to the client applications. An example would be implementing temporal relations.

Re: Soft deletion probably isn't worth it

#150

Post by ThePhysicist » Tue, Jul 19, 2022, 8:53 PM UTC

I don't get what the problem is with cascading deletes. I mean you typically only use them for foreign keys where deletion of the parent object makes the referencing object simply invalid, so there would be no reason to leave the referencing object in the database.

The point that is true is that queries get more complicated as you'll have to add a "WHERE deleted_at IS NULL" to every SELECT (once for each table you refer to), but that can be automated if you use an ORM. A paradigm that I often use is that all objects in the database belong to a role object that determines who can read/write/delete the given object. So before doing anything with an object I always check the role object (e.g. the "user" referencing an "invoice", to stay with the example OP gives), and as part of this I check whether the user object still exists. Alternatively, you could automate most of the required update logic using triggers as well.

But otherwise I agree, soft deletes often don't seem to be a worthwhile tradeoff, not sure if I would use them again when designing a relational schema. They are very useful for auditing and undo though: In a current project, whenever a set of objects gets updated I soft-delete the old versions and create new objects, keeping the UUIDs intact. That allows me to display the entire version history of each object to the user, which can be necessary e.g. for compliance reasons. You can achieve this with an audit log as well but that would require more logic and different queries, whereas querying soft-deleted objects just requires a slight modification of existing queries.