On 2019-Mar-27, Darafei "Komяpa" Praliaskouski wrote:
> Attached is sketch of small patch that fixes several edge cases with > autovacuum. Long story short autovacuum never comes to append only tables, > killing large productions.
Yeah, autovac is not coping with these scenarios (and probably others). However, rather than taking your patch's idea verbatim, I think we should have autovacuum use separate actions for those two (wildly different) scenarios. For example:
* certain tables would have some sort of partial scan that sets the visibility map. There's no reason to invoke the whole vacuuming machinery. I don't think this is limited to append-only tables, but rather those are just the ones that are affected the most.
What other machinery runs on VACUUM invocation that is not wanted there? Since Postgres 11 index cleanup is already skipped on append-only tables.
* tables nearing wraparound danger should use the (yet to be committed) option to skip index cleaning, which makes the cleanup action faster. Again, no need for complete vacuuming.
"Nearing wraparound" is too late already. In Amazon, reading table from gp2 after you exhausted your IOPS burst budget is like reading a floppy drive, you have to freeze a lot earlier than you hit several terabytes of unfrozen data, or you're dead like Mandrill's Search and Url tables from the link I shared.