DBAtlas · Database operations atlas

Find the xmin horizon holders that stop VACUUM from removing dead tuples

PostgreSQLMaintenance

Command

SELECT 'session' AS source, pid::text AS id, usename::text AS owner, state AS detail,
       age(backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL AND pid <> pg_backend_pid()
UNION ALL
SELECT 'prepared_xact', gid, owner::text, prepared::text, age(transaction)
FROM pg_prepared_xacts
UNION ALL
SELECT 'replication_slot', slot_name::text, slot_type, active::text,
       greatest(age(xmin), age(catalog_xmin))
FROM pg_replication_slots
WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL
UNION ALL
SELECT 'standby_feedback', application_name, usename::text, state, age(backend_xmin)
FROM pg_stat_replication
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC NULLS LAST;

What it does

Lists, ordered by xmin age, the four sources that can block dead tuple cleanup: sessions holding snapshots, prepared transactions, replication slots, and standbys sending hot_standby_feedback.

When to use it

When VACUUM VERBOSE reports 'dead row versions cannot be removed yet', bloat keeps growing and the XID age does not drop.

Caution

The oldest xmin defines the cleanup horizon; replication slots and standby feedback affect all databases. Prepared transactions survive restarts; verify the owner and close them with COMMIT PREPARED / ROLLBACK PREPARED '<gid>'. Dropping an unused slot breaks its downstream (standby, CDC); verify the owner first. Run on a standby, it shows only that standby's sessions.

Open in DBAtlas →

Related