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

391–400 of 514 posts

Re: Soft deletion probably isn't worth it

#391

Post by [deleted] »

JohnBooty wrote: post #373
avianlyric wrote: post #282

Earlier quoted context omitted.

Guess we’ll find out when a leaks happen, and companies start getting fines. GDPR does differentiate between structured data (I believe it uses the term “identifiable records” or similar), and huge heap of unstructured data where an individuals data can’t be quickly retrieved as it’s own atomic unit. With much stricter requirements for anything structured. So for data on a HDD that could be recovered, but is an unstr…

What does GDPR say about obscuring data? Instead of hard-deleting a structured record for Bob Smith, can you leave the record intact and scrub it of identifying data so that I don't e.g. break all of the records for orders that Bob Smith made?

[deleted]

Re: Soft deletion probably isn't worth it

#392

Post by mixmastamyk »

Pxtl wrote: post #120

Trivial case I hit: 1) Client wants to remove user from the system who have left their org but 2) There are objects that were contributed by that user which are required to persist beyond the user's deletion. Those are ideal cases for soft deletion. We can still query information about the deleted user to explain who created this object, with the note that their account has been deleted. Probably I should be doing fu…

Another way to handle is to set each obj.user to a “Deleted User” record.

Re: Soft deletion probably isn't worth it

#393

Post by jandrewrogers »

smolder wrote: post #381
ikiris wrote: post #326

Earlier quoted context omitted.

The really hard part is dealing with the data still in backups. It leads to having to do crazy things like individual keyed encryption per user, escrow hilarity, etc.

Thanks for that insight. It's an interesting solution to use per-user encryption for that. Don't you have the same problems all over again with respect to preserving access to user specific data? I.e. needing key backups for business continuity, but needing to wipe those keys for deletion requests.

Per user encryption doesn't work in real systems. These have been implemented for several decades and no one uses them because they have pathologically terrible scalability and performance. The simple act of requiring encryption of individual user data forces the use of extremely suboptimal data structures relative to what would be used in the non-encrypted case.

A single AES encryption block is 16 bytes; internal storage of records is managed in terms of individual bits if you don't encrypt them. You also can't compress data encrypted in this way, which is table stakes for databases. The encryption key schedule alone for a single record will be much larger than the typical record! Requiring a per-user encryption key has the unfortunate effect of bloating database storage and memory requirements by at least 10x, while massively reducing computational throughput.

The design of delete-efficient database kernels is an interesting unsolved problem in computer science. No one knows how to do it. People that think there are trivial solutions have not studied the problem adequately. It hasn't been a priority to solve this problem, but its reputation for being theoretically difficult is well-deserved.

Re: Soft deletion probably isn't worth it

#394

Post by techdragon »

JohnBooty wrote: post #370
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…

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. Yeah. As far as a user-facing "Undelete" button existing or being used... that's very rare in my experience. What's much more common is a user accidentally deletes some data. They deny they made an error. The developers are blamed. You then have to go on a wild goose chase figuri…

One of the common ways I've seen it is writing logs in a write only way. Most common way I've seen deployed is having the audit logs setup so they go to files in an AWS S3 bucket owned by a completely separate AWS account with Write Only credentials granted to the application doing the log writing. Its effectively "dumped" there and cannot be seen or touched even by the writing application. Sort of like having a 1 way valve in your logging "pipeline".

Re: Soft deletion probably isn't worth it

#395

Post by sa46 »

simcop2387 wrote: post #93
kukx wrote: post #76

Earlier quoted context omitted.

Is not there any attempt to improve the soft deletion at the engine/SQL level? I can see it as a possible feature request.

There's the idea of temporal tables, https://pgxn.org/dist/temporal_tables/ It's not a standard (I think) but it'd let you do a cascading delete and then be able to go and look at the old objects as they were at time of deletion too. You'd need to do things very differently to show a list of deleted objects though.

You lose traditional FK constraints with temporal tables since there's multiple copies of a row. One workaround is to partition current rows separately from historical rows and only enforce FK constraints on the current partition.

Re: Soft deletion probably isn't worth it

#396

Post by codemac »

mixmastamyk wrote: post #390
codemac wrote: post #42

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 function…

Advice should be aimed at the 99% rather than the 1%, right? I guess Heroku and Stripe don’t have the biggest datasets in the world but they are probably larger than most folks will need to manage.

Sure, the analysis is not "wrong" or something. That cannot be judged without a context. I just hope that those building systems they desire to be very large do not follow this post's advice.

Re: Soft deletion probably isn't worth it

#397

Post by moggers »

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…

I'm glad to see this thread. I've been mulling over this exact issue of deleted_at code leakage with a naive soft-delete implementation. My immediate thought was to use views, so its nice to see this is not yet another case of me using crack-brain ideas out of inexperience.

What's an appropriate naming convention?

Should I do it universally and put transparent views in front of all my tables so I don't have to refactor my code to point to new views whenever I do suddenly need to put in a model constraint that isn't 1:1 with my data layer? Is a transparent view a no-op in terms of perf? if it matters, this is being done in Postgres

I will probably make my constraints partial over the not-deleted records, particularly for unique constraints used for upserts. Am I about to footgun myself? Is it even necessary with the new uniqueness behavior with NULLs being implemented in postgres? Will my performance characteristics be better one way or the other in particular circumstances? It sounds like if I have a high ratio of deleted to not-deleted records a partial index becomes necessary.

Re: Soft deletion probably isn't worth it

#398

Post by pmontra »

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.

Last month I had to implement customer deletion on a new project I started working on recently. The basics were: anonymize the user personal data, delete subscriptions, keep anything else. I believe that payments and invoices are not stored in that database so I don't know what happens to them but in general you must not delete that kind of data because GDPR notwithstanding the law requires that they are available for inspection for a long time. Of course they cannot be anonymized in a way that cannot be reversed. Being able to connect them with a trail of operations in the main database is probably useful too. So, soft delete.

Re: Soft deletion probably isn't worth it

#399

Post by coder543 »

dataflow wrote: post #382
coder543 wrote: post #365

Earlier quoted context omitted.

Remapping of blocks isn't the problem here. IANAL, but you should be encrypting all the data for GDPR compliance to begin with. With it encrypted, how the storage device chooses to map its blocks is completely irrelevant. When the key goes away, all the data is erased by definition . Throwing away the key is one of the easiest ways to comply with Right to be Forgotten, from what I've seen. "Oh, but the key is still o…

I have no idea where the sudden hostility in your last paragraph came from, but it's ascribing nonexistent malice to me. I'll try to address a couple of your points the best I can regardless. > Once a month, you copy the per-user keys that haven't been deleted onto a new encrypted volume, then tell the TPM to delete the key for the old encrypted volume: garbage collecting the deleted keys in an unrecoverable fashion.…

You made several comments in a row that were extremely dismissive of how GDPR compliance could be achieved, and your comments repeatedly questioned whether other people considered basic concepts of data erasure. I apologize if I misattributed malice, but I do feel those comments could have been worded differently.

Also, this quote:

> To be honest, in the age of modern overprovisioned storage drives that remap blocks frequently, I'm not really sure you can implement genuine "hard" deletes without choosing significantly unorthodox hardware (or destroying a drive every time you need a single bit erased), no matter how much you want to in software.

That quote feels incompatible with your statement that…

> Quite the contrary in fact—I believe its current requirements can and should be complied with quite cheaply and efficiently

How can it be complied with cheaply and easily if it can’t be complied with without hardware support no matter how much software wants to? Surely you can see my confusion. Your last comment is far more in line with what I would expect to see in a discussion like this.

Regardless, I think I understand the argument in your last comment better now, but I still think destroying keys is the most effective way to achieve compliance. Even if a storage device manufacturer guaranteed that they would attempt to erase the specific block you want erased, there are problems with whether that data is still recoverable by reading the block repeatedly.

Re: Soft deletion probably isn't worth it

#400

Post by sverhagen »

gmiller123456 wrote: post #377
JohnBooty wrote: post #370

Earlier quoted context omitted.

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. Yeah. As far as a user-facing "Undelete" button existing or being used... that's very rare in my experience. What's much more common is a user accidentally deletes some data. They deny they made an error. The developers are blamed. You then have to go on a wild goose chase figuri…

How would you set up a secure audit trail that didn't rely on the application and/or database at some level? A database is fine for an audit trail. But the application shouldn't have permissions to cover up a change. Likewise, the audit trail should record that a record was created, deleted, maybe undeleted, etc and when, by who. Relying only on the "deleted" flag only shows you the current state, not what and when w…

Any tools you recommend here? Or just roll your own with triggers in the database?
Post reply on HN