IMV the right approach is to decouple *everything* in VACUUM and let it figure out what matters by noticing per-index/table problems as they happen. Bloat is both harmful and benign (often both). Top-down scheduling seems too complex due to non-linear behaviors. Bottom-up works.
Conversation
Generally agreed.
Having some pressure for doing such work close to each other also has benefits though - a considerably higher likelihood of cache hits and re-modifying pages that are already dirty before they're written out. And a lower likelihood of FPIs.
2
1
And I'm not sure I believe that it's a good idea to schedule such things in a "local" manner. E.g. when scheduling vacuums due to getting closer to the xid horizon, it makes sense to first schedule several heap vacuums in "age" order, then index vacuums separately.
4
Fwiw I've multiple times seen pathological systems where some minor index corruption cause vacuum to crash. So every minute it tried to vacuum the same table and failed. So autovacuum stopped making any progress at all.
1
1
Error from index ought to not prevent VACUUM from completing remaining work (excluding second heap pass). Postgres 14 failsafe mechanism should at least prevent a wraparound outage, though. Index vacuuming optional in principle now, so a comprehensive fix is quite possible.
2
1
I'm more concerned about a busy database not getting any vacuums for months. I'm thinking autovacuum should randomize the schedule a bit.
1
1
A related idea that I considered recently is to freeze some blocks earlier and at random, to spread out the cost of freezing blocks that are already ready to be frozen. The generic xid based cutoff isn't particularly natural.
1
2
Something along those lines would be very helpful. For whatever reason, vacuum_freeze_min_age doesn't seem to help that much in practice.
1
"Debt" mental model may be useful. Delaying freezing tuples is helpful if queries are probably going to update them soon anyway, or if doing so allows you to take the hit at a more convenient time instead (e.g., off-hours manual VACUUM). But that's it!
1
VACUUM could opportunistically vary when and where to freeze tuples based on qualitative assessments about costs and benefits at the block level, debt over time, etc. No simple XID cutoff is going to work well. That said, getting rid of freezing entirely might actually be easier!