Contents

pg_stat_progress_vacuum

A progress reporting view providing information on vacuum operations

pg_stat_progress_vacuum is a progress reporting view providing information on the progress of vacuum operations (whether triggered manually via the VACUUM command or automatically via autovacuum).

pg_stat_progress_vacuum was added in PostgreSQL 9.6.

Usage

pg_stat_progress_vacuum will contain one row for each current vacuum operation, which will be updated as the operation progresses. The current phase is reported in the phase field and will be one of the following values:

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

For more details on each phase, see the table VACUUM Phases in the PostgreSQL documentation.

VACUUM FULL

Note that VACUUM FULL progress is reported via the pg_stat_progress_cluster view.

Definition by PostgreSQL version

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  |           |          |

Documentation: 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  |           |          |

Documentation: 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  |           |          |

Documentation: 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  |           |          |

Documentation: 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  |           |          |

Documentation: 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  |           |          |

Documentation: 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  |

Documentation: pg_stat_progress_vacuum

Change history

Examples

Example pg_stat_progress_vacuum output:

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

Categories

Monitoring, Progress reporting view, Vacuuming

See also

VACUUM, Progress reporting, pg_stat_progress_analyze, pg_stat_progress_cluster