From: Shaun Thomas <sthomas@peak6.com>
To: Venkat Balaji <venkat.balaji@verse.in>
Cc: PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: : Performance Improvement Strategy
Date: Wed, 21 Sep 2011 08:03:13 -0500
Message-ID: <4E79E091.2030401@peak6.com> (raw)
In-Reply-To: <CAFrxt0j5UysgEU8hQxZ_mGbdex8Ev-bGdGsdpOGYYSO-jO24Fw@mail.gmail.com>
References: <CAFrxt0j5UysgEU8hQxZ_mGbdex8Ev-bGdGsdpOGYYSO-jO24Fw@mail.gmail.com>
On 09/20/2011 11:22 AM, Venkat Balaji wrote:
> Please help me understand how to calculate free space in Tables and
> Indexes even after vacuuming and analyzing is performed.
Besides the query Mark gave you using freespacemap, there's also the
pgstattuple contrib module. You'd use it like this:
SELECT pg_size_pretty(free_space) AS mb_free
FROM pgstattuple('some_table');
Query must be run as a super-user, and I wouldn't recommend running it
on huge tables, since it scans the actual data files to get its
information. There's a lot of other useful information in that function,
such as the number of dead rows.
> What i understand is that, even if we perform VACUUM ANALYZE
> regularly, the free space generated is not filled up.
VACUUM does not actually generate free space. It locates and marks
reusable tuples. Any future updates or inserts on that table will be put
in those newly reclaimed spots, instead of being bolted onto the end of
the table.
> 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? If your database is so desperate for space, VACUUM and REINDEX
won't really help you. A properly maintained database will still have a
certain amount of "bloat" equal to the number of rows that change
between maintenance intervals. One way or another, that space is going
to be used by *something*.
It sounds more like you need to tweak your autovacuum settings to be
more aggressive if you're seeing significant enough turnover that your
tables are bloating significantly. One of our tables, for instance, gets
vacuumed more than once per hour because it experiences 1,000% turnover
daily.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email-disclaimer/ for terms and conditions related to this email
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@peak6.com, venkat.balaji@verse.in
Subject: Re: : Performance Improvement Strategy
In-Reply-To: <4E79E091.2030401@peak6.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox