pg_stat_progress_vacuum
一个提供有关 vacuum 操作信息的进度报告视图
pg_stat_progress_vacuum 是一个提供有关 vacuum 操作进度信息的进度报告视图（无论是由 VACUUM 命令手动触发还是由 autovacuum 自动触发）。
pg_stat_progress_vacuum 在 PostgreSQL 9.6 中添加。
用法
pg_stat_progress_vacuum 将为每个当前的 vacuum 操作包含一行，并在操作进行时更新。当前阶段在 phase 字段中报告，并且将是以下值之一：
初始化扫描堆清理索引清理堆清理索引截断堆执行最终清理
有关每个阶段的更多详细信息，请参阅 PostgreSQL 文档中的表VACUUM 阶段。
VACUUM FULL
请注意，VACUUM FULL 进度通过 pg_stat_progress_cluster 视图报告。
PostgreSQL 版本定义
pg_stat_progress_vacuum (PostgreSQL 18)
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Collation | Nullable | Default
----------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
heap_blks_vacuumed | bigint | | |
index_vacuum_count | bigint | | |
max_dead_tuple_bytes | bigint | | |
dead_tuple_bytes | bigint | | |
num_dead_item_ids | bigint | | |
indexes_total | bigint | | |
indexes_processed | bigint | | |
pg_stat_progress_vacuum (PostgreSQL 17)
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Collation | Nullable | Default
----------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
heap_blks_vacuumed | bigint | | |
index_vacuum_count | bigint | | |
max_dead_tuple_bytes | bigint | | |
dead_tuple_bytes | bigint | | |
num_dead_item_ids | bigint | | |
indexes_total | bigint | | |
indexes_processed | bigint | | |
pg_stat_progress_vacuum (PostgreSQL 16)
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Collation | Nullable | Default
--------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
heap_blks_vacuumed | bigint | | |
index_vacuum_count | bigint | | |
max_dead_tuples | bigint | | |
num_dead_tuples | bigint | | |
pg_stat_progress_vacuum (PostgreSQL 15)
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Collation | Nullable | Default
--------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
heap_blks_vacuumed | bigint | | |
index_vacuum_count | bigint | | |
max_dead_tuples | bigint | | |
num_dead_tuples | bigint | | |
pg_stat_progress_vacuum (PostgreSQL 14)
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Collation | Nullable | Default
--------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
heap_blks_vacuumed | bigint | | |
index_vacuum_count | bigint | | |
max_dead_tuples | bigint | | |
num_dead_tuples | bigint | | |
pg_stat_progress_vacuum (PostgreSQL 13)
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Collation | Nullable | Default
--------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
heap_blks_vacuumed | bigint | | |
index_vacuum_count | bigint | | |
max_dead_tuples | bigint | | |
num_dead_tuples | bigint | | |
pg_stat_progress_vacuum (PostgreSQL 12)
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Collation | Nullable | Default
--------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
heap_blks_vacuumed | bigint | | |
index_vacuum_count | bigint | | |
max_dead_tuples | bigint | | |
num_dead_tuples | bigint | | |
pg_stat_progress_vacuum (PostgreSQL 11)
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Collation | Nullable | Default
--------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
heap_blks_vacuumed | bigint | | |
index_vacuum_count | bigint | | |
max_dead_tuples | bigint | | |
num_dead_tuples | bigint | | |
pg_stat_progress_vacuum (PostgreSQL 10)
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Collation | Nullable | Default
--------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
heap_blks_vacuumed | bigint | | |
index_vacuum_count | bigint | | |
max_dead_tuples | bigint | | |
num_dead_tuples | bigint | | |
pg_stat_progress_vacuum (PostgreSQL 9.6)
View "pg_catalog.pg_stat_progress_vacuum"
Column | Type | Modifiers
--------------------+---------+-----------
pid | integer |
datid | oid |
datname | name |
relid | oid |
phase | text |
heap_blks_total | bigint |
heap_blks_scanned | bigint |
heap_blks_vacuumed | bigint |
index_vacuum_count | bigint |
max_dead_tuples | bigint |
num_dead_tuples | bigint |
更改历史记录
- PostgreSQL 17
- PostgreSQL 9.6
- 添加 (提交 c16dc1ac)
示例
示例 pg_stat_progress_vacuum 输出
postgres=# SELECT * FROM pg_stat_progress_vacuum; -[ RECORD 1 ]------+-------------- pid | 5270 datid | 16389 datname | appdb relid | 16861 phase | scanning heap heap_blks_total | 162719 heap_blks_scanned | 100350 heap_blks_vacuumed | 0 index_vacuum_count | 0 max_dead_tuples | 11184810 num_dead_tuples | 0
参考文献
- PostgreSQL 文档： VACUUM 进度报告
有用链接
- 深入了解 Postgres 统计信息：pg_stat_progress_vacuum - 2017 年 10 月 Alexey Lesovsky / Data Egret 的博文