Postgres query which shows when autovacuum (freeze) is launched-CodePudding

Help me please to write query which shows when on each table it is time to start autovacuum (freeze). I mean when 'vacuum_freeze_table_age' is on.

I've got Postgres 11. I wrote query, but it seems not wright:

SELECT   
        age(c.relminmxid) - current_setting('vacuum_freeze_table_age')::int8           as left_for_vacuum_freeze
from (pg_class c 
      join pg_namespace n on (c.relnamespace=n.oid)
     )
where c.relkind IN ('r','m','t') --and (age(c.relfrozenxid)::int8 > (current_setting('autovacuum_freeze_max_age')::int8 * 0.8))
      AND n.nspname not like ('pg_temp%')

CodePudding user response：

You could try this query:

SELECT oid::regclass AS table_name,
       /* number of transactions over "vacuum_freeze_table_age" */
       age(c.relfrozenxid)
       - current_setting('vacuum_freeze_table_age')::integer AS overdue_by
FROM pg_class AS c 
WHERE c.relkind IN ('r','m','t')  /* tables, matviews, TOAST tables */
  AND age(c.relfrozenxid)
      > least(
           /* it is ok to go a bit beyond the limit where VACUUM is triggered */
           current_setting('autovacuum_freeze_max_age')::integer   50000000,
           /* but at this point, we'll get warnings */
           2^31 - 40000000
        )
ORDER BY /* worst first */ age(c.relfrozenxid) DESC;

I chose not to report a table unless it it 50 million transactions over vacuum_freeze_table_age, because there is nothing wrong with that. Only if anti-wraparound autovacuum is a couple of million transactions overdue, you should start to worry. If you prefer to start fretting before anti-wraparound autovacuum hits, change 50000000 to - 10000000 or so.