pg_stat_progress_vacuum
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:
initializingscanning heapvacuuming indexesvacuuming heapcleaning up indexestruncating heapperforming 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 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 | | |
Documentation: 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 | | |
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
- PostgreSQL 9.6
- added (commit c16dc1ac)
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
References
- PostgreSQL documentation: VACUUM Progress Reporting
Useful links
- Deep dive into Postgres stats: pg_stat_progress_vacuum - October 2017 blog article by Alexey Lesovsky / Data Egret