乱序写入导致的索引膨胀(B-tree, GIN, GiST皆如此)
背景
有些场景,用户会发现重建索引,索引比原来更小。
通常这种情况是索引字段乱序写入,导致索引频繁分裂,使得索引页并不是百分百填满。膨胀使然。
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 ]