Re: Why is postgres autovacuuming a table that is never updated?

On Tue, Apr 29, 2008 at 02:52:39PM -0400, Joseph S wrote:
> I'm running 8.2.6. I have a log table that is always INSERTed to, and no
> updates or deletes are ever run on. For some reason the autovacuum decided
> it needed to vacuum this table, and it is slowing down my production
> server.
>
> So my questions are:
> 1) Why vacuum, if this table is never updated?

Do any INSERTs ever fail? If so, you still need to vacuum. They
create dead tuples too.

Also, every table in every database that accepts connections in your
entire cluster (i.e. under one postmaster) MUST be vacuumed once every
so many transactions. Autovacuum will notice this in 8.2 and do
something about it; the docs say this: "Tables whose relfrozenxid
value is more than autovacuum_freeze_max_age transactions old are
always vacuumed."

> 2) How can I use pg_autovacuum table to disable autovac for this table?
> The docs are not clear on how to do this.

I think that would be a bad idea, given that autovacuum seems to think
you need to do it. Generally you want to alter autovacuum for a table
only if autovacuum isn't keeping up. Recheck your assumptions before
you do this. (The docs in fact tell you how to do it, but you have to
read two parts of the docs to figure it out. I am trying to
discourage you from doing what you're planning, so I'm unwilling to
tell you how to do it.)

A

--
Andrew Sullivan
ajs(at)commandprompt(dot)com
+1 503 667 4564 x104
http://www.commandprompt.com/