PostgreSQL 12: 支持在线重建索引(Reindex Concurrently)

DML操作频繁的表索引可能出现较大膨胀,导致占用空间多,对性能也有一定影响。 PostgreSQL 12 版本之前重建索引通常是首先创建一个同样的索引(索引列一样,索引名称不同),之后删除老的索引。

PostgreSQL 12 的 REINDEX 命令新增 CONCURRENTLY 选项,可以使用 REINDEX CONCURRENTLY 命令在线重建索引,一条命令即可,降低了维护成本。

发行说明

Add REINDEX CONCURRENTLY to allow reindexing concurrently (Michaël Paquier, Andreas Karlsson, Peter Eisentraut)

This is also controlled by the reindexdb –concurrently option.

补丁说明

Michael Paquier 提交的补丁,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
REINDEX CONCURRENTLY

This adds the CONCURRENTLY option to the REINDEX command. A REINDEX
CONCURRENTLY on a specific index creates a new index (like CREATE
INDEX CONCURRENTLY), then renames the old index away and the new index
in place and adjusts the dependencies, and then drops the old
index (like DROP INDEX CONCURRENTLY). The REINDEX command also has
the capability to run its other variants (TABLE, DATABASE) with the
CONCURRENTLY option (but not SYSTEM).

The reindexdb command gets the --concurrently option.

Author: Michael Paquier, Andreas Karlsson, Peter Eisentraut
Reviewed-by: Andres Freund, Fujii Masao, Jim Nasby, Sergei Kornilov
Discussion: https://www.postgresql.org/message-id/flat/60052986-956b-4478-45ed-8bd119e9b9cf%402ndquadrant.com#74948a1044c56c5e817a5050f554ddee

根据补丁说明,REINDEX CONCURRENTLY 命令包括以下几个步骤,如下:

  • 在线新建一个索引,相当于执行 CREATE INDEX CONCURRENTLY newindex ON table;
  • 重命名老索引,相当于执行 ALTER INDEX oldindex TO tmpindex;
  • 重命名新索引,相当于执行 ALTER INDEX newindex TO oldinex;
  • 调整相关依赖关系
  • 删除老索引,相当于执行 DROP INDEX CONCURRENTLY tmpindex

持有的表级锁

值得一提的是 REINDEX CONCURRENTLYCREATE INDEX CONCURRENTLY 持有的表级锁一样,都为 SHARE UPDATE EXCLUSIVE 锁,命令执行过程中不会阻塞表上的DML操作。

REINDEX 命令和 CREATE INDEX 持有的表级锁为 SHARE 锁,会阻塞表上的 DML 操作。

关于表级锁的更详细信息可查阅手册 13.3.1. Table-level Locks

验证 REINDEX CONCURRENTLY 持有的表级锁

REINDEX CONCURRENTLY 命令持有的表级锁为 SHARE UPDATE EXCLUSIVE,本文验证下,由于 REINDEX CONCURRENTLY 命令不支持在事务中执行,验证步骤稍复杂,思路如下,测试环境为笔记本一台4核1GB的虚拟机。

  • 1、首先使用 pgbench 把虚拟机的负载跑起来
  • 2、通过 REINDEX CONCURRENTLY 命令重建大表索引
  • 3、通过 pg_locks 视图监控锁情况

创建测试表,并插入 1000 万数据,之后创建索引,如下:

1
2
3
CREATE TABLE t_index (id int4, name text,ctime timestamp without time zone);                                 
INSERT INTO t_index (id,name,ctime) SELECT n, n||'_index',clock_timestamp() FROM generate_series(1,10000000) n;
CREATE INDEX idx_t_index_ctime ON t_index USING BTREE(ctime);

执行 pgbench 脚本,设置执行时间为 600 秒,如下:

1
[pg12@pghost2 ~]$ pgbench -c 2 -j 2 -T 600 -n  mydb -U pguser select_only &

会话1: 重建索引,如下:

1
2
3
4
5
6
7
8
mydb=> SELECT pg_backend_pid();
pg_backend_pid
----------------
28161
(1 row)

REINDEX INDEX CONCURRENTLY idx_t_index_id ;
命令执行过程中,暂未结束

注意: 会话1的进程号为 28161,并且 REINDEX INDEX CONCURRENTLY idx_t_index_id 并且暂未结束。

会话2: 查看进程 28161 持有表级锁情况,如下:

1
2
3
4
5
6
7
8
mydb=# SELECT relation::regclass,page,pid,mode,granted,fastpath FROM pg_locks WHERE pid= 28161;
relation | page | pid | mode | granted | fastpath
-----------------------------+------+-------+--------------------------+---------+----------
pguser.idx_t_index_id_ccnew | | 28161 | RowExclusiveLock | t | t
| | 28161 | ExclusiveLock | t | t
pguser.idx_t_index_id | | 28161 | ShareUpdateExclusiveLock | t | f
pguser.t_index | | 28161 | ShareUpdateExclusiveLock | t | f
(4 rows)

根据以上第3、4行看出持有的表级锁为 ShareUpdateExclusiveLock,验证了 REINDEX CONCURRENTLY 命令持有的是 SHARE UPDATE EXCLUSIVE 表级锁。

会话3: 删除一条数据测试,如下:

1
2
mydb=> DELETE  FROM t_index WHERE ID=2;
DELETE 1

删除数据正常,验证了 REINDEX CONCURRENTLY 命令不会阻塞 DML 操作。

注意事项

如果 REINDEX CONCURRENTLY 命令执行失败,那么这个索引状态将为 “invalid” 不可用,之后只能通过 REINDEX INDEX 命令重建,不带 CONCURRENTLY 选项的索引重建过程中会阻塞表上的 DML 操作,对于在线的业务系统是致命的,需重点关注。

参考

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

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

PostgreSQL实战
感谢支持!
0%