handling TOAST tables in autovacuum

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: handling TOAST tables in autovacuum
Date: 2008-06-08 23:03:48
Message-ID: 20080608230348.GD11028@alvh.no-ip.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

We've been making noises about dealing with TOAST tables as separate
entities in autovacuum for some time now. So here's a proposal:

Let's do it.

That's about it :-)

The only change of some consideration is that we will need two passes
over pg_class to get the list of relations to vacuum, instead of one as
we do currently. The problem is that we first need to fetch the
(heap relid, toast relid) mapping before attempting to figure out if any
given TOAST table needs vacuuming. This is because we want to be using
the main table's pg_autovacuum, and we can't get at that unless we know
the main relid.

Another open question is whether the TOAST table should be processed at
all if the main table is vacuumed. My opinion is we don't -- if we're
going to deal with them separately, let's go the whole nine yards.
Autovacuum will only process a toast table when, by itself, it shows
that it needs processing. (Obviously this doesn't mean we change
semantics of user-invoked VACUUM -- those will continue to vacuum the
TOAST table along the main table).

Should we display TOAST tables separately in pg_stat_*_tables? (Maybe
pg_stat_toast_tables?)

Thoughts?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Gregory Stark 2008-06-08 23:03:52 Re: math error or rounding problem Money type
Previous Message Tom Lane 2008-06-08 23:03:13 Re: Proposal - improve eqsel estimates by including histogram bucket numdistinct statistics