Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

DELETEs Are Difficult

notso.boringsql.com

Post reply on HN
Print view View original

21–30 of 121 posts

Re: DELETEs Are Difficult

#21

Post by physicsguy »

jsemrau wrote: post #10
aurareturn wrote: post #2

DELETE FROM films; I'm surprised databases makes it so easy to just delete an entire table. I think the command should be DELETE FROM films YES-I-KNOW-WHAT-I-AM-DOING;

MySQL has this as default as far as I recall. But then I never delete, I just set "deleted" to yes.

That just wouldn’t fly where you have business customers, many insist on data deletion at the end of contracts. In practice though partitioning or using seperate databases can be a better strategy for dealing with that as otherwise dealing with backups are challenging.

Re: DELETEs Are Difficult

#22

Post by konha »

burntcaramel wrote: post #18

If data isn’t actually removed until vacuuming, then are systems that perform SQL DELETES actually GDPR compliant? Because technically the private data is still there on disk and could be recovered. “Until the autovacuum process or a manual VACUUM operation reclaims the space, the “deleted” data remains.”

Yes. GDPR allows for delays when complying with deletion requests. You should ideally document it and factor the delay into any deadlines you might be bound to.

You’d need to make sure the process is somewhat predictable, like running the vacuum on a set schedule so you know for sure what maximum amount of time a deletion request will take.

Re: DELETEs Are Difficult

#23

Post by redox99 »

jandrewrogers wrote: post #13

DELETE is expensive at a deep fundamental level that we don’t think about much in computer science because we are more worried about losing data. The article is about Postgres but it generalizes. We don’t actually have any computer science for DELETE optimized databases. I’ve idly looked into delete-optimization in databases as thought experiments, since there isn’t much in the way of literature on it, and it is far…

I think garbage collection memory management can be thought of a delete optimized database.

Re: DELETEs Are Difficult

#24

Post by rrr_oh_man »

Svip wrote: post #17

> Unlike DELETEs, UPDATEs don’t trigger cascaded actions - they only involve triggers that are explicitly defined. That's not entirely true. ON UPDATE CASCADE is a thing for foreign keys, at least in PostgreSQL (which this article is talking about), meaning that the foreign row referencing the row gets updated. Though, personally, I would never use ON UPDATE CASCADE, as it seems kind of funky.

> Though, personally, I would never use ON UPDATE CASCADE, as it seems kind of funky.

Why?

Re: DELETEs Are Difficult

#25

Post by isbvhodnvemrwvn »

alexanderscott wrote: post #14
orionblastar wrote: post #3

Most databases I used have a Status column we could mark as active, inactive, or deleted. That way, you can see what records were marked as deleted and change them back in case of accidental deletion. Keep record retention with the Date_Modified column so you can use SQL delete to remove those deleted records that are older than a year or so.

this is a “soft delete”. as the author notes, depending on the nature of the data being stored a soft delete does not meet the requirements of many data privacy laws and compliance regulations (like GDPR’s right to erasure).

And in postgres soft delete is more expensive than a regular delete because it's effectively an insert and update, while delete is just an update.

Re: DELETEs Are Difficult

#26

Post by kijin »

jsemrau wrote: post #10
aurareturn wrote: post #2

DELETE FROM films; I'm surprised databases makes it so easy to just delete an entire table. I think the command should be DELETE FROM films YES-I-KNOW-WHAT-I-AM-DOING;

MySQL has this as default as far as I recall. But then I never delete, I just set "deleted" to yes.

It might depend on the version, but last time I checked, DELETEing an entire table was much slower than TRUNCATE TABLE.

Re: DELETEs Are Difficult

#27

Post by mpweiher »

jandrewrogers wrote: post #13

DELETE is expensive at a deep fundamental level that we don’t think about much in computer science because we are more worried about losing data. The article is about Postgres but it generalizes. We don’t actually have any computer science for DELETE optimized databases. I’ve idly looked into delete-optimization in databases as thought experiments, since there isn’t much in the way of literature on it, and it is far…

Yes...but it goes even deeper.

For example, in physics, the paradox of Maxwells Demon is resolved when you consider the cost of deleting data:

"In 1982, Charles Bennett showed that, however well prepared, eventually the demon will run out of information storage space and must begin to erase the information it has previously gathered.[8][12] Erasing information is a thermodynamically irreversible process that increases the entropy of a system."

https://en.wikipedia.org/wiki/Maxwell's_demon#Recent_progres...

It is also difficult for humans to delete information. In my humble and only a little facetious opinion this is one of the main drivers for ever new "To Do" apps: the existing app gets full because deleting is too hard, so we start fresh with a new app. The app isn't the point, the starting fresh is.

The underlying reason there being that the cost of maintaining small (to do) notes can be greater than the value of the note, which is one of the reasons we still use little scraps of paper and other mechanisms that will effectively auto-delete.

Understanding the micronote lifecycle: improving mobile support for informal note taking

https://dl.acm.org/doi/10.1145/985692.985779

Re: DELETEs Are Difficult

#28

Post by kijin »

alexanderscott wrote: post #14
orionblastar wrote: post #3

Most databases I used have a Status column we could mark as active, inactive, or deleted. That way, you can see what records were marked as deleted and change them back in case of accidental deletion. Keep record retention with the Date_Modified column so you can use SQL delete to remove those deleted records that are older than a year or so.

this is a “soft delete”. as the author notes, depending on the nature of the data being stored a soft delete does not meet the requirements of many data privacy laws and compliance regulations (like GDPR’s right to erasure).

There are different kinds of soft delete.

I've had cases where the rows in question absolutely could not be hard deleted, because of legacy foreign key relations. But the PII in those rows had to go. So we did a kind of "firm delete" by setting all columns (except the PK and a few necessary flags) to their default values and/or null.

Re: DELETEs Are Difficult

#30

Post by xg15 »

> For example, deleting 1 million rows in a single transaction is a textbook case of what not to do. Instead, splitting the operation into smaller batches, such as deleting 10,000 rows across 100 iterations, is far more effective.

Why do I as a user have to do that? Why can't the database implement batching internally and automatically transform my 1-million-rows query into an appropriate list of batched queries?

(Edit: Thanks a lot for the answers, that makes more sense - in particular the point that this would also lock one million rows at once)

Post reply on HN