Skip site navigation (1) Skip section navigation (2)

Re: pg_autovacuum: short, wide tables

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Cc: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>,mark reid <mail(at)markreid(dot)org>, pgsql-bugs(at)postgresql(dot)org,Jan Wieck <JanWieck(at)Yahoo(dot)com>
Subject: Re: pg_autovacuum: short, wide tables
Date: 2005-07-08 17:11:35
Message-ID: 24215.1120842695@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
>> Tom Lane wrote:
>>> The first of these would be easiest but it seems quite likely to break
>>> applications (eg, if unmodified, autovacuum would probably try to issue
>>> vacuums against toast tables).  And the last seems to be confusing.
>>> So I think I favor adding columns.

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

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

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Mark ReidDate: 2005-07-08 17:19:49
Subject: Re: pg_autovacuum: short, wide tables
Previous:From: Tom LaneDate: 2005-07-08 17:00:32
Subject: Re: pg_autovacuum: short, wide tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group