DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:to:cc:references:in-reply-to:subject:date:message-id
	:mime-version:content-type:content-transfer-encoding:x-mailer
	:thread-index:content-language;
	b=J22YcEPkbUTzCTVYHb68NwCxQYkvOOrSOKDjdAnv8b+jVKrkv7+CkI1+UD4cfprKHJ
	QCe8NjM26q1Rog9jIZQT1btgZtxpPmrn+mnixWUZVIWebf/C4/pxPrQoupO8wyr7+k1t
	+FOnavzqu1uUoIEOj/X+vZfRejAPaRhE50Iw8=
From: "Shridhar Polas" <shridharpolas@gmail.com>
To: "'Kenneth Marshall'" <ktm@rice.edu>
Cc: <pgsql-admin@postgresql.org>
References: <00b901cb90b3$ca9ddc40$5fd994c0$@gmail.com>
	<20101130172841.GK19162@aart.is.rice.edu>
In-Reply-To: <20101130172841.GK19162@aart.is.rice.edu>
Subject: Re: Space occupied by Postgres index.
Date: Tue, 30 Nov 2010 23:12:23 +0530
Message-ID: <00c301cb90b5$f54b6370$dfe22a50$@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Thread-Index: AQJiMbSbzqbJdI/95BjGL5d7ZCvbMwK4EJMTkkdFDjA=
Content-Language: en-us

Thanks for a quick response Ken.

One more query:-

If I do not perform VACUUM FULL and REINDEX, does Postgres reclaimed the
space automatically when number of records in tables reduce after touching
some limit? I mean the total disk space consumed by Postgres would ever be
decline at any point without performing VACUUM FULL and REINDEX?

In my test setup I found that the disk space consumed by Postgers is not
getting declined even after deleting records from tables, if I do not
perform VACUUM FULL and REINDEX.

Thanks again
Shridhar

-----Original Message-----
From: Kenneth Marshall [mailto:ktm@rice.edu] 
Sent: Tuesday, November 30, 2010 10:59 PM
To: Shridhar Polas
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Space occupied by Postgres index.

On Tue, Nov 30, 2010 at 10:56:53PM +0530, Shridhar Polas wrote:
> Hi,
> 
>  
> 
> I am facing a problem where indexes creates on some tables are 
> occupying huge space on disk and it seems to me that this space is not 
> getting reclaimed even when there are very few record in an associated
table.
> 
>  
> 
> When I ran full vacuum the disk space was reclaimed occupied by tables 
> but not by disk space occupied by indexes.
> 
>  
> 
> Can somebody please tell me when disk space occupied by Postgres index 
> is reclaimed, without performing re-indexing on those tables?
> 
>  
> 
> Thanks,
> 
> Shridhar
> 

VACUUM FULL will cause index bloat. You will need to REINDEX to recover the
space. Note, you should not really need to use VACUUM FULL in a normal
correctly configured system.

Cheers,
Ken