To: Kevin Ricords <kevin@silverback.com>
cc: pgsql-admin@postgresql.org
Subject: Re: index bloat on partial index 8.4 
In-reply-to: <4E89FB03.8010407@silverback.com> 
References: <4E89FB03.8010407@silverback.com>
Comments: In-reply-to Kevin Ricords <kevin@silverback.com>
	message dated "Mon, 03 Oct 2011 14:12:19 -0400"
Date: Mon, 03 Oct 2011 23:53:11 -0400
Message-ID: <27810.1317700391@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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