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 字段中报告，并将是以下值之一：

initializing
scanning heap
vacuuming indexes
vacuuming heap
cleaning up indexes
truncating heap
performing final cleanup

有关每个阶段的更多详细信息，请参阅 PostgreSQL 文档中的 VACUUM Phases 表。

列 delay_time（PostgreSQL 18 及更高版本）仅在 track_cost_delay_timing 设置为 on 时更新。

VACUUM FULL

请注意，VACUUM FULL 的进度是通过 pg_stat_progress_cluster 视图报告的。

按 PostgreSQL 版本定义

pg_stat_progress_vacuum (PostgreSQL 19)

                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           |           |          | 
 delay_time           | double precision |           |          |

文档： pg_stat_progress_vacuum

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           |           |          | 
 delay_time           | double precision |           |          |

文档： pg_stat_progress_vacuum

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

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

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

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

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

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

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

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

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  |

文档： pg_stat_progress_vacuum

变更历史

PostgreSQL 18
- 添加了列 delay_time (提交 bb8dff99)
PostgreSQL 17
- 添加了以下列（提交 46ebdfe1）
  - indexes_total
  - indexes_processed
- 修改了以下列（提交 667e65aa）
  - max_dead_tuples 到 max_dead_tuple_bytes
  - num_dead_tuples 到 dead_tuple_bytes
- 添加了列 num_dead_item_ids（提交 f1affb67）
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 的博客文章

另请参阅

VACUUM, 进度报告, pg_stat_progress_analyze, pg_stat_progress_cluster, track_cost_delay_timing

反馈

请在此处提交有关“pg_stat_progress_vacuum”的任何评论、建议或更正 这里。

版本图表

实用概述

有用链接

咖啡

目录

pg_stat_progress_vacuum

用法

VACUUM FULL

按 PostgreSQL 版本定义

pg_stat_progress_vacuum (PostgreSQL 19)

pg_stat_progress_vacuum (PostgreSQL 18)

pg_stat_progress_vacuum (PostgreSQL 17)

pg_stat_progress_vacuum (PostgreSQL 16)

pg_stat_progress_vacuum (PostgreSQL 15)

pg_stat_progress_vacuum (PostgreSQL 14)

pg_stat_progress_vacuum (PostgreSQL 13)

pg_stat_progress_vacuum (PostgreSQL 12)

pg_stat_progress_vacuum (PostgreSQL 11)

pg_stat_progress_vacuum (PostgreSQL 10)

pg_stat_progress_vacuum (PostgreSQL 9.6)

变更历史

示例

参考资料

有用链接

分类

另请参阅

反馈