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
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));