PostgreSQL 12 版本之前，对PostgreSQL大表创建索引时是一个比较痛苦的过程，创建索引过程中无法得知索引创建进度，PostgreSQL 12 在运维监控功能方面得到增强，新增 pg_stat_progress_create_index 视图可以监控索引的创建进度，本文简单演示。
CREATE INDEX 和 REINDEX 创建索引都可以通过 pg_stat_progress_create_index 视图监控。
发行说明
Report progress of CREATE INDEX and REINDEX operations (álvaro Herrera, Peter Eisentraut)
Progress is reported in the pg_stat_progress_create_index system view
环境准备
创建测试表并插入 1000 万数据 ，如下:
mydb=> CREATE TABLE t1 (id int4,name text,ctime timestamp without time zone default clock_timestamp());
CREATE TABLE
mydb=> INSERT INTO t1 (id,name ) SELECT n, n || '_CREATE_INDEX' FROM generate_series(1,10000000) n;
INSERT 0 10000000监控索引创建进度
开启会话1，创建索引，如下:
mydb=> CREATE INDEX idx_t1_ctime ON t1 USING BTREE(ctime);
命令未结束注意: 会话1未结束，在创建索引过程中。
开启会话2，查询 pg_stat_progress_create_index 视图，监控索引创建进度，如下:
mydb=> SELECT * FROM pg_stat_progress_create_index ;
-[ RECORD 1 ]------+-------------------------------
pid | 20539
datid | 16387
datname | mydb
relid | 16527
index_relid | 0
command | CREATE INDEX
phase | building index: scanning table
lockers_total | 0
lockers_done | 0
current_locker_pid | 0
blocks_total | 82353
blocks_done | 52293
tuples_total | 0
tuples_done | 0
partitions_total | 0
partitions_done | 0
mydb=> SELECT * FROM pg_stat_progress_create_index ;
-[ RECORD 1 ]------+---------------------------------------
pid | 20539
datid | 16387
datname | mydb
relid | 16527
index_relid | 0
command | CREATE INDEX
phase | building index: loading tuples in tree
lockers_total | 0
lockers_done | 0
current_locker_pid | 0
blocks_total | 0
blocks_done | 0
tuples_total | 10000000
tuples_done | 1173763
partitions_total | 0
partitions_done | 0每条索引创建进程在 pg_stat_progress_create_index 视图中对应一条记录，几个主要的字段解释如下:
- pid: 索引创建进程号
- relid: 表的OID
- index_relid: 索引的OID
- phase: 索引创建的当前处理阶段
- current_locker_pid: 阻塞索引创建的进程号
- blocks_total: 当前处理阶段需要处理的数据块
- lockers_done: 当前处理阶段已完成的数据块
- tuples_total: 当前处理阶段需要处理的记录数
- tuples_done: 当前处理阶段已完成的记录数
- partitions_total: 当在分区表上创建索引，当前处理阶段需要处理的总分区数
- partitions_done: 当在分区表上创建索引，当前处理阶段已处理的总分区数。
索引创建的时间主要花在 'building index: scanning table' 和'building index: loading tuples in tree' 阶段，因此，根据 blocks_total、blocks_done、tuples_total、blocks_done 很容易判断索引创建进度。
总结
本文演示了 CREATE INDEX 命令执行过程中，通过查询 pg_stat_progress_create_index 视图监控索引创建进度，对于大表创建索引场景非常有用，这个功能太棒了！
参考
- Waiting for PostgreSQL 12 – Report progress of CREATE INDEX operations
- https://paquier.xyz/postgresql-2/postgres-12-progress-reports/
- 27.4. Progress Reporting
本文转自 https://postgres.fun/20190716163500.html
新书推荐
最后推荐和张文升共同编写的《PostgreSQL实战》，本书基于PostgreSQL 10 编写，共18章，重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等，涵盖大量实战用例!