Re: GLOBAL vs LOCAL temp tables

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am asking more from a theoretical perspective --- can we say VACUUM
> regularly or VACUUM FULL are the same in terms of index recovery, or at
> least as similar as FULL/non-FULL are?

See the comments in nbtree.c's btvacuumcleanup().  FULL is able to
recycle empty pages faster than non-FULL, since it knows there can be no
other transactions with open indexscans.  So a freshly emptied index
page can be added to the FSM freelist immediately, whereas in the
non-FULL case it will need to wait till the next VACUUM (possibly even
longer if you have long-running transactions).  Also, VACUUM FULL will
truncate off any free pages at the end of the index, though I doubt this
is very effective since it won't move data across pages.

As I commented to Alvaro, I don't really see a need for an intermediate
level of cleanup between what VACUUM FULL does now and REINDEX.  Moving
data in an index is slow, would certainly require exclusive lock, and
helps to degrade the physical ordering of the index.  REINDEX gives you
a nice new freshly-sorted index and would probably be what you'd want
if you were going to lock down the index for a long period anyway.

> I don't remember the btree index
> compaction fix in CVS --- I just remember the recording of index free
> space by VACUUM --- did I forget something?

It's in there.
        regards, tom lane