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

281–290 of 514 posts

Re: Soft deletion probably isn't worth it

#281

Post by JohnBooty »

efsavage wrote: post #193
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…

Also if people know that deletion is reversible, they're more likely to actually do it, which can keep things generally tidier. I don't actually like using a "deleted" column, my standard table has a status column, and deleted is one of those states, along with active/pending/suspended/etc, as the needs dictate. This way I get soft deletes for basically free both in the schema, but also in the queries (which would ge…

For some applications this is fine, depending on your app/business logic but for a lot of applications states like active/pending/suspended and "deleted" are not mutually exclusive.

Suppose I soft-delete an active, pending, or suspended user using your scheme.

Now I need to un-delete the user. What status should they have? We don't know.

This is another "best practice" I've learned over the years. Those flags/statuses you think are mutually exclusive? Maybe they aren't. Or maybe they are now but won't always be. It's usually easier in the long run to give each status its own column even if you think they'll always be mutually exclusive. Because I mean, what are you really saving? A few bytes per record? In most cases that's not worth it.

Re: Soft deletion probably isn't worth it

#282

Post by avianlyric »

smolder wrote: post #243
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.

I wonder what percentage of companies who even appear to comply with deletion requests actually do full deletion in practice. I suspect it's small, knowing how many things are coded to fake-delete for convenience. Businesses also tend to keep cold data backups around. (Maybe backups are exempted? I don't know.) There might even be cases where ostensibly deleted data can still be recovered from a disk, if they haven't…

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 unstructured mess. You’re probably ok, as long as you took reasonable steps to protect the data, like full disk encryption (which could occurs below the file system, and thus allow you to recover data deleted from the FS with a full disk scan, as long as you still had the keys). If you just had peoples data unencrypted on a HDD, and didn’t securely erase it before dispose, then that’s probably still a GDPR violation.

Re: Soft deletion probably isn't worth it

#283

Post by jesseryoung »

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 was getting ready to disagree with you - but then I tried to think of any time I've actually pushed code to production with the "DELETE" keyword in it. The problems that I've had to solve in my career very rarely call for deleting something.

"Soft deletion" and "audit trail" are technical terms we developers come up for solutions the business wants but maybe hasn't asked for yet. It's not really a soft deletion it's a "deactivate" or "hide". Likewise, it's not an audit trail it's a "history" or "undo". Most of the time your stakeholders and users actually want these features, but don't ask because they perceive this as more expensive to build then just a "delete" button.

Re: Soft deletion probably isn't worth it

#284

Post by JohnBooty »

icedchai wrote: post #233
smackeyacky wrote: post #226

Earlier quoted context omitted.

What seems to be missing here is the DB tech he is using. On a proper database you can do your "undeleted" with triggers and it's relatively trivial. Nonsense like a "deleted" column on your main data table just seems silly.

I worked at a place that kept all the deleted stuff in their main tables. It turned out over 90% of the rows were deleted. I'm not sure how often something was undeleted, but it was not very frequent. Some of these soft deleted rows were 5+ years old. Archive that crap.

Yeah, for sure, if deletion is going to be the exception rather than the rule.... keeping deleted stuff in the main tables is not ideal.

Of course, you can have your cake and eat it too. You can e.g. keep your deleted crap in the main table (which makes certain things easier) and then hard-delete or archive it to another table after N days.

Re: Soft deletion probably isn't worth it

#285

Post by LunaSea »

avianlyric wrote: post #277
lazide wrote: post #274

Earlier quoted context omitted.

This should have the asterisk of * after the period of time you’ve said it will take for it to be deleted. It’s almost impossible to guarantee instant delete, folks just care if it’s gone in a documented and/or reasonable amount of time, and predictably. Correct?

Sure, but that time period is usually limited to 30 days, with an additional 30 day extension allowed if you contact the person and tell them you need more time. You can’t just declare that it take years to delete data, GDPR sets reasonable limits on how long a company can delay true deletion.

The GDPR gives limits on answering, I'm not sure it gives a limit on actually deleting the data everywhere.

Various types of data have various retention times for various reasons, some being legal reasons.

Re: Soft deletion probably isn't worth it

#286

Post by Gordonjcp »

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.

It's like people have forgotten what views are.

Re: Soft deletion probably isn't worth it

#287

Post by taeric »

necovek wrote: post #267
taeric wrote: post #195

Earlier quoted context omitted.

Don't make deletes appear instantaneous? If you have heavy weight systems, then it makes sense for provisioning and deleting entities is a process, that should be open to monitoring.

Exposing details of long lived background processes, and especially deletion processes to users (who in many cases couldn't care less) is a lot of work that's probably not worth it for the rare "hey, something went wrong" case — it's usually perfectly acceptable to raise that with the support team and let them use internal tools to debug and investigate. I am sure there are cases where it is worthwhile, but most of t…

I disagree. Any system that doesn't transition an entity to "deleting" is almost certainly going to be a pain point for me when I go to delete something.

This can be seen as related to soft deletes. But I consider it more marking intent in the system. Lets you make "delete" a simple field update that will be carried out by the backend.

Re: Soft deletion probably isn't worth it

#288

Post by delgaudm »

Gordonjcp wrote: post #286
pradn wrote: post #187

Earlier quoted context omitted.

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. It's like people have forgotten what views are.

Well, most likely they're queries that have "deleted=False" in them. ;-)

Re: Soft deletion probably isn't worth it

#289

Post by [deleted] »

remram wrote: post #158
SoftTalker wrote: post #106

Earlier quoted context omitted.

A problem (unless something has changed, my context is Oracle from some time ago) is that NULL values are not indexed. So the "WHERE deleted_at IS NULL" could trigger a full table scan. It can also cause row migration when the NULL value is eventually filled in. Unless you explicitly need the deleted date, it's probably better to use a non-nullable Y/N for this.

It seems Oracle does it although there is a special syntax to opt-in. That seems wild. I am not aware of another DBMS having that limitation though.

[deleted]

Re: Soft deletion probably isn't worth it

#290

Post by rodw »

djur wrote: post #262
rodw wrote: post #205

Earlier quoted context omitted.

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 query isn't broken. In the Rails community at least it is very common to use a nullable frobbed_at column to indicate both "was it frobbed" and "when was it frobbed". In that context, the boolean check is always NULL/NOT NULL, rather than a time comparison.

I can see that use case and I guess if that's a popular idiom within a particular dev community it's not indefensible, but from a first-principles code-complete/pragmatic-programmer kind of lens, that seems to me like a really terrible naming convention if that's your intent.

It seems to me that having a date-type column named `is_frobbed` is highly preferable to `frobbed_at` if your intent is for `IS NOT NULL` to mean frobbed.

Sure, it's a little weird to have boolean-sounding `is_frobbed` name for a date column, but the number of times that `frobbed_at IS [NOT] NULL` appears in the code is likely to dwarf the number of times you're inserting a date type into the `is_frobbed` column. I feel like there's always going to be someone (like me in this case) that's going to come across a `frobbed_at IS NOT NULL` case, notice that it's a date column and make the same flawed assumption I did. A similar thing will happen to the `is_frobbed` column too, but in that case trying to treat the date type as a boolean is going to make it obvious that you're not understanding the full context.

Frankly, if I was naming a column for this idiom and didn't have other constraints (like the Rails context), I would probably try to find a more exact and direct way to express it, if that's a little clunky. Maybe something like `frobbed_when_not_null`? But I don't love that. Honestly it may make more sense to have a actual boolean-valued `is_frobbed` column and an independent `frobbed_at` timestamp column that's populated by a trigger when the value of `is_frobbed` changes. I feel like "clunky but direct" beats out "elegant but misleading" in the long run, especially given the degree of "clunky" and "elegant" we're talking about here.

I don't expect to talk you out of it, and I'd fall in line with this in a Rails context too if that's the convention, but I think it's objectively poor design.

For what it's worth, if the `frobbed_at` convention usually intended to track the timestamp at which the frobbing happened, the good news is that my `AND frobbed_at <= NOW()` check would be unlikely to break anything in practice. Assuming that there's nothing weird going on with the timestamps, the frobbed_at dates will always be in the past anyway.

Post reply on HN