Re: autovacuum next steps, take 2

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Matthew T. O'Connor wrote:
>> I'm not sure it's a good idea to tie this to the vacuum cost delay
>> settings either, so let me as you this, how is this better than just
>> allowing the admin to set a new GUC variable like
>> autovacuum_hot_table_size_threshold (or something shorter) which we can
>> assign a decent default of say 8MB.

> Yeah, maybe that's better -- it's certainly simpler.

I'm not liking any of these very much, as they seem critically dependent
on impossible-to-tune parameters. I think it'd be better to design this
around having the first worker explicitly expose its state (list of
tables to process, in order) and having subsequent workers key off that
info. The shared memory state could include the OID of the table each
worker is currently working on, and we could keep the to-do list in some
simple flat file for instance (since we don't care about crash safety).

I'm not certain exactly what "key off" needs to mean; perhaps each
worker should make its own to-do list and then discard items that are
either in-progress or recently done by another worker when it gets to
them.

I think an absolute minimum requirement for a sane design is that no two
workers ever try to vacuum the same table concurrently, and I don't see
where that behavior will emerge from your proposal; whereas it's fairly
easy to make it happen if non-first workers pay attention to what other
workers are doing.

BTW, it's probably necessary to treat shared catalogs specially ...

regards, tom lane