[PostgreSQL 4/12] VACUUM과 TXID 프리징: 공간과 오래된 트랜잭션 관리

Об этом предложении

이 글에서 다룰 주제 공간: 행 수가 일정한데 파일이 커지는 이유는? 프리징: 삭제할 행이 적어도 유지보수가 필요한 이유는? 운영: 어떤 지표와 방해 요인을 함께 점검해야 하는가? 주요 단어 · VACUUM · Bloat · Autovacuum · XID Age · Freeze · Visibility Map 행 수가 일정하다고 파일 크기까지 일정한 것은 아니다. 반대로 dead tuple이 거의 없다고 유지보수가 필요 없는 것도 아니다. PostgreSQL의 유지보수에는 더 이상 필요하지 않은 버전의 공간을 재사용하는 일 과 오래된 트랜잭션 참조를 안전하게 처리하는 일 이 함께 들어 있다. VACUUM을 단순한 파일 압축 도구로 보면 이 두 문제를 놓치기 쉽다. VACUUM 은 불필요한 버전 정리와 공간 재사용, 프리징 등을 수행한다. Freeze 는 충분히 오래된 생성 트랜잭션을 일반 XID 비교의 대상에서 안전하게 제외하는 처리다. 자료와 예제 기준 — PostgreSQL 18을 중심으로 개인 학습 노트를 재구성했다. SQL·실행 계획·설정값은 설명 및 재현용 예제이며 이 글을 위해 운영 DB에서 새로 측정한 결과는 아니다. DDL/DML 예제는 독립적인 테스트 환경에서 사용한다. 1. Heap과 Index는 따로 부푼다 행 수가 그대로여도 이전 버전과 비효율적인 빈 공간 때문에 파일이 커질 수 있다. 구분 Table Bloat Index Bloat 대상 heap 페이지 인덱스 페이지 원인 예 정리 지연, 공간 재사용 불균형 이전 TID 엔트리, 낮은 페이지 밀도 영향 테이블 스캔·캐시 효율 저하 인덱스 스캔·캐시 효율 저하 HOT와 관계 새 버전은 여전히 생성됨 새 일반 인덱스 엔트리 생성을 줄임 non-HOT UPDATE에서는 키 값이 그대로여도 새 TID를 가리키는 인덱스 엔트리가 필요할 수 있다. PK의 논리적 유일성을 지키면서 여러 MVCC 버전에 대응하는 물리 엔트리가 존재할 수 있다. dead bytes + free bytes 를 곧바로 OS에 반환 가능한 공간으로 해석하지 않는다. fillfactor 여유, 오버헤드, 페이지별 분포, 곧 재사용될 공간을 구분해야 한다. Dead 비율 ≠ bloat 비율 — n_dead_tup은 행 개수 추정치다. 행 크기·빈 공간·인덱스·TOAST까지 설명하지 못한다. 빈 공간의 해석 — 곧 재사용하거나 fillfactor로 의도한 여유라면 모두 낭비는 아니다. 추이와 조회 비용을 함께 본다. 인덱스의 자체 관리 — B-tree에는 불필요한 엔트리 삭제·중복 제거 최적화도 있다. UPDATE마다 파일이 반드시 커지지는 않는다. 기억할 문장: 공간 크기보다 “다시 쓰이는가, 읽기 비용이 커지는가”를 확인하자. 2. 재사용할까, 파일을 줄일까? 세 방법의 선택 기준은 공간 반환의 필요성과 잠금 허용 시간이다. 기준 VACUUM VACUUM FULL pg_repack 방식 기존 페이지 정리 전체 재작성 복사·변경 반영·교체 목적 내부 공간 재사용 파일 축소 온라인 재구성·축소 일반 접근 대부분 읽기·쓰기 가능 대상 테이블 접근 차단 대부분 가능, 시작·마무리 강한 잠금 추가 공간 전체 복사본 불필요 새 파일 공간 필요 복사본·인덱스·변경 로그 등 필요 선택 일상 유지보수 점검 시간 확보 중단이 어려운 테이블 일반 VACUUM도 파일 끝 빈 페이지를 반환할 때 강한 잠금을 사용할 수 있다. TRUNCATE FALSE 는 이 동작을 생략한다. 일반 VACUUM은 보통 파일 전체를 축소하지 않는다. pg_repack은 트리거로 변경을 기록하고, 복사본에 적용한 뒤 교체한다. --no-kill-backend 는 잠금 충돌 시 다른 쿼리·세션을 강제로 취소·종료하는 기본 정책을 피하는 데 사용한다. 설치 버전과 옵션을 확인한다. FULL과 repack 이후에도 autovacuum이 필요하다. 일반 VACUUM — 인덱스 정리 후 DEAD 슬롯을 재사용 가능하게 할 수 있다. 프리징·Visibility Map도 관리한다. 추가 디스크 — FULL과 repack은 기존 파일을 유지한 채 새 파일을 만든다. 디스크가 가득 찬 상황의 즉시 해법이 아니다. pg_repack 조건 — 전체 테이블 재구성에는 PK 또는 적격 NOT NULL UNIQUE 키가 필요하다. 확장·클라이언트 버전도 맞춘다. 기억할 문장: 인덱스만 비대하면 REINDEX CONCURRENTLY를 별도로 검토한다. 3. 공간 재사용과 autovacuum VACUUM과 autovacuum 1 제거 가능한 시점을 판단해야 한다 최신 버전이 아니라는 이유만으로 이전 버전을 제거할 수 없다. 오래된 스냅샷에서 여전히 필요할 수 있기 때문이다. 더 이상 필요하지 않은 버전을 정리해 공간을 재사용한다. 기능 결과 불필요한 튜플·인덱스 엔트리 정리 재사용 공간 확보 Visibility Map 관리 Index-only scan에서 Heap 확인 감소 Freeze 오래된 XID의 순환 문제 방지 FSM 갱신 새 튜플이 들어갈 여유 페이지 탐색 지원 VACUUM 자체도 영구 저장 구조를 바꾸므로 WAL과 Dirty page를 만들 수 있다. ‘청소는 I/O가 없다’고 가정하면 안 된다. 2 일반 VACUUM과 FULL 항목 VACUUM VACUUM FULL 방식 기존 파일에서 버전 정리 새 파일로 재작성 공간 주로 내부 재사용 압축해 OS에 반환 DML 일반 읽기·쓰기와 병행 가능 ACCESS EXCLUSIVE로 차단 추가 공간 전체 재작성 복사본 불필요 새 테이블·인덱스를 위한 여유 필요 운영 목적 지속적인 유지보수 대규모 영구 감소 후 공간 회수 일반 VACUUM도 테이블 끝의 완전히 빈 페이지를 잘라내면 OS에 공간을 반환할 수 있다. 이 과정에는 강한 잠금이 필요할 수 있다. 따라서 ‘절대 파일이 줄지 않는다’, ‘어떤 잠금도 없다’는 설명은 부정확하다. FULL은 특정 인덱스 순서로 정렬하는 기능이 아니다. 새 파일을 만들 공간이 필요하므로 디스크가 꽉 찬 상태에서 무조건 실행하는 해결책도 아니다. Autovacuum은 FULL을 자동으로 실행하지 않는다. 3 VACUUM과 ANALYZE VACUUM orders; ANALYZE orders; VACUUM (ANALYZE) orders; ANALYZE는 컬럼 분포·히스토그램·빈도 등 플래너 통계를 수집한다. VACUUM도 일부 관계 통계를 갱신하지만 ANALYZE를 대체하지는 않는다. 파티션 부모의 통계는 자식 변경만으로 자동 갱신되지 않는 경우가 있어 별도 ANALYZE를 검토한다. 4 Visibility Map과 Freeze 일반 인덱스 엔트리만으로 MVCC 가시성을 완전히 알 수 없다. Index-only scan도 대상 페이지가 all-visible이 아니면 Heap을 확인할 수 있다. 따라서 인덱스가 모든 컬럼을 포함해도 Heap Fetches 가 발생한다. Freeze는 행을 수정 불가능하게 만드는 기능이 아니다. 오래된 생성 트랜잭션을 모든 적절한 미래 스냅샷보다 과거인 것으로 취급하도록 표시한다. 내부 XID의 순환에 대비하는 유지보수이므로 UPDATE·DELETE가 거의 없는 테이블도 필요하다. 5 Autovacuum의 실행과 처리 능력 기본적인 변경량 조건: base threshold + scale factor × estimated tuples PostgreSQL 18에서는 최대 임계값 설정도 적용된다. INSERT 기반 조건과 Freeze 관련 조건은 별도로 존재한다. 1,000만 행, base=50, scale=0.2이면 약 2,000,050개의 obsolete tuple 발생 조건을 계산한다. 이는 실행 가능성을 판단하는 기준이지 즉시 시작·완료 보장이 아니다. -- 부하 측정 후 조정할 교육용 예시 ALTER TABLE orders SET ( autovacuum_vacuum_threshold = 1000, autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.02 ); 임계값을 낮추면 더 자주 처리하지만 Worker·I/O·잠금 제약을 함께 봐야 한다. 오래된 스냅샷 때문에 제거 불가능한 버전은 Worker만 늘려도 정리되지 않는다. 복제 피드백, Slot이 유지하는 xmin, prepared transaction 등도 보존 경계에 영향을 줄 수 있다. 4. 오래된 미동결 XID를 관리해야 하는 이유 프리징 자체가 위험한 기능은 아니다. 오래된 미동결 XID가 계속 남아 있으면 번호 비교의 안전 범위를 벗어날 수 있고, PostgreSQL은 이를 막기 위해 신규 XID 발급을 차단할 수 있다. 애플리케이션에서는 INSERT·UPDATE 등의 실패로 나타난다. 이 문제의 시계는 날짜가 아니라 XID 소비량 으로 움직인다. 부하가 높은 시스템은 같은 시간을 운영해도 훨씬 빨리 위험 구간에 접근한다. 일반적인 서버 재시작으로 XID 나이나 미동결 데이터가 초기화되지는 않는다. 유지보수 경계가 실제로 전진해야 한다. 5. MVCC와 XID의 관계 PostgreSQL은 UPDATE 시 새로운 행 버전을 만들고, 트랜잭션 스냅샷에 따라 보이는 버전을 결정한다. 항목 의미 주의할 점 xmin 해당 행 버전을 생성한 트랜잭션 원래 숫자만으로 동결 상태를 판정할 수 없음 xmax 삭제·갱신 또는 행 잠금 관련 정보 0이 아닌 값이 있다고 무조건 삭제된 행은 아님 Snapshot 현재 조회에서 보이는 트랜잭션 범위와 진행 중 트랜잭션 정보 단순 숫자 비교만으로 가시성이 결정되지는 않음 xid 내부 32비트 트랜잭션 식별자 번호가 순환함 xid8 epoch를 포함한 64비트 식별자 내부 튜플의 프리징 필요성을 없애지는 않음 Virtual XID 트랜잭션을 식별하는 가상 식별자 영구 XID와 구분 예를 들어 xmin = 500 인 행 버전은 XID 500이 생성했다는 뜻이다. PostgreSQL은 그 트랜잭션의 커밋 여부, 스냅샷과의 관계, 삭제·갱신 상태 등을 함께 판단한다. 영구 XID는 일반적으로 최초 쓰기 시 발급된다. 따라서 SELECT 요청 수나 전체 커밋 건수를 XID 소비량과 동일하게 취급하면 안 된다. 서브트랜잭션이나 XID 할당을 유발하는 함수도 측정에 영향을 줄 수 있다. XID 카운터는 같은 PostgreSQL 클러스터의 모든 데이터베이스가 공유 한다. 한 테이블이 변경되지 않아도 다른 곳의 쓰기로 그 테이블의 XID 나이가 증가할 수 있다. 여기서 클러스터는 Kubernetes 클러스터가 아니라 하나의 PostgreSQL 데이터 디렉터리로 관리되는 데이터베이스 집합을 뜻한다. 6. 42.9억 번호 공간과 21.5억 비교 한계 32비트 번호 공간은 2^32 = 4,294,967,296 , 약 42.9억이다. 일부 특수 XID는 예약되어 있지만 전체 크기를 이해할 때는 이 숫자로 생각하면 된다. 그러나 과거·미래 비교가 가능한 범위는 대략 절반인 2^31 = 2,147,483,648 , 약 21.5억이다. 순환하는 번호 공간에서 한쪽 반 바퀴를 과거, 다른 반 바퀴를 미래로 해석하기 때문이다. 1 0~99 번호표로 축소한 예 생성 번호가 20인 행이 프리징되지 않은 채 남아 있다고 가정한다. 현재 번호 생성 번호 앞으로 진행한 거리 해석 30 20 10 생성 번호는 과거 60 20 40 아직 과거로 비교 가능 80 20 60 반 바퀴를 넘어 정상적인 과거 해석이 불가능 즉, 최댓값에서 0으로 돌아가는 순간만 감시하면 안 된다. 현재 XID와 가장 오래된 미동결 XID 경계 사이의 거리 가 중요하다. XID 순환은 정상적인 장기 운영에서도 일어난다. 위험한 상황은 순환 자체보다 오래된 XID 참조를 안전하게 처리하지 못한 상태 다. 7. 프리징의 실제 의미 학습 자료의 개념도 — VACUUM·FULL·pg_repack의 목적. 세부 조건은 본문 설명을 함께 읽는다. 프리징 전에는 행 생성 트랜잭션의 상태와 가시성을 일반 XID 규칙으로 판단한다. 프리징 후에는 생성 트랜잭션을 모든 일반 XID보다 오래된 것으로 취급한다. 구분 행 생성 트랜잭션의 의미 프리징 전 ‘XID 500이 만든 행’이라는 일반 트랜잭션 참조 프리징 후 ‘충분히 오래전에 커밋된 생성 트랜잭션’으로 확정 개념적으로는 FrozenTransactionId 로 취급한다. PostgreSQL 9.4 이후에는 원래 xmin 을 보존하고 튜플 헤더의 동결 플래그로 표현하므로, 프리징 후에도 SELECT xmin 결과가 500으로 남을 수 있다. 프리징은 생성 트랜잭션의 가시성 문제를 해결 하는 것이다. 행이 이후 삭제되거나 갱신되면 그 상태에 맞는 가시성 판단은 계속 필요하다. 데이터가 영구적으로 모든 조회에 보인다거나 수정 불가능해진다는 뜻이 아니다. 진행 중인 트랜잭션과 오래된 스냅샷 등이 안전한 처리 범위를 제한한다. FREEZE 옵션도 이 안전 조건을 무시하지 않는다. 8. VACUUM과 Visibility Map 1 VACUUM이 맡는 서로 다른 역할 작업 해결하려는 문제 죽은 튜플 정리 더 이상 필요 없는 행 버전이 차지하는 공간 프리징 오래된 트랜잭션 참조와 번호 순환 Visibility Map 갱신 페이지 상태를 이용한 조회·유지보수 최적화 ANALYZE 플래너 통계 갱신. 별도 명령이며 VACUUM과 함께 실행 가능 따라서 dead tuple이 거의 없다는 사실만으로 프리징이 불필요하다고 결론 내릴 수 없다. 2 all-visible과 all-frozen 상태 의미 활용 all-visible 페이지의 튜플이 모든 관련 트랜잭션에 보이는 상태 Index Only Scan의 heap 접근 생략에 활용 all-frozen 페이지의 튜플이 모두 동결된 상태 동결을 위한 재검사 생략에 활용 all-visible이라고 all-frozen인 것은 아니다. 일반 VACUUM은 일부 페이지를 건너뛸 수 있지만, aggressive VACUUM은 미동결 XID가 있을 수 있는 페이지까지 확인한다. 이미 all-frozen인 페이지는 보통 다시 검사할 필요가 없다. PostgreSQL 18에는 일반 VACUUM 중 일부 all-visible 페이지를 미리 동결하려는 eager freezing도 있다. 세부 검사 정책은 버전에 따라 다르므로, ‘일반 VACUUM은 프리징을 전혀 하지 않는다’고 이해하면 안 된다. 3 VACUUM FREEZE와 VACUUM FULL -- 예시: 지정한 테이블을 적극적으로 동결 VACUUM (FREEZE, VERBOSE) public.target_table; FREEZE 는 동결 관련 나이 기준을 낮추어 안전하게 처리 가능한 튜플을 적극적으로 동결한다. VACUUM FULL 은 테이블을 재작성하여 공간을 축소하는 작업으로, 목적과 잠금 비용이 다르다. XID 위험 대응에서 FULL을 기본 처방으로 선택하지 않는다. 일반 VACUUM은 보통 읽기·쓰기와 함께 실행할 수 있지만 I/O를 사용하고 DDL 등과 충돌할 수 있다. 테이블 끝의 빈 페이지를 잘라내는 단계에서는 더 강한 잠금이 필요할 수 있다. 9. 자동 유지보수 설정과 트레이드오프 아래는 PostgreSQL 18 기본값이다. 단위는 초·일·행 수가 아닌 XID 나이다. 설정 기본값 역할 vacuum_freeze_min_age 50,000,000 프리징 여부를 결정하는 나이 기준 vacuum_freeze_table_age 150,000,000 실행되는 VACUUM이 적극적인 검사 전략을 선택하는 기준 autovacuum_freeze_max_age 200,000,000 wraparound 방지용 autovacuum 실행 대상이 되는 기준 vacuum_failsafe_age 1,600,000,000 VACUUM이 비상 전략으로 동결 진전을 우선하는 기준 2억은 쓰기 중단 한계가 아니라 예방 작업을 시작하는 기준이다. vacuum_freeze_table_age 는 별도의 스케줄러가 아니며, VACUUM의 실행 방식에 영향을 준다. failsafe는 비용 기반 지연을 중단하고 인덱스 정리 등 일부 부가 작업을 건너뛰어 위험을 줄이려 한다. 설정 간에는 유효 범위 보정도 있으므로 숫자를 각각 독립적으로 해석하지 않는다. 조정 방향 기대 효과 비용·주의점 더 일찍 프리징 나중에 몰리는 작업량 완화 곧 변경될 행까지 처리하면 불필요한 작업 증가 방지용 실행 기준을 늦춤 일부 테이블의 강제 유지보수 빈도 감소 상태 보존량 증가, 대응 여유와 처리량 검토 필요 VACUUM 처리량 확대 오래된 XID 경계가 빨리 전진 서비스 I/O·캐시 사용과 경쟁 autovacuum을 꺼도 wraparound 방지용 프로세스는 실행될 수 있다. 그러나 이 마지막 보호 장치에만 의존하는 운영은 바람직하지 않다. 10. 프리징이 밀리는 원인 1 오래 열린 트랜잭션 장기 트랜잭션이나 오래된 스냅샷은 VACUUM의 안전한 정리 경계를 붙잡을 수 있다. 단순히 연결이 오래 살아 있다는 것과 오래된 XID·스냅샷을 보유한다는 것은 구분해야 한다. 특히 idle in transaction 은 쿼리를 실행하지 않더라도 트랜잭션이 끝나지 않은 상태다. 실제 영향은 backend_xid , backend_xmin , 잠금 상태를 함께 보고 판단한다. 2 Prepared transaction 2단계 커밋의 prepared transaction은 일반 세션이 종료되어도 남을 수 있다. 업무 결과를 확인하고 COMMIT 또는 ROLLBACK해야 하며, 단순히 오래되었다는 이유로 일괄 처리해서는 안 된다. 3 복제 슬롯 슬롯 필드 주로 붙잡는 대상 restart_lsn 재시작에 필요한 WAL xmin VACUUM의 사용자 데이터 정리 경계 catalog_xmin 시스템 카탈로그 정리 경계 WAL 디스크 증가와 XID 프리징 정체는 관련될 수 있지만 같은 문제는 아니다. 슬롯의 실제 필드와 소비자 상태를 확인한다. 4 처리량 부족과 잠금 대형 테이블, 느린 스토리지, 제한된 worker·I/O 예산, 충돌하는 잠금, 반복 취소는 유지보수를 지연시킨다. ‘VACUUM이 실행 중’이라는 사실보다 완료 후 경계가 전진하는지 가 중요하다. 11. 읽기 전용 점검 SQL 아래 쿼리는 진단 예시다. 일부 세션 정보는 pg_monitor 등 적절한 권한이 필요하다. 데이터베이스 전체 진단과 테이블 진단의 범위를 구분한다. 1 실제 설정 확인 SELECT name, setting, unit, source FROM pg_settings WHERE name IN ( 'autovacuum', 'autovacuum_freeze_max_age', 'vacuum_freeze_min_age', 'vacuum_freeze_table_age', 'vacuum_failsafe_age' ) ORDER BY name; 테이블별 storage parameter가 전역 설정을 덮어쓸 수 있다는 점도 확인한다. 2 데이터베이스별 XID 나이 SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC; datfrozenxid 는 해당 DB에 남은 미동결 XID의 하한 경계다. 사용자 테이블뿐 아니라 카탈로그 등도 영향을 준다. 3 현재 DB의 테이블과 TOAST 나이 SELECT c.oid::regclass AS table_name, age(c.relfrozenxid) AS main_xid_age, age(t.relfrozenxid) AS toast_xid_age, GREATEST(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size, c.reloptions FROM pg_class AS c LEFT JOIN pg_class AS t ON t.oid = c.reltoastrelid WHERE c.relkind IN ('r', 'm') ORDER BY xid_age DESC LIMIT 30; relfrozenxid 는 그보다 오래된 XID가 안전하게 처리되었다고 보장하는 경계다. 원래 xmin 값의 단순 최솟값과 다르다. total_size 는 인덱스 등을 포함하므로 실제 heap 스캔량과 동일하지 않다. 파티션 부모보다 실제 데이터를 담는 자식 테이블의 상태를 확인한다. 4 오래된 트랜잭션·스냅샷 후보 SELECT pid, datnam