Re: BUG #16098: unexplained autovacuum to prevent wraparound

On Thu, Nov 7, 2019 at 11:39 AM Alessandro Ferraresi <alessandro.ferraresi1@gmail.com> wrote:

That's the output of the following query to check the progress of XID to autovacuum_freeze_max_age:

WITH max_age AS (    SELECT 2000000000 as max_old_xid       , setting AS autovacuum_freeze_max_age        FROM pg_catalog.pg_settings        WHERE name = 'autovacuum_freeze_max_age' )
, per_database_stats AS (    SELECT datname       , m.max_old_xid::int       , m.autovacuum_freeze_max_age::int       , age(d.datfrozenxid) AS oldest_current_xid    FROM pg_catalog.pg_database d    JOIN max_age m ON (true)    WHERE d.datallowconn ) 
SELECT max(oldest_current_xid) AS oldest_current_xid   , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound   , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac 
FROM per_database_stats

What is your setting of vacuum_freeze_table_age?  That is the point where a regularly scheduled vacuum will get promoted to a wraparound vacuum.  What if you delete the "WHERE d.datallowcon", and then replace the last 4 lines with "SELECT * from per_database_stats?

Cheers,

Jeff