Re: [PERFORM] 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@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828


application/pkcs7-signature attachment: smime.p7s