From: | Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Curious case of huge simple btree indexes bloat. |
Date: | 2015-06-01 14:04:46 |
Message-ID: | CAK-MWwRd54M69Uz1K9BBQ=WgrcOrjmgYyJgUvihromy=kHkWDw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Jun 1, 2015 at 3:18 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Maxim Boguk <maxim(dot)boguk(at)gmail(dot)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/ <http://www.postgresql-consulting.com/>
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Ulbrich | 2015-06-01 14:06:54 | Find out what on what function depends an index |
Previous Message | Adrian Klaver | 2015-06-01 13:56:28 | Re: date type changing to timestamp without time zone in postgres 9.4 |