Skip site navigation (1) Skip section navigation (2)

Re: Disk Utilization Increases And Time for Vacuum Increases.

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>,Jeff Boes <jboes(at)qtm(dot)net>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Disk Utilization Increases And Time for Vacuum Increases.
Date: 2003-08-30 01:07:19
Message-ID: 200308300107.h7U17JO05341@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-admin by date

Next:From: alexander v pDate: 2003-08-30 02:34:24
Subject: Re: eRserver
Previous:From: Tom LaneDate: 2003-08-29 21:25:04
Subject: Re: Define function with no return value?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group