On Mon, Jan 9, 2023 at 5:22 PM Andres Freund <and...@anarazel.de> wrote: > I've also seen the inverse, with recent versions of postgres: Autovacuum can > only ever make progress if it's an anti-wraparound vacuum, because it'll > always get cancelled otherwise. I'm worried that substantially increasing the > time until an anti-wraparound autovacuum happens will lead to more users > running into out-of-xid shutdowns. > > I don't think it's safe to just increase the time at which anti-wrap vacuums > happen to a hardcoded 1 billion.
That's not what the patch does. It doubles the time that the anti-wrap no-autocancellation behaviors kick in, up to a maximum of 1 billion XIDs/MXIDs. So it goes from autovacuum_freeze_max_age to autovacuum_freeze_max_age x 2, without changing the basic fact that we initially launch autovacuums that advance relfrozenxid/relminmxid when the autovacuum_freeze_max_age threshold is first crossed. These heuristics are totally negotiable -- and likely should be thought out in more detail. It's likely that most of the benefit of the patch comes from simply trying to advance relfrozenxid without the special auto-cancellation behavior one single time. The main problem right now is that the threshold that launches most antiwraparound autovacuums is exactly the same as the threshold that activates the auto-cancellation protections. Even doing the latter very slightly later than the former could easily make things much better, while adding essentially no risk of the kind you're concerned about. > I'm also doubtful that it's ok to just make all autovacuums on relations with > an age > 1 billion anti-wraparound ones. For people that use a large > autovacuum_freeze_max_age that will be a rude awakening. Actually, users that have autovacuum_freeze_max_age set to over 1 billion will get exactly the same behavior as before (except that the instrumentation of autovacuum will be better). It'll be identical. If you set autovacuum_freeze_max_age to 2 billion, and a "standard" autovacuum is launched on a table whose relfrozenxid age is 1.5 billion, it'll just be a regular dead tuples/inserted tuples autovacuum, with the same old familiar locking characteristics as today. -- Peter Geoghegan
