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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sun, Feb 9, 2014 at 4:40 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Claudio Freire <klaussfreire@xxxxxxxxx> 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.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux