Re: index bloat on partial index 8.4

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