Re: Thoughts on maintaining 7.3

Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> > I think what Tom is concerned about is that this hasn't been tested
> > enough with big datasets.  Also there a little loss of index pages but
> > it's much less (orders of magnitude, I think) than what was before.
> > This is because the index won't shrink "vertically".
> 
> The fact that we won't remove levels shouldn't be meaningful at all ---
> I mean, if the index was once big enough to require a dozen btree
> levels, and you delete everything, are you going to be upset that it
> drops to 13 pages rather than 2?  I doubt it.
> 
> The reason I'm waffling about whether the problem is completely fixed or
> not is that the existing code will only remove-and-recycle completely
> empty btree pages.  As long as you have one key left on a page it will
> stay there.  So you could end up with ridiculously low percentage-filled
> situations.  This could be fixed by collapsing together adjacent
> more-than-half-empty pages, but we ran into a lot of problems trying to
> do that in a concurrent fashion.  So I'm waiting to find out if real
> usage patterns have a significant issue with this or not.

Though the new code will put empty index pages into the free-space map,
will it also shrink the index file to remove those pages?  For example,
if I have 200M rows in a table, and I delete all of them except 100,
does the index shrink, or the pages just become available for reuse. 
With VACUUM FULL, we have a way to shrink the heap.  Do we shrink the
index?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073