作者
digoal
日期
2017-11-21
标签
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 ]
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
