Maxim Boguk <maxim.boguk@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
