Understanding Postgres’ autovacuum isn’t easy at the beginning: it’s a balance between many settings. To help you, here’s a query to understand them and know which tables are pending for autovacuum and how many dead tuples are required before a table gets vacuumed again.
Note: this query uses settings taken from the config files, if you had set special autovacuum parameters for a single table, the won’t be considered.
SELECT psut.relname, to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum, to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum, to_char(pg_class.reltuples, '9G999G999G999') AS n_tup, to_char(psut.n_dead_tup, '9G999G999G999') AS dead_tup, to_char(CAST(current_setting('autovacuum_vacuum_threshold') AS bigint) + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric) * pg_class.reltuples), '9G999G999G999') AS av_threshold, CASE WHEN CAST(current_setting('autovacuum_vacuum_threshold') AS bigint) + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric) * pg_class.reltuples) < psut.n_dead_tup THEN '*' ELSE '' END AS expect_av FROM pg_stat_user_tables psut JOIN pg_class on psut.relid = pg_class.oid ORDER BY 1;
The above query is taken from: http://dba.stackexchange.com/questions/21068/aggressive-autovacuum-on-postgresql