[PostgreSQL] 긴 트랜잭션 열어둔 채 방치하면 VACUUM이 죽은 튜플을 못 지운다

설치·접속: PostgreSQL 설치와 접속

부제: 애플리케이션이 BEGIN만 해놓고 커밋을 깜빡한 커넥션 하나 때문에, 테이블이 계속 부풀고 autovacuum이 헛도는 상황

-- 세션 A: 트랜잭션을 열고 스냅샷만 잡은 채 방치 (idle in transaction)
BEGIN;
SELECT 1;          -- 여기서 스냅샷 획득 -> 커밋 전까지 계속 붙들고 있음
-- (커밋을 잊고 커넥션이 살아있음)

-- 그 사이 다른 세션이 같은 테이블을 대량 UPDATE -> 죽은 튜플(dead tuple) 발생
UPDATE m7_bloat SET v = v + 1;   -- 1000행 갱신 = 1000개 dead tuple
VACUUM (VERBOSE) m7_bloat;

--- 오래된 스냅샷을 붙든 백엔드 (state=active로 pg_sleep 중) ---
  pid  | state  | xmin_age | open_for |        query
-------+--------+----------+----------+----------------------
 10488 | active |       10 | 00:00:01 | SELECT pg_sleep(4);

--- 긴 트랜잭션이 열린 채로 VACUUM (dead tuple 못 지움) ---
tuples: 0 removed, 2000 remain, 1000 are dead but not yet removable
removable cutoff: 944, which was 15 XIDs old when operation ended

--- 긴 트랜잭션 종료 후 다시 VACUUM (이제 회수됨) ---
tuples: 1000 removed, 1000 remain, 0 are dead but not yet removable
index scan needed: 5 pages ... had 1000 dead item identifiers removed

VACUUM은 "지금 열려 있는 가장 오래된 트랜잭션이 아직 볼지도 모르는" 죽은 튜플은 지우지 못한다. 세션 A가 트랜잭션을 열어 스냅샷(오래된 xmin)을 붙들고 있으면, 그 뒤에 생긴 dead tuple 1000개가 전부 dead but not yet removable로 남아 회수되지 않는다. A가 커밋하는 순간 removable cutoff가 앞으로 당겨져 그제서야 1000개가 정리된다. 방치된 긴 트랜잭션(특히 idle in transaction) 하나가 테이블 bloat, 인덱스 팽창, 그리고 최악의 경우 transaction ID wraparound 위험까지 부른다. pg_stat_activity의 backend_xmin과 xact_start로 범인을 찾고, idle_in_transaction_session_timeout으로 이런 좀비 트랜잭션을 자동으로 끊어야 한다.

이렇게도 쓴다

가장 오래 열려 있는 트랜잭션부터 찾아낸다. (조합: pg_stat_activity)

SELECT pid, state, age(backend_xmin) AS xmin_age, now()-xact_start AS open_for
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL ORDER BY xact_start LIMIT 5;

 

idle 상태로 방치된 트랜잭션을 자동으로 끊는다. (조합: idle_in_transaction_session_timeout)

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

 

wraparound가 얼마나 임박했는지 DB 단위로 확인한다. (조합: datfrozenxid)

SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC;

 

특정 테이블의 죽은 튜플이 얼마나 쌓였는지 본다. (조합: pg_stat_user_tables)

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;

 

범인 세션을 강제로 끊는다. (조합: pg_terminate_backend)

SELECT pg_terminate_backend(10488);   -- 위에서 찾은 pid

 

wraparound를 막기 위해 공격적으로 동결시킨다. (조합: VACUUM FREEZE)

VACUUM (FREEZE, VERBOSE) m7_bloat;