Re: autovacuum next steps, take 2

Alvaro Herrera wrote:
> Matthew T. O'Connor wrote:
>> I'm not sure how pg_class.relpages is maintained but what happens to a
>> bloated table? For example, a 100 row table that is constantly updated
>> and hasn't been vacuumed in a while (say the admin disabled autovacuum
>> for a while), now that small 100 row table has 1000 pages in it most of
>> which are just bloat, will we miss this table? Perhaps basing this on
>> reltuples would be better?
>
> Well, this would only happen the first time, until the plain worker
> processed the table; next time it would be picked up by the hot table
> worker. But yeah, we can build a better estimate using the same trick
> the planner uses: estimate tuple density as reltuples/relpages times the
> actual number of blocks on disk.

Well even skipping it the first time isn't good, anything that further
delays a hot table from getting vacuumed is bad. Also, I'm not sure it
would just be the first time since plain VACUUM isn't going to reclaim
most of the space, just mark it as reusable. This is moot however if we
use a good metric, I have no idea if what you suggest above would be
good enough.

>>> A table over 600 pages does not sound like a good candidate for hot, so
>>> this seems more or less reasonable to me. On the other hand, maybe we
>>> shouldn't tie this to the vacuum cost delay stuff.
>> 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.

Simple is better, at least until proven otherwise.