[saisrirampur]

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 statements following it, because of how lock queues work in Postgres. And you cannot keep doing high concurrent DROP TABLEs to run your large scale CRUD app.

[CharlieDigital]

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

[saisrirampur]

(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;

[parthdesai]

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

UUID v7 to the rescue!

[saisrirampur]

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