I will be working on our existing "auto-vacuuming" strategy to see if that's optimal. But, we do have VACUUM VERBOSE ANALYZE running at the cluster level every day and auto-vacuum is aggressive for highly active tables.
Today, we have vacuumed a 10GB table and the table size decreased to 5 GB.
I understand that, it would very expensive for the table to reclaim the space back from the filesystem. We have decided to do the maintenance after a thorough analysis and our databases were not subjected to any kind of maintenance activity since 2 yrs (with downtime).
I as a DBA, suggested to perform VACUUM FULL and RE-INDEXING + ANALYZE to ensure that IO performance and Indexing performance would be good and the PG optimizer would pick up the optimal plan. As said earlier, our databases have never been part of any re-organization since 2 years and are highly transactional databases. I believe that, performing VACUUM FULL and RE-INDEXING would have tightly packed rows (in every page) would ensure good IOs.
I might have not put across the explanation in an understandable manner.
Please help me know the following -
1. When would pg_stat_user_tables will be updated and what would the information show ?
2. Will the information about dead-rows and live-rows vanish after VACUUM or ANALYZE or VACUUM FULL ?
I am just preparing a monitoring system which would help us know the rate of bloats and data generation on daily basis.
Sorry for the long email !
Looking forward for your help !
Thanks
Venkat
On Wed, Sep 21, 2011 at 7:27 PM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote:
Shaun Thomas <sthomas@xxxxxxxxx> wrote:
> Venkat Balaji wrote:I've been wondering that, too. And talking about the space being
>> 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?
"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
