Per documentation
(http://www.postgresql.org/docs/8.4/static/routine-reindex.html):
In PostgreSQL 7.4 and later, index pages that have become completely
empty are reclaimed for re-use. There is still a possibility for
inefficient use of space: if all but a few index keys on a page have
been deleted, the page remains allocated. So a usage pattern in which
all but a few keys in each range are eventually deleted will see poor
use of space. For such usage patterns, periodic reindexing is recommended.
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.
>select relname, indexrelname, pg_relation_size(indexrelid) from
pg_stat_user_indexes where relname = 't';
"t";"t_partial_idx";58064896
>select count(1) from t where t_summarized=false;
34
Even if the 34 rows were each in different pages with a deep index
structure, the index size is not justified, if I understand the
documentation correctly. I'd guess that 'completely empty' refers to
whether the row exists, rather than whether the row belongs in the index.
Any ideas on this pattern of index bloat other than reindex/rebuilding
periodically? Has this been addressed in a newer release?
-Kevin