Re: How to disable the autovacuum ?

## Esteban Zimanyi (ezimanyi(at)ulb(dot)ac(dot)be):

> Is there a step-by-step procedure specified somewhere?

The first step is not to disable autovacuum... (why would you want to
do that?).

> For example, before launching the tests there is a load.sql file that loads
> all the test tables. The file starts as follows
>
> SET statement_timeout = 0;

That's all session parameters.
The general principle here is: We have parameters which can be set
inside a session - "SET ..." - or even inside a transaction - "SET LOCAL"
- and reset again ("RESET ..."). These parameters are also in the
server configuration - I like to think of those settings as the defaults
for new sessions (except when overridden on a user/database/function
level, etc.).
Other parameters have to be set on the server level - that is, added
to the configuration file(s). ("ALTER SYSTEM" is just a way to add
configuration directives via the postgresql.auto.conf file). Changes
the the configuration files become active after a server reload (or
restart, of course).
Some of these settings can only be set on server start (not reload)
or even have to match the data directory.

In any case, the documentation is very clear if a restart is
required for changing a parameter, e.g. in
https://www.postgresql.org/docs/13/runtime-config-autovacuum.html
"This parameter can only be set in the postgresql.conf file or on the
server command line"

And some parameters can be set as storage parameters on a per-object
(table, index, ...) base, but that's the next can of worms.

The takeaways of this are:
1. ALTER SYSTEM only edits the configuration, reloads and restarts have
to be handled by the operator as usual.
Documentation: https://www.postgresql.org/docs/13/sql-altersystem.html
"Values set with ALTER SYSTEM will be effective after the next server
configuration reload, or after the next server restart in the case of
parameters that can only be changed at server start. A server
configuration reload can be commanded by calling the SQL function
pg_reload_conf(), running pg_ctl reload, or sending a SIGHUP signal
to the main server process."
2. Different parameters can have different contexts, which you should
be aware of when changing them. Session and object ("storage")
parameters take their defaults from the server configuration.
Check parameter documentation and the pg_settings view (documented
here: https://www.postgresql.org/docs/13/view-pg-settings.html )
for parameter contexts.
3. Don't disable autovacuum unless you really know what you're doing.

Gruß,
Christoph

--
Spare Space