Skip site navigation (1) Skip section navigation (2)

Re: Memory usage - indexes

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Tobias Brox <tobixen(at)gmail(dot)com>
Cc: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Memory usage - indexes
Date: 2010-09-24 18:05:34
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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
Author, "PostgreSQL 9.0 High Performance"    Pre-ordering at:

In response to

pgsql-performance by date

Next:From: Brad NicholsonDate: 2010-09-24 18:34:06
Subject: Re: Memory usage - indexes
Previous:From: Greg SmithDate: 2010-09-24 18:01:12
Subject: Re: Memory usage - indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group