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

71–80 of 514 posts

Re: Soft deletion probably isn't worth it

#71

Post by hn_throwaway_99 »

The deleted records table he mentions at the end is a good approach, but:

1. This can easily be done with a trigger, so that you just call a DELETE on the table and deleted tables are copied to the deletion table automatically.

2. I prefer, instead of having a jsonb column, that each table has a corresponding `deleted_original_table_name` table that exactly matches the schema of the base table, with the addition that the first column is a `deleted_at` timestamp. It's easy to use helper methods in schema migrations to always keep the table definitions in sync.

Re: Soft deletion probably isn't worth it

#72

Post by tfigment »

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.

Re: Soft deletion probably isn't worth it

#73

Post by semiquaver »

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…

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 fast, but can be catastrophic if you have any matviews in your table dependency tree. A matview can easily turn what should be an instantaneous DDL operation into a partial outage while the matview is being regenerated.

(this is all postgres specific, it may be untrue for other systems)

Re: Soft deletion probably isn't worth it

#74

Post by kardianos »

This poster misses the point completly. Soft delete is a must have for historical data, where you want to keep history, but keep the current set clean.

Effectively, you don't check for the soft delete flag if you get to it from a an un-deleted record, but you do check for it if you access it the other way around.

Re: Soft deletion probably isn't worth it

#75

Post by chrisshroba »

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…

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?

Re: Soft deletion probably isn't worth it

#76

Post by kukx »

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…

Is not there any attempt to improve the soft deletion at the engine/SQL level? I can see it as a possible feature request.

Re: Soft deletion probably isn't worth it

#77

Post by zozbot234 »

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 be used to implement pretty much any kind of automated inference, reasoning, rules etc. on the "raw" table data. The example of filtering out deleted records is just one of the simplest. That one single feature can easily transform a simple DB platform into a fully-featured knowledge base system, easily usable to support even complex reasoning tasks.

Re: Soft deletion probably isn't worth it

#78

Post by aeyes »

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…

At least in Postgres, having a huge amount of "dead" data in large tables is problematic because vacuum always has to read the full data set.

Even with conditional indexes where you exclude deleted data you take a significant performance hit reading dead blocks because there is no way to quickly vacuum them. You accumulate hours of bloat until your vacuum finishes.

You can't beat a separate insert only archive table which you never have to vacuum.

Re: Soft deletion probably isn't worth it

#80

Post by MonkeyMalarky »

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

Ha, and then there is the opposite regulation that you have to delete user data.

Well you see, you need a complete record of when it was created, every change that occurred, everyone who could view it and also log every access attempt. But it. You're not actually supposed to keep it. Just everything surrounding it.
Post reply on HN