The default settings for autovacuum_*_scale_factor and autovacuum_*_threshold aren't great for large tables. I like to set them to 0 and 1m respectively for tables over 20m rows or so. #PostgreSQL
Conversation
Replying to
I don't think that's a good idea. The cost of vacuuming continues to increase on larger tables due to index vacuums, and the gain of vacuuming 1 million dead rows on a 10billion row table isn't meaningful.
3
The problem is that a lot of applications have a situation where they have a small number of hot rows in a large table of mostly historical data. Ideally we would be able to vacuum just the hot data frequently without incurring issues trying to vacuum the large data set causes.
2
1
Yes. But even with that improvement VACUUM may still prune HOT chains that the workload is perfectly capable of handling opportunistically and as-needed. So it could technically remove lots of bloat, but also not help at all in a very real practical sense.
1
I don't disagree with you at all. My point is that "relativistic" view of bloat is relevant. Bottom-up approaches (driven by concrete problems, like logical row migrating to new heap block) seem most promising because top-down modeling is really hard to do well.