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

1 minute read

背景

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

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

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 ]

Flag Counter

digoal’s 大量PostgreSQL文章入口