Skip site navigation (1) Skip section navigation (2)

Re: set autovacuum=off

From: Thom Brown <thom(at)linux(dot)com>
To: Alessandro Gagliardi <alessandro(at)path(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: set autovacuum=off
Date: 2012-02-23 12:34:29
Message-ID: CAA-aLv4KL4atuaqJFi3BBWiRsWiJOVQZMk65JYw=H8ezZV6O8g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Claudio FreireDate: 2012-02-23 14:07:21
Subject: Re: Very long deletion time on a 200 GB database
Previous:From: Venkat BalajiDate: 2012-02-23 12:21:26
Subject: : Cost calculation for EXPLAIN output

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group