pg_stat_progress_vacuum — PostgreSQL statistics view

The PostgreSQL pg_stat_progress_vacuum statistics view: full column reference (names, types, descriptions), catalog relationships and version support.

The Sev-1 Admin
The Sev-1 Admin  ·  4 min read

Summary

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 for VACUUM FULL commands is reported via pg_stat_progress_cluster because both VACUUM FULL and CLUSTER rewrite the table, while regular VACUUM only modifies it in place. See cluster_progress_reporting.

(Description quoted from the official PostgreSQL documentation.)

Columns

The pg_stat_progress_vacuum statistics view exposes the following columns (names, types and descriptions are taken verbatim from the PostgreSQL documentation):

  • pid integer
    Process ID of backend.
  • datid oid
    OID of the database to which this backend is connected.
  • datname name
    Name of the database to which this backend is connected.
  • relid oid
    OID of the table being vacuumed.
  • phase text
    Current processing phase of vacuum. See vacuum_phases.
  • heap_blks_total bigint
    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_scanned bigint
    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_vacuumed bigint
    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_count bigint
    Number of completed index vacuum cycles.
  • max_dead_tuple_bytes bigint
    Amount of dead tuple data that we can store before needing to perform an index vacuum cycle, based on maintenance_work_mem.
  • dead_tuple_bytes bigint
    Amount of dead tuple data collected since the last index vacuum cycle.
  • num_dead_item_ids bigint
    Number of dead item identifiers collected since the last index vacuum cycle.
  • indexes_total bigint
    Total number of indexes that will be vacuumed or cleaned up. This number is reported at the beginning of the vacuuming indexes phase or the cleaning up indexes phase.
  • indexes_processed bigint
    Number of indexes processed. This counter only advances when the phase is vacuuming indexes or cleaning up indexes.
  • delay_time double precision
    Total time spent sleeping due to cost-based delay (see runtime_config_resource_vacuum_cost), in milliseconds (if track_cost_delay_timing is enabled, otherwise zero). This includes the time that any associated parallel workers have slept. However, parallel workers report their sleep time no more frequently than once per second, so the reported value may be slightly stale.
  • mode text
    The mode in which the current VACUUM operation is running. See vacuum_for_wraparound for details of each mode. Possible values are: normal: The operation is performing a standard vacuum. It is neither required to run in aggressive mode nor operating in failsafe mode. aggressive: The operation is running an aggressive vacuum, which must scan every page that is not marked all-frozen. The parameters vacuum_freeze_table_age and vacuum_multixact_freeze_table_age determine when a table requires aggressive vacuuming. failsafe: The vacuum has entered failsafe mode, in which it performs only the minimum work necessary to avoid transaction ID or multixact ID wraparound failure. The parameters vacuum_failsafe_age and vacuum_multixact_failsafe_age determine when the vacuum enters failsafe mode. The vacuum may start in this mode or switch to it while running; the value of the mode column may transition from another mode to failsafe during the operation.
  • started_by text
    Shows what caused the current VACUUM operation to be started. Possible values are: manual: The vacuum was started by an explicit VACUUM command. autovacuum: The vacuum was started by an autovacuum worker. Vacuums run by autovacuum workers may be interrupted due to lock conflicts. autovacuum_wraparound: The vacuum was started by an autovacuum worker to prevent transaction ID or multixact ID wraparound. Vacuums run for wraparound protection are not interrupted due to lock conflicts.

Version applicability

Present in PostgreSQL 17, 18, 19 (verified against each release’s documentation). This is a long-standing system object that also exists in earlier PostgreSQL releases.

Related & references

Reference: PostgreSQL documentation — pg_stat_progress_vacuum.