4 ms·

Only by a weird definition of "scalable". The first sentence says: > Counterintuitively, large DELETEs add work to the database. There is nothing counterintui

Only 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 trick for all major relational databases (not just Postgres) that if you want to delete 90% of rows from a large table, it's much faster to just copy the rows you want to keep to a new table, run DROP TABLE on the old table, and rename the new table to the old table. Since DROP TABLE is ~instantaneous, mainly involving table-level metadata. DELETE scales just fine, in the sense that if you are constantly inserting and deleting individual rows, DELETE scales the same as INSERT. Basic database functionality is designed around the assumption of lots of small transactions. Whenever you have to do something involving millions of rows at once, you generally need to investigate solutions that work well in "bulk". E.g. loading rows directly from a file rather than with SQL, adding indices only after the data has been loaded rather than before, disabling foreign key checks on large operations (if you know by design that the keys are valid)... and yes, taking advantage of DROP TABLE instead of DELETE. This doesn't mean small transactions aren't scalable, it just means bulk operations are qualitatively different and benefit from their own solutions. And DELETE is no different from INSERT in this regard.

echoangle 4mo ago
> And yes, it's a well-known trick for all major relational databases (not just Postgres) that if you want to delete 90% of rows from a large a table, it's much faster to just copy the rows you want to keep to a new table, run DROP TABLE on the old table, and rename the new table to the old table. Dumb question but why does the optimizer not just do that in secret then? Seems like something that should be detectable with some heuristics.
- mordae 4mo ago
  It drops dependents.
- sgarland 4mo ago
  I assume partly because that would be extremely surprising behavior, and depending on the RDBMS and version, could introduce unexpected stalls. For example, MySQL < 8.0.23 scans the entire buffer pool to clear pages that were dropped, which can take a long time on large instances. There is / was a similar issue with its adaptive hash index, which AFAIK wasn’t ever fixed, though AHI’s default being shifted to OFF in 8.4 is a workaround, in a very hacky way.
- deleted 4mo ago
  [deleted]
- Retr0id 4mo ago
  Maintaining the expected observable behaviours would get complicated if queries (especially other updates) against the same table are happening concurrently.
- crazygringo 4mo ago
  Because what do you do if rows are being inserted in the original table, while the new table is having rows copied over? You'll get missing rows. You can only do the DROP TABLE trick if you know nothing else is writing to the table at the same time. You know if that's the case, according to your business logic. The database has no idea. The DROP TABLE trick effectively bypasses all the normal guarantees of data consistency. This is why it's so fast. But you have to know that that's a safe thing to do for your data.
  - nostrademons 4mo ago
    There are ways the DB could recover the data consistency guarantees, eg. keeping a log of operations that came in while the table was being copied over and then applying the relevant ones afterwards. The tricky part is that the latency characteristics of these operations would be pretty surprising and unintuitive. It has the same problems as virtual memory and mark/sweep GC; sometimes, depending on system state and things that other threads are doing, an unrelated operation might block for very long time periods and give you huge user-visible pauses. It's often better to force these expensive operations to be explicit so that the developer has to think through the latency & consistency implications and make the tradeoffs they want.
    - convolvatron 4mo ago
      except in this particular case, as long as you don't exhaust resources, mvcc kind of lets you get away with making a transactionally consistent copy under the covers without blocking anyone, since its a big-ol read.
- layer8 4mo ago
  Because dropping a table effectively requires an exclusive lock on the table during that whole operation, affecting parallel transactions.
setr 4mo ago
> 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. It takes far more work to delete/update than insert. My recent example is updating ~2TB of text data was about 40x slower than inserting 12TB (was trying to correct some large text truncation that occurred during migration into PG, ended up being faster to redo).
- crazygringo 4mo ago
  > 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 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.
  - gopalv 4mo ago
    > So in terms of allocating space, it's more like a delete plus an insert. Unless you're using zHeap, you have a narrow Heap-only-Tuples scenario where the indexes stay the same. TOAST kinda helps there, if the update is off the tuple area itself. The original zHeap docs have a lot of detail about why an UNDO log can help with long running transactions from the past etc. That is a postgresql specific thing though. Mysql indexes were created with the idea of different storage engines in mind, so Mysql doesn't suffer from the index update ovehead on update/delete the same way. Uber had a long blog post about switching to Mysql from Postgres for wide tables with hundreds of indexes. The HN entry is still there[1], but I can't read the original post now. As a side note, I've used postgres partitions to the same effect to drop old data periodically - detach and then drop the partition instead of a direct DELETE (similar tricks in HBase existed). [1] - https://news.ycombinator.com/item?id=10894047 https://news.ycombinator.com/item?id=10894047
    - 4mo ago
winterbloom 4mo ago
how does that solution work if the table that is dropped has foreign key constraints?
- crazygringo 4mo ago
  That's why I said: > disabling foreign key checks on large operations And you have to know that, according to your business logic, what you're doing is safe.
Retr0id 4mo ago
> if you are constantly inserting and deleting individual rows, DELETE scales the same as INSERT Technically correct, but for a small table with a high churn rate, the performance characteristics may be surprising in that the "n" in most big-O calculations includes all inserts since the last VACUUM, not the actual number of resident rows.
coldtea 4mo ago
>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.
umpalumpaaa 4mo ago
Does this “Drop hack” work well with foreign keys, triggers and constraints?
globular-toast 4mo ago
> It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Because your data structure/algorithm supports fast deletes? File systems support deleting entire directories instantly. I'm not aware of any fundamental reason why DELETE in a SQL database must take as long as an insert?
- silversmith 4mo ago
  I usually don't like the "read the article" replies, but in this case it's warranted - the second section explains how multiple transactions interact there, and the "deleted" record still needs to actually exist for older transactions.
- crazygringo 4mo ago
  I suggest you read the following sentence of my comment, the one after your quote. It provides your answer. And file systems deleting huge directories instantly is the equivalent of DROP TABLE here, which I also mention in my comment.
- dpark 4mo ago
  > File systems support deleting entire directories instantly. What file system supports this? I’ve never experienced the joy of deleting an entire large directory and seeing it disappear instantly. I’m not sure how this would work with any modern file system that supports basic notions like hard links.
drob518 4mo ago
Yea, this seems as obvious to me as why it’s more efficient to reformat a drive partition than to delete every file that might be stored there. Or why it’s more efficient to free a whole memory arena than to free every single memory block allocated within it. If you know you’re throwing everything away, it’s more efficient to invoke a “throw it all away” action than to throw away each piece individually.

echoangle 4mo ago

> And yes, it's a well-known trick for all major relational databases (not just Postgres) that if you want to delete 90% of rows from a large a table, it's much faster to just copy the rows you want to keep to a new table, run DROP TABLE on the old table, and rename the new table to the old table. Dumb question but why does the optimizer not just do that in secret then? Seems like something that should be detectable with some heuristics.

mordae 4mo ago
It drops dependents.
sgarland 4mo ago
I assume partly because that would be extremely surprising behavior, and depending on the RDBMS and version, could introduce unexpected stalls. For example, MySQL < 8.0.23 scans the entire buffer pool to clear pages that were dropped, which can take a long time on large instances. There is / was a similar issue with its adaptive hash index, which AFAIK wasn’t ever fixed, though AHI’s default being shifted to OFF in 8.4 is a workaround, in a very hacky way.
deleted 4mo ago
[deleted]
Retr0id 4mo ago
Maintaining the expected observable behaviours would get complicated if queries (especially other updates) against the same table are happening concurrently.
crazygringo 4mo ago
Because what do you do if rows are being inserted in the original table, while the new table is having rows copied over? You'll get missing rows. You can only do the DROP TABLE trick if you know nothing else is writing to the table at the same time. You know if that's the case, according to your business logic. The database has no idea. The DROP TABLE trick effectively bypasses all the normal guarantees of data consistency. This is why it's so fast. But you have to know that that's a safe thing to do for your data.
- nostrademons 4mo ago
  There are ways the DB could recover the data consistency guarantees, eg. keeping a log of operations that came in while the table was being copied over and then applying the relevant ones afterwards. The tricky part is that the latency characteristics of these operations would be pretty surprising and unintuitive. It has the same problems as virtual memory and mark/sweep GC; sometimes, depending on system state and things that other threads are doing, an unrelated operation might block for very long time periods and give you huge user-visible pauses. It's often better to force these expensive operations to be explicit so that the developer has to think through the latency & consistency implications and make the tradeoffs they want.
  - convolvatron 4mo ago
    except in this particular case, as long as you don't exhaust resources, mvcc kind of lets you get away with making a transactionally consistent copy under the covers without blocking anyone, since its a big-ol read.
layer8 4mo ago
Because dropping a table effectively requires an exclusive lock on the table during that whole operation, affecting parallel transactions.

setr 4mo ago

> 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. It takes far more work to delete/update than insert. My recent example is updating ~2TB of text data was about 40x slower than inserting 12TB (was trying to correct some large text truncation that occurred during migration into PG, ended up being faster to redo).

crazygringo 4mo ago
> 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 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.
- gopalv 4mo ago
  > So in terms of allocating space, it's more like a delete plus an insert. Unless you're using zHeap, you have a narrow Heap-only-Tuples scenario where the indexes stay the same. TOAST kinda helps there, if the update is off the tuple area itself. The original zHeap docs have a lot of detail about why an UNDO log can help with long running transactions from the past etc. That is a postgresql specific thing though. Mysql indexes were created with the idea of different storage engines in mind, so Mysql doesn't suffer from the index update ovehead on update/delete the same way. Uber had a long blog post about switching to Mysql from Postgres for wide tables with hundreds of indexes. The HN entry is still there[1], but I can't read the original post now. As a side note, I've used postgres partitions to the same effect to drop old data periodically - detach and then drop the partition instead of a direct DELETE (similar tricks in HBase existed). [1] - https://news.ycombinator.com/item?id=10894047 https://news.ycombinator.com/item?id=10894047
  - 4mo ago

winterbloom 4mo ago

how does that solution work if the table that is dropped has foreign key constraints?

crazygringo 4mo ago
That's why I said: > disabling foreign key checks on large operations And you have to know that, according to your business logic, what you're doing is safe.

Retr0id 4mo ago

> if you are constantly inserting and deleting individual rows, DELETE scales the same as INSERT Technically correct, but for a small table with a high churn rate, the performance characteristics may be surprising in that the "n" in most big-O calculations includes all inserts since the last VACUUM, not the actual number of resident rows.

coldtea 4mo ago

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

umpalumpaaa 4mo ago

Does this “Drop hack” work well with foreign keys, triggers and constraints?

globular-toast 4mo ago

> It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Because your data structure/algorithm supports fast deletes? File systems support deleting entire directories instantly. I'm not aware of any fundamental reason why DELETE in a SQL database must take as long as an insert?

silversmith 4mo ago
I usually don't like the "read the article" replies, but in this case it's warranted - the second section explains how multiple transactions interact there, and the "deleted" record still needs to actually exist for older transactions.
crazygringo 4mo ago
I suggest you read the following sentence of my comment, the one after your quote. It provides your answer. And file systems deleting huge directories instantly is the equivalent of DROP TABLE here, which I also mention in my comment.
dpark 4mo ago
> File systems support deleting entire directories instantly. What file system supports this? I’ve never experienced the joy of deleting an entire large directory and seeing it disappear instantly. I’m not sure how this would work with any modern file system that supports basic notions like hard links.

drob518 4mo ago

Yea, this seems as obvious to me as why it’s more efficient to reformat a drive partition than to delete every file that might be stored there. Or why it’s more efficient to free a whole memory arena than to free every single memory block allocated within it. If you know you’re throwing everything away, it’s more efficient to invoke a “throw it all away” action than to throw away each piece individually.