PostgreSQL 13: Btree索引优化(引入Deduplication技术)

PostgreSQL 13 版本的Btree索引在存储层面引入了一个重要的技术,名为Deduplication,是指索引项去重技术。

Deduplication技术的引入能够减少索引的存储空间和维护开销,同时提升查询效率。

索引的Deduplication选项默认是开启的,如果想关闭指定索引的Deduplication,设置存储参数deduplicate_items为off即可。

本文简单介绍下 Deduplication 技术,并给出示例演示。

手册说明

More efficiently store duplicates in btree indexes (Anastasia Lubennikova, Peter Geoghegan)

This allows efficient btree indexing of low cardinality columns by storing duplicate keys only once. Users upgrading with pg_upgrade will need to use REINDEX to make use of this feature.

Deduplication介绍

PostgreSQL 13 版本前 Btree 索引会存储表的所有索引键,从而产生很多重复的索引项,13 版本引入的 deduplication 技术,可以大幅度减少重复索引项。

Deduplication 会定期的将重复的索引项合并,为每组形成一个发布列表元组,重复的索引项在此列表中仅出现一次,当表的索引键重复项很多时,能显著减少索引的存储空间。

Deduplication的优点

Deduplication技术的引入具有以下优点:

  • 减少存储空间: 重复的索引项被合并,能显著减少索引的存储空间。
  • 减少索引维护开销: 重建索引速度更快,vacuum索引的开销更低。
  • 提升查询效率: 更小的索引能够减少查询的时延,并提升吞吐量。

环境准备

计划在PostgreSQL 12 和 13 版本分别创建unique索引和重复项很多的索引,比较索引的大小。

创建测试表的脚本如下:

1
2
3
4
CREATE TABLE user_info (userid int4, username character varying(32),regtime timestamp without time zone);
INSERT INTO user_info (userid,username,regtime) SELECT n, 'user_info' || n, '2020-06-29 21:00:00' FROM generate_series(1,5000000) n;
CREATE UNIQUE INDEX idx_user_info_usename ON user_info USING BTREE(username);
CREATE INDEX idx_user_info_regtime ON user_info USING BTREE(regtime);

分别在PostgreSQL 12.2和 PostgreSQL 13 Beta1创建两个索引

  • idx_user_info_usename为unique索引,存储的索引项唯一。
  • idx_user_info_regtime为deduplicated 索引,存储的索引项都相同。

测试索引大小

PostgreSQL 12.2 查看索引大小,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres=# \di+ idx_user_info_usename
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-----------------------+-------+----------+-----------+--------+-------------
public | idx_user_info_usename | index | postgres | user_info | 185 MB |
(1 row)

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

PostgreSQL 13 Beta1 查看索引大小,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres=# \di+ idx_user_info_usename
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+-----------------------+-------+----------+-----------+-------------+--------+-------------
public | idx_user_info_usename | index | postgres | user_info | permanent | 185 MB |
(1 row)

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

根据以上看出,创建索引后,unique索引在12版本13版本大小一致,deduplicated索引差异较大,13版本的deduplicated索引大小约为12版本的三分之一。

唯一索引是否受Deduplication影响?

手册上提到: 即使是unique索引也可以使用Deduplication技术控制重复数据的膨胀,因为索引项的TIDs指向同一行数据的不同版本。

换句话说,当表的数据被update时,依据PostgreSQL的MVCC机制,老的tuple依然保留在原有PAGE上,并新增一条tuple,索引将同时存储新版本和老版本表数据的索引键。

目前没想到恰当的场景演示Deduplication对Unique索引影响,后续想到了再补充。

如何关闭索引的Deduplication?

可通过存储参数deduplicate_items控制索引是否启用Deduplication,这个参数默认为开启。

关闭索引的Deduplication,如下:

1
2
postgres=# ALTER INDEX idx_user_info_regtime SET (deduplicate_items=off);
ALTER INDEX

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%