postgresql autovacuum not working although process is running

Question

I have PostgreSQL 9.2 running on Ubuntu. The autovacuum process is running on the server.

The database has heavy load, 1000's of additions and 100's of deletions daily and yet it seems that autovacuum isn't firing up.

There is nothing relating to vacuum in the logs.

Doing ps -ef | grep -i vacuum

produces: postgres 1406 1130 0 Oct13 00:00:14 postgres: autovacuum launcher process

Running query SELECT last_autovacuum , last_autoanalyze FROM pg_stat_user_tables; produces no records in last autovacuum column, and 2 entrys in the suto_analyze column.

The pertinent section of postgresql.conf is:

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = on         # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.
log_autovacuum_min_duration = 250   # -1 disables, 0 logs all actions and
                    # their durations, > 0 logs only
                    # actions running at least this number
                    # of milliseconds.
autovacuum_max_workers = 3      # max number of autovacuum subprocesses
                    # (change requires restart)
#autovacuum_naptime = 1min      # time between autovacuum runs
autovacuum_vacuum_threshold = 128   # min number of row updates before
                    # vacuum
autovacuum_analyze_threshold = 128  # min number of row updates before
                    # analyze
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum
                    # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                    # autovacuum, in milliseconds;
                    # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1  # default vacuum cost limit for
                    # autovacuum, -1 means use
                    # vacuum_cost_limit

Any thoughts?

Answer 1

The relevant entry in the quoted configuration is probably:

autovacuum_vacuum_scale_factor = 0.2

With this default, it's normal that autovacuum skips a table if less than 20% of its rows have been deleted or updated (or less than autovacuum_vacuum_threshold rows but at 128 that shouldn't matter here).

See its definition in the documentation:

autovacuum_vacuum_scale_factor (floating point)

Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of table size). This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.

To have autovacuum process certain tables if you don't like the defaults, you may lower this parameter on a case by case basis. For example to set it at 1%:

ALTER TABLE tablename SET (autovacuum_vacuum_scale_factor=0.01);