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

31–40 of 121 posts

Re: DELETEs Are Difficult

#31

Post by sureglymop »

xg15 wrote: post #30

> 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? (Ed…

And a follow up question: would the current best way to handle this be to "mark records as deletable" and then do the batched deletion operations when convenient?

Re: DELETEs Are Difficult

#32

Post by Svip »

rrr_oh_man wrote: post #24
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?

Personally, I like to be explicit and in control. In the application layer, I may be far away (at least mentally speaking) from the constraints in the database, and if I update/delete something, I don't want it to "magically" cascade through the database. For those reasons, I always prefer RESTRICT, both for ON DELETE and ON UPDATE. This forces me to clean up before I make the actual change I'm interested in, and anyone reading the code later, can uncover that behaviour quickly.

That being said, I can see the arguments for ON DELETE CASCADE, particularly in a codebase, where there is a lot of functionality in the database itself (in the formed of stored procedures, and what have you), since you are always mentally closer to the action. But ON UPDATE CASCADE feels weird, because why are you updating the primary key (which is usually what foreign keys references) of your rows? That feels like something that needs a good explanation.

Though, I do recognise, you need to jump through a lot of hoops to modify your primary key values with ON UPDATE RESTRICT, because you basically need to cover all your bases in a large convoluted common table expression (depending on the number of foreign keys, of course), when an ON UPDATE CASCADE would do that for you. But I'd also rather be blocked from updating primary row values entirely, since it feels like the wrong thing to do. (Yes, I know that foreign keys doesn't have to reference other primary keys, and there may be niche cases for this, but personally, I'd just avoid it altogether.)

Re: DELETEs Are Difficult

#34

Post by Nican »

xg15 wrote: post #30

> 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? (Ed…

Transactional consistency / ACID guarantees.

Before you execute the query, you should be able to query any of the data, and after you execute the query, none of the data should be available. The mechanisms to make a transactional database is tricky.

Some databases, like CockroachDB, provides some built-in TTL capabilities.

But also- if you are having to delete huge ranges of data and do not care about consistency, you are probably looking at an analytical workload, and there would be better databases suited for that, like Clickhouse.

Re: DELETEs Are Difficult

#35

Post by asQuirreL »

xg15 wrote: post #30

> 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? (Ed…

One reason I can think of is that the database needs to maintain atomicity and isolate effects of any given operation (the A and I in ACID).

By manually batching the deletes, you are telling the database that the whole operation does not need to be atomic and other operations can see partial updates of it as they run. The database wouldn't be able to do that for every large delete without breaking its guarantees.

Re: DELETEs Are Difficult

#36

Post by karrieberrie553 »

xg15 wrote: post #30

> 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? (Ed…

Because they mean different things. Deleting all records in a SINGLE transaction means something different than many smaller transactions. Even if for most use cases, you want the latter, it may be the case that you need to lock the DB down to perform the deletions in a single step.

Re: DELETEs Are Difficult

#38

Post by hamandcheese »

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.

> 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.

For this to work with soft-deletes, wouldn't you have to include the soft-delete column in a compound foreign key? That sounds funky indeed.

Re: DELETEs Are Difficult

#39

Post by lucianbr »

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.”

If vacuum runs at least once per day, seems pretty GDPR compliant to me. Even if it runs once every two or three days.

Now if your database runs vacuum once every 6 months, yeah, DELETE might not actually be a delete. But is it really a GDPR issue? What's really going on in this system?

I don't think any EU agency is going to fine your company if the data you say you deleted survived 6 or even 60 hours after deletion, if that is the end of it.

Post reply on HN