Re: : Performance Improvement Strategy

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Venkat Balaji" <venkat(dot)balaji(at)verse(dot)in>
Cc: <sthomas(at)peak6(dot)com>, "PGSQL Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: : Performance Improvement Strategy
Date: 2011-09-21 13:57:35
Message-ID: 4E79A6FF0200002500041506@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2011-09-21 14:12:52 Re: REINDEX not working for wastedspace
Previous Message Marcos Luis Ortiz Valmaseda 2011-09-21 13:52:29 Re: [pgsql-es-ayuda] parámetros de postgres y linux en maquinas virtuales