PostgreSQL VACUUM·오토배큠 튜닝: 테이블 부풀림과 트랜잭션 ID 소진 막기

디스크 사용량이 이유 없이 계속 늘어난다. 데이터는 몇 달째 비슷한 규모인데 테이블 물리 크기만 부풀어 오르고, 어제까지 멀쩡하던 조회 쿼리가 점점 느려진다. 로그를 뒤져 보면 autovacuum 관련 경고가 쌓여 있고, 심하면 database is not accepting commands to avoid wraparound data loss 같은 무시무시한 메시지까지 등장한다. 이 증상들은 대부분 한 뿌리에서 나온다. 죽은 튜플(dead tuple)이 제때 청소되지 않아 테이블 부풀림(bloat)이 쌓이고, 그 결과 트랜잭션 ID 소진(wraparound) 위험까지 번지는 상황이다.

PostgreSQL의 이 문제는 버그가 아니라 MVCC(다중 버전 동시성 제어)라는 설계의 필연적인 부산물이다. 원리를 이해하면 VACUUM과 오토배큠은 두려운 대상이 아니라 튜닝 가능한 도구가 된다. 이 글에서는 dead tuple이 생기는 원리, bloat 측정법, VACUUM과 VACUUM FULL의 차이, autovacuum 파라미터 튜닝, 트랜잭션 ID wraparound 방어, 그리고 실무 모니터링 쿼리까지 실전 관점에서 다룬다.

MVCC와 dead tuple: 왜 지우면 안 지워지는가

PostgreSQL은 UPDATE나 DELETE를 해도 기존 행을 즉시 물리적으로 지우지 않는다. 대신 각 행 버전에 트랜잭션 ID(xmin, xmax)를 붙여 “이 시점 이후로는 죽었다”고 표시만 한다. 여러 트랜잭션이 서로 다른 스냅샷을 보면서도 락 없이 읽을 수 있는 이유가 바로 이 다중 버전 덕분이다. 대신 대가가 있다. 아무도 더는 볼 수 없게 된 옛 버전, 즉 dead tuple이 테이블 파일 안에 그대로 남는다.

• UPDATE 한 번은 새 버전을 추가하고 옛 버전을 dead로 만든다 — 즉 dead tuple 1개가 생긴다
• DELETE는 새 버전 없이 기존 버전만 dead로 표시한다
• dead tuple은 어떤 살아있는 트랜잭션도 참조하지 않게 된 순간 회수 가능해진다
• 이 회수 작업을 하는 것이 바로 VACUUM이다

VACUUM이 하는 일은 dead tuple이 차지하던 공간을 테이블 내부에서 재사용 가능한 상태로 표시하는 것이다. 주의할 점은, 일반 VACUUM은 그 공간을 운영체제에 돌려주지 않는다. 이후 INSERT가 그 자리를 다시 쓸 뿐이다. 갱신·삭제가 잦은 테이블에서 VACUUM이 뒤처지면 dead tuple이 계속 쌓여 테이블이 물리적으로 부풀고, 이것이 우리가 겪는 bloat다.

bloat 측정: 감이 아니라 숫자로

“느려진 것 같다”는 감이 아니라 실제 dead tuple 비율을 봐야 한다. 가장 간단한 방법은 통계 뷰 pg_stat_user_tables를 조회해 살아있는 튜플 대비 죽은 튜플 비율을 계산하는 것이다.

-- dead tuple 비율이 높은 테이블 상위 20개
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC
LIMIT 20;

dead_pct가 20%를 넘어가면 청소가 뒤처지고 있다는 신호다. 다만 통계 뷰의 숫자는 추정치라 정확한 물리 bloat를 알려면 pgstattuple 확장을 쓴다. 이 확장은 테이블을 실제로 훑어 살아있는 데이터와 낭비 공간의 비율을 정밀하게 보고한다.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- orders 테이블의 실제 낭비 공간 비율(%) 확인
SELECT
    table_len,
    tuple_percent,          -- 살아있는 튜플이 차지하는 비율
    dead_tuple_percent,     -- dead tuple 비율
    free_percent            -- 비어 있는(재사용 가능) 비율
FROM pgstattuple('orders');

pgstattuple은 전체 테이블을 스캔하므로 대형 테이블에서는 부하가 크다. 큰 테이블에는 샘플링 방식인 pgstattuple_approx('orders')를 쓰면 훨씬 빠르게 근사치를 얻을 수 있다. bloat를 확인했다면 이제 어떻게 되돌릴지가 문제다.

VACUUM과 VACUUM FULL: 결정적 차이는 락

둘은 이름이 비슷하지만 완전히 다른 도구다. 이 차이를 모르고 운영 중에 VACUUM FULL을 실행하면 서비스가 멈춘다.

• VACUUM: dead tuple 공간을 테이블 내부에서 재사용 가능하게 표시한다. SHARE UPDATE EXCLUSIVE라는 약한 락만 잡아 읽기·쓰기와 동시 실행된다. 디스크는 운영체제에 반환되지 않는다.
• VACUUM FULL: 테이블을 통째로 새로 다시 써서 물리적으로 압축한다. 디스크를 운영체제에 반환하지만, ACCESS EXCLUSIVE 락을 잡아 작업 내내 해당 테이블에 대한 모든 읽기·쓰기가 완전히 차단된다.

-- 일상 운영: 온라인으로 안전하게 dead tuple 회수
VACUUM (VERBOSE, ANALYZE) orders;

-- 물리 압축이 꼭 필요할 때(점검 창에서만!): 테이블 전체 락
VACUUM FULL orders;

이미 심각하게 부푼 테이블의 디스크를 실제로 되찾아야 한다면 VACUUM FULL 대신 pg_repack 확장을 권한다. pg_repack은 백그라운드에서 테이블을 재구성하면서도 짧은 순간만 락을 잡아, 서비스를 멈추지 않고 물리 압축을 해낸다. 핵심 원칙은 하나다. 운영 중인 서비스에서 VACUUM FULL은 금지이고, 평소에는 dead tuple이 애초에 쌓이지 않도록 autovacuum을 잘 돌리는 것이 정답이다.

autovacuum 동작 원리와 파라미터 튜닝

수동 VACUUM을 사람이 챙길 수는 없다. 그래서 PostgreSQL은 백그라운드에서 autovacuum 런처가 주기적으로 깨어나, dead tuple이 임계치를 넘긴 테이블에 워커를 붙여 자동으로 VACUUM한다. 테이블별 발동 임계치는 다음 공식으로 결정된다.

-- autovacuum 발동 임계 dead tuple 수 =
--   autovacuum_vacuum_threshold
--   + autovacuum_vacuum_scale_factor * (테이블의 살아있는 튜플 수)
--
-- 기본값: threshold=50, scale_factor=0.2 (즉 전체의 20%)

기본값 scale_factor = 0.2는 작은 테이블에는 적절하지만 큰 테이블에는 치명적이다. 1억 행짜리 테이블이라면 dead tuple이 2천만 개나 쌓여야 비로소 autovacuum이 발동한다. 그 사이 테이블은 이미 심하게 부풀어 있다. 따라서 큰 테이블일수록 scale_factor를 낮춰 더 자주 청소하게 만든다. 전역 설정이 아니라 테이블 단위로 오버라이드하는 것이 실무의 정석이다.

-- 갱신이 잦은 대형 테이블: 비율 대신 절대치 기준으로 자주 청소
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- 20% → 1%
    autovacuum_vacuum_threshold    = 5000,   -- 절대 하한도 조정
    autovacuum_vacuum_cost_limit   = 2000    -- 이 테이블만 더 공격적으로
);

전역 파라미터도 함께 봐야 한다. 다음은 postgresql.conf에서 조정하는 핵심 항목이다.

# postgresql.conf — autovacuum 전역 튜닝 예시
autovacuum_naptime = 15s                 # 런처가 깨어나는 주기(기본 1min)

autovacuum_max_workers = 5               # 동시 청소 워커 수(기본 3)

autovacuum_vacuum_cost_limit = 2000      # 청소 속도 상한(기본 200, 클수록 빠름)

autovacuum_vacuum_cost_delay = 2ms       # I/O 부하 조절용 지연(기본 2ms)

여기서 가장 오해가 많은 것이 autovacuum_vacuum_cost_limit이다. autovacuum은 I/O 폭주를 막으려 일부러 비용 기반으로 스스로 속도를 제한한다. 청소가 계속 뒤처진다면 이 값을 올려 워커가 더 빠르게 일하게 해야 한다. naptime을 줄이면 자주 깨어나고, max_workers를 늘리면 여러 테이블을 동시에 청소한다. 다만 워커를 무작정 늘리면 각 워커가 나눠 갖는 cost budget이 줄어 개별 속도가 오히려 느려질 수 있으니, 워커 수와 cost_limit은 함께 조정한다.

트랜잭션 ID wraparound: 반드시 막아야 하는 재앙

bloat는 성능 문제지만, 트랜잭션 ID wraparound는 데이터베이스가 멈추는 가용성 문제다. PostgreSQL의 트랜잭션 ID는 약 42억(2^32)개로 유한하며 순환한다. 각 행 버전은 자신을 만든 트랜잭션 ID를 기록하는데, ID가 한 바퀴 돌면 “과거”와 “미래”가 뒤섞여 오래된 데이터가 갑자기 안 보이게 되는 재앙이 발생할 수 있다.

이를 막기 위해 VACUUM은 dead tuple 청소뿐 아니라, 충분히 오래된 살아있는 행 버전을 freeze 처리한다. freeze된 행은 “영원한 과거”로 표시되어 wraparound의 영향을 받지 않는다. autovacuum은 테이블의 가장 오래된 트랜잭션 나이가 autovacuum_freeze_max_age(기본 2억)에 다다르면, dead tuple이 없어도 강제로 freeze용 VACUUM(anti-wraparound vacuum)을 실행한다.

-- 각 데이터베이스의 wraparound까지 남은 여유 확인
-- age가 클수록 위험하다(2억 접근 시 강제 vacuum 발동)
SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    2^31 - age(datfrozenxid) AS remaining_before_wraparound
FROM pg_database
ORDER BY xid_age DESC;

-- 테이블별로 어떤 게 freeze가 급한지
SELECT
    relname,
    age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY xid_age DESC
LIMIT 20;

anti-wraparound vacuum은 취소할 수 없고 미룰수록 위험해지므로, 절대 방치하면 안 된다. 대량 갱신 테이블에서는 freeze가 몰려 한꺼번에 무거운 vacuum이 도는 것을 막기 위해 vacuum_freeze_min_age를 조정하기도 한다. 핵심은 xid_age가 위험 수위에 이르기 전에 autovacuum이 꾸준히 돌고 있는지를 상시 모니터링하는 것이다.

모니터링과 long-running 트랜잭션 함정

튜닝의 마지막은 감시다. autovacuum이 실제로 각 테이블을 언제 돌았는지, 뒤처진 테이블은 없는지 정기적으로 확인해야 한다.

-- 청소가 오래 안 된, 그러면서 dead tuple이 많은 위험 테이블
SELECT
    relname,
    n_dead_tup,
    n_live_tup,
    last_autovacuum,
    last_autoanalyze,
    now() - last_autovacuum AS since_last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY last_autovacuum ASC NULLS FIRST
LIMIT 20;

그런데 파라미터를 아무리 공격적으로 잡아도 VACUUM이 전혀 진전하지 못하는 경우가 있다. 원인은 대부분 오래 살아있는 트랜잭션(long-running transaction)이다. VACUUM은 “현재 실행 중인 가장 오래된 트랜잭션보다 나중에 죽은 튜플”은 회수할 수 없다. 누군가 커밋도 롤백도 안 한 채 몇 시간째 트랜잭션을 열어 두면, 그 시점 이후의 모든 dead tuple이 회수 불가 상태로 묶여 bloat가 폭증한다. 흔한 주범이 idle in transaction 상태의 커넥션이다.

-- 오래 열려 있어 VACUUM을 막는 트랜잭션 색출
SELECT
    pid,
    state,
    now() - xact_start AS xact_age,
    now() - state_change AS idle_age,
    left(query, 80) AS query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start IS NOT NULL
ORDER BY xact_start ASC
LIMIT 20;

이런 커넥션을 방치하지 않으려면 idle_in_transaction_session_timeout = '10min' 같은 설정으로 방치된 트랜잭션을 자동 종료하고, 애플리케이션이 트랜잭션을 열어 둔 채 무거운 외부 호출을 하지 않도록 코드 레벨에서 통제해야 한다. 아무리 autovacuum을 잘 튜닝해도, 이 함정 하나면 모든 노력이 무력화된다.

마무리

PostgreSQL의 bloat와 wraparound는 MVCC라는 설계에서 필연적으로 따라오는 관리 과제일 뿐, 결함이 아니다. dead tuple이 왜 생기는지 이해하고, pg_stat_user_tables와 pgstattuple로 상태를 숫자로 측정하고, 큰 테이블은 autovacuum_vacuum_scale_factor를 낮춰 자주 청소하고, cost_limit·naptime·max_workers로 청소 속도를 서비스 부하에 맞게 조율하면 된다. 운영 중 VACUUM FULL은 금물이고, 물리 압축이 필요하면 pg_repack을 쓴다. wraparound는 age(datfrozenxid)를 상시 감시해 위험 수위 전에 대응하고, long-running 트랜잭션이 VACUUM을 막지 않도록 타임아웃으로 통제한다. 결국 VACUUM 튜닝의 본질은 “청소가 갱신 속도를 따라잡게 만드는 것”이다. 청소가 뒤처지지 않는 한 bloat도, wraparound도 문제가 되지 않는다.

자주 묻는 질문

Q. autovacuum이 성능에 부담을 주는데 그냥 꺼도 되나요?
A. 절대 끄면 안 됩니다. autovacuum을 끄면 dead tuple이 무한정 쌓여 bloat가 폭증하고, 더 위험하게는 트랜잭션 ID wraparound를 막지 못해 데이터베이스가 명령 수신을 거부하는 상태에 빠질 수 있습니다. 부담이 문제라면 끄는 것이 아니라 cost_delay와 cost_limit으로 청소 속도를 조절하거나, 부하가 적은 시간대에 더 공격적으로 돌도록 조율하는 것이 맞습니다.

Q. VACUUM을 돌렸는데도 디스크 사용량이 그대로예요. 왜죠?
A. 정상입니다. 일반 VACUUM은 dead tuple 공간을 테이블 내부에서 재사용 가능하게 표시할 뿐, 운영체제에 디스크를 반환하지 않습니다. 회수된 공간은 이후 INSERT가 다시 채웁니다. 물리적으로 디스크를 되찾으려면 VACUUM FULL(전체 락)이나 pg_repack(온라인 재구성)을 써야 하며, 평소에는 bloat가 애초에 쌓이지 않도록 autovacuum을 잘 돌리는 것이 더 근본적인 해법입니다.

Q. 대량 배치로 수백만 행을 DELETE했는데 이후 조회가 느려졌어요.
A. 대량 삭제는 그만큼의 dead tuple을 한꺼번에 만들어 냅니다. autovacuum이 이를 따라잡기 전까지는 테이블에 죽은 행이 가득해 조회가 느려집니다. 배치 삭제 직후에는 해당 테이블에 수동으로 VACUUM (ANALYZE) 테이블명;을 실행해 dead tuple을 즉시 회수하고 통계를 갱신하는 것이 좋습니다. 반복되는 대량 삭제 대상이라면 파티셔닝 후 파티션을 DROP하는 방식이 VACUUM보다 훨씬 효율적입니다.