Scenario
VACUUM FULL or CLUSTER rewrites the entire table — both require an exclusive lock and can run for hours on large tables. pg_stat_progress_cluster shows the phase and estimated completion. Diagnose it -- Available since PostgreSQL 12:…
Investigation Path
VACUUM FULL or CLUSTER rewrites the entire table — both require an exclusive lock and can run for hours on large tables. pg_stat_progress_cluster shows the phase and estimated completion.
Diagnose it
-- Available since PostgreSQL 12:
SELECT p.pid,
p.datname,
p.command, -- 'VACUUM FULL' or 'CLUSTER'
p.phase,
n.nspname || '.' || c.relname AS table_name,
p.heap_blks_total,
p.heap_blks_scanned,
p.heap_tuples_scanned,
p.heap_tuples_written,
round(
100.0 * p.heap_tuples_written
/ NULLIF(p.heap_tuples_scanned, 0),
1) AS pct_written,
p.index_rebuild_count
FROM pg_stat_progress_cluster p
JOIN pg_class c ON c.oid = p.relid
JOIN pg_namespace n ON n.oid = c.relnamespace;Phases: initializing → seq scanning heap → index scanning heap → sorting tuples →
writing new heap → swapping relation files → rebuilding index → performing final cleanup.
Why it happens
VACUUM FULL creates a new copy of the table, copies all live tuples, rebuilds
all indexes, then swaps the files. The exclusive lock is held throughout. Unlike regular
VACUUM, it returns space to the OS but requires 2× the table size in disk space temporarily
and blocks all reads and writes for its duration.
How to fix it
If a VACUUM FULL is blocking production access and must be cancelled, use
pg_cancel_backend(pid) — PostgreSQL will clean up the partial work safely.
For a no-lock alternative, consider pg_repack which rebuilds tables concurrently
(third-party extension — test in staging first).
Prevent it next time
Avoid VACUUM FULL in production during business hours. Instead, use regular
VACUUM tuned to run frequently enough that bloat never accumulates to the point requiring
a full rewrite. Reserve VACUUM FULL for post-migration cleanup or one-time
bulk delete operations during planned maintenance windows.
Related & next steps
Career Impact
This scenario builds production judgment and operational confidence under pressure.