PostgreSQL 튜닝 - Autovacuum 최적화에 대하여

엔라이즈 개발팀이 2014년부터 PostgreSQL을 운용하며 정리한 Autovacuum 최적화 방법이다. dead tuple이 무엇이고 왜 쌓이는지부터 시작해, 기본 설정이 왜 큰 테이블에 부적합한지, 어떤 파라미터를 테이블 단위로 조정해야 하는지까지 이어진다. 완전 관리형 서비스를 쓰더라도 기본 설정은 크게 다르지 않다는 점도 짚는다.

핵심 포인트

PostgreSQL의 MVCC 구현 때문에 update와 delete는 참조되지 않는 dead tuple을 남긴다.
Autovacuum 발동 조건은 threshold와 live tuple 대비 scale_factor의 합이라 테이블이 커질수록 발동 주기가 길어지고 한 번에 처리할 양이 늘어난다.
기본값 scale_factor 0.2를 그대로 두면 1억 건 테이블은 dead tuple 2천만 건이 쌓여야 Autovacuum이 돈다.
가장 간단한 최적화는 큰 테이블에 한해 scale_factor를 0으로 두고 threshold 절대값으로 발동시키는 것이다.
autovacuum_vacuum_cost_limit은 한 번 실행의 지속 시간을 결정하며 테이블 단위로 올릴 수 있다.
Autovacuum은 공간을 FSM으로 반환할 뿐 디스크 사용량을 줄이지 않는다. 줄이려면 락이 걸리는 Full Vacuum이 필요하다.

상세 정리

Vacuum의 위치: Oracle, MySQL, SQL Server에는 없는 개념이라 처음 PostgreSQL을 쓰는 사람이 설정을 방치했다가 트랜잭션이 늘었을 때 예상 밖의 성능 저하를 겪기 쉽다.
두 가지 발동 상황: XID wraparound를 막으려 XID를 고정할 때와, 임계점을 넘긴 dead tuple을 정리해 FSM으로 반환할 때다. 글은 후자를 다룬다.
dead tuple 생성 경로: 업데이트 시 FSM 여유를 확인하고 빈 공간에 새 tuple을 기록한 뒤 포인터를 새 tuple로 옮긴다. 이전 공간이 참조되지 않는 dead tuple로 남는다.
부수 효과 1: 저장 공간이 계속 불어나는 Data Bloat이 생긴다. update와 delete가 잦을수록 사용량이 급속히 는다.
부수 효과 2: select 시 일정 크기의 chunk 단위로 읽는데 그 안에 정리되지 않은 dead tuple이 섞여 있으면 같은 live tuple을 읽는 데 더 많은 디스크 I/O가 든다.
부수 효과 3: 통계 수집기가 왜곡된 정보를 바탕으로 인덱스가 있는데도 쓰지 않는 실행 계획을 세우는 경우까지 생긴다.
기본값이 보수적인 이유: PostgreSQL은 최고 성능보다 다양한 기기에서 무난히 도는 쪽으로 기본 설정을 잡는다는 배포 철학을 갖는다.
발동 공식: autovacuum_vacuum_threshold 기본 50과 autovacuum_vacuum_scale_factor 기본 0.2를 합쳐 계산한다. 10만 건 테이블이면 20,050건에서 발동한다.
규모 문제: 비율 기반이라 레코드가 늘수록 주기가 길어지고 한 번에 처리할 양도 늘어, 처리하다 중단되면 dead tuple이 오히려 증가하는 상태에 빠진다.
모니터링 1: pg_stat_user_tables에서 n_live_tup과 n_dead_tup의 비율을 뽑아 dead tuple이 1,000건을 넘는 테이블을 정렬해 본다. insert만 일어나는 테이블은 dead tuple이 사실상 없다.
모니터링 2: 같은 뷰의 last_vacuum, last_autovacuum, last_analyze, last_autoanalyze로 테이블별 마지막 실행 시각을 확인한다.
최적화 1: ALTER TABLE로 scale_factor를 0.0, threshold를 10만 같은 절대값으로 두면 그 테이블은 dead tuple 10만 건마다 일정하게 Autovacuum이 돈다. 운영 중 적용해도 무방하다.
최적화 2: cost_limit 기본 200 credit은 shared buffer 1, 디스크 10, dirty page 20씩 소모하다 소진되면 종료된다. 테이블 단위로 1,000까지 올리면 한 번에 약 5배를 처리한다.
최적화 3: 통계 갱신도 dead tuple의 영향을 받으므로 autovacuum_analyze_scale_factor와 threshold를 vacuum 쪽과 같은 값으로 맞춘다.
최적화 4: autovacuum_work_mem은 -1이면 maintenance_work_mem을 공유하며, PostgreSQL 전용 서버라면 메모리 1GB당 50MB가 통상적인 기준이다. autovacuum_max_workers는 관리 대상 테이블이 많으면 늘려야 하고 변경 시 재시작이 필요하다.
Full Vacuum과의 차이: 수동 Full Vacuum은 락을 걸지만 FSM을 재설정해 디스크 사용량이 실제로 줄고, Autovacuum은 락 없이 공간을 재사용 가능하게 만들 뿐이다.
pg_repack의 한계: 복제본을 만들어 교체하는 방식이라 운영 중에도 쓸 수는 있지만 시작과 종료 시 순단이 있고 리소스를 심하게 쓴다. AWS RDS 문서에 이런 경고가 없는 점을 아쉬움으로 든다.