Re: Autovacuum and toast tables

Gregory Stark wrote:

> When autovacuum vacuums a table it always vacuums the toast table as well. Is
> there any particular reason to consider the two together? I think this may
> just be a holdover from the interactive command which considers them together
> because the toast table is supposed to be invisible to users.
>
> But autovacuum could look at the stats for the toast tables and make decisions
> about them independently from the main tables, no?
>
> The reason I ask is because looking in the logs from benchmarks I see lots of
> useless vacuuming of empty toast tables which have no dead tuples. Now in this
> case it doesn't cost anything because they're empty. But I could easily see
> situations where the toast tables could be quite large but not be receiving
> any updates when the main table is receiving a large volume of updates on
> other columns.

I think there is some special code in tqual.c that skips some checks
assuming that the toast table is vacuumed at the same time as the main
heap. I am not sure how vital is that. Maybe it can be fixed, or maybe
it is not a problem at all.

We go certain lengths in autovacuum to make sure tables are vacuumed
when their toast table needs vacuuming and the main table does not,
which is all quite kludgy. So we already look at their stats and make
decisions about them. But what we do after that is force a vacuum to
the main table, even if that one does not need any vacuuming, which is
dumb.

We could certainly fix that, mainly pending analysis of the above
problem.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support