找出阻止 VACUUM 清除死元组的 xmin horizon 持有者

命令

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;

作用

按 xmin 年龄排序，列出可能阻碍死元组清理的四类来源：持有快照的会话、prepared transaction、复制槽，以及发送 hot_standby_feedback 的备库。

适用场景

当 VACUUM VERBOSE 报告 'dead row versions cannot be removed yet'、膨胀持续增长且 XID 年龄不下降时。

注意

最旧的 xmin 决定清理边界；复制槽和备库反馈会影响所有数据库。prepared transaction 在重启后依然存在；请核实所有者，并用 COMMIT PREPARED / ROLLBACK PREPARED '<gid>' 关闭。删除未使用的复制槽会破坏其下游（备库、CDC）；请先核实所有者。在备库上运行时，只显示该备库的会话。