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

41–50 of 514 posts

Re: Soft deletion probably isn't worth it

#41

Post by i_hate_pigeons »

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

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.

not every thing is a transaction though, say a deleted piece of ref data that still has FKs to other stuff etc

Re: Soft deletion probably isn't worth it

#42

Post by codemac »

Well, there are several problems with this analysis when you go very large (>10000 machines):

- For many applications, it's easiest to put the state of the object in the primary key, and thus point reads will fail when something gets deleted. This has other problems though with hotspotting and compaction during deletes. The deleted table doesn't really solve this either.

- For storage systems, GC is critical functionality to implement. Most systems whether they want to believe it or not are glorified storage systems. Garbage collection is hard to do at scale, and I've never seen it implemented as SQL statements rather than code. Especially for GDPR etc.

- For large scale distributed systems, foreign key constraints are rare if impossible to implement with reasonable latency, so they don't exist either way. I haven't worked on a system in >15 years that had fk constraints.

- For large scale restores where you need to undelete trillions of rows, keeping the rows basically pre-assigns the distribution of writes. When you have to re-create the rows, you tend to get intense hotspotting and failures along the way as you attempt to load balance on the keyspace of the writes.

A deleted records table is good for smaller (<10000 machine) systems when latency between nodes can be kept within the same campus. It can really improve performance of your GC if reading by column isn't fast compared to reading by table.

Re: Soft deletion probably isn't worth it

#43

Post by firloop »

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.

Re: Soft deletion probably isn't worth it

#44

Post by jaydub »

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…

Based on my experience, I like the author's approach since it makes things pretty clear-cut and optimized the storage in the core table (in my experience as well, deletes happen frequently and the soft deletes are rarely touched). In large, row-oriented tables that that storage can add up and even with views/materialized views there's a cost to using/maintaining those as well.

Re: Soft deletion probably isn't worth it

#47

Post by airstrike »

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 are such a powerful concept I’m honestly disheartened by how hard it is to use, replicate or leverage that functionality outside of dropping straight into the db shell

Re: Soft deletion probably isn't worth it

#48

Post by dafelst »

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.

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).

Re: Soft deletion probably isn't worth it

#49

Post by pbardea »

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 a CDC stream into a separate historical system of record.

Re: Soft deletion probably isn't worth it

#50

Post by justin_oaks »

tehbeard wrote: post #26
habibur wrote: post #14

Which is why I don't add that extra deleted field. Rather duplicate all the tables into a new database called "archive" and then insert there before deleting from main. That works for updates too, by preserving the old data and showing you a time machine like backlog. But the archive database gets too large over time and you need to purge it periodically. You can create some delete triggers for automating this "save…

How do you account for maintaining integrity in the archive? E.g. you have 3 users sign up with the same email (a unique field) one after the other with deletions in-between each sign-up?

I'm not who you asked the question of, but I do sometimes make use of archive/deleted/history tables. I'll refer to them as history tables from here on out.

In short, I leave data integrity to the original table and drop it for the history table.

The history table isn't identical to the original table. It has it's own primary keys that are separate from the original table. It doesn't include the original table's unique constraints or foreign key constraints. It also generally has a timestamp to know when the record was put there.

Post reply on HN