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

251–260 of 514 posts

Re: Soft deletion probably isn't worth it

#251

Post by dragonwriter »

pbardea wrote: post #49
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…

> This is one gripe I have with soft-deletion. Since I can no longer rely on ON DELETE CASCADE relationships

If you use soft deletes on all tables, you can also cascade them as long as you either cascade updates to the real keys as well, or prevent such updates, by having a deleted flag column on each table, including it in a unique constraint with the actual key column(s), and including it in the foreign key.

Re: Soft deletion probably isn't worth it

#252

Post by gigatexal »

There’s a lot wrong with this write up. Why would anyone want to delete corresponding invoices when you “delete” the corresponding user? And GDPR provides a caveat that if you need the data for a biz usecase like legacy reporting you can keep the data (I think it has to be masked or something but it’s not insane to say you must delete data on request that could materially affect a company like removing transactions).

Just put a filtered index on the column to better query non deleted data.

On the whole I don’t think in practice the author’s take makes much sense.

Re: Soft deletion probably isn't worth it

#253

Post by afiori »

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.

I would guess that there are quite a few limits to that...

A user has a long history of participating on your forum and other users have quoted their messages far and wide. Collectively all of the messages posted on your forums (with or without timestamps) reveal a few PII about the user. Do you have to delete those?

The user filed a bug report about a functionality not working, do you have to delete the text of the bug report?

Arguably if your user table look like [user_id, creation_date, deletion_date, status, account_type] then this table does not contain any PII.

Assuming that user content is not automatically PII, whose responsibility is it to track where PII can be?

Sometimes users doxx themselves (like mistakenly sharing tax return forms instead cat pics), in such a case it is the user responsibility to signal this to you.

If the user filed an issue saying "when I insert my name (Abe Cox) the input validation fails" is it you or the user that need to read through all the issues to find this case?

My point is that GDPR + right to be forgotten cannot make it look like you never had an account at all, especially without user assistance.

Re: Soft deletion probably isn't worth it

#254

Post by semiquaver »

slt2021 wrote: post #126
semiquaver wrote: post #73

Earlier quoted context omitted.

The main problem with views for this use case in practice is that they ossify your schema. Views and matviews are effectively a dependency tree, and many common types of schema evolution become substantially more difficult when the system forces you to wrap your DDL in a series of view drop/recreation steps. This is merely annoying when dealing with regular views because recreating even a large number of views is fas…

it is very dangerous to have dependency on materialized view - it is a poor architectural decision from DBA to do that. if you want view depending on mat view - materialize it yourself in a table, and refresh it yourself controllably.

Aren’t you just saying that materialized views should not be used? I’m pretty sure that was my original point.

Re: Soft deletion probably isn't worth it

#255

Post by doctor_eval »

wst_ wrote: post #236
wnevets wrote: post #182

Earlier quoted context omitted.

This is something that I was forced to learn the hard way more than once. Literally today I needed to undelete a record because a customer was confused by what the "delete" button did and wanted their record back.

Isn't it the problem of UI, though. If the user would be informed about the consequences (possibly with bold red font and with a confirmation checkbox) would they still click that button?

File this under "falsehoods programmers believe about users": they act rationally.

Re: Soft deletion probably isn't worth it

#256

Post by Akronymus »

changoplatanero wrote: post #220
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.

User deleting an account is just one way that stuff gets deleted. There are other deletion scenarios where it is appropriate to keep the information after its deleted.

Yeah. Basically anything that an employee can mess up, should be reversible IMO. But actions such as deleting an account should have the option of "YES, DELETE IT PERMANENTLY, THIS CAN'T BE UNDONE"

Re: Soft deletion probably isn't worth it

#258

Post by 411111111111111 »

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.

Stop spreading misinformation. Nobody is required to hard delete the very second the [delete] button is pressed. You're following the law as long as that mentioned reaper job exists and runs at least once per month, to be safely within the allowed timeframe

Re: Soft deletion probably isn't worth it

#259

Post by thrashh »

wst_ wrote: post #236
wnevets wrote: post #182

Earlier quoted context omitted.

This is something that I was forced to learn the hard way more than once. Literally today I needed to undelete a record because a customer was confused by what the "delete" button did and wanted their record back.

Isn't it the problem of UI, though. If the user would be informed about the consequences (possibly with bold red font and with a confirmation checkbox) would they still click that button?

As a user, I would still sometimes and then regret my decision

Soft deletions are awesome

Re: Soft deletion probably isn't worth it

#260

Post by doctor_eval »

rodw wrote: post #205
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…

Seriously. That "Downsides: Code leakage" point is nonsensical. ``` CREATE OR REPLACE VIEW active_customer AS SELECT * FROM customer WHERE deleted_at IS NULL OR deleted_at There, I fixed it. Just use `active_customer` instead of `customer ... deleted_at IS NULL`. In fact, since the deleted_at column is a timestamp, the original "leakage" query: ``` SELECT * FROM customer WHERE id = @id AND deleted_at IS NULL; ``` is…

> the database is part of the application

100% this. If you accept that the database is part of the application, you give yourself permission to use the full feature set of the database, and life becomes a lot simpler. Using views, stored procedures and other features lets you implement things like soft delete trivially, without it infecting all your application code.

In my entire career I've changed backend databases for an application exactly twice. It's not easy, and no amount of abstraction is likely to make it easier.

Post reply on HN