Re: table size growing out of control

On Tue, Jul 16, 2002 at 03:45:08PM -0400, Robert Treat wrote:

> anyway. One thing I picked out from the archives is that vacuum cannot
> recover disk space if it cannot obtain an exclusive lock on the table.
> If this is still the case (someone confirm this and I'll add a note to
> the docs) it might explain part of my problem since that table is almost
> continually being updated. I gathered some more statistics that might be
> of interest:

But as I understand it, the standard, non-blocking vacuum marks
unused pages for reuse by the backend.  That approach can only
"remember" so many recovered pages.  Adjusting the free space map
setting improves that, so if you have a lot of turnover in your
tables, you can increase the FSM and vacuum more frequently.  You
still need up to double the size of the table, however, to
accommodate the turnover.

Now, I _think_ the above is correct, and I hope someone will correct
me if I'm wrong.  One question I have, however, is what the
performance penalty is of having a more-or-less constant vacuum
process running.  Historically, of course, one had to trade off
vacuuming against the cost of an exclusive table lock.  But now that
vacuum doesn't block everyone else, is there some reason not to run
vacuum (say) hourly (aside, obviously, from load on the machine).

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110