Re: Subject: bool / vacuum full bug followup part 2

On Sat, May 04, 2002 at 10:48:47AM -0700, Jeffrey Baker wrote:
> On Fri, May 03, 2002 at 03:47:54PM -0400, Tom Lane wrote:
> > Scott Marlowe <scott(dot)marlowe(at)ihs(dot)com> writes:
> > > And reclaimed the space. Is that the official way, short of dropping and
> > > recreating an index to reclaim its space? Is there a plan to make vacuum
> > > reclaim unused space in indexes?
> >
> > Yes, and yes, but don't hold your breath on the latter part --- that
> > TODO item has been around for awhile. And it's gotten harder now that
> > we have lazy VACUUM; that means we need to be able to condense indexes
> > concurrently with other index operations.
> >
> > AFAIK there's not a big problem with index growth if the range of index
> > keys remains reasonably static. The problem comes in if you have a
> > range of values that keeps growing (eg, you are indexing a SERIAL or
> > timestamp column). The right end of the btree keeps growing, but
> > there's no mechanism to collapse out no-longer-used space at the left
> > end.
>
> Wouldn't that explain the complaints I have about my toast tables
> always growing? Because each toast table has an index, and the
> above paragraph makes it sound like indexes on serial values grow
> all the time, that would imply that table that where tuples live for
> windows of time will always be growing.
>
> Or did I read that incorrectly?

Indeed, I did not. Part of the space leak I am seeing is from this:

Start.
Insert 20,000 tuples of 13KB each.
Delete 20,000 tuples.
Vacuum full.
Goto Start.

Toast index grows by ~535 pages or 4.2MB[1] per cycle, even though
vacuum is able to truncate the main relation and the toast relation
to zero pages.

-jwb

1: This implies a page size of 16KB in the index. I expected it to
be smaller.