Re: Brain dump: btree collapsing - Mailing list pgsql-hackers

I think having VACUUM record free index pages just like free heap pages
makes perfect sense, and is consistent.

This brings up one item it would be nice to address at the same time. 
It would be nice if VACUUM FULL would be able to compress the actual
index file and return unused space to the operating system.  REINDEX
does this, but I was thinking of something a little lighter that could
be done automatically as part of VACUUM FULL.  If we can do that, it
would make consistent behavior for vacuum and heap/index files.

---------------------------------------------------------------------------

Tom Lane wrote:
> Justin Clift <justin@postgresql.org> writes:
> > Tom Lane wrote:
> >> The deletion procedure could be triggered immediately upon removal of the
> >> last item in a page, or when the next VACUUM scan finds an empty page.
> >> Not sure yet which way is better.
> 
> > Having it triggered immediately upon removal of the last item in a page 
> > would make for a more "self maintaining" system wouldn't it?  That 
> > sounds nice.  :)
> 
> Maybe.  This isn't about getting rid of VACUUM --- there's still a need
> for routine maintenance vacuums.  So the question really comes down to
> whether it's more efficient to do it "in bulk" during routine
> maintenance sweeps, or "retail".  I'm not sold yet, but am leaning to
> the bulk side.
> 
> >> In theory, if we find recyclable page(s) at the physical end of the index,
> >> we could truncate the file (ie, give the space back to the filesystem)
> >> instead of reporting these pages to FSM.  I am not sure if this is worth
> >> doing --- in most cases it's likely that little space can be released this
> >> way, and there may be some tricky locking issues.
> 
> > Sounds like this would be beneficial for environments with high 
> > update/delete transaction volumes, perhaps on smaller amounts of 
> > live/valid data.
> 
> It would only really be worth doing if you made a substantial reduction
> in the number of rows in a table, and had no near-term intention of
> loading the table back up again.  Seems like it might be sufficient to
> tell people to REINDEX if they want the index size to drop in that
> scenario.  I will look at physically truncating the index during VACUUM,
> but I don't think it's worth getting real tense about...
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

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