From: Kevin Ricords <kevin@silverback.com>
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
Date: Tue, 4 Oct 2011 14:28:00 -0400
Message-ID: <4E8B5030.7090902@silverback.com> (raw)
In-Reply-To: <27810.1317700391@sss.pgh.pa.us>
References: <4E89FB03.8010407@silverback.com>
<27810.1317700391@sss.pgh.pa.us>
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
> .
>
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: kevin@silverback.com, tgl@sss.pgh.pa.us
Subject: Re: index bloat on partial index 8.4
In-Reply-To: <4E8B5030.7090902@silverback.com>
* 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