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