Re: Index space growing even after cleanup via autovacuum in Postgres 9.2

Tirthankar Barari <tbarari@verizon.com> writes:
> However, we are noticing that after autovacuum, our disk space
> consumption is still increasing and the increase is in the index size
> (by querying pg_total_relation_size("mytable") and
> pg_indexes_size("mytable")).

> In Postgres 9.2.2, doesn't autovacuum cleanup dead indexes and reuse
> that space too? So, why do we see this not happening?

You've not provided enough info to speak in more than generalities, but:
whether index space is reclaimable depends a whole lot on your usage
patterns.  There's provision to recycle pages that've become completely
empty, but as long as there's at least one key left on a leaf page,
it won't be recycled.  So for instance if you have a time-ordered index
and you delete all but one out of every hundred entries in time sequence,
you're not going to get that space back short of a REINDEX.

            regards, tom lane