Re: : Performance Improvement Strategy

Thank you very much for your detailed explanation !

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(dot)Grittner(at)wicourts(dot)gov
> wrote:

> 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
>