cancel
Showing results for 
Search instead for 
Did you mean: 

What tables will be vacuumed at the next autovacuum cycle?

Moderator

What tables will be vacuumed at the next autovacuum cycle?

Someone recently asked me if there was a way to tell which table(s) are slated for vacuum at the next autovacuum cycle.  I couldn't find any results after a short search on Google, so I decided to come up with my own query.  According to source, a table is slated for vacuum when threshold = vac_base_thresh + vac_scale_factor * reltuples, as per a comment in autovacuum.c.

 

With this knowledge, a simple query to determine tables requiring an autovacuum would be:

SELECT c.relname
  FROM pg_stat_all_tables t,
       pg_class c,
      (SELECT setting
         FROM pg_settings
        WHERE name = 'autovacuum_vacuum_threshold') AS avt,
      (SELECT setting
         FROM pg_settings
        WHERE name = 'autovacuum_vacuum_scale_factor') AS avsf
 WHERE c.oid = t.relid
   AND n_dead_tup > avt.setting::numeric + (avsf.setting::numeric * reltuples);

Enjoy!

2 REPLIES
EDB Team Member

Re: What tables will be vacuumed at the next autovacuum cycle?

Neat.  I think the logic internal to autovacuum multiplies the scale factor by pg_class.reltuples rather than by n_live_tup.

Moderator

Re: What tables will be vacuumed at the next autovacuum cycle?

rhaas wrote:

Neat.  I think the logic internal to autovacuum multiplies the scale factor by pg_class.reltuples rather than by n_live_tup.


Ah, that's right.  Fixed it--thanks!