51–60 of 121 posts
> 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.
Earlier quoted context omitted.
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 s…
That said, I agree it would be nice to have a DELETE BATCH option to make it even easier.
[1]: https://learn.microsoft.com/en-us/sql/t-sql/statements/delet...
> 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.
You really shouldn’t be updating primary key values though.
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 number (due to mistyping it or similar).
Of course, if one does not use a natural key then this isn't such an issue.
Another case was where we've had to merge databases after two companies merged. If they had overlapping primary key ranges, then the easiest was to switch to ON UPDATE CASCADE and then renumber the existing rows before inserting the other rows.
We'd change back after the merge though.
Earlier quoted context omitted.
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 s…
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 allow the database to just delete.
In MSSQL in some extreme circumstances, we've partitioned our tables specifically so we can use the 'TRUNCATE TABLE' command as delete is just too expensive.
That operation can wipe gbs in seconds.
Earlier quoted context omitted.
And queries should start with WHERE.
FROM, then WHERE, then SELECT
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.”
GDPR supervisory authorities disagree on what to do about data in backups. France has said you don't have to delete data from backups. The Danish authorities have said that you have to delete from backups where it is technically possible. The UK (which still has GDPR) has said that you must put the data "beyond use" which most have taken to mean that you have to make sure that if you ever restore data from the backup you will omit the data that is supposed to be forgotten.
I don't know what other supervisor authorities have said--those three are just the ones that tend to show up when searching on this topic. I would not be surprised if there are at least a dozen other different opinions from the rest.
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…
You have to break it into small batches across the cascade of associated data.
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…
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…
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.