Orbit
Xstreami
Why PostgreSQL Progress Reporting Matters for DBAs
Whether you're a DBA, developer, or monitoring enthusiast, one question haunts every long-running database operation: "Is it done yet?"
Operations like index creation, vacuuming, data loading, and base backups can take significant time — sometimes minutes, sometimes hours. Without visibility, these operations feel like black boxes. Thankfully, PostgreSQL progress reporting provides a powerful transparency feature that allows you to track these operations in real time.
What Is PostgreSQL Progress Reporting? Definition & Core Purpose
Progress reporting in PostgreSQL refers to dynamic system views that expose the real-time status of long-running internal operations. These are live, in-memory views that reflect what PostgreSQL is doing at any given moment — no logging required, no wait-and-see guesswork.
What DBAs Can Do with Progress Views
In real-world PostgreSQL environments handled by Mafiree, these views are heavily used to build proactive monitoring and alerting systems.
Real-Time PostgreSQL Progress Monitoring: Operational Benefits
Operational Challenges Without Progress Views
Before these views existed, DBAs had limited options: parse logs, run pg_stat_activity for rough signals, or simply wait. This created uncertainty around maintenance windows, DR tests, and bulk operations.
PostgreSQL Progress Reporting Views: Complete List by Operation
All Available Views in PostgreSQL 18
Here is a complete breakdown of the progress-reporting views available as of PostgreSQL 18:
PostgreSQL Progress Views in Action: Live Query Examples
Below are practical, real outputs captured from running PostgreSQL commands. These examples help you understand how to use each view in production environments.
Query pg_stat_progress_vacuum whenever autovacuum or manual VACUUM is running on a large table. It's especially useful during post-bulk-load cleanup or when the autovacuum seems slow.
miru_sports=# select * from pg_stat_progress_vacuum\gx
-[ RECORD 1 ]------+--------------
pid | 82089
datid | 25296
datname | miru_sports
relid | 25303
phase | scanning heap
heap_blks_total | 73334
heap_blks_scanned | 0
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuples | 11184809
num_dead_tuples | 0
Time: 0.267 ms- phase — current step: scanning heap, vacuuming indexes, or cleanup
- heap_blks_scanned / heap_blks_total — derive a completion percentage
- num_dead_tuples — how much bloat is being reclaimed
- index_vacuum_count — how many index passes have occurred
Use pg_stat_progress_analyze when large tables are being analyzed after bulk loads, or when autoanalyze is running longer than expected and you want to track how far along it is.
miru_sports=# select * from pg_stat_progress_analyze\gx
-[ RECORD 1 ]-------------+----------------------
pid | 82089
datid | 25296
datname | miru_sports
relid | 25298
phase | acquiring sample rows
sample_blks_total | 2616
sample_blks_scanned | 517
ext_stats_total | 0
ext_stats_computed | 0
child_tables_total | 0
child_tables_done | 0
current_child_table_relid | 0
Time: 0.169 ms- phase — current step: acquiring inherited sample rows or acquiring sample rows
- sample_blks_scanned / sample_blks_total — percentage of sampling completed
- ext_stats_computed — tracks extended statistics progress for multi-column stats
- child_tables_done — relevant for partitioned tables
Index creation on large tables can take considerable time. pg_stat_progress_create_index shows exactly which phase the build is in, whether using standard or CONCURRENTLY mode.
-[ RECORD 1 ]------+-------------
pid | 82293
datid | 25296
datname | miru_sports
relid | 25298
index_relid | 0
command | CREATE INDEX
phase | initializing
lockers_total | 0
lockers_done | 0
current_locker_pid | 0
blocks_total | 0
blocks_done | 0
tuples_total | 0
tuples_done | 0
partitions_total | 0
partitions_done | 0
Time: 0.416 ms-[ RECORD 1 ]------+-------------------------------
pid | 82293
datid | 25296
datname | miru_sports
relid | 25298
index_relid | 0
command | CREATE INDEX
phase | building index: scanning table
lockers_total | 0
lockers_done | 0
current_locker_pid | 0
blocks_total | 2616
blocks_done | 161
tuples_total | 0
tuples_done | 0
partitions_total | 0
partitions_done | 0
Time: 0.416 ms- phase — know exactly which build stage is in progress
- blocks_done / blocks_total — compute percentage during the scanning phase
- tuples_done / tuples_total — relevant during the sorting phase
- partitions_done — useful for CREATE INDEX on partitioned tables
CLUSTER physically rewrites the entire table in index order, which is a heavy, locking operation. Use pg_stat_progress_cluster to track its progress and plan your maintenance window accordingly.
miru_sports=# SELECT * FROM pg_stat_progress_cluster\gx
-[ RECORD 1 ]---
pid | 82755
datid | 25296
datname | miru_sports
relid | 25298
command | CLUSTER
phase | writing new heap
cluster_index_relid | 0
heap_tuples_scanned | 83200
heap_tuples_written | 1303
heap_blks_total | 2630
heap_blks_scanned | 2630
index_rebuild_count | 0
Time: 0.405 ms- phase — seq scanning heap, index scanning heap, or writing new heap
- heap_tuples_written / heap_tuples_scanned — row-level progress of the rewrite
- heap_blks_scanned — block-level scan progress
- index_rebuild_count — how many indexes have been rebuilt so far
COPY is commonly used for bulk data loads and exports. pg_stat_progress_copy lets you calculate load speed and estimate when a large import will finish — invaluable during ETL jobs and migrations.
miru_sports=# select * from pg_stat_progress_copy\gx
-[ RECORD 1 ]----+----------
pid | 9143
datid | 103879
datname | miru_sports
relid | 103880
command | COPY FROM
type | FILE
bytes_processed | 100073472
bytes_total | 137777792
tuples_processed | 3652000
tuples_excluded | 0
tuples_skipped | 0
Time: 0.164 msFrom the sample output above you can derive useful metrics in real time:
- tuples_excluded / tuples_skipped — flag any data quality issues mid-load
- type — FILE, PIPE, PROGRAM, or STDIN — useful for distinguishing load sources
Base backups can take a long time, especially for large databases or slow storage. pg_stat_progress_basebackup tells you exactly which phase the backup is in and how much data has been streamed.
pid | phase | backup_total | backup_streamed | tablespaces_total | tablespaces_streamed
-------+----------------------------------+-------------+-----------------+------------------+---------------------
83918 | waiting for checkpoint to finish | | 0 | 0 | 0
Time: 0.992 ms- phase — a long pause on "waiting for checkpoint to finish" may indicate checkpoint pressure
- backup_streamed / backup_total — bytes transferred vs. estimated total (NULL until size estimation completes)
- tablespaces_streamed — relevant for multi-tablespace databases
Stop Guessing: PostgreSQL Progress Reporting Gives You Full Visibility
PostgreSQL's progress reporting system views bring real transparency into long-running maintenance operations. By leveraging these views, DBAs can:
Facing challenges like autovacuum bloat, slow index builds, or uncertainty around backups? At Mafiree, our PostgreSQL experts leverage advanced tools like progress reporting views to deliver deep visibility and proactive monitoring as part of every managed engagement.
