VACUUM의 dead tuple 제거를 막는 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;

하는 일

dead tuple 정리를 막을 수 있는 네 가지 원인을 xmin age 순으로 나열한다: snapshot을 보유한 세션, prepared transaction, replication slot, hot_standby_feedback을 보내는 standby.

사용 시점

VACUUM VERBOSE가 'dead row versions cannot be removed yet'을 보고하고, bloat이 계속 증가하며 XID age가 줄지 않을 때.

주의

가장 오래된 xmin이 정리 기준선을 결정한다. replication slot과 standby feedback은 모든 데이터베이스에 영향을 준다. prepared transaction은 재시작 후에도 남으므로 소유자를 확인하고 COMMIT PREPARED / ROLLBACK PREPARED '<gid>'로 닫는다. 사용하지 않는 slot을 삭제하면 하위 시스템(standby, CDC)이 깨지므로 먼저 소유자를 확인한다. standby에서 실행하면 해당 standby의 세션만 보여 준다.