Re: perfromance impact of vacuum

On Tue, 15 Jul 2003, Andrew Sullivan wrote:

> On Tue, Jul 15, 2003 at 11:04:53AM -0700, Jay O'Connor wrote:
> > Actually what I meant is how long the vacuum runs.  We're going to have a
> > big database (few TB projected, but I don't know where those numbers come
> > from) and I'm trying to ausage concerns that vacuuming will impact
> > performance significantly.
>
> It depends very heavily on your expired-tuple percentage.  But it is
> still not free to vacuum a large table.  And vacuum full always scans
> the whole table.
>
> Remember that vacuum operates on tables, which automatically means
> that it does nasty things to your cache.
>
> The stand-alone analyse can be helpful here.  It only does
> samples of the tables under analysis, so you don't face the same I/O
> load.  If all you're doing is adding to a table, it may be worth
> investigating.  Keep in mind, though, you still need to vacuum every
> 2 billion transactions.

this sounds like one of those places where the ability of a file system to
be told not to cache the accesses of a certain child process would be a
big win.

Wasn't there some discussionon BSD's ability to do this recently and
whether it was a win to port it into postgresql. I'd say that for large
databases being vacuumed mid-day it would be a great win.