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

41–50 of 121 posts

Re: DELETEs Are Difficult

#41

Post by yen223 »

magicalhippo wrote: post #4
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;

Agreed, I've long been thinking that DELETE should require a WHERE clause. If you really want to just delete everything, just do WHERE 1=1.

It would be nice if there was a psql option that would warn you if you're about to do any kind of update, delete or insert without doing a BEGIN;

(You could always set autocommit=false, which forces you to explicitly commit or rollback every action, but that has its own baggage)

Re: DELETEs Are Difficult

#42

Post by martinsnow »

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…

Admin panels may do that. Say for example deleting a bucket with a million rows.

In that case I'd fire off a job and tell the user, yeah sure the delete is successful. But check the logs to be sure.

Re: DELETEs Are Difficult

#43

Post by wruza »

asQuirreL wrote: post #35
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.

I think that gp’s comment can be reinterpreted as: why should this landmine exist when databases could notify a reader of its manual about this issue in an explicit way, for example:

  DELETE FROM t WHERE … BATCH 100
Which would simulate batched queries when called outside of transaction. This would remove the need of a client to be connected (or at least active) for a duration of this lenghty operation.

If DELETE is so special, make special ways to manage it. Don’t offload what is your competence onto a clueless user, it’s recipe for disaster. Replace DELETE with anything and it’s still true.

  ALTER DATABASE d SET UNBATCHED DELETE LIMIT 500000
I know a guy (not me) who deleted rows from an OLTP table that served a country-level worth of clients and put it down for two days. That is completely database’s fault. If its engine was designed properly for bigdata, it should have refused to do so on a table with gazillions of rows and suggested a proper way to do it.

Re: DELETEs Are Difficult

#44

Post by tybit »

redox99 wrote: post #23
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.

Runtimes with garbage collectors typically optimize for allocation, not deletion.

Re: DELETEs Are Difficult

#45

Post by SgtBastard »

redox99 wrote: post #23
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.

Only if you profoundly misunderstand what GC is.

Re: DELETEs Are Difficult

#46

Post by akira2501 »

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…

> We don’t actually have any computer science for DELETE optimized databases.

There is actually a fair amount if you consider databases with fixed length records. Which used to be the dominant form.

Re: DELETEs Are Difficult

#47

Post by pepelotas »

arcanemachiner wrote: post #19
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. As a relative noob in the world of database administration, I'm glad to hear that someone else feels this way.

It does if your key is an auto increment or random unique identifier. But if you had a key that is also data, say a "Genre" colum, it starts to make sense that you'd want to cascade updates

Re: DELETEs Are Difficult

#48

Post by rrr_oh_man »

Svip wrote: post #32
rrr_oh_man wrote: post #24

Earlier quoted context omitted.

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

Thanks for the explanation.

The first case I was thinking about was a merger of two accounts. Second case would be a db-wide update of one or more keys for some reason. That’s why I tend to leave it ON UPDATE CASCADE.

Although both of these cases are more diffuse muscle memory than rational foresight.

Re: DELETEs Are Difficult

#49

Post by redman25 »

One solution for performance degradation with soft deletes is to partition the table by some field like `created` monthly. Queries will need to include `created` in the query is the main downside.

Re: DELETEs Are Difficult

#50

Post by dataflow »

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

Even vacuuming wouldn't actually destroy the data right? Because filesystems don't guarantee they will overwrite or wipe any particular disk blocks. And even if they did, SSDs still wouldn't promise that the blocks aren't remapped instead of being wiped & reused.
Post reply on HN