Re: set autovacuum=off

On 22 February 2012 23:50, Alessandro Gagliardi <alessandro(at)path(dot)com> wrote:
> I have a database where I virtually never delete and almost never do
> updates. (The updates might change in the future but for now it's okay to
> assume they never happen.) As such, it seems like it might be worth it to
> set autovacuum=off or at least make it so vacuuming hardly ever occurs.
> Actually, the latter is probably the more robust solution, though I don't
> know how to do that (hence me writing this list). I did try turning
> autovacuum off but got:
>
> ERROR: parameter "autovacuum" cannot be changed now
> SQL state: 55P02
>
> Not sure what, if anything, I can do about that.

Autovacuum is controlled by how much of a table has changed, so if a
table never changes, it never gets vacuumed (with the exceptional case
being a forced vacuum freeze to mitigate the transaction id
wrap-around issue). The settings which control this are
autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor.
Therefore it isn't necessary to disable autovacuum.

But if you are adamant about disabling it, you need to change it in
your postgresql.conf file and restart the server.

--
Thom