CODEWITHSUNDEEP
postgresql
jacoblambert
jacoblambert

Reputation: 787

Postgres - Configuration Change to Vacuum Requires Restart?

In the postgressql.conf file, I'd like to turn on autovacuum and change the threshold to 100. Because changing the max_workers thread explicitly states that it requires a restart, I am inferring that turning authvacuum on and changing its threshold does not. Can someone confirm? 

autovacuum = on         # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.
#log_autovacuum_min_duration = -1   # -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 = 100   # min number of row updates before
                # vacuum

However, in the following post I found evidence for the opposite inference; note that effective_cache_size explicitly states that a restart is not required while autovacuum does not mention a restart: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Upvotes: 1

Views: 4194

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324491

When a value in postgresql.conf is commented out, that means the default is used.

autovacuum defaults to on.

Run:

SHOW autovacuum;

to see the current value.

For more detail about a parameter use pg_settings:

postgres=> \x
Expanded display is on.

postgres=> select * from pg_settings where name = 'autovacuum';
-[ RECORD 1 ]---------------------------------
name       | autovacuum
setting    | on
unit       | 
category   | Autovacuum
short_desc | Starts the autovacuum subprocess.
extra_desc | 
context    | sighup
vartype    | bool
source     | default
min_val    | 
max_val    | 
enumvals   | 
boot_val   | on
reset_val  | on
sourcefile | 
sourceline |

See the context entry? That tells you, for certain, when it can be changed. In this case, it can be changed at sighup time, which is a postmaster reload. So a kill -HUP of the postmaster, a pg_ctl reload, or a SELECT pg_reload_conf(); will update the setting, causing a new value in postgresql.conf to take effect.

The same is true of autovacuum_vacuum_threshold.

In general, if the docs don't mention that you need a restart you'll usually instead need a config reload to have changes in the config file take effect.

Upvotes: 3

Related Questions