On Wed, 15 Dec 2021 at 15:30, Peter Geoghegan <p...@bowt.ie> wrote: > > My emphasis here has been on making non-aggressive VACUUMs *always* > advance relfrozenxid, outside of certain obvious edge cases. And so > with all the patches applied, up to and including the opportunistic > freezing patch, every autovacuum of every table manages to advance > relfrozenxid during benchmarking -- usually to a fairly recent value. > I've focussed on making aggressive VACUUMs (especially anti-wraparound > autovacuums) a rare occurrence, for truly exceptional cases (e.g., > user keeps canceling autovacuums, maybe due to automated script that > performs DDL). That has taken priority over other goals, for now.
While I've seen all the above cases triggering anti-wraparound cases by far the majority of the cases are not of these pathological forms. By far the majority of anti-wraparound vacuums are triggered by tables that are very large and so don't trigger regular vacuums for "long periods" of time and consistently hit the anti-wraparound threshold first. There's nothing limiting how long "long periods" is and nothing tying it to the rate of xid consumption. It's quite common to have some *very* large mostly static tables in databases that have other tables that are *very* busy. The worst I've seen is a table that took 36 hours to vacuum in a database that consumed about a billion transactions per day... That's extreme but these days it's quite common to see tables that get anti-wraparound vacuums every week or so despite having < 1% modified tuples. And databases are only getting bigger and transaction rates faster... -- greg
