Re: autovacuum: recommended?

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