Message-ID: <4BB02ACE.9000804@2ndquadrant.com>
Date: Mon, 29 Mar 2010 00:21:34 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Pierre C <lists@peufeu.com>
CC: gnanam@zoniac.com, pgsql-performance@postgresql.org
Subject: Re: Database size growing over time and leads to performance
 impact
References: <004701cacdad$7013e9f0$503bbdd0$@com>
	<op.u98ao1lxeorkce@immo.peufeu.com>
In-Reply-To: <op.u98ao1lxeorkce@immo.peufeu.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Pierre C wrote:
> 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...

In 8.2, it's included, but not turned on by default.  And it can only 
have a single autovacuum worker, which limits its ability to keep up 
with more difficult workloads.

As for CLUSTER, the implementation in 8.2 is limited compared to the 8.3 
one.  If you look at 
http://www.postgresql.org/docs/8.2/static/sql-cluster.html you'll see a 
scary paragraph starting with "CLUSTER loses all visibility information 
of tuples..." that is missing from later versions, because that problem 
was fixed in 8.3.  I try to avoid using CLUSTER on 8.2 or earlier 
versions unless I can block all clients during the maintenance window 
it's running in.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us