PostgreSQL 12: 新增 pg_stat_progress_create_index 视图监控索引创建进度

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
简介: PostgreSQL 12 版本之前,对PostgreSQL大表创建索引时是一个比较痛苦的过程,创建索引过程中无法得知索引创建进度,PostgreSQL 12 在运维监控功能方面得到增强,新增 pg_stat_progress_create_index 视图可以监控索引的创建进度,本文简单演示。

PostgreSQL 12 版本之前,对PostgreSQL大表创建索引时是一个比较痛苦的过程,创建索引过程中无法得知索引创建进度,PostgreSQL 12 在运维监控功能方面得到增强,新增 pg_stat_progress_create_index 视图可以监控索引的创建进度,本文简单演示。

CREATE INDEXREINDEX 创建索引都可以通过 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 视图监控索引创建进度,对于大表创建索引场景非常有用,这个功能太棒了!

参考

本文转自 https://postgres.fun/20190716163500.html

新书推荐

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

链接:https://item.jd.com/12405774.html
_5_PostgreSQL_

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
4月前
|
存储 监控 关系型数据库
B-tree不是万能药:PostgreSQL索引失效的7种高频场景与破解方案
在PostgreSQL优化实践中,B-tree索引虽承担了80%以上的查询加速任务,但因多种原因可能导致索引失效,引发性能骤降。本文深入剖析7种高频失效场景,包括隐式类型转换、函数包裹列、前导通配符等,并通过实战案例揭示问题本质,提供生产验证的解决方案。同时,总结索引使用决策矩阵与关键原则,助你让索引真正发挥作用。
259 0
|
监控 关系型数据库 数据库
PostgreSQL的索引优化策略?
【8月更文挑战第26天】PostgreSQL的索引优化策略?
397 1
|
8月前
|
SQL 关系型数据库 OLAP
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
196 2
|
9月前
|
JSON 关系型数据库 PostgreSQL
PostgreSQL 9种索引的原理和应用场景
PostgreSQL 支持九种主要索引类型,包括 B-Tree、Hash、GiST、SP-GiST、GIN、BRIN、Bitmap、Partial 和 Unique 索引。每种索引适用于不同场景,如 B-Tree 适合范围查询和排序,Hash 仅用于等值查询,GiST 支持全文搜索和几何数据查询,GIN 适用于多值列和 JSON 数据,BRIN 适合非常大的表,Bitmap 适用于低基数列,Partial 只对部分数据创建索引,Unique 确保列值唯一。
|
存储 监控 关系型数据库
监控 PostgreSQL 的性能指标
监控 PostgreSQL 的性能指标
594 3
|
SQL 监控 关系型数据库
实时计算 Flink版操作报错合集之在设置监控PostgreSQL数据库时,将wal_level设置为logical,出现一些表更新和删除操作报错,怎么办
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
1405 0
|
关系型数据库 数据库 PostgreSQL
PostgreSQL索引维护看完这篇就够了
PostgreSQL索引维护看完这篇就够了
951 0
|
存储 关系型数据库 数据库
PostgreSQL技术大讲堂 - 第28讲:索引内部结构
从零开始学PostgreSQL技术大讲堂 - 第28讲:索引内部结构
1099 2
|
关系型数据库 Go 数据库
《提高查询速度:PostgreSQL索引实用指南》
《提高查询速度:PostgreSQL索引实用指南》
747 0

推荐镜像

更多