From: Greg Smith <greg@2ndquadrant.com>
To: Pierre C <lists@peufeu.com>
Cc: gnanam@zoniac.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Database size growing over time and leads to performance impact
Date: Mon, 29 Mar 2010 00:21:34 -0400
Message-ID: <4BB02ACE.9000804@2ndquadrant.com> (raw)
In-Reply-To: <op.u98ao1lxeorkce@immo.peufeu.com>
References: <004701cacdad$7013e9f0$503bbdd0$@com>
<op.u98ao1lxeorkce@immo.peufeu.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, lists@peufeu.com, gnanam@zoniac.com
Subject: Re: Database size growing over time and leads to performance impact
In-Reply-To: <4BB02ACE.9000804@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox