I want to use autovacuum for vacuum and analyze in PostgreSQL 11.5.
I am trying to enable it on a dev server but I can't get it started.

According to documentation :

autovacuum = on
log_autovacuum_min_duration = 0
#autovacuum_max_workers = 3     # max number of autovacuum subprocesses
                # (change requires restart)
autovacuum_naptime = 60     # time between autovacuum runs
autovacuum_vacuum_threshold = 0 
autovacuum_analyze_threshold = 0
#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_multixact_freeze_max_age = 400000000    # maximum multixact 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

Analyze and vacuum thresold are set to 0 to make autovacuum start on the dev server where there is no activity.

I added as mentionned : track_counts = on

Then I enabled autovacuum on just one table because I realized that reloptions field in pg_class was NULL. Now I can read {autovacuum_enabled= true} for my table.

I reloaded the server conf many times and checked the statistics of my tables with pgadmin4, especially the columns last autovacuum and last autoanalyze. They are still empty.

  • It is a test on a dev server. It will be different on the server in production. That is why I want to learn how it works. – Leehan Oct 22 '19 at 12:06
  • You can have detailed information about autovacuum by reading 24.1.6. The Autovacuum Daemon section. You should know that autovacuum parameters can be set for particular tables. ref: Storage Parameters – Sahap Asci Oct 22 '19 at 13:16
  • 2
    You are trying to solve a problem which does not exist. Throw some activity at the server, and autovac will kick in. (If you want it to kick in every time anything is deleted or updated, then you should set autovacuum_vacuum_scale_factor to zero as well as threshold) – jjanes Oct 22 '19 at 13:49
  • and in this case, should I comment or not autovacuum = on ? – Leehan Oct 22 '19 at 13:58
  • 1
    autovacuum defaults to 'on'. So having it commented out or not makes no difference. – jjanes Oct 22 '19 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.