Re: autovacuum next steps, take 2

Jim C. Nasby wrote:
> On Mon, Feb 26, 2007 at 10:18:36PM -0500, Matthew T. O'Connor wrote:
>> Jim C. Nasby wrote:
>> Here is a worst case example: A DB with 6 tables all of which are highly
>> active and will need to be vacuumed constantly. While this is totally
>> hypothetical, it is how I envision things working (without the threshold).
>
> I fail to see how a simple 6 table case is 'worst case'. It's common to
> see hundreds of tables, and I've run across more than one database with
> thousands of tables (think partitioning). In cases like those it's
> certainly possible, perhaps even likely that you would get many daemons
> running in the database at one time just from different tables suddenly
> needing vacuuming and appearing at a higher point in the list than other
> tables. With 100 ~1G tables getting updates it certainly wouldn't be
> hard to end up with 10 of those being vacuumed all at the same time.

Yes 6 tables is small, the worst-case part of the example was that all
the tables would need to be vacuumed constantly. Most databases only
have a few hot tables. Most tables only need to vacuumed every once in
a while.

> I do like the idea since it should be easier to tune, but I think we
> still need some limit on it. Perhaps as a first-pass we could just have
> a hard limit and log a message and/or set a flag any time we hit it.
> That would hopefully allow us to get information about how big a problem
> it really is. We could go one step further and say that the last daemon
> that can start in a database will only vacuum tables that can be done
> quickly; that's essentially what we've been talking about, except the
> limit we've been discussing would be hard-coded at 2.

I'm confused, what limit would be set at 2? The number of concurrent
workers? I've never said that.