PG中的索引膨胀
Tags： PGPostgreSQL索引膨胀膨胀
正确的理解索引膨胀
postgres运维中因为有dead tuple，所以表膨胀是很常见的问题，正确的配置好autovacuum相关参数后，其实不用过多的去干预表膨胀，autovacuum进程会对符合条件的表和索引进行dead tuple的清理。
那么索引膨胀又该怎么定义呢？不能单独的认为表膨胀，这个表的索引也膨胀吧？假设对一个索引进行顺序的数据插入，那么索引分裂应该只会发生在最右边的叶子结点，如果对索引进行无序的插入，那么中间的叶子结点会进行了分裂，多出了很多空闲空间，索引扫描的时候需要扫描更多的页，造成了io和存储空间上的浪费，这样应该能符合索引膨胀的定义。
那么该如何解决呢？也很简单，reindex就可以，而且pg12之后支持CONCURRENTLY能更方便的进行reindex，定期的检查膨胀率过大的索引进行reindex是很有必要的，所以下面就如何确定一个索引是否发生膨胀进行详细说明。
使用扩展查看索引信息
常用的模块就能满足需求，为方便测试创建了一个测试表，int字段创建索引idx_test02_id，插入100w条数据,pgstattuple的pgstatindex()可以查看索引的统计信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | create extension pgstattuple; create table test02(id int, name text); create index idx_test02_id on test02(id); insert into test02 select n,'a' from generate_series(1, 1000000) n; db1=# \d+ test02 Table "public.test02" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+---------+-----------+----------+---------+----------+-------------+--------------+------------- id | integer | | | | plain | | | name | text | | | | extended | | | Indexes: "idx_test02_id" btree (id) Access method: heap db1=# \dt+ test02 List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+--------+-------+----------+-------------+---------------+-------+------------- public | test02 | table | postgres | permanent | heap | 35 MB | (1 row) db1=# \di+ idx_test02_id List of relations Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description --------+---------------+-------+----------+--------+-------------+---------------+-------+------------- public | idx_test02_id | index | postgres | test02 | permanent | btree | 21 MB | (1 row) select * from pgstatindex('idx_test02_id'); version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation ---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------- 4 | 2 | 22487040 | 412 | 11 | 2733 | 0 | 0 | 90.06 | 0 (1 row) |
对结果信息进行解释
可以通过pgstattuple的源码确认index_size的计算方法: all pages * block size
1 2 3 4 5 6 | (1 + /* include the metapage in index_size */ indexStat.leaf_pages + indexStat.internal_pages + indexStat.deleted_pages + indexStat.empty_pages) * BLCKSZ |
我们重点关注以下2个指标：
avg_leaf_density 为实际索引数据占总量的百分比, 越低表示索引浪费空间越多，建议小于30%进行reindex，
源码中的计算方式如下
100.0 - (double) indexStat.free_space / (double) indexStat.max_avail * 100.0);
max_avail计算方式为页面大小减去页面中已使用的空间大小和页面头部数据的大小
1 2 | max_avail = BLCKSZ - (BLCKSZ - ((PageHeader) page)->pd_special + SizeOfPageHeaderData); free_space = page.pd_upper – page.pd_lower |
因为leaf page的fillfactor为90%，所以理想情况下，avg_leaf_density应该为90%。
leaf_fragmentation表示碎片率，数字越大表示物理上越不连续，源码中解释为它比较下一个叶子页的块号（opaque->btpo_next）与当前块号（blkno）。如果下一个叶子页在较早的块上，表示存在碎片化。索引中的碎片化是指索引的叶子页在磁盘上不是物理上连续的。这可能是由于页面分裂或删除等原因导致的。碎片化可能会影响索引扫描的性能，并可能需要维护操作，如索引重组或重建。
1 2 3 4 5 6 7 | /* * If the next leaf is on an earlier block, it means a * fragmentation. */ if (opaque->btpo_next != P_NONE && opaque->btpo_next < blkno) indexStat.fragments++; |
综上信息给出一个大致结论：avg_leaf_density越小表示浪费空间越多，结合实际索引大小进行reindex，reindex后avg_leaf_density应很接近90；leaf_fragmentation越大表示物理上的连续行很差，扫描该索引会产生很多随机io影响性能，reindex后会应该为0。
这种方法大致能评估出一个index是否需要进行reindex，好不好用呢？好，但是还不够好；因为得一个个索引去执行，不够敏捷，过于被动，有没有方法可以一下统计出所有的索引膨胀信息并进行排序呢？后面我们进行尝试。
评估索引空间
当我们知道一个表有多少条记录，索引字段的平均长度，就可以大致算出这个存储该index需要多少个page，后面我们称这个数字为iotta，也就是我们需要计算出膨胀前的pages，理论上说一个无重复字段顺序插入的索引，iotta应该等于pg_class中的relpages，或者说一个无重复字段的索引进行reindex后，iotta应该等于pg_class中的relpages，也和pgstatindex结果中的leaf pages + internal_pages + 1个meta page的结果相等。
用iotta去除以索引当前的pg_class.relpages，得出的比率值，就相当于这个索引的膨胀率，那么如何计算出iotta呢？
大致思路为：计算一个page能存多少个index tuple， tuples总数除以这个数字大致就是leaf pages的数量，再通过leaf pages的数量来倒推branch pages的数量，最后leaf pages + branch pages + 1 root page + 1 meta page,就是我们需要得到的结果。
后面我们进行实验验证，先来一起学习一些知识点，方便后面的计算。
