Re: Bloated tables and why is vacuum full the only option

On Sun, Feb 9, 2014 at 4:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Claudio Freire <klaussfreire(at)gmail(dot)com> writes:
>>>> I also do routine reindexing to stop index bloat on its tracks, yet
>>>> freshly-reindexed indexes get considerably reduced in size with vacuum
>>>> full.
>
> AFAIK there's no reason for vacuum full to produce a different result
> from reindex. Did you mean to say that the indexes get smaller than
> what they had been after some normal operation? If so it's worth noting
> this comment from the btree index building code (nbtsort.c):

Smaller than after reindex. It was a surprise to me too.

> Also, there are certain usage patterns that can result in btree indexes
> having densities much lower than the conventional-wisdom 70%. The main
> one I've seen in practice is "decimation", where you delete say 99 out
> of every 100 entries in index order. This leaves just a few live entries
> in each leaf page --- but our btree code doesn't reclaim an index page
> for recycling until it's totally empty. So you can end up with a very
> low load factor after doing something like that, and a reindex is the
> only good way to fix it.

That's exactly the kind of pattern the "archival" step results in,
that's why I do routine reindexing.