Re: Database size growing over time and leads to performance impact

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(at)2ndQuadrant(dot)com www.2ndQuadrant.us