From: Kenneth Marshall <ktm@rice.edu>
To: Shridhar Polas <shridharpolas@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Space occupied by Postgres index.
Date: Tue, 30 Nov 2010 12:41:03 -0600
Message-ID: <20101130184103.GM19162@aart.is.rice.edu> (raw)
In-Reply-To: <00c301cb90b5$f54b6370$dfe22a50$@gmail.com>
References: <00b901cb90b3$ca9ddc40$5fd994c0$@gmail.com>
<20101130172841.GK19162@aart.is.rice.edu>
<00c301cb90b5$f54b6370$dfe22a50$@gmail.com>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: ktm@rice.edu, shridharpolas@gmail.com
Subject: Re: Space occupied by Postgres index.
In-Reply-To: <20101130184103.GM19162@aart.is.rice.edu>
References: <00b901cb90b3$ca9ddc40$5fd994c0$@gmail.com> <20101130172841.GK19162@aart.is.rice.edu> <00c301cb90b5$f54b6370$dfe22a50$@gmail.com> <20101130184103.GM19162@aart.is.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox