Contents
see List왜 Autovacuum 점검이 필요한가
PostgreSQL은 MVCC 구조를 사용하므로 UPDATE와 DELETE가 발생해도 기존 행을 즉시 덮어쓰거나 제거하지 않습니다. 기존 버전은 dead tuple로 남고, 이후 VACUUM이 이를 정리해야 같은 공간을 다시 사용할 수 있습니다. 이 과정이 밀리면 테이블과 인덱스가 실제 데이터보다 커지고, 실행 계획이 부정확해지며, 단순 조회도 디스크 읽기를 많이 발생시킵니다. 운영 환경에서 갑자기 쿼리가 느려졌는데 애플리케이션 코드는 그대로라면, 인덱스보다 먼저 dead tuple, 통계 최신성, autovacuum 동작 여부를 확인해야 합니다.
Autovacuum은 기본값만으로도 대부분의 작은 테이블에는 충분합니다. 문제는 주문, 로그, 알림, 세션, 이벤트처럼 쓰기가 집중되는 테이블입니다. 이런 테이블은 기본 scale factor 기준으로는 VACUUM이 너무 늦게 시작될 수 있습니다. 예를 들어 5천만 행 테이블에서 기본 비율이 적용되면 수백만 개의 변경 행이 쌓인 뒤에야 정리가 시작될 수 있습니다. 이 문서는 운영자가 바로 실행할 수 있는 조회 SQL과 조정 순서 중심으로 정리합니다.
현재 상태를 먼저 숫자로 확인하기
가장 먼저 볼 것은 테이블별 dead tuple 수, 마지막 vacuum/analyze 시간, autovacuum 실행 횟수입니다. 특정 테이블만 계속 커지거나, last_autovacuum이 오래 비어 있거나, n_dead_tup이 평소보다 빠르게 증가한다면 후보가 됩니다. 아래 SQL은 운영 점검용으로 그대로 사용할 수 있습니다.
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_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze,
vacuum_count,
autovacuum_count
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;dead_pct가 높다고 항상 장애는 아니지만, 쓰기량이 많은 테이블에서 dead tuple이 계속 증가하고 last_autovacuum이 갱신되지 않는다면 autovacuum이 따라가지 못하는 상황일 수 있습니다. 반대로 autovacuum_count가 자주 증가하는데도 dead tuple이 줄지 않는다면 작업 시간이 부족하거나, 장기 트랜잭션이 오래된 행 정리를 막고 있을 가능성이 큽니다.
장기 트랜잭션부터 제거하기
VACUUM은 아직 어떤 트랜잭션에서 볼 수 있는 행 버전을 삭제할 수 없습니다. 그래서 오래 열린 트랜잭션 하나가 전체 정리를 막을 수 있습니다. 배치 작업, 관리 툴, 커넥션 풀 누수, 열린 psql 세션이 원인인 경우가 많습니다. 다음 조회로 오래된 트랜잭션을 찾습니다.
SELECT
pid,
usename,
application_name,
state,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
LEFT(query, 160) AS query_sample
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC
LIMIT 20;xact_age가 비정상적으로 긴 세션은 우선 애플리케이션 소유자를 확인하고 정상 종료를 유도합니다. 긴급 상황에서는 pg_terminate_backend를 사용할 수 있지만, 실행 중인 업무 트랜잭션을 끊을 수 있으므로 대상 PID와 영향 범위를 확인해야 합니다. 운영 규칙으로는 idle in transaction을 오래 방치하지 않도록 statement_timeout, idle_in_transaction_session_timeout을 서비스 계정별로 설정하는 것이 좋습니다.
테이블별 Autovacuum 임계값 조정
Autovacuum 시작 조건은 대략 기본 임계값과 테이블 행 수 비율로 결정됩니다. 큰 테이블에서는 scale factor를 낮추는 방식이 효과적입니다. 전체 서버 설정을 무작정 낮추면 모든 테이블의 백그라운드 작업이 늘어날 수 있으므로, 먼저 문제가 확인된 테이블에만 storage parameter를 적용합니다.
ALTER TABLE public.orders SET (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_vacuum_threshold = 5000,
autovacuum_analyze_scale_factor = 0.01,
autovacuum_analyze_threshold = 3000
);
ALTER TABLE public.orders SET (
autovacuum_vacuum_cost_limit = 2000,
autovacuum_vacuum_cost_delay = 2
);위 설정은 예시입니다. 핵심은 자주 바뀌는 큰 테이블의 VACUUM과 ANALYZE를 더 일찍 시작하게 만드는 것입니다. orders처럼 조회와 변경이 모두 많은 테이블은 통계가 늦게 갱신될 때 실행 계획이 잘못 잡히기 쉽습니다. 조건 컬럼의 분포가 빠르게 바뀐다면 analyze 기준을 vacuum보다 더 민감하게 잡는 것이 유리합니다.
수동 VACUUM과 ANALYZE 실행 기준
장애 대응 중에는 autovacuum을 기다리지 않고 수동으로 VACUUM 또는 ANALYZE를 실행할 수 있습니다. 단, VACUUM FULL은 테이블을 새로 쓰며 강한 잠금을 유발하므로 일반 운영 시간에 팽창 해결 목적으로 쉽게 사용하면 안 됩니다. 대부분은 일반 VACUUM과 ANALYZE로 먼저 대응합니다.
VACUUM (VERBOSE, ANALYZE) public.orders;
ANALYZE VERBOSE public.orders;
SELECT pg_size_pretty(pg_total_relation_size('public.orders')) AS total_size,
pg_size_pretty(pg_relation_size('public.orders')) AS table_size,
pg_size_pretty(pg_indexes_size('public.orders')) AS indexes_size;일반 VACUUM은 dead tuple 공간을 재사용 가능하게 만들지만 운영체제에 파일 크기를 즉시 반환하지는 않습니다. 파일 크기 자체가 큰 문제가 되고, 재작성 잠금을 감당할 수 없는 서비스라면 pg_repack 같은 온라인 재구성 도구를 별도 검토합니다. 다만 도구 도입 전에는 백업, 복구 절차, 여유 디스크, 복제 지연 가능성을 먼저 확인해야 합니다.
인덱스 팽창과 불필요한 인덱스 확인
테이블만 보는 것도 부족합니다. UPDATE가 많은 테이블은 인덱스도 같이 커질 수 있습니다. 또한 사용하지 않는 인덱스가 많으면 쓰기 비용과 VACUUM 부담이 늘어납니다. 아래 조회로 크지만 거의 사용되지 않는 인덱스를 후보로 찾을 수 있습니다.
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE pg_relation_size(indexrelid) > 100 * 1024 * 1024
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 30;idx_scan이 낮다고 바로 삭제하면 안 됩니다. 월말 배치, 관리자 화면, 드물지만 중요한 장애 대응 쿼리에서만 쓰는 인덱스일 수 있습니다. 삭제 전에는 pg_stat_statements, 애플리케이션 쿼리 로그, 배치 일정을 함께 확인합니다. 중복 인덱스나 prefix가 겹치는 복합 인덱스는 정리 효과가 큰 편입니다.
운영 체크리스트
- 쓰기 많은 대형 테이블은 dead tuple 비율과 last_autovacuum을 매일 확인합니다.
- idle in transaction이 오래 유지되지 않도록 타임아웃과 커넥션 풀 반환 규칙을 점검합니다.
- 서버 전체 autovacuum 값을 바꾸기 전에 테이블별 storage parameter로 범위를 좁혀 조정합니다.
- VACUUM FULL은 최후 수단으로 보고, 잠금과 디스크 여유 공간을 먼저 검토합니다.
- 느린 쿼리 대응 시 EXPLAIN만 보지 말고 ANALYZE 최신성, 테이블 크기, 인덱스 크기, 장기 트랜잭션을 함께 확인합니다.
정리하면 PostgreSQL 성능 관리는 인덱스를 더 추가하는 일만이 아닙니다. 변경된 행이 제때 정리되고, 통계가 최신 상태로 유지되며, 오래 열린 트랜잭션이 VACUUM을 방해하지 않는 구조를 만드는 것이 기본입니다. 위 SQL을 정기 점검 항목으로 넣고, 문제가 반복되는 테이블만 좁혀 autovacuum 기준을 조정하면 불필요한 장애 대응과 디스크 증설을 줄일 수 있습니다.