Message-ID: <4E8B5030.7090902@silverback.com>
Date: Tue, 4 Oct 2011 14:28:00 -0400
From: Kevin Ricords <kevin@silverback.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: index bloat on partial index 8.4
References: <4E89FB03.8010407@silverback.com> <27810.1317700391@sss.pgh.pa.us>
In-Reply-To: <27810.1317700391@sss.pgh.pa.us>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit

Thank you for your response.  I believe I misunderstood "reclaimed for 
re-use".  Using contrib/pg_freespace, I see empty index pages can be 
reused elsewhere in the same index, but are not deallocated.  To keep my 
index at the size I expected, I will have to vacuum more frequently or 
reindex/rebuild regularly.

-Kevin

On 10/3/2011 11:53 PM, Tom Lane wrote:
> Kevin Ricords<kevin@silverback.com>  writes:
>> My implementation is a 150 million row table with a partial index on
>> newly created rows, where every row will be updated to not match the
>> index condition every few minutes.
>> The index size appears to grow proportional to the number of rows added
>> to the table, but doesn't shrink when rows are updated to no longer meet
>> the partial index condition.
> Well, a btree index is basically never going to shrink, short of a
> rebuild (REINDEX).  The right administrative goal is to prevent it from
> growing.  The key issues you need to deal with are (1) making sure it
> gets vacuumed often enough; (2) making sure there are not long-lived
> transactions that prevent VACUUM from removing recently-dead tuples.
> You've not really provided enough data for anyone to guess whether the
> problem is (1) or (2) or both.  What's the vacuuming configuration on
> your installation?  Have you checked for applications failing to close
> their transactions?
>
> 			regards, tom lane
> .
>