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@xxxxxxxxxxxxxx> 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
.
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
