Re: check_postgres_bloat

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