On Tue, 2005-03-08 at 11:35, John R Pierce wrote:
> I've found that 7.x pgsql databases periodically need to be pg_dump'd, dropdb,
> then reloaded from the dump to continue to perform well... on a heavy use web
> server database which includes a busy user forum, I find doing this about 2-3
> times a year keeps things smooth. there may be a better way to clean them up,
> but I've not found it.
The most common cause here was index bloat. dropping / recreating the
indexes usually fixes the performance problems. reindex indexname from
the psql command line works well to do this.
note that some usage patterns that cause catalog indexes to bloat can
only be corrected by reindexing while in single user mode.