Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The challenges of soft delete

atlas9.dev

Post reply on HN
Print view View original

71–80 of 157 posts

Re: The challenges of soft delete

#71

Post by tharkun__ »

pixl97 wrote: post #53
tharkun__ wrote: post #36

Earlier quoted context omitted.

Agreed. And if deletes are soft, you likely really just wanted a complete audit history of all updates too (at least that's for the cases I've been part of). And then performance _definitely_ would suffer if you don't have a separate audit/archive table for all of those.

I mean, yes, growth forever doesn't tend to work. I've seen a number of apps that require audit histories work on a basis where they are archived at a particular time, and that's when the deletes occurred and indexes fully rebuilt. This is typically scheduled during the least busy time of the year as it's rather IO intensive.

Oldest I've worked with was a project started in ~1991. I don't recall when they started keeping history and for how long and they might have trimmed history after some legal period that's shorter but, I worked on it ~15 years after that. And that's like what, 15,..., 20 years ago by now and I doubt they changed that part of the system. You've all likely bought products that were administered through this system.

FWIW, no "indexes fully rebuilt" upon "actual deletion" or anything like that. The regular tables were always just "current" tables. History was kept in archive tables that were always up-to-date via triggers. Essentially, current tables never suffered any performance issues and history was available whenever needed. If history access was needed for extensive querying, read replicas were able to provide this without any cost to the main database but if something required "up to the second" consistency, the historic tables were available on the main database of course with good performance (as you can tell from the timelines, this was pre-SSDs, so multi-path I/O over fibre was what they had at the time I worked with it with automatic hot-spare failover between database hosts - no clouds of any kind in sight). Replication was done through replicating the actual SQL queries modifying the data on each replica (multiple read replicas across the world) vs. replicating the data itself. Much speedier, so that the application itself was able to use read replicas around the globe, without requiring multi-master for consistency. Weekends used to "diff" in order to ensure there were no inconsistencies for whatever reason (as applying the modifying SQL queries to each replica does of course have the potential to have the data go out of sync - theoretically).

Gee, I'm old, lol!

Re: The challenges of soft delete

#72

Post by phito »

yxhuvud wrote: post #65
salomonk_mur wrote: post #39

Earlier quoted context omitted.

But... It's true. Deleting data completely is an easy way to gimp and lobotomize your future analysis. Storage is cheap. Never delete data.

Deleting data is also a very easy way to not get GDPR compliance issues. Data is a cost and a risk, and should be minimised to what is actually relevant. Storage is the least part of the cost.

Not an issue if you're not building SaaS

Re: The challenges of soft delete

#73

Post by nitwit005 »

3rodents wrote: post #29

Soft deletes are an example of where engineers unintentionally lead product instead of product leading engineering. Soft delete isn’t language used by users so it should not be used by engineers when making product facing decisions. “Delete” “archive” “hide” are the type of actions a user typically wants, each with their own semantics specific to the product. A flag on the row, a separate table, deleting a row, these…

I'd be careful of thinking of everything as product facing or not. Many features are for support, legal compliance, etc.

It's fairly common in some industries to get support requests to recover lost data.

Re: The challenges of soft delete

#74

Post by patates »

Trigger-based approach is the only one that really works in my experience. Partition the archive table in a way that makes sense for your data and you're good to go.

Some more rules to keep it under control:

Partition table has to be append-only. Duh.

Recovering from a delete needs to be done in the application layer. The archive is meant to be a historical record, not an operational data store. Also by the time you need to recover something, the world may have changed. The application can validate that restoring this data still makes sense.

If you need to handle updates, treat them as soft deletes on the source table. The trigger captures both the old state (before update) and continues normally. Your application can then reconstruct the timeline by ordering archive records by timestamp.

Needless to say, make sure your trigger fires BEFORE the operation, not AFTER. You want to capture the row state before it's gone. And keep the trigger logic dead simple as any complexity there will bite you during high-traffic periods.

For the partition strategy, I've found monthly partitions work well for most use cases. Yearly if your volume is low, daily if you're in write-heavy territory. The key is making sure your common queries (usually "show me history for entity X" or "what changed between dates Y and Z") align with your partition boundaries.

Re: The challenges of soft delete

#75

Post by [deleted] »

hnthrow0287345 wrote: post #49

Maybe I'm shooting for the moon, but I'd like soft delete to be some kind of built-in database feature. It would be nice to enable it on a table then choose some built-in strategies on how it's handled. Soft-delete is a common enough ask that it's probably worth putting the best CS/database minds to developing some OOTB feature.

[deleted]

Re: The challenges of soft delete

#77

Post by keithluu »

rorylaitila wrote: post #16

Databases store facts. Creating a record = new fact. "Deleting" a record = new fact. But destroying rows from tables = disappeared fact. That is not great for most cases. In rare cases the volume of records may be a technical hurdle; in which case, move facts to another database. The times I've wanted to destroy large volume of facts is approximately zero.

Agreed. In fact I believe there should be 2 main operations in a data store: retrieve and insert. For this to actually work in practice, you probably need different types of data stores for different phases of data. Unfortunately few people have a good understanding of the Data life cycle.

Re: The challenges of soft delete

#78

Post by d0100 »

jamilbk wrote: post #22

At Firezone we started with soft-deletes thinking it might be useful for an audit / compliance log and quickly ran into each of the problems described in this article. The real issue for us was migrations - having to maintain structure of deleted data alongside live data just didn't make sense, and undermined the point of an immutable audit trail. We've switched to CDC using Postgres which emits into another (non-rep…

In simple projects where database is only changed via an API, we just audit the API instead. It's easier to display and easier to store than tracking each DB change a single transaction does

Re: The challenges of soft delete

#79

Post by refset »

Centigonal wrote: post #63
hnthrow0287345 wrote: post #49

Maybe I'm shooting for the moon, but I'd like soft delete to be some kind of built-in database feature. It would be nice to enable it on a table then choose some built-in strategies on how it's handled. Soft-delete is a common enough ask that it's probably worth putting the best CS/database minds to developing some OOTB feature.

Many data warehousing paradigms (e.g. Iceberg, Delta Lake, BigQuery) offer built-in "time travel," sometimes combined with scheduled table backups. That said, a lot of the teams I've worked with who want soft-delete also have other requirements that necessitate taking a custom approach (usually plain ol' SCD) instead of using the platform-native implementation.

> other requirements

In my experience, usually along the lines of "what was the state of the world?" (valid-time as-of query) instead of "what was the state of the database?" (system-time as-of query).

Post reply on HN