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