Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The only scalable delete in Postgres is DROP TABLE

planetscale.com

Post reply on HN
Print view View original

21–30 of 87 posts

Re: The only scalable delete in Postgres is DROP TABLE

#21

Post by crazygringo »

setr wrote: post #12
crazygringo wrote: post #4

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

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

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

Re: The only scalable delete in Postgres is DROP TABLE

#22

Post by winterbloom »

crazygringo wrote: post #4

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

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

Re: The only scalable delete in Postgres is DROP TABLE

#23

Post by crazygringo »

echoangle wrote: post #7
crazygringo wrote: post #4

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

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

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.

Re: The only scalable delete in Postgres is DROP TABLE

#24

Post by crazygringo »

winterbloom wrote: post #22
crazygringo wrote: post #4

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

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

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.

Re: The only scalable delete in Postgres is DROP TABLE

#25

Post by Retr0id »

crazygringo wrote: post #4

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

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

Re: The only scalable delete in Postgres is DROP TABLE

#27

Post by cowthulhu »

IMO, needing to clear out an entire table is an indicator that something has gone wrong with your design.

Don't get me wrong, I've definitely done it before, but it's in the same bucket as VACUUM for me... high impact interventions used to fix a mistake I made, not "course of business" actions.

Re: The only scalable delete in Postgres is DROP TABLE

#28

Post by gopalv »

crazygringo wrote: post #21
setr wrote: post #12

Earlier quoted context omitted.

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

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

> 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

Re: The only scalable delete in Postgres is DROP TABLE

#29

Post by pixl97 »

crazygringo wrote: post #21
setr wrote: post #12

Earlier quoted context omitted.

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

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

Re: The only scalable delete in Postgres is DROP TABLE

#30

Post by nostrademons »

crazygringo wrote: post #23
echoangle wrote: post #7

Earlier quoted context omitted.

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

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

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.

Post reply on HN