PostgreSQL Auto Vacuum Snippets

postgres_autovacuum.sql

-- Get the default settings of the postgresql
select *
from pg_settings
where name like '%autovacuum%';

-- Get the table specific settings
select relname, reloptions
from pg_class
         join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where pg_namespace.nspname = 'public' and relam = 2
order by reloptions, relname;

-- Get table statics with dead row or ratio etc.
SELECT relname,
       n_ins_since_vacuum,
       n_dead_tup,
       n_live_tup,
       n_dead_tup
           / (n_live_tup
                  * current_setting('autovacuum_vacuum_scale_factor')::float8
           + current_setting('autovacuum_vacuum_threshold')::float8) as ratio,
       last_autovacuum,
       autovacuum_count
FROM pg_catalog.pg_stat_all_tables
where schemaname = 'public'
order by n_dead_tup desc;

-- Get the size of tables
select table_name,
       pg_size_pretty(pg_relation_size(quote_ident(table_name))),
       pg_relation_size(quote_ident(table_name)) as size
from information_schema.tables
where table_schema = 'public'
order by size desc;

-- Analyze and vacuum for a table
-- VACUUM (VERBOSE) logs;

-- Change auto vacuum configurations for a specific table (recommended settings for more than 10GB tables)
-- ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0);
-- ALTER TABLE t SET (autovacuum_vacuum_threshold = 10000);