pgsql-performance-owner@postgresql.org wrote:
>>> Have you tried reindexing your active tables?
> It will cause some performance hit while you are doing it. It
> sounds like something is bloating rapidly on your system and
> the indexes is one possible place that could be happening.
You might consider using contrib/oid2name to monitor physical growth of
tables and indexes. There have been some issues with bloat in PostgreSQL
versions prior to 8.0, however there might still be some issues under
certain circumstances even now, so it does pay to cast an eye on what's
going on. If you haven't run vaccum regularly, this might lead to
regular vacuums not reclaiming enough dead tuples in one go, so if
you've had quite a lot of UPDATE/DELETE activity going onin the past and
only just started to use pg_autovacuum after the DB has been in
production for quite a while, you might indeed have to run a VACUUM FULL
and/or REINDEX on the affected tables, both of which will more or less
lock out any client access to the tables als long as they're running.
Kind regards
Markus