dead tuple이 안 줄 때 xmin horizon 추적하기

한서윤 · 2026년 11월 2일 · 운영과 도구

알림 발송 대기열로 쓰는 테이블이 있었다. 워커 여러 대가 행을 집어 가서 상태를 ‘처리 중’으로, 다시 ‘완료’로 바꾸고, 하루 지난 행은 배치가 지운다. 행 수는 늘 수십만 건 안쪽에서 오르내렸다. 그런데 어느 월요일 아침 디스크 사용량 경보가 울렸다. 그 테이블이 평소의 열 배 가까이 불어 있었다.

pg_stat_user_tables를 열어 보니 n_dead_tup이 이백만을 넘었고, last_autovacuum은 몇 분 전 시각이었다. autovacuum은 부지런히 돌고 있는데 죽은 행이 그대로 남아 있었다. 손으로 VACUUM을 돌려도 숫자는 꿈쩍하지 않았다. 처음엔 autovacuum 설정이 잘못된 줄 알고 설정 파일부터 뒤졌다.

설정 문제가 아니었다. VACUUM이 게으른 게 아니라 지우고 싶어도 지울 수 없게 붙잡혀 있었다. 무엇이 붙잡고 있는지 찾아간 과정을 적어 둔다.

죽은 행이 남는 이유

PostgreSQL에서 UPDATE는 행을 제자리에서 고치지 않는다. 새 버전을 하나 더 쓰고 옛 버전에는 죽었다는 표시를 남긴다. DELETE도 표시만 한다. 옛 버전을 바로 지우지 못하는 이유는 그 변경이 일어나기 전에 시작한 트랜잭션이 아직 옛 버전을 봐야 할 수도 있어서다.

그래서 VACUUM은 일을 시작할 때 기준선을 하나 정한다. 지금 살아 있는 트랜잭션 가운데 가장 오래된 것이 볼 수 있는 시점이다. 이 기준선을 xmin horizon이라고 부른다. 기준선보다 먼저 죽은 행은 아무도 볼 일이 없으니 지우고, 기준선 뒤에 죽은 행은 남겨 둔다. 이때 중요한 점이 하나 있다. 같은 데이터베이스 안이라면 대기열 테이블을 전혀 건드리지 않는 트랜잭션도 이 기준선을 끌어내린다.

기준선을 붙잡을 수 있는 것은 생각보다 많다.

• 오래 열린 채 방치된 트랜잭션. idle in transaction 상태가 대표적이다.
• 소비자가 사라졌거나 밀린 복제 슬롯
• PREPARE TRANSACTION으로 준비만 하고 끝내지 않은 트랜잭션
• hot_standby_feedback을 켠 스탠바이에서 오래 도는 쿼리

VACUUM VERBOSE 출력 읽기

가장 먼저 할 일은 VACUUM이 무엇에 막혔는지 VACUUM에게 직접 묻는 것이다.

VACUUM (VERBOSE) notification_queue;

PostgreSQL 15부터는 출력이 이런 모양이다. 그날 본 숫자를 옮긴다.

INFO:  finished vacuuming "app.public.notification_queue": index scans: 0
pages: 0 removed, 41250 remain, 41250 scanned (100.00% of total)
tuples: 312 removed, 2544632 remain, 2364210 are dead but not yet removable
removable cutoff: 88412907, which was 1934471 XIDs old when operation ended

tuples 줄의 remain에는 아직 지우지 못한 죽은 행도 들어 있다. 이백오십만 건이 남았는데 그중 이백삼십만 건이 dead but not yet removable이다. 지울 수 있는데 안 지운 게 아니라 지우면 안 되는 행이다. 이 숫자가 크면 autovacuum 설정을 만질 때가 아니다.

다음 줄의 removable cutoff가 바로 기준선이다. 뒤에 붙은 1934471 XIDs old는 VACUUM이 끝난 시점에 기준선이 트랜잭션 번호로 이백만 가까이 뒤처져 있었다는 뜻이다. 이 숫자를 메모해 두면 범인을 찾을 때 대조할 수 있다. 14 이하에서는 같은 정보가 2364210 dead row versions cannot be removed yet, oldest xmin: 88412907처럼 한 줄로 나온다.

pg_stat_activity에서 범인 찾기

기준선을 붙잡은 세션을 찾으려고 처음 짠 쿼리는 backend_xmin이 오래된 순으로 정렬하는 것이었다. 맨 위에 API 서버 커넥션 하나가 올라왔고, 그걸 끊으려다 손을 멈췄다. 아래로 수십 개 세션이 똑같은 backend_xmin을 달고 있었기 때문이다.

이유는 이렇다. backend_xmin은 그 세션이 스냅숏을 잡을 때 본 기준선이다. 오래된 트랜잭션 하나가 열려 있으면 그 뒤에 스냅숏을 잡는 세션은 모두 같은 옛 번호를 받아 든다. 그 세션들은 피해자다. 범인은 실제로 트랜잭션 번호를 받아 놓고 끝내지 않은 쪽이고, 그 번호는 backend_xid에 찍힌다.

SELECT pid, usename, application_name, client_addr, state,
       backend_xid, backend_xmin,
       age(backend_xid)  AS xid_age,
       age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_age,
       left(query, 60)   AS last_query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY greatest(age(backend_xid), age(backend_xmin)) DESC;

backend_xid를 같이 보자 범인이 바로 나왔다. 분석용 GUI 도구에서 붙은 세션이었고 상태는 idle in transaction, xact_start는 금요일 오후였다. 자동 커밋을 끈 채 UPDATE 한 줄을 실행하고 창을 닫지 않고 퇴근한 것이다. 사흘 동안 열려 있던 그 트랜잭션 하나가 대기열 테이블의 죽은 행 이백만 건을 붙잡고 있었다. 허탈했다. 번호도 VACUUM이 알려 준 removable cutoff와 정확히 맞았다.

세션이 깨끗한데도 줄지 않을 때

몇 달 뒤 같은 증상이 다시 나왔는데, 이번엔 위 쿼리에서 수상한 세션이 보이지 않았다. 다만 backend_type이 walsender인 행 하나가 오래된 backend_xmin을 들고 있었다. 복제 연결이 기준선을 붙잡은 것이다. 스탠바이에는 hot_standby_feedback이 켜져 있었고, 그 위에서 월말 정산 리포트 쿼리가 몇 시간째 돌고 있었다. 이 설정을 켜면 스탠바이의 기준선이 프라이머리로 전달된다. 공식 문서도 프라이머리에서 죽은 행 정리가 늦어져 테이블이 불어날 수 있다고 적어 두었다.

세션 쪽이 깨끗하면 나머지 셋을 차례로 본다.

-- 스탠바이가 보내 온 기준선
SELECT application_name, client_addr, backend_xmin, age(backend_xmin)
FROM pg_stat_replication;

-- 복제 슬롯
SELECT slot_name, slot_type, active, xmin, catalog_xmin,
       age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots;

-- 끝나지 않은 prepared transaction
SELECT gid, prepared, owner, database, age(transaction)
FROM pg_prepared_xacts;

복제 슬롯은 소비자가 사라져도 스스로 지워지지 않는다. 테스트하려고 만든 논리 복제 슬롯을 잊고 지내다 같은 일을 겪을 수도 있다. active가 false인데 age가 계속 커지는 슬롯은 한번 의심해 볼 만하다.

기준선을 풀고 나서 할 일

범인이 세션이면 SELECT pg_terminate_backend(pid);로 끊는다. 슬롯이면 쓰지 않는 게 확실한지 확인한 뒤 pg_drop_replication_slot으로 지우고, prepared transaction이면 ROLLBACK PREPARED로 정리한다. 그다음 VACUUM VERBOSE를 다시 돌려 dead but not yet removable이 0 근처로 내려오는지 본다. 그날은 숫자가 한 번에 내려가는 걸 보고서야 숨을 돌렸다.

기준선이 묶여 있을 때 VACUUM FULL은 답이 아니다. 아직 누군가 볼 수 있는 행은 VACUUM FULL도 새 파일로 그대로 옮겨 쓴다. 그러면서 테이블 전체에 배타 잠금을 건다. 기준선부터 풀어야 한다. 또 일반 VACUUM은 정리한 공간을 대개 운영체제에 돌려주지 않고 테이블 안에서 다시 쓴다. 파일 크기가 바로 줄지 않는다고 실패한 것은 아니다. n_dead_tup과 VERBOSE 출력이 먼저 확인할 지표다.

지금 쓰는 방식

그 뒤로 사람이 붙는 계정에는 idle_in_transaction_session_timeout을 걸어 둔다. 공식 문서도 이 설정을 설명하면서 열린 트랜잭션이 죽은 행 정리를 막아 테이블을 불릴 수 있다고 적고 있다.

ALTER ROLE analyst SET idle_in_transaction_session_timeout = '15min';

애플리케이션 계정은 커넥션 풀 동작과 맞춰 봐야 해서 값을 더 신중하게 고른다. 모니터링에는 위의 pg_stat_activity 쿼리에서 age가 가장 큰 값 하나만 뽑아 수치로 쌓는다. 디스크가 불어난 뒤에 알게 되는 것보다 기준선이 뒤처지기 시작할 때 알게 되는 편이 훨씬 싸다. 서버 부하를 따질 때는 pg_stat_statements로 무거운 쿼리를 찾는 일이 먼저지만, 테이블이 이유 없이 커지면 이제는 쿼리보다 기준선부터 본다.

습관도 하나 생겼다. dead tuple이 안 줄어든다는 말을 들으면 autovacuum 설정을 열기 전에 VACUUM VERBOSE부터 한 번 돌린다. dead but not yet removable이 0이면 VACUUM 쪽 문제고, 크면 어딘가에 끝나지 않은 트랜잭션이 있다. 그 한 줄이 어디를 봐야 할지 거의 알려 준다.