Thread: How to disable the autovacuum ?

## Esteban Zimanyi (ezimanyi@ulb.ac.be):

> I have tried
> alter system set autovacuum = off;
> but it does not seem to work.

Did you reload the configuration ("SELECT pg_reload_conf()" etc) after
that? If not, that's your problem right there.

Regards,
Christoph

-- 
Spare Space

Esteban Zimanyi <ezimanyi@ulb.ac.be> writes:
> Any idea how to disable the autovacuum during the regression and coverage
> tests for the MobilityDB extension ?

TBH, this seems like a pretty bad idea.  If your extension doesn't
behave stably with autovacuum it's not going to be much use in the
real world.

In the core tests, we sometimes disable autovac for individual
tables using a per-table storage option, but that's a last resort.

            regards, tom lane

## Esteban Zimanyi (ezimanyi@ulb.ac.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