Thread: Autovacuum and toast tables

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.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com

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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> 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 don't believe there is any such connection.  We do use a special
snapshot for toast tables, but it's only needed to make sure VACUUM FULL
on a toast table will work (ie, we have to respect MOVED_IN/MOVED_OFF).

> 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.

Yeah --- getting rid of that mess would be a plus.
        regards, tom lane