Обсуждение: Space occupied by Postgres index.
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
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
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
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 > >
"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