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

Re: pg_autovacuum: short, wide tables

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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 19:38:23
Message-ID: 20050708193823.GG1915@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-bugs
On Fri, Jul 08, 2005 at 02:35:14PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> > On Fri, Jul 08, 2005 at 01:29:03PM -0400, Tom Lane wrote:
> >> The main bit of additional logic that might be needed is an awareness
> >> that firing a VACUUM on a main table will implicitly fire one on its
> >> toast table, and so you'd not want to go and issue the toast table
> >> VACUUM separately.
> 
> > I don't see any reason why this wouldn't work.  I even think it'd be
> > very easy to implement (from the autovacuum POV -- not sure about the
> > stat system).  Furthermore, the awareness you mention is also very easy
> > to implement: we just need to make sure the pg_autovacuum tuple for the
> > toast table is updated when it is vacuumed, which is just an additional
> > function call.
> 
> I'm having some second thoughts about allowing VACUUM on a toast table
> independently of its parent table --- it's a bit scary to be messing
> with the toast table when we have no lock at all on the parent.  It
> might work OK, but I'm not sure I want to take the risk.  If we simply
> expose toast tables in the stats views, what has to be done to
> autovacuum to get it to work properly?  ("Properly" in this case would
> mean "fire a VACUUM on the parent table if either it or its toast table
> look like they need vacuumed".)  Is this much worse than what you
> say above?

No, just a little bit more logic, I think.  It requires storing info
about toast tables in pg_autovacuum, but I don't see a problem with
that.  Currently there's a heap scan of pg_autovacuum, on which we would
need to skip toast tables and consider them only in conjunction with
their respective main table.

Another issue altogether is the stat system.  I don't know if it stores
info about toast tables.  Note that it doesn't matter that it doesn't
show up in the pg_stat views, because we don't use those; rather we
access the hash tables directly.

> > Have you looked at the autovacuum integration patch?
> 
> No, but will do.

Cool.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Nunca se desea ardientemente lo que solo se desea por razón" (F. Alexandre)

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-07-08 20:02:36
Subject: Re: pg_autovacuum: short, wide tables
Previous:From: Matthew T. O'ConnorDate: 2005-07-08 18:54:57
Subject: Re: pg_autovacuum: short, wide tables

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