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
