28.4. Progress Reporting

PostgreSQL has the ability to report the progress of certain commands during command execution. Currently, the only command which supports progress reporting is VACUUM. This may be expanded in the future.

28.4.1. VACUUM Progress Reporting

Whenever VACUUM is running, the pg_stat_progress_vacuum view will contain one row for each backend (including autovacuum worker processes) that is currently vacuuming. The tables below describe the information that will be reported and provide information about how to interpret it. Progress reporting is not currently supported for VACUUM FULL and backends running VACUUM FULL will not be listed in this view.

表 28-20. pg_stat_progress_vacuum View

ColumnTypeDescription
pidintegerProcess ID of backend.
datidoidOID of the database to which this backend is connected.
datnamenameName of the database to which this backend is connected.
relidoidOID of the table being vacuumed.
phasetext Current processing phase of vacuum. See 表 28-21.
heap_blks_totalbigint Total number of heap blocks in the table. This number is reported as of the beginning of the scan; blocks added later will not be (and need not be) visited by this VACUUM.
heap_blks_scannedbigint Number of heap blocks scanned. Because the visibility map is used to optimize scans, some blocks will be skipped without inspection; skipped blocks are included in this total, so that this number will eventually become equal to heap_blks_total when the vacuum is complete. This counter only advances when the phase is scanning heap.
heap_blks_vacuumedbigint Number of heap blocks vacuumed. Unless the table has no indexes, this counter only advances when the phase is vacuuming heap. Blocks that contain no dead tuples are skipped, so the counter may sometimes skip forward in large increments.
index_vacuum_countbigint Number of completed index vacuum cycles.
max_dead_tuplesbigint Number of dead tuples that we can store before needing to perform an index vacuum cycle, based on maintenance_work_mem.
num_dead_tuplesbigint Number of dead tuples collected since the last index vacuum cycle.

表 28-21. VACUUM 的阶段

阶段描述
初始化 VACUUM正在准备开始扫描堆。这个阶段应该很简短。
扫描堆 VACUUM正在扫描堆。如果需要,它将会对每个页面进行修建以及碎片整理,并且可能会执行冻结动作。heap_blks_scanned列可以用来监控扫描的进度。
清理索引 VACUUM当前正在清理索引。如果一个表拥有索引,那么每次清理时这个阶段会在堆扫描完成后至少发生一次。如果maintenance_work_mem不足以存放找到的死亡元组,则每次清理时会多次清理索引。
清理堆 VACUUM当前正在清理堆。清理堆与扫描堆不是同一个概念,清理堆发生在每一次清理索引的实例之后。如果heap_blks_scanned小于heap_blks_total,系统将在这个阶段完成之后回去扫描堆;否则,系统将在这个阶段完成后开始清理索引。
清除索引 VACUUM当前正在清除索引。这个阶段发生在堆被完全扫描并且对堆和索引的所有清理都已经完成以后。
截断堆 VACUUM正在截断堆,以便把关系尾部的空页面返还给操作系统。这个阶段发生在清除完索引之后。
执行最后的清除 VACUUM在执行最终的清除。在这个阶段中,VACUUM将清理空闲空间映射、更新pg_class中的统计信息并且将统计信息报告给统计收集器。当这个阶段完成时,VACUUM也就结束了。