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@xxxxxxxx] > Sent: Tuesday, November 30, 2010 10:59 PM > To: Shridhar Polas > Cc: pgsql-admin@xxxxxxxxxxxxxx > Subject: Re: 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 > > -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
