Re: Curious case of huge simple btree indexes bloat. - Mailing list pgsql-general

From Maxim Boguk
Subject Re: Curious case of huge simple btree indexes bloat.
Date
Msg-id CAK-MWwRd54M69Uz1K9BBQ=WgrcOrjmgYyJgUvihromy=kHkWDw@mail.gmail.com
Whole thread Raw
In response to Re: Curious case of huge simple btree indexes bloat.  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general


On Mon, Jun 1, 2015 at 3:18 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Maxim Boguk <maxim.boguk@gmail.com> writes:
> On the one of databases under my support I found very curious case of the
> almost endless index bloat (index size stabilises around 100x of the
> original size).

> The table have 5 indexes and they all have the same bloating behaviour
> (growth to almost 100x and stabilisation around that amount). An original
> index size 4-8Mb (after manual reindex), over time of the 5 days they all
> monotonically growth to 300-900MB. In the same time table size staying
> pretty constant at 30-50Mb (and amount of rows in the same don't vary
> widely and stays between 200k and 500k).

At least for the index you gave stats for, it seems like it's stabilizing
at one index entry per page.  This is a known possible pathological
behavior if the application's usage involves heavy decimation of original
entries; say, you insert sequential timestamps and then later remove all
but every one-thousandth one, leaving at most one live entry on every
index page.  Btree can recover the totally-empty leaf pages but it has no
provision for merging non-empty leaf pages, so those all stay as they are
indefinitely.

It would be pretty unusual for all the indexes on a table to be used like
that, though.

                        regards, tom lane

Thank you very much for an explanation.
This table are part of the complicated 3-tables session info structure with a lot of short living sessions and some very long living.
And most used id's are bigserials. So yet every index field on that table have the same bad behaviour.

--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/


pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: date type changing to timestamp without time zone in postgres 9.4
Next
From: Andreas Ulbrich
Date:
Subject: Find out what on what function depends an index