주문 상태를 자주 갱신하는 테이블에서 n_dead_tup이 계속 늘어납니다. 운영 담당자가 확인한 통계에는 최근 autovacuum 실행 시각이 남아 있습니다. 같은 DB에서 보고서 SQL은 끝났는데 트랜잭션이 idle in transaction 상태로 남은 세션도 보입니다.
PostgreSQL 18을 기준으로 구성한 설명용 업무 장면입니다. 먼저 대상 테이블의 VACUUM 이력과 진행 단계를 확인합니다. 실행을 마쳤는데도 지울 수 없었던 행 버전이 로그에 남아 있다면, 그 시각에 오래된 트랜잭션이 유지되었는지 대조합니다.
VACUUM이 실행돼도 이전 행 버전이 남는 이유
PostgreSQL은 UPDATE나 DELETE가 일어나도 이전 행 버전을 즉시 지우지 않습니다. 여러 트랜잭션이 각자의 시점에 맞는 데이터를 읽도록 하는 방식인 MVCC 때문입니다. 다른 트랜잭션에서 이전 버전을 볼 가능성이 남아 있으면 일반 VACUUM은 그 버전을 보존합니다. PostgreSQL 18 — 일상적인 VACUUM 관리
장기 트랜잭션이 오래된 스냅샷을 유지하면 VACUUM이 테이블을 훑고 끝나도 일부 행 버전은 남을 수 있습니다. 스냅샷은 트랜잭션이 어느 시점의 데이터를 볼 수 있는지 정하는 기준입니다. 그래서 보고서용 읽기 세션도 오래된 스냅샷을 유지하고 있다면 점검 대상에 들어갑니다. PostgreSQL 18 — 유휴 트랜잭션 제한 시간
세션에서 트랜잭션 시작 시각과 xmin 확인
문제가 있는 테이블의 데이터베이스에 연결한 뒤, 아래 조회를 자동 커밋 상태에서 실행합니다. xact_start는 트랜잭션 시작 시각이고, state_change는 현재 상태로 바뀐 시각입니다. idle in transaction 세션에서 두 값을 비교하면 트랜잭션 전체 경과 시간과 현재 유휴 상태의 지속 시간을 구분할 수 있습니다. 다른 계정의 세션 정보를 보려면 pg_read_all_stats 등 적절한 권한이 필요합니다. PostgreSQL 18 — 누적 통계와 세션 상태
SELECT pid, usename, application_name, state,
statement_timestamp() - xact_start AS xact_elapsed,
state_change, backend_xid, backend_xmin,
age(backend_xmin) AS xmin_age_in_xids
FROM pg_stat_activity
WHERE datname = current_database()
AND backend_type = 'client backend'
AND xact_start IS NOT NULL
AND pid <> pg_backend_pid()
ORDER BY xact_start;backend_xmin은 세션이 유지하는 현재 xmin 경계입니다. age(backend_xmin)은 트랜잭션 ID 기준 나이입니다. 경과 시간은 xact_elapsed에서 확인합니다. 시작 시각이 오래됐어도 backend_xmin이 비어 있을 수 있으므로, backend_xid도 함께 봅니다. 오래된 값이 잡히면 업무명과 시각을 기록해 VACUUM 결과와 대조할 후보로 둡니다. PostgreSQL 18 — 누적 통계와 세션 상태 · PostgreSQL 18 — 일상적인 VACUUM 관리
테이블에서 VACUUM 실행과 정리 결과 대조
아래 조회에서 public.orders를 실제 스키마와 테이블 이름으로 바꿉니다. last_autovacuum과 autovacuum_count는 실행 이력을, phase는 조회 순간의 진행 단계를 보여 줍니다. n_dead_tup은 추정치이므로, 새로 쌓인 행 버전과 VACUUM이 제거하지 못한 행 버전을 구분하려면 같은 테이블의 완료 로그가 필요합니다. PostgreSQL 18 — 누적 통계와 세션 상태
SELECT s.last_autovacuum, s.autovacuum_count, s.n_dead_tup,
s.n_tup_upd, s.n_tup_del,
v.phase, v.heap_blks_scanned, v.heap_blks_total
FROM pg_stat_user_tables AS s
LEFT JOIN pg_stat_progress_vacuum AS v
ON v.relid = s.relid
AND v.datname = current_database()
WHERE s.relid = to_regclass('public.orders');| 관찰 결과 | 다음 확인 |
|---|---|
last_autovacuum 갱신이 없고 n_dead_tup 증가 | 자동 VACUUM 대상 기준과 테이블별 설정을 확인합니다. 로그가 남아 있다면 잠금 충돌로 건너뛴 기록도 찾습니다. |
phase에 진행 단계 표시 | phase와 heap_blks_scanned를 확인합니다. 블록 수는 scanning heap 단계에서만 증가하므로, 인덱스 정리 단계에서는 수치가 그대로일 수 있습니다. |
| 자동 VACUUM 완료 로그에 제거할 수 없었던 행 버전 표시 | 같은 시간대의 오래된 backend_xmin·backend_xid와 대조합니다. 뚜렷한 세션이 없으면 오래된 준비 트랜잭션과 복제 슬롯의 xmin도 확인합니다. |
phase가 비어 있으면 조회 시점에 이 뷰에서 확인할 수 있는 진행 작업이 없는 상태입니다. 두 시점의 n_tup_upd, n_tup_del 차이로 그사이에 갱신·삭제가 얼마나 발생했는지 살펴봅니다. 완료 로그가 없다면 해당 시점의 log_autovacuum_min_duration 설정과 테이블별 재정의 여부를 확인합니다. PostgreSQL 18 — VACUUM 진행 상태 · PostgreSQL 18 — autovacuum 로그 설정 · PostgreSQL 18 — 일상적인 VACUUM 관리
세션 종료 전 업무 확인과 재발 방지
후보 세션을 찾으면 application_name과 접속 계정을 바탕으로 업무 담당자를 확인합니다. 진행 중인 업무와 미커밋 변경의 영향을 확인하고, 애플리케이션이 작업 결과에 맞춰 COMMIT 또는 ROLLBACK으로 트랜잭션을 끝내도록 합니다. 이후 같은 테이블의 다음 VACUUM 결과와 n_dead_tup 추이를 다시 확인합니다. PostgreSQL 18 — 일상적인 VACUUM 관리
같은 세션이 반복해서 남으면 보고서 생성이나 외부 호출이 트랜잭션 안에서 대기하는지 살펴봅니다. idle_in_transaction_session_timeout을 적용할 때는 정상적인 대기 시간과 연결 풀의 재연결 동작을 검증한 뒤 대상 역할부터 적용합니다. 이 설정은 열린 트랜잭션에서 다음 요청을 기다리는 세션을 종료하므로, 실행 중인 긴 쿼리는 별도로 살펴야 합니다. PostgreSQL 18 — 유휴 트랜잭션 제한 시간
결론
점검 기록에는 테이블의 실행 이력, 완료 로그의 정리 결과, 같은 시간대 세션의 backend_xmin·backend_xid를 함께 남깁니다. 세션이 트랜잭션을 마친 뒤에도 같은 현상이 이어지면, 다음 VACUUM의 정리 결과를 확인하고 그 사이에 추가된 갱신·삭제량을 대조합니다.