On Mon, Jun 1, 2015 at 3:18 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Maxim Boguk <maxim.bo...@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/ <http://www.postgresql-consulting.com/>
