From my POV, autovacuum is doing a very good job, with the exception of:
> - There might be time-based exclusions to the effect that large tables
> oughtn't be processed during certain periods (backup time?)
Either (per table!) exception or permission based control of when a
table can be vacuumed is needed to avoid vacuuming big tables during
peek business periods. While this can be alleviated by setting lower
vacuum cost settings, and it won't block anymore other vacuums, it will
still need the multiple vacuum stuff to still process small tables:
> - One might have *two* consumers, one that will only process small
> tables, so that those little, frequently updated tables can get
> handled quickly, and another consumer that does larger tables.
> Or perhaps that knows that it's fine, between 04:00 and 09:00 UTC,
> to have 6 consumers, and blow through a lot of larger tables
> simultaneously.
So one of the 2 might be enough. I guess time-based
exclusion/permissions are not that easy to implement, and also not easy
to set up properly... so what could work well is:
- allow a "priority" setting per table in pg_autovacuum;
- create a vacuum thread for each priority;
- each thread checks it's own tables to be processed based on the
priority setting from pg_autovacuum;
- there have to be a default priority for tables not explicitly set up
in pg_autovacuum;
- possibly set a per priority default vacuum cost and delay;
In 8.2 the different vacuum threads for the different priorities won't
step on each other toes, and the default settings for the priorities can
be used to create some easily manageable settings for vacuuming table
categories with different update/delete patterns.
There could be some preset priorities, but creating new ones would be
useful so the user can create one per table update/delete pattern.
Maybe priority is not the best word for this, but I can't think now on
other better...
Cheers,
Csaba.
