pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSpace occupied by Postgres index.
5+ messages / 3 participants
[nested] [flat]
* Space occupied by Postgres index.
@ 2010-11-30 17:26 Shridhar Polas <shridharpolas@gmail.com>
2010-11-30 17:28 ` Re: Space occupied by Postgres index. Kenneth Marshall <ktm@rice.edu>
0 siblings, 1 reply; 5+ messages in thread
From: Shridhar Polas @ 2010-11-30 17:26 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Space occupied by Postgres index.
2010-11-30 17:26 Space occupied by Postgres index. Shridhar Polas <shridharpolas@gmail.com>
@ 2010-11-30 17:28 ` Kenneth Marshall <ktm@rice.edu>
2010-11-30 17:42 ` Re: Space occupied by Postgres index. Shridhar Polas <shridharpolas@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kenneth Marshall @ 2010-11-30 17:28 UTC (permalink / raw)
To: Shridhar Polas <shridharpolas@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Space occupied by Postgres index.
2010-11-30 17:26 Space occupied by Postgres index. Shridhar Polas <shridharpolas@gmail.com>
2010-11-30 17:28 ` Re: Space occupied by Postgres index. Kenneth Marshall <ktm@rice.edu>
@ 2010-11-30 17:42 ` Shridhar Polas <shridharpolas@gmail.com>
2010-11-30 18:41 ` Re: Space occupied by Postgres index. Kenneth Marshall <ktm@rice.edu>
2010-11-30 18:53 ` Re: Space occupied by Postgres index. Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 5+ messages in thread
From: Shridhar Polas @ 2010-11-30 17:42 UTC (permalink / raw)
To: 'Kenneth Marshall' <ktm@rice.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Space occupied by Postgres index.
2010-11-30 17:26 Space occupied by Postgres index. Shridhar Polas <shridharpolas@gmail.com>
2010-11-30 17:28 ` Re: Space occupied by Postgres index. Kenneth Marshall <ktm@rice.edu>
2010-11-30 17:42 ` Re: Space occupied by Postgres index. Shridhar Polas <shridharpolas@gmail.com>
@ 2010-11-30 18:41 ` Kenneth Marshall <ktm@rice.edu>
1 sibling, 0 replies; 5+ messages in thread
From: Kenneth Marshall @ 2010-11-30 18:41 UTC (permalink / raw)
To: Shridhar Polas <shridharpolas@gmail.com>; +Cc: pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Space occupied by Postgres index.
2010-11-30 17:26 Space occupied by Postgres index. Shridhar Polas <shridharpolas@gmail.com>
2010-11-30 17:28 ` Re: Space occupied by Postgres index. Kenneth Marshall <ktm@rice.edu>
2010-11-30 17:42 ` Re: Space occupied by Postgres index. Shridhar Polas <shridharpolas@gmail.com>
@ 2010-11-30 18:53 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2010-11-30 18:53 UTC (permalink / raw)
To: Shridhar Polas <shridharpolas@gmail.com>; 'Kenneth Marshall' <ktm@rice.edu>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-11-30 18:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-30 17:26 Space occupied by Postgres index. Shridhar Polas <shridharpolas@gmail.com>
2010-11-30 17:28 ` Kenneth Marshall <ktm@rice.edu>
2010-11-30 17:42 ` Shridhar Polas <shridharpolas@gmail.com>
2010-11-30 18:41 ` Kenneth Marshall <ktm@rice.edu>
2010-11-30 18:53 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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