乱序写入导致的索引膨胀(B-tree, GIN, GiST皆如此)

标签

PostgreSQL , 索引分裂 , 乱序写入

背景

有些场景，用户会发现重建索引，索引比原来更小。

通常这种情况是索引字段乱序写入，导致索引频繁分裂，使得索引页并不是百分百填满。膨胀使然。

B-Tree索引

由于索引页中的数据是有序的，因此在乱序写入时，索引页可能出现分裂，分裂多了，空洞就会多起来（一页里面没有填满）。

例子

1、先建索引，乱序写入。

postgres=# create table t_idx_split(id int);
CREATE TABLE
postgres=# create index idx_t_idx_split on t_idx_split (id);
CREATE INDEX
postgres=# insert into t_idx_split select random()*10000000 from generate_series(1,10000000);
INSERT 0 10000000
postgres=# \di+ t_idx_sp  

postgres=# \di+ idx_t_idx_split
                                List of relations
 Schema |      Name       | Type  |  Owner   |    Table    |  Size  | Description
--------+-----------------+-------+----------+-------------+--------+-------------
 public | idx_t_idx_split | index | postgres | t_idx_split | 280 MB |
(1 row)

2、先建索引，顺序写入。

postgres=# truncate t_idx_split ;
TRUNCATE TABLE
postgres=# \di+ idx_t_idx_split
                                  List of relations
 Schema |      Name       | Type  |  Owner   |    Table    |    Size    | Description
--------+-----------------+-------+----------+-------------+------------+-------------
 public | idx_t_idx_split | index | postgres | t_idx_split | 8192 bytes |
(1 row)  

postgres=# insert into t_idx_split select generate_series(1,10000000);
INSERT 0 10000000
postgres=# \di+ idx_t_idx_split
                                List of relations
 Schema |      Name       | Type  |  Owner   |    Table    |  Size  | Description
--------+-----------------+-------+----------+-------------+--------+-------------
 public | idx_t_idx_split | index | postgres | t_idx_split | 214 MB |
(1 row)

3、先写入，后建索引。

postgres=# drop index idx_t_idx_split ;
DROP INDEX
postgres=# create index idx_t_idx_split on t_idx_split (id);
CREATE INDEX
postgres=# \di+ idx_t_idx_split
                                List of relations
 Schema |      Name       | Type  |  Owner   |    Table    |  Size  | Description
--------+-----------------+-------+----------+-------------+--------+-------------
 public | idx_t_idx_split | index | postgres | t_idx_split | 214 MB |
(1 row)

很显然，顺序写入时，索引大小和后建索引大小一致，没有出现膨胀。

GIN索引

GIN索引也是树结构，也有膨胀的可能。

对于gin索引，实际上膨胀现象更加的明显，因为通常GIN是对多值类型的索引，而多值类型，通常输入的顺序更加无法保证。

GIN主树索引页会膨胀较厉害。

GiST和SP-GiST索引

同样存在这个现象，当写入的空间数据BOUND BOX是空间无序写入的，那么就会导致膨胀。

重建索引，可以收缩膨胀

建议并行建索引，防止堵塞DML

使用CONCURRENTLY关键字，并行创建索引，不会堵塞DML，但是创建索引的时间比正常创建索引的时间会略长。

Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]