Re: [PATCHES] smartvacuum() instead of autovacuum

Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.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.)
> 
> 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.

I think this is one of the reasons table specific delay settings were 
designed in from the beginning.  I think the main use cases for multiple 
vacuums at once are:
1) Vacuum per table space assuming each table space is on a different 
drive with it's own I/O.
2) the frequently updated table that can't wait to be vacuumed while a 
large table is being vacuumed.  In this case if you set a system default 
delay setting and set a more aggressive table specific delay setting for 
your hot spot tables then multiple vacuums become a clear win.  This is 
an important case that I hope we handle soon.  At this point it's one of 
the main failings of the current autovacuum system.