RE: Index grows huge, possible leakage?

> After several weeks our idicies grow very large (in one case to
> 4-5 gigabytes) After droppping and recreating the indecies they
> shrink back to something more reasonable (500megs same case).
>
> We are currently using Vadim's vacuum patches for VLAZY and MMNB,
> against 7.0.3. We are using a LAZY vacuum on these tables
>
> However a normal (non-lazy) vacuum doesn't shrink the index, the
> only thing that helps reduce the size is dropping and recreating.
>
> Is this a bug in 7.0.3? A possible bug in Vadim's patches? Or is
> this somewhat expected behavior that we have to cope with?

When index is created its pages are filled in full => any insert
into such pages results in page split - ie in additional page.
So, it's very easy to get 4Gb from 500Mb.

Vacuum was never able to shrink indices - it just removes dead index
tuples and so allows to re-use space ... if you'll insert the same
keys.

To know does VLAZY work properly or not I would need in vacuum debug
messages. Did you run vacuum with verbose option or do you have
postmaster' logs? With LAZY vacuum writes messages like

Index _name_: deleted XXX unfound YYY

YYY supposed to be 0...

Vadim