On Fri, Mar 23, 2007 at 10:29:59AM -0000, Victoria Parsons wrote:

> Because we are not ready to upgrade slony at the moment we will solve
> this with a vacuum full of pg_listener for each database once an hour.
> That seems to be keeping our replication under control.

You ought to be able to cause the same effect with a plain vacuum,
maybe more often than once an hour.  

Remember that VACUUM FULL takes a write lock on the table, so you're
going to block things while you do this.

Just as important, VACUUM FULL collapses the table, meaning that the
very next write on the table will require an I/O operation to extend
the table _before_ it can write in the table.  A regular VACUUM will
merely mark the relevant areas as eligible spots for writing.  That
means that the next write operation that comes alone can re-use that
free space, which actually means less I/O and therefore better
performance.

Note that we have actually had tables in some of our databases where
we were, historically, vacuuming every 5 minutes.  The key is to watch
your I/O and table growth.  There's a sweet spot where you vacuum that
often, the table remains managable in size, and the I/O is as low as
it can get.

A


-- 
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew at ca.afilias.info>                              M2P 2A8
jabber: ajsaf at jabber.org                 +1 416 646 3304 x4110