Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: gnanam@zoniac.com, pgsql-performance@postgresql.org
Subject: Re: Database size growing over time and leads to
 performance impact
References: <004701cacdad$7013e9f0$503bbdd0$@com>
Date: Sat, 27 Mar 2010 14:35:15 +0100
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.u98ao1lxeorkce@immo.peufeu.com>
In-Reply-To: <004701cacdad$7013e9f0$503bbdd0$@com>
User-Agent: Opera Mail/10.10 (Linux)

> 1.     VACUUM FULL ANALYZE once in a week during low-usage time and

VACUUM FULL compacts tables, but tends to bloat indexes. Running it weekly  
is NOT RECOMMENDED.

A correctly configured autovacuum (or manual vacuum in some circumstances)  
should maintain your DB healthy and you shouldn't need VACUUM FULL.

If you realize you got a bloat problem, for instance due to a  
misconfigured vacuum, use CLUSTER, which re-generates table AND index  
data, and besides, having your table clustered on an index of your choice  
can boost performance quite a lot in some circumstances.

8.2 is so old I don't remember if autovacuum is even included. Please try  
upgrading to the latest version...

Since your database probably fits in RAM, CLUSTER will be pretty fast.
You can schedule it weekly, if you need clustering. If you don't,  
autovacuum will suffice.
Hint : add a "SELECT count(*) FROM yourtable;" before "CLUSTER yourtable;"  
so that the table is pulled in the OS disk cache, it'll make CLUSTER  
faster.