[SOLVED] Postgres query which shows when autovacuum (freeze) is launched

Postgres query which shows when autovacuum (freeze) is launched

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%')

Solution

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.