On Mon, Apr 12, 2021 at 5:38 PM Virender Singla <virender....@gmail.com> wrote: > > Hi Postgres Community, > > Regarding anti wraparound vacuums (to freeze tuples), I see it has to scan > all the pages which are not frozen-all (looking at visibility map). That > means even if we want to freeze less transactions only (For ex - by > increasing parameter vacuum_freeze_min_age to 1B), still it will scan all the > pages in the visibility map and a time taking process.
If vacuum_freeze_min_age is 1 billion, autovacuum_freeze_max_age is 2 billion (vacuum_freeze_min_age is limited to the half of autovacuum_freeze_max_age). So vacuum freeze will still have to process tuples that are inserted/modified during consuming 1 billion transactions. It seems to me that it’s not fewer transactions. What is the use case where users want to freeze fewer transactions, meaning invoking anti-wraparound frequently? > > Can there be any improvement on this process so VACUUM knows the tuple/pages > of those transactions which need to freeze up. > > Benefit of such an improvement is that if we are reaching transaction id > close to 2B (and downtime), that time we can quickly recover the database > with vacuuming freeze only a few millions rows with quick lookup rather than > going all the pages from visibility map. Apart from this idea, in terms of speeding up vacuum, vacuum_failsafe_age parameter, introduced to PG14[1], would also be helpful. When the failsafe is triggered, cost-based delay is no longer be applied, and index vacuuming is bypassed in order to finish vacuum work and advance relfrozenxid as quickly as possible. Regards [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1e55e7d1755cefbb44982fbacc7da461fa8684e6 -- Masahiko Sawada EDB: https://www.enterprisedb.com/
