Track Running VACUUM Progress in PostgreSQL
Shows which phase each running vacuum is in and how far it has progressed. Helps estimate when a long vacuum will finish.
SELECT p.pid, p.datname, p.relid::regclass AS table_name, p.phase,
p.heap_blks_total, p.heap_blks_scanned,
round(100.0 * p.heap_blks_scanned / NULLIF(p.heap_blks_total, 0), 1) AS pct_scanned,
now() - a.xact_start AS running_for,
left(a.query, 80) AS query
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a USING (pid);Verify in a staging environment prior to running in production. This is a read-only diagnostic query; it does not acquire exclusive locks.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 9.6).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Engineering Triad: Related Runbooks & Incident Response
Which production incidents and emergency drills execute this script as a diagnosis step?
PostgreSQL Transaction ID Wraparound Emergency: Freeze Plan
Steps to save a database from going read-only when XID age reaches a critical level. Clears blockers and runs VACUUM FREEZE in the right order.
PostgreSQL Bloat and Autovacuum Tuning
Diagnoses dead tuple growth, evaluates autovacuum triggers and uses safe per-table tuning to prevent recurring bloat.
Other POSTGRESQL Diagnostic Scripts
View All (100)Find Long-Running Queries and Idle-in-Transaction Sessions in PostgreSQL
Lists queries running longer than a minute, their wait events, and idle-in-transaction sessions that block vacuum. The first script to run during a performance incident.
PostgreSQL Lock Chain: Who Is Blocking Whom?
Uses pg_blocking_pids to pair blocked and blocking sessions with their queries and wait times. Shows the root cause of a lock chain in seconds.
PostgreSQL Dead Tuples, Bloat and Autovacuum Health Check
Shows dead tuple ratio and last vacuum/analyze times per table. Helps you find tables where autovacuum settings are not keeping up.
Find Unused Indexes in PostgreSQL
Lists never-scanned indexes that are neither unique nor primary keys, with their sizes. Surfaces cleanup candidates to cut write overhead and disk usage.