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

71–80 of 121 posts

Re: DELETEs Are Difficult

#71

Post by immibis »

pixl97 wrote: post #67
cogman10 wrote: post #54

Earlier quoted context omitted.

Rather than batching, I would want a "NO ROLLBACK DELETE" sort of command. The real expensive part of the delete is rewriting the records into the transaction log so that a cancel or crash can undo the delete. If you've gone to the effort of batching things, you are still writing out those records, you are just giving the db a chance to delete them from the log. I'd like to save my ssds that heartache and instead all…

I would say it can unlink gbs in seconds. The data is still on the disk until it's trimed or overwritten.

So why does it need to be copied into the WAL log until vacuum runs?

And vacuum is not expected or required to be atomic, since it deletes data that was necessarily unreferenced anyway, so it also shouldn't need to copy the old data into WAL files.

Re: DELETEs Are Difficult

#72

Post by lukevp »

mpweiher wrote: post #27
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 increase…

Every day I’d have more and more state accumulation on my machine - open apps, unsaved changes, open tabs. I’ve tried many methods for preventing this from happening over the years, but the only and most effective solution I’ve been using for the last year - a script I wrote that just quits every browser tab and every open app (leaving unsaved apps still running) every evening. I wake up and the machine is new and fresh. It’s amazing and has benefited my productivity so much. It changes the state game to where if I have a specific task I want to resume tomorrow I have to make myself a clear note to read the next day, and if I have tabs open that I care about, I have to bookmark them. What I’ve found is that the actual info that needs to be transferred between days is very small.

I have an endless reminders app and todo list. I wonder if something similar (items expire automatically unless you flag them as permanent or something) would help keep a clearer list. Sometimes ephemerality is best!

Re: DELETEs Are Difficult

#73

Post by epistasis »

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…

There was a common thread through all of my algorithms and data structures class: Hash maps, b-trees, etc. are all beautiful structures until you add a delete operation and have to start dealing with all those little holes... Removing data complicates everything.

Re: DELETEs Are Difficult

#74

Post by Kinrany »

cogman10 wrote: post #54
wruza wrote: post #43

Earlier quoted context omitted.

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

Rather than batching, I would want a "NO ROLLBACK DELETE" sort of command. The real expensive part of the delete is rewriting the records into the transaction log so that a cancel or crash can undo the delete. If you've gone to the effort of batching things, you are still writing out those records, you are just giving the db a chance to delete them from the log. I'd like to save my ssds that heartache and instead all…

What happens when two transactions select and then delete two rows in the opposite order while requesting "no rollback"?

Re: DELETEs Are Difficult

#75

Post by marcosdumay »

magicalhippo wrote: post #53
SoftTalker wrote: post #51

Earlier quoted context omitted.

You really shouldn’t be updating primary key values though.

While I overall agree, there have been cases where I have found it handy. In one case, we had a table of invoices and a table of goods items, and each goods item should point to an invoice. If one wants to use the natural key, invoice number, and have a foreign key to ensure the goods items can't point to an invoice that doesn't exist, then ON UPDATE CASCADE was needed in case the user had to change the invoice numbe…

> If one wants to use the natural key

Yeah, that's why you shouldn't use natural keys as primary ones.

Re: DELETEs Are Difficult

#76

Post by magicalhippo »

marcosdumay wrote: post #75
magicalhippo wrote: post #53

Earlier quoted context omitted.

While I overall agree, there have been cases where I have found it handy. In one case, we had a table of invoices and a table of goods items, and each goods item should point to an invoice. If one wants to use the natural key, invoice number, and have a foreign key to ensure the goods items can't point to an invoice that doesn't exist, then ON UPDATE CASCADE was needed in case the user had to change the invoice numbe…

> If one wants to use the natural key Yeah, that's why you shouldn't use natural keys as primary ones.

Learned that lesson a bit too late for that.

But yea, these days I never use natural keys.

Re: DELETEs Are Difficult

#77

Post by mattashii »

immibis wrote: post #71
pixl97 wrote: post #67

Earlier quoted context omitted.

I would say it can unlink gbs in seconds. The data is still on the disk until it's trimed or overwritten.

So why does it need to be copied into the WAL log until vacuum runs? And vacuum is not expected or required to be atomic, since it deletes data that was necessarily unreferenced anyway, so it also shouldn't need to copy the old data into WAL files.

Many DBMSs with index-oriented storage (MySQL, Oracle, MSSQL) use undo logging for a transaction's MVCC, so that for deletion the old version is put into the undo log of that transaction and referred to as an old version of the record (or page, or ...), immediately cleaning up space on the page for new data while the transaction is still goin on. This is great for short transactions and record updates, as a page only has to hold one tuple version at a time, but that is at the cost of having to write the tuples that are being removed into a log, just in case the transaction needs to roll back.

Re: DELETEs Are Difficult

#78

Post by tremon »

Nican wrote: post #34
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, yo…

> none of the data should be available

As written, that's not required. The data should not be retrieveable by query, but ACID only specifies what happens at the client-server boundary, not what happens at the server-storage boundary (Durability prescribes that the server must persist the data, but not how to persist it). A database that implements DELETEs by only tombstoning the row and doesn't discard the data until the next re-index or vacuum operation would still be ACID-compliant.

Re: DELETEs Are Difficult

#79

Post by eddd-ddde »

kijin wrote: post #26
jsemrau wrote: post #10

Earlier quoted context omitted.

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.

I'm pretty sure that only applies to Postgres.

Re: DELETEs Are Difficult

#80

Post by marcosdumay »

magicalhippo wrote: post #76
marcosdumay wrote: post #75

Earlier quoted context omitted.

> If one wants to use the natural key Yeah, that's why you shouldn't use natural keys as primary ones.

Learned that lesson a bit too late for that. But yea, these days I never use natural keys.

I really hate that the educational literature says it's an option. Yet everybody knows it's something you should never do, no exceptions.
Post reply on HN