From: Greg Smith <greg@2ndquadrant.com>
To: dx k9 <bitsandbytes88@hotmail.com>
Cc: posgres support <pgsql-admin@postgresql.org>
Subject: Re: check_postgres_bloat
Date: Fri, 11 Jun 2010 11:41:18 -0400
Message-ID: <4C12591E.8010607@2ndquadrant.com> (raw)
In-Reply-To: <SNT123-W2DB788D752D60727FF1D8D1D90@phx.gbl>
References: <SNT123-W2DB788D752D60727FF1D8D1D90@phx.gbl>
dx k9 wrote:
>
> check_postgres checks for both index and table bloat. It looks like
> my indexes are ok, this is just picking up on table bloat. I'm not
> sure what I can do to reclaim the wasted space other than vacuum full
> & analyze. Maybe a pgdump will do it.
>
CLUSTER will rebuild a new copy of the table without any table bloat,
and it's much faster than VACUUM FULL. See
http://wiki.postgresql.org/wiki/VACUUM_FULL
Note that the bloat estimate from check_postgres is extremely rough and
it's quite possible to get misleading results from it. I wouldn't do
anything just based on an initial report from it that a table is bloated
other than move the thresholds up until it stops complaining. The idea
is that once calibrated usefully to what is normal bloat levels for your
app by its measurement technique, you then monitor for excess bloat
outside of historical norms. You should not assume the number itself is
really accurate, and you should do a manual VACUUM VERBOSE against the
table to see if it's right or not before taking drastic action (like
VACUUM FULL or CLUSTER). You might also use pg_freespacemap instead to
compute more accurate bloat numbers, but most people consider that too
much work relative to the improvement you get over the simpler
check_postgres estimate.
--
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-admin@postgresql.org
Cc: greg@2ndquadrant.com, bitsandbytes88@hotmail.com
Subject: Re: check_postgres_bloat
In-Reply-To: <4C12591E.8010607@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