Why autovacuum is not running

Question

If the parameter autovacuum is on, and the parameter track_counts is on also - why it's look like the autovacuum has never worked?

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze 
from pg_stat_user_tables 
where last_vacuum is not null 
or last_autovacuum is not null;

no rows...

Which actions needed to make it run?

My version is : PostgreSQL 9.6

Answer 1

From the docs:

Postgres AutoVacuum will run when num_obselete_tuples exceeds the vacuum_threshold.

vacuum_threshold = base_threshold + (scale_factor * num_tuples)

• base_threshold = autovacuum_vacuum_threshold
• scale_factor = autovacuum_vacuum_scale_factor
• num_tuples can be found by querying pg_class

SELECT relname, reltuples FROM pg_class

You should be able to use the numbers above to determine how many dead_tuples are required before the autovacuum will start.

You can see the n_dead_tuples with:

SELECT n_dead_tup FROM pg_stat_user_tables where relname = ‘transaction’;

A UPDATE or DELETE statement will create a dead_tuple. You can check the autovacuum settings and plug numbers in to find the autovacuum threshold and then update that many rows in your table which ought to trigger the autovacuum daemon.

You can look for running autovacuum processes with:

SELECT query, xact_start, state FROM pg_stat_activity WHERE query LIKE 'autovacuum:%';