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

311–320 of 514 posts

Re: Soft deletion probably isn't worth it

#311

Post by JohnBooty »

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…

    but that is less of a cost than having to add "deleted=False" 
    predicates in all of your queries.
Maybe or maybe not. You can use a view. Or you may be using an ORM that lets you set a default scope (essentially, a default WHERE clause - ActiveRecord lets you do this)

It also depends on if you're designing an app for this from the ground up or if you're trying to retrofit an existing app with 90 million different hardcoded queries.

Also depends on what you want to do with the deleted records. Do you want to do things with them? Maybe after a user is deleted, you want them to be able to log in, but you would prefer them to see a "sorry - your account has been deleted" message instead of "user not found." Maybe you want your support staff to be able to look at deleted users. Etc. Now you need to update your app logic so that it's flexible enough to look at "users_deleted" and "users". Which may be at least as onerous as messing with the WHERE clause on every single query.

To be clear, I don't hate the "another table" solution. It's the right choice in a lot of situations IMO.

Re: Soft deletion probably isn't worth it

#312

Post by zrobotics »

alerighi wrote: post #216
gmiller123456 wrote: post #172

Earlier quoted context omitted.

The author uses the "no one ever undeleted anything" as the primary justification. I think this is the part they miss. I've never undeleted a user either, but there have been many times I've gone back to look at something. Either a complaint finally gets around to me as to why the user wanted their account deleted (e.g. feature not working) and it helps to figure out why. Or they're returning and want things set up l…

It may be convenient, but under the GDPR is illegal. When an user deletes an account, all the personal data associated with that user must be deleted (or anonymize it in a way that it's no longer possible to associate it back to the particular user). You cannot just keep user information forever "just in case" they are useful again.

What we did (and legal was OK with it) was to create a "GDPR Delete" function in the admin backend. It doesn't actually delete, but sets a soft delete identical to the regular user delete function and overwrites any PII with stuff like name:gdpr_delete_name_xxxx

That way log and system context is available for debugging, but the system no longer holds any PII. It does delete some logs, but anonymizing data seemed better than hard deletion. It wasn't hard to do, it took legal longer to approve than writing and testing the code and has come in handy a few times for debugging errors.

Edit: typo

Re: Soft deletion probably isn't worth it

#313

Post by he0001 »

astura wrote: post #278
alerighi wrote: post #216

Earlier quoted context omitted.

It may be convenient, but under the GDPR is illegal. When an user deletes an account, all the personal data associated with that user must be deleted (or anonymize it in a way that it's no longer possible to associate it back to the particular user). You cannot just keep user information forever "just in case" they are useful again.

>under the GDPR [soft delete] is illegal This is obviously not always true. Any European can't, for example, delete their online account with their mortgage company and demand that the mortgage company deletes the records saying that they owe them money so they can get their house for free. Nor can anyone call up their previous employer and require them to delete all the work they ever did from their company's comput…

> Nor can anyone call up previous employer and require them to delete all the work they ever did from their company’s computer systems.

You can’t delete IP as this is not covered by GDPR, but you sure can ask them to delete your identification data from their records as GDPR also works for employees not just customers. It’s a problem with for example vcs.

Unless there’s another law in ruling, GDPR is the baseline. It’s for any identification of an individual, regardless of usage. Most “media apps” don’t have any other law that’s in effect, so GDPR is mandating what you need to do. (IANAL but worked on implementing GDPR with lawyers).

Re: Soft deletion probably isn't worth it

#314

Post by dinvlad »

dataflow wrote: post #280
jcheng wrote: post #276

Earlier quoted context omitted.

GDPR fines are structured to make it extremely risky/expensive to take the “ask for forgiveness not permission” approach you’re suggesting. I think for this reason, the fine is designed to scale with the size of the offending company’s revenue, not so much with the actual damages suffered by anyone. Take a look at some of these fines, tens of millions of euros cause the UI for your cookie consent dialog is poorly des…

I don't know if you were the exception or the rule, but I know for a fact some do go the forgiveness approach for requirements that they believe would be disproportionately burdensome. Probably almost all companies actually, including yourself (just to a different extent) - what did you do about the storage remapping thing I mentioned? Did it come up/did you guys discuss it? Do you believe you're in compliance despit…

Please, don't give ideas to whomever among their lawyers reads this later :-). We don't need another Schrems II..

Re: Soft deletion probably isn't worth it

#315

Post by dataflow »

dinvlad wrote: post #314
dataflow wrote: post #280

Earlier quoted context omitted.

I don't know if you were the exception or the rule, but I know for a fact some do go the forgiveness approach for requirements that they believe would be disproportionately burdensome. Probably almost all companies actually, including yourself (just to a different extent) - what did you do about the storage remapping thing I mentioned? Did it come up/did you guys discuss it? Do you believe you're in compliance despit…

Please, don't give ideas to whomever among their lawyers reads this later :-). We don't need another Schrems II..

Sorry!

Re: Soft deletion probably isn't worth it

#316

Post by JohnBooty »

cpursley wrote: post #310
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…

Can you recommend any best practices, design patterns, etc - for implementing soft delete in SQL systems?

I think the best answer is to optimize for the expected data and use cases.

- How many records are there? Enough that performance will be an issue? If I'm planning on keeping things in the same table, can I utilize database features like indexes and partitions to mitigate any perf issues? (For some access patterns, partitions might solve 100% of your perf concerns)

- How common is deletion? Are we expecting 1% of the records to be soft-deleted, or more like 90%? If it's the latter, you may not want all those records clogging up your main table.

- Is this greenfield development, or am I adding soft-delete to an existing app? Greenfield favors "same table" soft delete; if you're retrofitting an existing app it may be better to keep deleted stuff in a separate table so that you don't break existing functionality.

- What do you want to do with the soft-deleted records? Are there times when you want to treat them just like regular records, e.g. "Give me a list of all the users who joined last week, even if we've deleted their accounts?" If the answer is "yes" then a lot of those things will probably be easier if you keep deleted and non-deleted in a single table.

Re: Soft deletion probably isn't worth it

#317

Post by sgjohnson »

znpy wrote: post #60
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_...

Your taste in database design is probably nor gdpr compliant, i hope you don’t work in the eu.

GDPR is probably the worst piece of law ever written.

If an account is linked to invoices, it is perfectly reasonable to keep personally idenfitiable information for up to 10 years (would depend on each member state, but I don't think any have laws requiring you to keep invoices for more than 10 years). And because of that legal requirement you can justify keeping an audit trail of everything relevant for those transactions.

Re: Soft deletion probably isn't worth it

#318

Post by dinvlad »

dataflow wrote: post #315
dinvlad wrote: post #314

Earlier quoted context omitted.

Please, don't give ideas to whomever among their lawyers reads this later :-). We don't need another Schrems II..

Sorry!

I think lawyers probably miss a lot of these technicalities (which explains why they can require something as technically unreasonable as Schrems II), but I just wonder if someone more technical can actually push for something like that, to win themselves some questionable points, in the name of improving privacy.

Re: Soft deletion probably isn't worth it

#319

Post by JohnBooty »

corrral wrote: post #167
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…

I'd add tagging, for anything that could conceivably use it, when you're doing DB design. May as well start with support, even if the functionality's initially dormant. Someone will ask for it, directly or indirectly, and it won't take long before they do.

I personally would not agree.

But... your experiences are also real and I believe you when you say that your experiences DO support your conclusion. :)

The "soft delete" design decision is usually pretty impactful and is in my experience potentially much more of a pain in the butt to implement later if you haven't included it from day 0.

Audit trails and soft-deletes are also crazy useful for developers (both for debugging and for general cover-thine-ass utility) even if end users never touch them.

Whereas, tags are easier to tack on later and are not intrinsically useful to developers.

Re: Soft deletion probably isn't worth it

#320

Post by JohnBooty »

makeitdouble wrote: post #293
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…

> unless there's some reason not to. Yes. To note, with GDPR there's now legal reasons to do so regarding user personal data. That can be the moment the devs realize they actually can't delete the data, because they soft deleted for so long, many relations are now interlocked and the data model needs to be changed to give a starting point to the deletion cascade. My lesson from that was to at least have one test dele…

    My lesson from that was to at least have one test 
    deleting a mock user that spans the maximum data 
    breadth of the service . We caught a bunch of these 
    loops in test at dev time and that was pretty great.
Thank you. That is SUPER insightful. If it wasn't too late to edit my initial post I would add this.
Post reply on HN