[PostgreSQL][Autovacuum]VACUUM FULL 전에 dead tuples와 autovacuum부터 보는 이유

IT 실무 리서치

[PostgreSQL][Autovacuum]VACUUM FULL 전에 dead tuples와 autovacuum부터 보는 이유

PostgreSQL 테이블이 커졌다고 바로 VACUUM FULL을 실행하면 운영 잠금 문제가 생길 수 있다. dead tuples, last_autovacuum, long transaction, lock 상태를 먼저 확인하고 일반 VACUUM/ANALYZE와 설정 조정부터 검토해야 한다.

목차

1. 개요
2. VACUUM FULL 전에 보는 통계
3. dead tuples 쿼리
4. 오래 열린 트랜잭션 찾기
5. 실행 순서
6. 결론
7. 참고 링크

1. 개요

테이블 크기가 커지면 VACUUM FULL로 바로 줄이고 싶어진다. 하지만 VACUUM FULL은 테이블 잠금 영향이 크고, dead tuple이 생긴 원인을 해결하지 않으면 같은 문제가 다시 생긴다.

먼저 `pg_stat_user_tables`에서 dead tuples와 last_autovacuum을 보고, 오래 열린 transaction이 vacuum을 막고 있는지 확인한다. 그 다음 일반 VACUUM/ANALYZE, autovacuum 설정 조정, 점검 시간의 VACUUM FULL 순서로 판단한다.

2. VACUUM FULL 전에 보는 통계

공식 문서는 VACUUM의 목적과 autovacuum의 기본 동작을 이해하는 데 쓴다. 실제 판단은 통계 뷰와 현재 transaction 상태에서 시작한다.

PostgreSQL routine vacuuming 문서에서 autovacuum과 일반 VACUUM의 역할을 확인한다.

문서를 읽은 뒤 바로 VACUUM FULL을 실행하지 않는다. 먼저 어떤 테이블에서 dead tuples가 쌓였는지 확인한다.

아래 표는 VACUUM FULL 전에 확인할 최소 지표다. 한 지표만 보고 명령을 실행하지 말고 함께 본다.

박스가 있는 n_dead_tup을 먼저 보되, last_autovacuum과 transaction 상태를 같이 봐야 한다.

n_dead_tup이 높아도 오래 열린 transaction이 있으면 반복 vacuum으로 해결되지 않을 수 있다. 이때는 원인 transaction을 먼저 찾아야 한다.

3. dead tuples 쿼리

vacuum-diagnosis.sql

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

운영 DB에서 실행할 때는 읽기 쿼리부터 시작한다. 쿼리 결과에 사용자 정보나 내부 SQL 원문이 포함되면 공개 캡처 전에 마스킹한다.

실행 결과에서는 dead tuple 숫자와 last_autovacuum을 같이 본다. 아래 박스 줄처럼 autovacuum 이력이 없거나 오래됐으면 설정과 transaction 상태를 같이 확인한다.

event_logs의 last_autovacuum이 NULL이고 long transaction이 있으면 VACUUM FULL보다 원인 확인이 먼저다.

long transaction이 오래 열려 있으면 dead tuple 회수가 막힐 수 있다. 이 상태에서 VACUUM 명령만 반복하면 원인 해결 없이 부하만 늘 수 있다.

4. 오래 열린 트랜잭션 찾기

트랜잭션 화면에서는 `idle in transaction`을 특히 본다. 박스가 있는 줄처럼 오래 열린 세션은 애플리케이션 커넥션 관리 문제일 수 있다.

idle in transaction이 오래 남아 있으면 vacuum이 회수할 수 있는 범위가 제한될 수 있다.

세션을 종료하기 전에는 애플리케이션 담당자와 영향 범위를 확인한다. 무작정 kill하면 사용자 요청이나 배치 작업이 중간에 실패할 수 있다.

5. 실행 순서

아래 흐름에서 중요한 점은 VACUUM FULL이 첫 번째가 아니라는 것이다. 화살표 순서대로 확인하면 운영 잠금 위험을 줄일 수 있다.

VACUUM FULL은 마지막에 검토한다. 먼저 회수 방해 요소와 autovacuum 설정을 본다.

디스크 공간을 즉시 줄여야 하는 특별한 상황이 아니라면 일반 VACUUM/ANALYZE와 autovacuum 설정 조정으로 재발을 줄이는 편이 낫다.

6. 결론

VACUUM FULL은 용량을 줄일 수 있지만 첫 번째 조치로 두기에는 운영 영향이 크다.

dead tuples, last_autovacuum, 오래 열린 transaction, lock 상태를 먼저 확인하고 일반 VACUUM/ANALYZE와 설정 조정부터 검토해야 같은 문제가 반복되지 않는다.

7. 참고 링크

1. https://www.postgresql.org/docs/current/routine-vacuuming.html
2. https://www.postgresql.org/docs/current/sql-vacuum.html
3. https://www.postgresql.org/docs/current/monitoring-stats.html