From shridharpolas@gmail.com Tue Nov  30 17:27:02 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 5F0E11337B5D
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 30 Nov 2010 13:27:02 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 53158-01 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 30 Nov 2010 17:26:55 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yw0-f46.google.com (mail-yw0-f46.google.com
	[209.85.213.46])
	by mail.postgresql.org (Postfix) with ESMTP id DF7401335B36
	for <pgsql-admin@postgresql.org>; Tue, 30 Nov 2010 13:26:54 -0400 (AST)
Received: by ywl5 with SMTP id 5so2855215ywl.19
	for <pgsql-admin@postgresql.org>; Tue, 30 Nov 2010 09:26:54 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:from:to:subject:date
	:message-id:mime-version:content-type:x-mailer:thread-index
	:content-language;
	bh=meeBH1hgE6UBhY86kXg3j/sS91tyLCrX73l9WU5fnRo=;
	b=F2IhzE9OyvQbeU4WIQ4x1SJJ0rKS4TMKTd1C1rVluzNaKMcMg4Soc+JwsW4dxlsrv5
	O3Mg/8as5XzBuoSwBb+iA/SDVjhtUz271JO015tlqTBTyhjkl1XJqA764fjbjboQOxPj
	zkgypaigMYcCzWG79WcXcyO6fievBhVEeuGr4=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:to:subject:date:message-id:mime-version:content-type:x-mailer
	:thread-index:content-language;
	b=jeQ1Kpj0/9VDfebTgvT2AQve1PkJBkUs72gdLnQN//v3T6wXyVak7F9T9rRaDy5Jl9
	afGppSAKTeXqbq5CMZmo+ScCxe9Ru+M23quKLBQDtDeygQYII5Ybx6vgkc0i9KOJA7ux
	F1/s5PZB25CJ59ZplkGe40AMuGzCQkz5E80RE=
Received: by 10.204.119.84 with SMTP id y20mr1123504bkq.56.1291138013842;
	Tue, 30 Nov 2010 09:26:53 -0800 (PST)
Received: from 345bf1f2bc8c491 ([114.143.126.120])
	by mx.google.com with ESMTPS id a24sm588445fak.1.2010.11.30.09.26.50
	(version=SSLv3 cipher=RC4-MD5); Tue, 30 Nov 2010 09:26:52 -0800 (PST)
From: "Shridhar Polas" <shridharpolas@gmail.com>
To: <pgsql-admin@postgresql.org>
Subject: Space occupied by Postgres index.
Date: Tue, 30 Nov 2010 22:56:53 +0530
Message-ID: <00b901cb90b3$ca9ddc40$5fd994c0$@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00BA_01CB90E1.E4561840"
X-Mailer: Microsoft Outlook 14.0
Thread-Index: AcuQsw+yg9h1Em6bSyaC2ApcpXViHA==
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201011/257
X-Sequence-Number: 36223

This is a multipart message in MIME format.

------=_NextPart_000_00BA_01CB90E1.E4561840
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit

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


------=_NextPart_000_00BA_01CB90E1.E4561840
Content-Type: text/html;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><META =
HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii"><meta name=3DGenerator content=3D"Microsoft Word 14 =
(filtered medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri","sans-serif";}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DEN-US link=3Dblue =
vlink=3Dpurple><div class=3DWordSection1><p =
class=3DMsoNormal>Hi,<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>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.<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>When I ran =
full vacuum the disk space was reclaimed occupied by tables but not by =
disk space occupied by indexes.<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>Can somebody =
please tell me when disk space occupied by Postgres index is reclaimed, =
without performing re-indexing on those tables?<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>Thanks,<o:p></o:p></p><p =
class=3DMsoNormal>Shridhar<o:p></o:p></p></div></body></html>
------=_NextPart_000_00BA_01CB90E1.E4561840--




From ktm@rice.edu Tue Nov  30 17:28:50 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 72C831337B4F
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 30 Nov 2010 13:28:50 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 69209-03 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 30 Nov 2010 17:28:43 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mh3.mail.rice.edu (mh3.mail.rice.edu [128.42.199.10])
	by mail.postgresql.org (Postfix) with ESMTP id 3D643133655C
	for <pgsql-admin@postgresql.org>; Tue, 30 Nov 2010 13:28:43 -0400 (AST)
Received: from mh3.mail.rice.edu (localhost.localdomain [127.0.0.1])
	by mh3.mail.rice.edu (Postfix) with ESMTP id AEF5128F6CD;
	Tue, 30 Nov 2010 11:28:42 -0600 (CST)
X-Virus-Scanned: by amavis-2.6.4 at mh3.mail.rice.edu, auth channel
Received: from mh3.mail.rice.edu ([127.0.0.1])
	by mh3.mail.rice.edu (mh3.mail.rice.edu [127.0.0.1]) (amavis,
	port 10026)
	with ESMTP id m3kQ5CI8wTfM; Tue, 30 Nov 2010 11:28:42 -0600 (CST)
X-SMTP-Auth: no
X-SMTP-Auth: no
Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48])
	by mh3.mail.rice.edu (Postfix) with ESMTP id 5D43928F701;
	Tue, 30 Nov 2010 11:28:42 -0600 (CST)
Received: by aart.is.rice.edu (Postfix, from userid 18612)
	id 199CA2D5A6; Tue, 30 Nov 2010 11:28:42 -0600 (CST)
Date: Tue, 30 Nov 2010 11:28:41 -0600
From: Kenneth Marshall <ktm@rice.edu>
To: Shridhar Polas <shridharpolas@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Space occupied by Postgres index.
Message-ID: <20101130172841.GK19162@aart.is.rice.edu>
References: <00b901cb90b3$ca9ddc40$5fd994c0$@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <00b901cb90b3$ca9ddc40$5fd994c0$@gmail.com>
User-Agent: Mutt/1.5.16 (2007-06-09)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201011/258
X-Sequence-Number: 36224

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



From shridharpolas@gmail.com Tue Nov  30 17:42:32 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 66267133655C
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 30 Nov 2010 13:42:32 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 74012-07 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 30 Nov 2010 17:42:25 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com
	[209.85.213.174])
	by mail.postgresql.org (Postfix) with ESMTP id 25A301335B36
	for <pgsql-admin@postgresql.org>; Tue, 30 Nov 2010 13:42:24 -0400 (AST)
Received: by yxt3 with SMTP id 3so2358856yxt.19
	for <pgsql-admin@postgresql.org>; Tue, 30 Nov 2010 09:42:24 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:from:to:cc:references
	:in-reply-to:subject:date:message-id:mime-version:content-type
	:content-transfer-encoding:x-mailer:thread-index:content-language;
	bh=79sJtzgM2Y4Nk0ksRUfp65nnFTupv+MBg/sKTfQNlpI=;
	b=V3FFMklYyse2JbYGoR3i3wwv7NZC8QZU1PF7Kigc/S7zbhLgmu1vulCjMgfnltQLq6
	Npv7CLPXs2KLHf3YVqpAuaxivKP381zVW5CRrcv2pH49a+/fgIJXF8MW9/Ae9Jxq4TeX
	gHA7M+EGqaIOZ6Alxfx2/I7e/wHiveuyEZtK4=
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=
Received: by 10.223.83.141 with SMTP id f13mr7142558fal.17.1291138944054;
	Tue, 30 Nov 2010 09:42:24 -0800 (PST)
Received: from 345bf1f2bc8c491 ([114.143.126.120])
	by mx.google.com with ESMTPS id 5sm655085fak.23.2010.11.30.09.42.20
	(version=SSLv3 cipher=RC4-MD5); Tue, 30 Nov 2010 09:42:23 -0800 (PST)
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
X-Mailer: Microsoft Outlook 14.0
Thread-Index: AQJiMbSbzqbJdI/95BjGL5d7ZCvbMwK4EJMTkkdFDjA=
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201011/260
X-Sequence-Number: 36226

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




From ktm@rice.edu Tue Nov  30 18:41:13 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 7A2E4133655C
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 30 Nov 2010 14:41:13 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 65783-02 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 30 Nov 2010 18:41:06 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mh5.mail.rice.edu (mh5.mail.rice.edu [128.42.199.32])
	by mail.postgresql.org (Postfix) with ESMTP id 331471335B36
	for <pgsql-admin@postgresql.org>; Tue, 30 Nov 2010 14:41:06 -0400 (AST)
Received: from mh5.mail.rice.edu (localhost.localdomain [127.0.0.1])
	by mh5.mail.rice.edu (Postfix) with ESMTP id E49FF28F75D;
	Tue, 30 Nov 2010 12:41:05 -0600 (CST)
X-Virus-Scanned: by amavis-2.6.4 at mh5.mail.rice.edu, auth channel
Received: from mh5.mail.rice.edu ([127.0.0.1])
	by mh5.mail.rice.edu (mh5.mail.rice.edu [127.0.0.1]) (amavis,
	port 10026)
	with ESMTP id l8EaY3CYUOOE; Tue, 30 Nov 2010 12:41:05 -0600 (CST)
X-SMTP-Auth: no
X-SMTP-Auth: no
Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48])
	by mh5.mail.rice.edu (Postfix) with ESMTP id B573628F755;
	Tue, 30 Nov 2010 12:41:05 -0600 (CST)
Received: by aart.is.rice.edu (Postfix, from userid 18612)
	id D46F72D5A6; Tue, 30 Nov 2010 12:41:03 -0600 (CST)
Date: Tue, 30 Nov 2010 12:41:03 -0600
From: Kenneth Marshall <ktm@rice.edu>
To: Shridhar Polas <shridharpolas@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Space occupied by Postgres index.
Message-ID: <20101130184103.GM19162@aart.is.rice.edu>
References: <00b901cb90b3$ca9ddc40$5fd994c0$@gmail.com>
	<20101130172841.GK19162@aart.is.rice.edu>
	<00c301cb90b5$f54b6370$dfe22a50$@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <00c301cb90b5$f54b6370$dfe22a50$@gmail.com>
User-Agent: Mutt/1.5.16 (2007-06-09)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201011/261
X-Sequence-Number: 36227

A regular vacuum will free space reclaimed at the end of a table.
In normal use, your optimum space consumption is that needed to
hold the current table data plus the space needed to hold the
old deleted or modified data until a regular vacuum can make it
available for reuse. In addition, using HOT updates with table
fillfactor < 100, can greatly reduce table fragmentation and 
bloating.

Cheers,
Ken

On Tue, Nov 30, 2010 at 11:12:23PM +0530, Shridhar Polas wrote:
> 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
> 
> 



From Kevin.Grittner@wicourts.gov Tue Nov  30 18:54:00 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 5AD8F1337B8B
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 30 Nov 2010 14:54:00 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 49697-09 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 30 Nov 2010 18:53:53 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id D5A601337BB9
	for <pgsql-admin@postgresql.org>; Tue, 30 Nov 2010 14:53:52 -0400 (AST)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Tue, 30 Nov 2010 12:53:52 -0600
Message-Id: <4CF4F3D80200002500037F9E@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Tue, 30 Nov 2010 12:53:44 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Shridhar Polas" <shridharpolas@gmail.com>,
	"'Kenneth Marshall'" <ktm@rice.edu>
Cc: <pgsql-admin@postgresql.org>
Subject: Re: Space occupied by Postgres index.
References: <00b901cb90b3$ca9ddc40$5fd994c0$@gmail.com>
	<20101130172841.GK19162@aart.is.rice.edu>
	<00c301cb90b5$f54b6370$dfe22a50$@gmail.com>
In-Reply-To: <00c301cb90b5$f54b6370$dfe22a50$@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201011/262
X-Sequence-Number: 36228

"Shridhar Polas" <shridharpolas@gmail.com> wrote:
 
> 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.
 
True, but if you expect the tables to grow again it's generally
better to leave the space allocated to PostgreSQL than to make it
give it back up to the OS for the moment and have to go ask for it
again later.
 
If you don't expect PostgreSQL to need the space again and you want
to release it back to the OS, prior to release 9.0 it is usually
better to use CLUSTER than to use VACUUM FULL and REINDEX.
 
-Kevin