Re: pg_autovacuum: short, wide tables

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:

>>Hmm. With integrated autovacuum, we could set something up to issue
>>vacuums separately to TOAST tables and the main table. It'd probably be
>>a tad easier if the toast stats are separate from the main table; and an
>>autovac of the main table not necessarily would invoke vacuuming the
>>toast table.
>>
>>
>>I'm not proposing it for 8.1 though ...
>>
>>
>
>Well, why not? Arguably Mark's problem is a bug, and it's not too late
>to address bugs.
>
>I checked what actually happens if you try to vacuum a toast table:
>
>regression=# vacuum pg_toast.pg_toast_169901;
>WARNING: skipping "pg_toast_169901" --- cannot vacuum indexes, views, or special system tables
>VACUUM
>regression=#
>
>So it's not as bad as I thought. Maybe just weakening the filter in the
>stats tables views isn't a bad idea after all. Furthermore, we could
>allow VACUUM on a toast table to go through (is there any good reason
>to disallow it?) and then autovacuum would do more or less the right
>things with no further changes.
>
>

That would be a nice improvement. Any increase in the granularity of
vacuum can't be a bad thing.

>I'm not sure about the idea of not vacuuming the toast table when we
>decide to vacuum the main table.
>

Perhaps by default issuing a vacuum against a table will also vacuum
it's associated toast tables (can there be more than one toast table?)
but add an option to the vacuum command not to vacuum the toast table.
Or, perhaps this shouldn't be made totally public, ie. proving sql
commands for it, perhaps it should only be available internally, that is
to autovacuum and other tools that might issue a vacuum at the same low
level as autovacuum.

Matt