Search Postgresql Archives

Re: Vacuum problems

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux