PostgreSQL can report the progress of some commands during command execution. Currently, the commands that support progress reporting are CREATE INDEX, VACUUM and CLUSTER. This range may be expanded in the future.
CREATE INDEX
Whenever an index is created or REINDEX is run, the PG > stat > progress > create > index view will contain a row for each backend process that is currently creating an index.
postgres=# \d pg_stat_progress_create_index View "pg_catalog.pg_stat_progress_create_index" Column | Type | Collation | Nullable | Default --------------------+---------+-----------+----------+--------- pid | integer | | | datid | oid | | | datname | name | | | relid | oid | | | index_relid | oid | | | command | text | | | phase | text | | | lockers_total | bigint | | | lockers_done | bigint | | | current_locker_pid | bigint | | | blocks_total | bigint | | | blocks_done | bigint | | | tuples_total | bigint | | | tuples_done | bigint | | | partitions_total | bigint | | | partitions_done | bigint | | |
phase: Current processing phase of index creation About stage description reference Official documents
VACUUM
Whenever vacuum is running, the PG? Stat? Progress? Vacuum view will contain a row for each back-end process (including the automatic vacuum worker process) that is currently being purged.
postgres=# \d pg_stat_progress_vacuum 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 | | |
phase:Current processing phase of vacuum. About stage description reference Official documents
CLUSTER
CLUSTER is used to sort column data by index. Both CLUSTER and VACUUM FULL physically move data. Whenever CLUSTER or VACUUM FULL runs, the PG? Stat? Progress? CLUSTER view will contain a row for each backend process that is currently running any command.
postgres=# \d pg_stat_progress_cluster View "pg_catalog.pg_stat_progress_cluster" Column | Type | Collation | Nullable | Default ---------------------+---------+-----------+----------+--------- pid | integer | | | datid | oid | | | datname | name | | | relid | oid | | | command | text | | | phase | text | | | cluster_index_relid | oid | | | heap_tuples_scanned | bigint | | | heap_tuples_written | bigint | | | heap_blks_total | bigint | | | heap_blks_scanned | bigint | | | index_rebuild_count | bigint | | |
phase:Current processing phase. About stage description reference Official documents
test
The above view is very helpful for the daily operation and maintenance process. You can observe the operation progress, especially for long-running operations. The manageability of pg is increasing.
eg1: create index test_parallel_idx on test_parallel(name); postgres=# select pid,datname,relid,command,phase,current_locker_pid,tuples_total,tuples_done from pg_stat_progress_create_index; pid | datname | relid | command | phase | current_locker_pid | tuples_total | tuples_done -------+---------+-------+--------------+--------------------------------+--------------------+--------------+------------- 12612 | test | 16387 | CREATE INDEX | building index: scanning table | 0 | 0 | 0 (1 row) postgres=# select pid,datname,relid,command,phase,current_locker_pid,tuples_total,tuples_done from pg_stat_progress_create_index; pid | datname | relid | command | phase | current_locker_pid | tuples_total | tuples_done -------+---------+-------+--------------+----------------------------------------+--------------------+--------------+------------- 12612 | test | 16387 | CREATE INDEX | building index: loading tuples in tree | 0 | 11003000 | 1814012 (1 row)