Shaun Thomas <sthomas(at)peak6(dot)com> 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