[deleted]
The only scalable delete in Postgres is DROP TABLE
41–50 of 87 posts
Re: The only scalable delete in Postgres is DROP TABLE
#42DROP DATABASE, for when a bunch of calls to DROP TABLE seems like too much overhead...
Re: The only scalable delete in Postgres is DROP TABLE
#43Only by a weird definition of "scalable". The first sentence says: > Counterintuitively, large DELETEs add work to the database. There is nothing counterintuitive about this. It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Obviously you have to do almost all the same operations: write a log, write the deletion, update indices, replicate it, etc. And yes, it's a well-known tric…
>There is nothing counterintuitive about this. It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it?
Because e.g. DROP also effectively deletes the rows but takes way way less work.
Re: The only scalable delete in Postgres is DROP TABLE
#44Earlier quoted context omitted.
> I'm not aware of instances where a delete is "far more work" than an equivalent insert though. That's not the general case, and I'm having a hard time thinking of any situations where that would be true. Transactionally across related items with constraints it can explode fast. If you've ever used FoundationDB this rapidly becomes the defining PITA due to the transaction size limits. Adding/inserting/updates are al…
But in that case, you need to compare like-for-like with the situation where you need to insert all the prerequisite rows too. You can't just compare a delete cascade with a single insert where all the foreign keys are already satisfied.
The whole problem with the delete cascade is you can't tell how big it will be until you have entered the transaction to do it. An insert you either know or it will fail and you can retry.
Re: The only scalable delete in Postgres is DROP TABLE
#45Earlier quoted context omitted.
> It takes far more work to delete/update than insert. Updating rows of text data is going to be more work, because variable-length text can't be updated in-place. So in terms of allocating space, it's more like a delete plus an insert. That's not surprising. (An in-place update that doesn't touch indices is generally going to be faster than an insert, though.) I'm not aware of instances where a delete is "far more w…
Not directly database related, but when it comes to writing files on disks, deletes on SSDs can be rather expensive because of the delete block size vs a simple write.
[deleted]
Re: The only scalable delete in Postgres is DROP TABLE
#46Earlier quoted context omitted.
But in that case, you need to compare like-for-like with the situation where you need to insert all the prerequisite rows too. You can't just compare a delete cascade with a single insert where all the foreign keys are already satisfied.
The whole problem with the delete cascade is you can't tell how big it will be until you have entered the transaction to do it. An insert you either know or it will fail and you can retry.
That's true, but now you have moved the goalposts. The original claim upthread was "it takes just as much work to delete a row as it takes to insert a row", not "it's hard to predict the performance of a delete with cascade effects". And the obvious rebuttal to that is that it's equally hard to provide an upper bound for the runtime of a single insert: an application cannot control the other processes running on the database, some of which may delay, interfere with or even invalidate your query and you must account for that. A delete operation is just as much "it might fail and you can retry" as an insert, or the database you're working with isn't ACID-compliant.
Re: The only scalable delete in Postgres is DROP TABLE
#47Earlier quoted context omitted.
The whole problem with the delete cascade is you can't tell how big it will be until you have entered the transaction to do it. An insert you either know or it will fail and you can retry.
That's true, but now you have moved the goalposts. The original claim upthread was "it takes just as much work to delete a row as it takes to insert a row", not "it's hard to predict the performance of a delete with cascade effects". And the obvious rebuttal to that is that it's equally hard to provide an upper bound for the runtime of a single insert: an application cannot control the other processes running on the…
> And the obvious rebuttal to that is that it's equally hard to provide an upper bound for the runtime of a single insert
This is precisely where you're going wrong. The insert is upper boundable in advance (you know the set of everything you might potentially have to insert), the delete isn't because you don't know what's in the db until you look.
I strongly recommend poking around with Foundation for this, because it becomes clear that this problem is the defining flaw with the way they tried to architect with layers, to the point they have a queuing system for processing large jobs of this type.
Re: The only scalable delete in Postgres is DROP TABLE
#48The same is true to a lesser extent in MySQL / MariaDB. It does better since it doesn’t do oldest-to-newest tuple chains, but it’s still adding non-trivial work to the DB, much of which is effectively wasted if you don’t care about the visibility of the deleted (or soon-to-be deleted) tuples to other transactions. I sincerely hope that Planetscale’s efforts succeed long-term to shift devs’ understanding and acceptanc…
mysql/maria also lets you turn off/down the isolation level for queries if you know the guarantees aren't needed, to speed things up. I think postgres does not have that option.
Postgres does support changing the isolation level at query, session and config: https://www.postgresql.org/docs/current/sql-set-transaction....
Re: The only scalable delete in Postgres is DROP TABLE
#49Years ago I heard Oracle's db had an edge on PG when it comes to DELETEs. I guess that's still the case...
Re: The only scalable delete in Postgres is DROP TABLE
#50Earlier quoted context omitted.
That's true, but now you have moved the goalposts. The original claim upthread was "it takes just as much work to delete a row as it takes to insert a row", not "it's hard to predict the performance of a delete with cascade effects". And the obvious rebuttal to that is that it's equally hard to provide an upper bound for the runtime of a single insert: an application cannot control the other processes running on the…
> And the obvious rebuttal to that is that it's equally hard to provide an upper bound for the runtime of a single insert This is precisely where you're going wrong. The insert is upper boundable in advance (you know the set of everything you might potentially have to insert), the delete isn't because you don't know what's in the db until you look. I strongly recommend poking around with Foundation for this, because…
> The insert is upper boundable in advance
A concurrent DML happening then suddenly your MERGE INTO WHEN NOT MATCHED INSERT/INSERT INTO SELECT is way larger that you thought? I thought "some workloads can suddenly be way larger that I expected" was supposed to be a thing in all non-trivial DML.