PostgreSQL‎ > ‎

Vacuuming

Helpful Queries

Query for tables that maybe in need of vacuuming:
select schemaname, relname, last_autoanalyze, last_autovacuum, n_dead_tup, n_live_tup
        , (n_dead_tup * 100 / case(n_live_tup) when 0 then 1 else n_live_tup end)::INT||'%' as pct_dead
from pg_stat_all_tables 
where schemaname not like 'pg_%'
and n_dead_tup > 50 
order by schemaname, relname;

Another one via Richard Yen:
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);

To get more detail for a single table:
select last_analyze, last_autoanalyze, last_vacuum, last_autovacuum, n_dead_tup, n_live_tup
from pg_stat_all_tables
where schemaname='foo' and relname='bar';

To see running autovacuum jobs:
-- For < 9.6
select pid, usename, xact_start, waiting, query
from pg_stat_activity
where query like 'autovacuum%';

-- For 9.6
select pid, usename, xact_start, wait_event, query
from pg_stat_activity
where query like 'autovacuum%';

Documentation

Reading

Comments