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

31–40 of 514 posts

Re: Soft deletion probably isn't worth it

#31

Post by tgbugs »

One use case that I think is not sufficiently considered in this is related to two comments I made about a year ago [0, 1].

If you can _actually_ delete something, then that means that a malicious actor can fabricate data an claim that you deleted it. GDPR may be well intentioned but systems that have the ability to remove any record of a thing lay the groundwork for systematic fabrication of data, because any record of the past has been erased.

Operationally, I can totally see why soft delete might be considered to be problematic in certain cases, but from an information security point of view I think it is absolutely critic for protecting users against a whole class of attacks.

0. https://news.ycombinator.com/item?id=27249738 1. https://news.ycombinator.com/item?id=27691442

Re: Soft deletion probably isn't worth it

#32

Post by jelkand »

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

Why not just use an audit table, to keep from littering your indices?

An audit table is a similar, but not entirely equivalent tool. There are circumstance where both audit tables / soft deletes are appropriate, and where only one of the two is. Other systems that work are append-only tables, event driven systems, and I'm sure there are more that I'm not aware of.

Re: Soft deletion probably isn't worth it

#33

Post by munk-a »

llimos wrote: post #11

Do any databases let you refer to constant values in foreign keys? Then you could do FOREIGN KEY (foreign_id, NULL) REFERENCES foreign_table(id, deleted_at)

I don't believe that's possible in postgres at least - but I don't think it's a huge concern either - you can have deleted_at cascade via trigger or just use views to hide the data - both are extremely easy to implement at the DB level without the application devs ever needing to worry about what's what.

Re: Soft deletion probably isn't worth it

#34

Post by habibur »

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?

No PK, FK or Unique constrains on the archive. Rather use simple index to speed up queries.

Re: Soft deletion probably isn't worth it

#35

Post by munk-a »

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?

Re: Soft deletion probably isn't worth it

#36

Post by deerIRL »

As someone who has done development work with Class A data and specifically in the realm of justice, soft deletes aren't simply a good idea, they are required by law.

Most of these downsides are easily mitigatable issues as well. As many users have stated, something like views solves the issue of forgetting the 'deleted' clause.

Lastly, I'm not sure the issue with foreign keys/stray records really resonates with me. I'd be hard pressed to be comfortable allowing a developer or DBA who isn't fully comfortable with the data model to be hard deleting records, let alone flagging them as soft deleted.

Re: Soft deletion probably isn't worth it

#37

Post by m-p-3 »

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

Is the same true in mass surveillance?

and the blockchain.

Re: Soft deletion probably isn't worth it

#38

Post by nousermane »

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…

> (...updatable view...) WHERE deleted_at IS NULL

This is the way. Also, save record creation timestamp, and you can have very flexible "time-machine" selects/views of your table essentially for free.

Re: Soft deletion probably isn't worth it

#39

Post by Apreche »

I agree with the author that a separate table is the way to go, but I go one step further than the author and use database triggers to manage that second table. Alternatively, a combination of database views and triggers can do the same thing without having an actual extra table to manage.

Either way, it allows you to have soft deletion and/or full activity logging functionality without the application having to know about it.

Re: Soft deletion probably isn't worth it

#40

Post by [deleted] »

eeperson wrote: post #29
jewayne wrote: post #7

Earlier quoted context omitted.

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.

Couldn't a malfunctioning sync process undo a soft delete as well?

[deleted]
Post reply on HN