The only scalable delete in Postgres is DROP TABLE • Hacker News Board

Re: The only scalable delete in Postgres is DROP TABLE

#31

Post by fidotron » Sun, Jun 14, 2026, 5:20 PM UTC

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…

> 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 all far more predictably bounded.

Re: The only scalable delete in Postgres is DROP TABLE

#32

Post by CharlieDigital » Sun, Jun 14, 2026, 5:30 PM UTC

saisrirampur wrote: post #11

Partially true but too much of a blanket statement and clickbaity. DELETE with well-tuned autovacuum works pretty well. Have seen it work at TBs scale with no hicuups. If DELETEs are large, we used to recommend customers to follow that with a manual VACUUM for table to reclaim space right away for future rows. DROP TABLE can be risky, it requires an ACCESS EXCLUSIVE LOCK and if its waiting, it blocks all other statem…

    > And you cannot keep doing high concurrent DROP TABLEs to run your large scale CRUD app

In this kind of use case/design, I would assume it would make use of partitions to make this more palatable in which case it would seem that you would bypass this issue of "high concurrent DROP TABLE". Large scale CRUD app just points to recent-ish partitions. Old partitions are either going to be low or on access and can be dropped easily or transformed/transferred into some long term/cold storage.

Re: The only scalable delete in Postgres is DROP TABLE

#33

Post by layer8 » Sun, Jun 14, 2026, 6:10 PM UTC

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 dropping a table effectively requires an exclusive lock on the table during that whole operation, affecting parallel transactions.

Re: The only scalable delete in Postgres is DROP TABLE

#34

Post by saisrirampur » Sun, Jun 14, 2026, 6:20 PM UTC

CharlieDigital wrote: post #32

saisrirampur wrote: post #11

Partially true but too much of a blanket statement and clickbaity. DELETE with well-tuned autovacuum works pretty well. Have seen it work at TBs scale with no hicuups. If DELETEs are large, we used to recommend customers to follow that with a manual VACUUM for table to reclaim space right away for future rows. DROP TABLE can be risky, it requires an ACCESS EXCLUSIVE LOCK and if its waiting, it blocks all other statem…

> And you cannot keep doing high concurrent DROP TABLEs to run your large scale CRUD app In this kind of use case/design, I would assume it would make use of partitions to make this more palatable in which case it would seem that you would bypass this issue of "high concurrent DROP TABLE". Large scale CRUD app just points to recent-ish partitions. Old partitions are either going to be low or on access and can be drop…

(Most) CRUD/OLTP applications don't delete data by timestamp; they delete by primary key. For those workloads, DROP TABLE (or dropping a partition) isn't a palatable option.

The entire premise here is really about time-series workloads where most operations are based on a timestamp. In those apps partition dropping has been a standard and recommended retention strategy for years. That's precisely why extensions like pg_partman and TimescaleDB exist. Given that context, the title feels more clickbaity, and could easily mislead readers into thinking this applies broadly to OLTP systems when it doesn't;

Re: The only scalable delete in Postgres is DROP TABLE

#35

Post by saisrirampur » Sun, Jun 14, 2026, 6:24 PM UTC

saisrirampur wrote: post #11

Partially true but too much of a blanket statement and clickbaity. DELETE with well-tuned autovacuum works pretty well. Have seen it work at TBs scale with no hicuups. If DELETEs are large, we used to recommend customers to follow that with a manual VACUUM for table to reclaim space right away for future rows. DROP TABLE can be risky, it requires an ACCESS EXCLUSIVE LOCK and if its waiting, it blocks all other statem…

Separately, this is one of the Postgres autovacuum tuning blog that I've ever read. Have seen it work across many customers and it is also simple to decipher and implement. https://www.citusdata.com/blog/2022/07/28/debugging-postgres...

Re: The only scalable delete in Postgres is DROP TABLE

#36

Post by convolvatron » Sun, Jun 14, 2026, 6:27 PM UTC

nostrademons wrote: post #30

crazygringo wrote: post #23

Earlier quoted context omitted.

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…

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.

Re: The only scalable delete in Postgres is DROP TABLE

#37

Post by baq » Sun, Jun 14, 2026, 6:35 PM UTC

cowthulhu wrote: post #27

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.

You should run vacuum as often as possible in Postgres if you’re doing anything other than INSERTs, this is a design tradeoff in Postgres itself. It’s the reason autovacuum exists and why tuning it is so important for performance; nothing wrong with doing a VACUUM ANALYZE after finishing a large DML batch job.

Re: The only scalable delete in Postgres is DROP TABLE

#38

Post by [deleted] » Sun, Jun 14, 2026, 6:47 PM UTC

sgarland wrote: post #2

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

[deleted]

Re: The only scalable delete in Postgres is DROP TABLE

#39

Post by tremon » Sun, Jun 14, 2026, 7:12 PM UTC

fidotron wrote: post #31

crazygringo wrote: post #21

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

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

Re: The only scalable delete in Postgres is DROP TABLE

#40

Post by kro » Sun, Jun 14, 2026, 7:14 PM UTC

sgarland wrote: post #2

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