Re: Vacuum problems

Scott Marlowe wrote:

> While I don't wholly disagree with periodic reindexing, I do recommend
> that one keeps track of bloat. It's easy enough to have an alarm that
> goes off if any index gets over 50% dead space, then go look at the
> database.

Reading this list, I've noticed that:

- Many admins don't understand vacuum vs vacuum full at all,
and are unaware of the probable need for a reindex after
vacuum full. They're often landing up with very bloated indexes
from excessive use of vacuum full, or very bloated tables due
to insufficient fsm space / infrequent vacuuming.

- It's hard to spot table and (especially) index bloat. Pg doesn't
warn about bloated tables or indexes in any way that people seem to
notice, nor are there any built-in views or functions that might help
the admin identify problem tables and indexes.

- Most people have a lot of trouble understanding where and how
their storage is being used.

I'm wondering if it might be a good idea to adopt one of the handy views
people have written for getting table/index bloat information as a
standard part of Pg (probably as an SQL function rather than a view) so
people can just "SELECT pg_bloat()" to get a useful summary of
table/index status.

The other thing I wonder about is having EXPLAIN and EXPLAIN ANALYZE
report information on the amount of free space that a table seq scan or
an index scan is having to traverse. That might bring problems to the
attention of people who're investigating query performance issues
without being aware that the underlying issue is actually bloated
indexes, not bad plans.

--
Craig Ringer