Curious case of huge simple btree indexes bloat.

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.

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

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)com
МойКруг: http://mboguk.moikrug.ru/

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."