Re: [PATCHES] smartvacuum() instead of autovacuum

Jim C. Nasby wrote:
> On Mon, Oct 23, 2006 at 03:08:03PM -0400, Tom Lane wrote:
> > "Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> > > The only case I can think of where autovac might not work as well as
> > > smartvacuum would be if you had a lot of databases in the cluster, since
> > > autovacuum will only vacuum one database at a time.
> >
> > It's conceivable that it'd make sense to allow multiple autovac
> > processes running in parallel. (The infrastructure part of this is easy
> > enough, the hard part is keeping them from all deciding to vacuum the
> > same table.)
>
> It might be worth creating a generic framework that prevents multiple
> vacuums from hitting a table at once, autovac or not.

That one is easy, because vacuum gets a lock on the affected table that
conflicts with itself. The problem is that the second vacuum would
actually wait for the first to finish.

A naive idea is to use ConditionalLockAcquire, and if it fails just skip
the table.

> > One reason we have not done that already is the thought that multiple
> > vacuum processes would suck too much I/O to be reasonable. Now you
> > could dial back their resource demands with the cost-delay settings,
> > but it's not clear that ten autovacs running at one-tenth speed are
> > better than one autovac using all the cycles you can spare. Usually
> > I think it's best if a vacuum transaction finishes as fast as it can.

In the scenario where one table is huge and another is very small, it
can certainly be useful to vacuum the small table several times while
the huge one has only been vacuumed once. For that you definitively
need the ability to run parallel vacuums.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.