Re: Disk Utilization Increases And Time for Vacuum Increases.

I have looked at the code and it seems VACUUM FULL does collect index
free space into the FSM via index_vacuum_cleanup().  Is that true?

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

Bruce Momjian wrote:
> Bruce Momjian wrote:
> > > > AFAIK, VACUUM doesn't reclaim space taken up by indexes. In fact, the more
> > > > deletes you do, the larger the index space gets, and the *slower* the index
> > > > performs. A periodic REINDEX cleans up a lot of problems.
> > >
> > > Note that in 7.4 the fix for this is in, so if you have a chance to test
> > > it out with your indexes and their growth problem please test it to see if
> > > it works right.
> > >
> > > I haven't tested 7.4 beta1 yet very hard, just on my workstation, with
> > > relatively low level stuff.
> >
> > I am not sure we have completely dealt with index growth in 7.4.  What
> > we have new in 7.4 is the ability for non-FULL VACUUM to collect info on
> > free index pages and reuse them.
> >
> > However, VACUUM FULL does not shrink the index table unless those pages
> > are the last pages of the file.  (Could it shift free pages to the end
> > and then truncate index?)  Also, does VACUUM FULL on an index put the
> > empty index pages in the FSM map?  It doesn't do that for heap pages
> > because there are none after vacuum, but there might be free index pages
> > that we should record.
>
> I haven't seen anyone comment on this.  I think we need to address this
> for 7.4.  (Collecting index free space in FSM is new in 7.4.)  I am
> concerned that people who are only running VACUUM FULL at night will
> have problems with index growth.  Also, why can't we move totally-empty
> index pages to the end and truncate the file?
>
> > Interesting I found a reference of doing an auto-reindex as part of
> > VACUUM FULL:
> >
> >     #ifdef NOT_USED
> >
> >         /*
> >          * reindex in VACUUM is dangerous under WAL. ifdef out until it
> >          * becomes safe.
> >          */
> >         if (reindex)
> >         {
> >             vac_close_indexes(nindexes, Irel);
> >             Irel = (Relation *) NULL;
> >             activate_indexes_of_a_table(onerel, false);
> >         }
> >     #endif   /* NOT_USED */
>
> Will we ever reindex as part of VACUUM FULL?
>
> --
>   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, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--
  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, Pennsylvania 19073