Re: Finetuning Autovacuum

On Mon, Jan 4, 2010 at 6:38 PM, Benjamin Krajmalnik <kraj(at)illumen(dot)com> wrote:
> PostgreSQL 8.4/FreeBSD 7.2 amd64
>
> I have a database which has  3 tables which get a very high level of
> activity (about 40 thousand updates per minute).

That's quite a lot. Even if you do get autovac / vacuum aggressive
enough, you're gonna have a lot of dead (but available for writing)
rows all the time to provide the space for the new rows to go into.

> The tables are getting quite bloated, since autovacuum is apparently not
> optimally configured (it is using the default settings).

Yeah, it's not setup for something quite this aggressive by default.

> Anything I do must be such that it will not cause deadlocks, since the
> effects can be catastrophic with the amount of data being pumped through the
> system.
>
> Initially, I had scheduled tasks through pgagent running a vacuum analyze
> every 15 minutes, but other posts I have read here have stated this could
> cause deadlocks, and mentioned running autovacuum is preferable

Autovacuum is just a daemon that calls vacuum (regular) for you, so if
regular vacuum could cause deadlocks then so could autovacuum. In my
experience plain vacuum does not cause locks, or deadlocks, that get
in the way of very much. The only experience I have with vacuum
getting in the way is with slony thrown in the mix on a machine
running execute on ddl on the slony nodes. Which is a pretty odd
combo and turning off autovac during slony maintenance fixed me right
up.

You should set up a test and see how it runs.

> Also, are rows “vacuumed” in the indices made available without having to
> reindex?  The reason I am asking is because the indices seem to be bloating
> to a much higher factor than the data table.

yes, both indexes and tables have their free space made available by
regular vacuum.