PostgreSQL | 空间又告警了，先从整理索引碎片开始

前言

当一个生产数据库上线使用后，经过一段时间之后，索引往往会变得很大，并且产生大量的碎片，严重地影响数据库的性能。因此，我们将对碎片率高的索引执行reindex
操作。因此，如何识别索引碎片？一种行之有效的方法是安装pgstattuple
插件并利用pgstatindex
函数。

索引碎片模拟

首先我们来模拟一下索引碎片。先安装插件pgstattuple
，这是内置的您无需下载它。只需直接创建就可以了。

postgres=# create extension pgstattuple;
CREATE EXTENSION

下一步是创建表。插入一千万数据。

postgres=# create table test(id int);
CREATE TABLE
postgres=# insert into test values(generate_series(1,10000000));
INSERT 0 10000000
postgres=# create index idx_fragmented on test(id);
CREATE INDEX

postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pgstatindex('idx_fragmented');
-[ RECORD 1 ]------+----------
version            | 4
tree_level         | 2
index_size         | 224641024
root_block_no      | 290
internal_pages     | 98
leaf_pages         | 27323
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 90.09
leaf_fragmentation | 0

pgstatindex
函数将返回关于B树索引的信息。下面是一些重要信息，包括索引的高度，索引的大小，索引根的位置，空页的数量，被删除页的数量，碎片的程度等等。今天我们要观察的指标是leaf_fragmentation，当前为0。

接下来我们来插入和修改一些数据。

postgres=# insert into test values(generate_series(1,10000000));
INSERT 0 10000000
postgres=# update test set id =99 where id > 3000000; 
UPDATE 14000000
postgres=# SELECT * FROM pgstatindex('idx_fragmented');
-[ RECORD 1 ]------+----------
version            | 4
tree_level         | 2
index_size         | 539639808
root_block_no      | 290
internal_pages     | 189
leaf_pages         | 42739
empty_pages        | 0
deleted_pages      | 22945
avg_leaf_density   | 73.1
leaf_fragmentation | 37.18

现在，我们再一次通过pgstatindex
函数查看，发现碎片率达到37.18%，而当前索引的大小为515 MB。

postgres=# \di+ idx_fragmented
                                    List of relations
 Schema |      Name      | Type  |  Owner   | Table | Persistence |  Size  | Description 
--------+----------------+-------+----------+-------+-------------+--------+-------------
 public | idx_fragmented | index | postgres | test  | permanent   | 515 MB | 
(1 row)

在这个时候，我们要做的就是REINDEX
索引，回收不必要的空间。

postgres=# reindex index idx_fragmented;
REINDEX
postgres=# \di+ idx_fragmented
                                    List of relations
 Schema |      Name      | Type  |  Owner   | Table | Persistence |  Size  | Description 
--------+----------------+-------+----------+-------+-------------+--------+-------------
 public | idx_fragmented | index | postgres | test  | permanent   | 208 MB | 
(1 row)

postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pgstatindex('idx_fragmented');
-[ RECORD 1 ]------+----------
version            | 4
tree_level         | 2
index_size         | 218529792
root_block_no      | 209
internal_pages     | 112
leaf_pages         | 26563
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 89.98
leaf_fragmentation | 0

空间从515 MB回收后仅为208 MB，节约了大约307 MB的空间。而leaf_fragmentation
显示的页面碎片率也降为0了。

整个sehema的索引碎片检查

下一步我们将去生产库查一下。下面的语句使用了pg_stat_user_indexes
视图。

SELECT a.indexrelname, b.* FROM pg_stat_user_indexes a, LATERAL pgstatindex(indexrelname) b where a.schemaname='xxx' and b.leaf_fragmentation>30 ORDER BY leaf_fragmentation DESC;  

该查询比较慢，因为需要计算每个索引的值。所有碎片率超过30%的都列出来了，又可以清理出一大片空间了。

但在生产环境中，要执行reindex
之类的昂贵操作是非常困难的。由于该操作阻止正在执行的DML语句，因此在业务高峰期间进行重建是完全不可能的，该操作通常需要在停机时执行。而在PostgreSQL 12版之后， 新增了REINDEX CONCURRENTLY
选项，它可以在线重建不会阻塞DML操作。最后建议在REINDEX
操作后再执行ANALYZE更新一下统计信息。

后记

碎片收拾干净了，感觉PG比Oracle要方便一些。