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

51–60 of 514 posts

Re: Soft deletion probably isn't worth it

#51

Post by firloop »

dafelst wrote: post #48
firloop wrote: post #43

Earlier quoted context omitted.

Views can really bite you performance wise, at least with Postgres. If you add a WHERE against a query on a view, Postgres (edit: often) won't merge in your queries' predicates with the predicates of the view, often leading to large table scans.

IIRC Postgres has supported predicate push down on trivial views like this for over a decade now, and possibly even more complex views these days (I haven't kept up with the latest greatest changes).

Postgres can do it, you're correct, but in my experience it rarely happens with any view that's even slightly non-trivial even on recent versions of Postgres. Most views with a join break predicate pushdown. It greatly reduces the usecases of views in practice.

Re: Soft deletion probably isn't worth it

#52

Post by GartzenDeHaes »

munk-a wrote: post #35
GartzenDeHaes wrote: post #23

Personally I like no delete designs, which give you a full audit history of changes. This is similar to generally accepted accounting principles. https://en.wikipedia.org/wiki/Generally_Accepted_Accounting_...

So if an account was active in your system and is active no longer... do you soft delete it (even if that means UPDATE ... SET active = 'f') or hard delete it?

That depends on the problem domain and how you design the system, since there are several way to do this. Typically in financial systems you would either have a start and end date on a summary record, or add an inactive record to a transaction table that has a record for each change to the account, or both.

Re: Soft deletion probably isn't worth it

#55

Post by [deleted] »

firloop wrote: post #43
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…

Views can really bite you performance wise, at least with Postgres. If you add a WHERE against a query on a view, Postgres (edit: often) won't merge in your queries' predicates with the predicates of the view, often leading to large table scans.

[deleted]

Re: Soft deletion probably isn't worth it

#56

Post by [deleted] »

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.

[deleted]

Re: Soft deletion probably isn't worth it

#57

Post by bjourne »

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 have a "deleted" column in the Customer table you need to remake the Invoice table as a view so that it hides invoices for deleted customers. The same goes for the InvoiceItem table (foreign key of a foreign key) and all author auxiliary information related to the soft-deleted customers.

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!

The author is right; soft-deletes are probably most definitely not worth it. There are many better ways to solve the problem.

Re: Soft deletion probably isn't worth it

#58

Post by dexwiz »

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…

You may end up doing this anyways if you have any application code that needs access to delete hooks, or access control varies across objects. At this point, you are probably using a ORM instead of direct queries, and place logic that could be in the db instead at the app layer.

Re: Soft deletion probably isn't worth it

#59

Post by trinovantes »

jewayne wrote: post #7
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.

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.

Assuming you're using a modern database, replication is done with paxos/raft and they are formally proven to not allow this to happen as both edits/deletions are both just entries in distributed event log.

Re: Soft deletion probably isn't worth it

#60

Post by znpy »

GartzenDeHaes wrote: post #23

Personally I like no delete designs, which give you a full audit history of changes. This is similar to generally accepted accounting principles. https://en.wikipedia.org/wiki/Generally_Accepted_Accounting_...

Your taste in database design is probably nor gdpr compliant, i hope you don’t work in the eu.
Post reply on HN