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

Re: autovacuum: recommended?

From: Decibel! <decibel(at)decibel(dot)org>
To: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
Cc: Tobias Brox <tobias(at)nordicbet(dot)com>, Gábor Farkas <gabor(at)nekomancer(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: autovacuum: recommended?
Date: 2007-11-19 04:14:09
Message-ID: D654996C-8EB9-49E4-BB59-DD3426013C0F@decibel.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Nov 16, 2007, at 5:56 AM, Csaba Nagy wrote:
> We are doing that here, i.e. set up autovacuum not to touch big  
> tables,
> and cover those with nightly vacuums if there is still some  
> activity on
> them, and one weekly complete vacuum of the whole DB ("vacuum" without
> other params, preferably as the postgres user to cover system tables
> too).

IIRC, since 8.2 autovacuum will take note of manual vacuums so as not  
to needlessly vacuum something that's been recently vacuumed  
manually. In other words, you shouldn't need to disable autovac for  
large tables if you vacuum them every night and their churn rate is  
low enough to not trigger autovacuum during the day.

> In fact we also have a few very frequently updated small tables, those
> are also covered by very frequent crontab vacuums because in 8.2
> autovacuum can spend quite some time vacuuming some medium sized  
> tables
> and in that interval the small but frequently updated ones get  
> bloated.
> This should be better with 8.3 and multiple autovacuum workers.

+1. For tables that should always remain relatively small (ie: a web  
session table), I usually recommend setting up a manual vacuum that  
runs every 1-5 minutes.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828


In response to

pgsql-performance by date

Next:From: tvDate: 2007-11-19 09:53:08
Subject: Re: autovacuum: recommended?
Previous:From: Decibel!Date: 2007-11-19 04:11:15
Subject: Re: autovacuum: recommended?

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