From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Ricords <kevin@silverback.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: index bloat on partial index 8.4
Date: Mon, 03 Oct 2011 23:53:11 -0400
Message-ID: <27810.1317700391@sss.pgh.pa.us> (raw)
In-Reply-To: <4E89FB03.8010407@silverback.com>
References: <4E89FB03.8010407@silverback.com>
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
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, kevin@silverback.com
Subject: Re: index bloat on partial index 8.4
In-Reply-To: <27810.1317700391@sss.pgh.pa.us>
* 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