Re: Memory usage - indexes

Tobias Brox wrote:
> We do have some bloat-problems as well - every now and then we decide
> to shut down the operation, use pg_dump to dump the entire database to
> an sql file and restore it. The benefits are dramatic, the space
> requirement goes down a lot, and often some of our
> performance-problems goes away after such an operation.
>

You can do the same thing with far less trouble if you just CLUSTER the
table. It takes a lock while it runs so there's still downtime needed,
but it's far faster than a dump/reload and safer too.

> If it was to me, we would have had autovacuum turned on. We've had
> one bad experience when the autovacuumer decided to start vacuuming
> one of the biggest table at the worst possible moment - and someone
> figured autovacuum was a bad idea. I think we probably still would
> need regular vacuums to avoid that happening, but with autovacuum on,
> maybe we could have managed with regular vacuums only once a week or
> so.
>

The answer to "we once saw autovacuum go mad and cause us problems" is
never the knee-jerk "disable autovacuum", it's usually "change
autovacuum so it runs far more often but with lower intesity".
Sometimes it's "keep autovacuum on but proactively hit the biggest
tables with manual vacuums at slow times" too.

--
Greg Smith, 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance" Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book