Shaun Thomas <sthomas@xxxxxxxxx> wrote: > Venkat Balaji wrote: >> I see lot of free spaces or free pages in Tables and Indexes. >> But, I need to give an exact calculation on how much space will >> be reclaimed after VACUUM FULL and RE-INDEXING. > > Why? I've been wondering that, too. And talking about the space being "reclaimed" seems to be at odds with your subject line. The space is given up by the database engine to the file system free space, where reuse by the database will be much more expensive. For good performance you want some free space in the tables and indexes, where it can be allocated to new tuples without going out through OS calls to the file system. Clearly, if free space gets higher than necessary to support creation of new tuples, it can start to harm performance, and you may need to take aggressive action (such as CLUSTER) to reclaim it; but any time you find it necessary to do *that* you should be investigating what went wrong to put you in such a spot. Either your autovacuum is (as Shaun suggested) not aggressive enough, or you have some long running transaction (possibly "idle in transaction") which is preventing vacuums from doing their work effectively. Investigating that is going to help more than calculating just how much space the database is going to give up to file system free space. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
