List tables that will be autovacuumed next.

5 Nov 2017

This is a query inspired by Richard Yen to find out which tables will be vacuumed the next time the autovacuumer kicks in. It's based on a formula written right in the source code of src/backend/postmaster/autovacuum.c:

threshold = vac_base_thresh + vac_scale_factor * reltuples

Here's a query that will figure that out. Note that when there are no tables that will get vacuumed at the next autovacuum, this query will return no rows.

select t.relname as table_name,
       t.reltuples as row_count,
       s.n_dead_tup as dead_tuples,
       current_setting('autovacuum_vacuum_threshold') as avt,
       current_setting('autovacuum_vacuum_scale_factor') as avsf
  from pg_class as t
  join pg_stat_all_tables as s
    on t.oid = s.relid
 where s.n_dead_tup > (
           current_setting('autovacuum_vacuum_threshold')::numeric
           + (current_setting('autovacuum_vacuum_scale_factor')::numeric
              * t.reltuples));