Autovacuum running for hours — diagnosing and intervening

An autovacuum worker has been running on a single table for several hours, blocking DDL (ALTER TABLE, CREATE INDEX CONCURRENTLY) or simply consuming I/O for an uncomfortably long time.

Diagnose it

-- Autovacuum workers and their progress:
SELECT p.pid,
       p.datname,
       p.phase,
       p.heap_blks_total,
       p.heap_blks_vacuumed,
       round(100.0 * p.heap_blks_vacuumed
             / NULLIF(p.heap_blks_total, 0), 1)    AS pct_done,
       p.index_vacuum_count,
       p.dead_tuple_bytes,
       p.num_dead_item_ids,
       now() - a.query_start                       AS running_for,
       n.nspname || '.' || c.relname               AS table_name
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity        a ON a.pid = p.pid
JOIN pg_class                c ON c.oid = p.relid
JOIN pg_namespace            n ON n.oid = c.relnamespace
WHERE a.backend_type = 'autovacuum worker';

Note: num_dead_item_ids and dead_tuple_bytes
require PostgreSQL 16 or later. On earlier releases those columns are absent.

Why it happens

Autovacuum workers sleep between pages when autovacuum_vacuum_cost_delay
is set (the default is non-zero), making them intentionally slow to avoid saturating I/O.
A table with millions of dead tuples from a large batch delete, or one that has not been
vacuumed for months, can keep a worker busy for hours even with the throttle.
The worker holds a ShareUpdateExclusiveLock on the table — it does not block
reads or normal writes, but it does block DDL and VACUUM FULL.