Re: pg_autovacuum: short, wide tables

On Fri, Jul 08, 2005 at 02:35:14PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.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)