PostgreSQL Autovacuum과 Lock

문제 상황

• 운영 중 PostgreSQL에서 CPU 100% 상태가 지속되며, 원인이 일반 쿼리가 아닌 wraparound 방지 autovacuum으로 확인됨
• 트래픽 증가와 백그라운드 정리 지연으로 특정 테이블의 xid_age가 누적되고, 강제 실행된 autovacuum이 문제 테이블을 처리하며 장시간 정체 발생

원인 분석

• UPDATE/DELETE로 남는 dead tuple과 트랜잭션 ID(xid) 누적이 autovacuum 처리 속도보다 빨라지며 임계값에 도달
• 일반 autovacuum은 비교적 가벼운 lock이지만, 오래 열린 트랜잭션이나 DDL과 겹치면 정리 지연과 lock 경합이 발생 가능

해결 방법

• pg_stat_activity로 실행 중 쿼리를 확인해 wraparound 방지 autovacuum 여부를 판단
• autovacuum_freeze_max_age를 일시 상향해 강제 대상에서 제외시키고, 문제 테이블을 정리한 뒤 설정을 원복

주의할 점

• wraparound 방지 모드는 프로세스 종료나 autovacuum 비활성화로 멈출 수 없음
• 오래 열린 트랜잭션은 dead tuple 정리를 막아 bloat와 성능 저하를 유발할 수 있음

적용해볼 점

• xid_age, dead tuple 비율, autovacuum 상태를 상시 모니터링
• 배포 시 DDL과 autovacuum의 lock 충돌 가능성을 고려