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

221–230 of 514 posts

Re: Soft deletion probably isn't worth it

#221

Post by tomnipotent »

layer8 wrote: post #105
aeyes wrote: post #78

Earlier quoted context omitted.

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…

Shouldn’t partitioning help with that? (I have no experience with Postgres.)

Only if those partitions are on separate storage, otherwise you have the same number of dead tuples/dirty pages.

Re: Soft deletion probably isn't worth it

#222

Post by duxup »

> All our selects look something like this: SELECT * FROM customer WHERE id = @id AND deleted_at IS NULL;

Solution… a whole other table of deleted stuff… in a new structure.

Man soft deletes just look better to my eye.

Re: Soft deletion probably isn't worth it

#223

Post by anarazel »

aeyes wrote: post #78
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…

Vacuum does not have to read the full data set every time. The visibility map tracks, on a block level, whether all rows in a page are known to be visible to everyone (starting in 8.4 or such) and whether the page is "frozen", i.e., does not contain visibility information that might need vacuuming (starting in 9.6 IIRC).

However, indexes do currently have to be scanned as a whole. But that's only done by autovacuum if there's enough row versions for that to be worth it (in recent versions).

Re: Soft deletion probably isn't worth it

#224

Post by yen223 »

vlunkr wrote: post #65
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…

That doesn't solve the foreign key problem. You can still easily have a reference to a record that is "deleted"

Also in Postgres, you cannot have a foreign key constraint that references a view, not even a materialised view.

I'm with the author on this one. Any soft delete logic does have a tendency to bleed into other systems and make your systems more complicated, for very little gain.

Re: Soft deletion probably isn't worth it

#225

Post by dunkelheit »

This brings back memories... Some time ago I was an intern in a team working on a UGC map editor. We were using this soft-delete pattern and for some task I needed to deploy a database migration that fiddled with the "deleted" status field. It was quite late and after the migration finished I almost went home but for some reason decided to check community forums. There users were having a time of their life taking screenshots of deleted objects that suddenly became visible (many of them quite amusing, including swear words written in 500km letters). Dunno how this escaped testing, but horror of what I have done brought clarity of mind and I quickly found an error and devised another migration that fixed the data. That worked and I was able to finally go home.

So yeah, be careful with the soft-delete pattern :)

Re: Soft deletion probably isn't worth it

#226

Post by smackeyacky »

pradn wrote: post #187
JohnBooty wrote: post #151

I've been a software dev since the 90s and at this point, I've learned to basically do things like audit trails and soft deletion by default, unless there's some reason not to. Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. It helps the business, it helps you as developer by giving you debug information as well as helping you to c…

Soft deletion is just one way to achieve undeletion. The author's proposed solution of moving the resource to another table works just as well. You can move it back to the non-deleted table to perform the undeletion. You can keep around these deleted objects as long as you want; they work as a subset of a proper audit trail. The cost of course is you have more tables, but that is less of a cost than having to add "de…

What seems to be missing here is the DB tech he is using. On a proper database you can do your "undeleted" with triggers and it's relatively trivial. Nonsense like a "deleted" column on your main data table just seems silly.

Re: Soft deletion probably isn't worth it

#227

Post by AdrianB1 »

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.

There is no impact with views in MS SQL. You can also have indexed views and filtered indexes, so you can have even better performance.

Re: Soft deletion probably isn't worth it

#228

Post by michaelt »

dspillett wrote: post #197
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…

> Since I can no longer rely on ON DELETE CASCADE relationships Cascaded deletes scare me anyway. It only takes one idiot to implement UPSERT as DELETE+INSERT because it seems easier, and child data is lost. You could always use triggers to cascade you soft-delete flags as an alternative method, though that would be less efficient (and more likely to be buggy) than the built-in solution that cascaded deletes are. If…

> It only takes one idiot to implement UPSERT as DELETE+INSERT because it seems easier, and child data is lost.

Seems unfortunate to miss out on all the referential integrity benefits of a serious database when hiring standards, training and code reviews should all be preventing idiotic changes.

If I’m making a shopping cart system, I want to know every order line belongs to an order, every order belongs to a user and so on. Anyone who can’t be trusted to write an update statement certainly can’t be trusted to avoid creating a bunch of orphan records IMHO.

Re: Soft deletion probably isn't worth it

#229

Post by madisp »

rodw wrote: post #205
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…

Seriously. That "Downsides: Code leakage" point is nonsensical. ``` CREATE OR REPLACE VIEW active_customer AS SELECT * FROM customer WHERE deleted_at IS NULL OR deleted_at There, I fixed it. Just use `active_customer` instead of `customer ... deleted_at IS NULL`. In fact, since the deleted_at column is a timestamp, the original "leakage" query: ``` SELECT * FROM customer WHERE id = @id AND deleted_at IS NULL; ``` is…

I'd argue that the simple `deleted_at IS NULL` check is not broken - unless your product / domain specifically allows and requires scheduled future deletions adding such logic can easily introduce bugs. For example, you could to get the comparison flipped by accident, and if it's only in one place out of many that bug could go unnoticed for a while.

Re: Soft deletion probably isn't worth it

#230

Post by AdrianB1 »

munk-a wrote: post #110
brtkdotse wrote: post #13

Earlier quoted context omitted.

In banking and bookkeeping, there’s no such thing as a “delete”. Once something is in the ledger you can’t undo it - you have to make a new entry that negates the old one.

Yes, but banks tend to have websites with accounts and those accounts need to be deactivated when a customer should no longer have access (or, even more finicky, specific accounts for a client need to be deactivated or activated as they change their usage). All this essentially forces the use of some sort of soft deletion. (Activation flags are sort of just a more complicated form of soft deletion).

Status (active/inactive/other) and existence (present or deleted) are very different things, they may be separated most of the time. Legal requirements can prevent deletion of inactive data, so in some cases one may have a lot of inactive but must keep data. Soft delete can help in some scenarios, for example you want to give people a way to re-activate accounts, but hide the ones marked as deleted.
Post reply on HN