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

Hi,

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).

Graph of one index size history attached (other indexes have an similar time/size graphs).

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).

The table have large amount of the inserts/update/deletes, but autovacuum tuned to be pretty aggressive and I sure that there are no long transactions (longer then few minutes). Also there are no standby replica with hot_standby=on and no prepared transactions used, and not batch deletes/inserts/updates used. The server have plenty of RAM (database fit into shared buffers), IO and CPU available so there are no visible resource starvation.

​Background information:

The PostgreSQL version 9.4.2 64 bit on Linux.

Table structure:

\d+ clientsession
                                                                 Table "public.clientsession"
     Column      |           Type           |                                Modifiers                                | Storage  | Stats target | Description
-----------------+--------------------------+-------------------------------------------------------------------------+----------+--------------+-------------
 globalsessionid | bigint                   | not null default nextval('clientsession_globalsessionid_seq'::regclass) | plain    |              |
 deviceuid       | text                     |                                                                         | extended |              |
 localsessionid  | bigint                   |                                                                         | plain    |              |
 createddate     | timestamp with time zone |                                                                         | plain    |              |
 lastmodified    | timestamp with time zone |                                                                         | plain    |              |
 keypairid       | bigint                   |                                                                         | plain    |              |
 sessiondataid   | bigint                   |                                                                         | plain    |              |
Indexes:
    "clientsession_pkey" PRIMARY KEY, btree (globalsessionid) CLUSTER
    "clientsession_ukey" UNIQUE CONSTRAINT, btree (deviceuid, localsessionid)
    "clientsession_keypairid_key" btree (keypairid)
    "clientsession_sessiondataid_key" btree (sessiondataid)
    "clientsession_uduid_localid_idx" btree (upper(deviceuid), localsessionid)
Foreign-key constraints:
    "clientsession_keypair_fkey" FOREIGN KEY (keypairid) REFERENCES keypair(id) ON DELETE CASCADE
    "clientsession_sessiondata_id" FOREIGN KEY (sessiondataid) REFERENCES sessiondata(id) ON DELETE CASCADE
Referenced by:
    TABLE "remotecommand" CONSTRAINT "remotecommand_clientsessionid_fkey" FOREIGN KEY (clientsessionid) REFERENCES clientsession(globalsessionid) ON DELETE CASCADE
Options: fillfactor=50, autovacuum_vacuum_scale_factor=0.01

Results of pgstatindex for one of bloated indexes:

select * from pgstatindex('clientsession_pkey');
-[ RECORD 1 ]------+----------
version            | 2
tree_level         | 2
index_size         | 552640512
root_block_no      | 290
internal_pages     | 207
leaf_pages         | 67224
empty_pages        | 0
deleted_pages      | 29
avg_leaf_density   | 1.08
leaf_fragmentation | 3.02

List of current index sizes (they stabilized 1 day ago):
\di+ clientsession*
                                         List of relations
 Schema |              Name               | Type  |  Owner  |     Table     |  Size  | Description
--------+---------------------------------+-------+---------+---------------+--------+-------------
 public | clientsession_keypairid_key     | index | phoenix | clientsession | 545 MB |
 public | clientsession_pkey              | index | phoenix | clientsession | 527 MB |
 public | clientsession_sessiondataid_key | index | phoenix | clientsession | 900 MB |
 public | clientsession_uduid_localid_idx | index | phoenix | clientsession | 254 MB |
 public | clientsession_ukey              | index | phoenix | clientsession | 254 MB |

I never seen such behaviour on other databases and all my attempts to get this index bloat under control have no effect.

If anyone have any ideas (even crazy ones) - welcome.