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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
