Re: index bloat on partial index 8.4

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Kevin Ricords <kevin(at)silverback(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: index bloat on partial index 8.4
Date: 2011-10-04 03:53:11
Message-ID: 27810.1317700391@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Kevin Ricords <kevin(at)silverback(dot)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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Ricords 2011-10-04 18:28:00 Re: index bloat on partial index 8.4
Previous Message Tom Lane 2011-10-03 23:10:45 Re: diagnosing a db crash - server exit code 2