PostgreSQL 테이블에서 많은 행을 DELETE한 뒤 파일 크기를 확인하면 기대와 다른 결과를 볼 수 있다. 행은 사라졌는데 디스크 사용량은 바로 줄지 않고, 삭제와 갱신을 반복한 테이블이 오히려 커 보이기도 한다. 처음 접하면 삭제가 실패했거나 PostgreSQL이 공간을 반환하지 않는 버그처럼 느껴진다.
이 현상을 이해하려면 DELETE를 파일에서 바이트를 즉시 잘라내는 명령이 아니라, 여러 트랜잭션이 서로 다른 시점의 행을 볼 수 있게 하는 MVCC 상태 변경으로 봐야 한다. 삭제된 옛 행 버전은 당장 모든 트랜잭션에서 불필요해지는 것이 아니며, VACUUM이 재사용 가능한 공간으로 정리하는 과정도 별도로 있다.
이 글은 특정 운영 장애를 해결했다는 기록이 아니다. 현재 PostgreSQL 공식 문서를 기준으로 MVCC, dead tuple, 일반 VACUUM, VACUUM FULL, autovacuum, 오래 열린 트랜잭션의 관계를 재현 가능한 점검 순서로 정리한 실험 노트다. 테이블 이름과 수치는 예시이며, 실제 환경에서는 실행계획과 통계 값을 직접 확인해야 한다.
MVCC에서는 한 행에도 여러 버전이 생길 수 있다
MVCC는 Multi-Version Concurrency Control의 약자다. PostgreSQL은 트랜잭션마다 적절한 데이터 스냅샷을 보여 주어 읽기와 쓰기가 불필요하게 서로를 막지 않게 한다. 어떤 트랜잭션이 행을 갱신하는 동안 다른 트랜잭션은 자신의 스냅샷에서 이전 버전을 계속 볼 수 있다.
UPDATE는 같은 위치의 값을 단순 덮어쓰는 것으로만 이해하면 안 된다. 새 행 버전을 만들고 이전 버전은 더 이상 새 트랜잭션에 보이지 않는 상태가 된다. DELETE도 기존 튜플을 즉시 파일에서 제거하기보다 삭제된 것으로 표시한다. 아직 옛 스냅샷을 가진 트랜잭션에는 그 버전이 필요할 수 있기 때문이다.
이전 버전을 흔히 dead tuple이라고 부른다. 모든 트랜잭션에서 더 이상 볼 필요가 없어진 뒤에야 정리 대상으로 확정된다. 따라서 SQL 결과에서 행이 사라진 시점과 물리 공간이 다시 쓰일 수 있는 시점은 다를 수 있다. 이 차이가 삭제 직후 파일 크기가 그대로인 첫 번째 이유다.
일반 VACUUM은 공간을 운영체제에 바로 돌려주지 않는다
일반 VACUUM은 더 이상 필요한 스냅샷이 없는 dead tuple을 찾아 그 공간을 같은 테이블의 이후 INSERT나 UPDATE가 재사용할 수 있게 한다. 테이블 내부의 빈자리를 정리하는 작업이라고 생각하면 이해하기 쉽다. 이 과정은 보통 읽기와 쓰기를 계속 허용하면서 실행된다.
재사용 가능 공간이 생겨도 관계 파일의 전체 크기가 즉시 작아질 필요는 없다. 파일 안쪽에 생긴 빈 공간을 앞으로 같은 테이블이 사용할 수 있기 때문이다. 운영체제의 du나 파일 크기만 보면 변화가 없어도, PostgreSQL 내부에서는 새 행을 넣을 수 있는 공간으로 바뀌었을 수 있다.
일반 VACUUM이 파일 끝의 완전히 빈 페이지를 잘라 반환할 수 있는 경우도 있지만, 항상 가능한 것은 아니다. 빈 공간이 파일 곳곳에 흩어져 있거나 끝부분에 사용 중인 페이지가 남아 있으면 파일을 단순히 줄일 수 없다. “VACUUM을 했는데 디스크가 줄지 않았다”만으로 실패라고 판단하면 안 되는 이유다.
VACUUM FULL은 목적과 비용이 다르다
VACUUM FULL은 살아 있는 행을 새 물리 파일에 다시 써서 테이블을 조밀하게 만든다. 그래서 사용하지 않는 공간을 운영체제에 반환할 수 있지만, 일반 VACUUM보다 훨씬 큰 작업이다. 대상 테이블에 강한 잠금이 필요하고 새 복사본을 만드는 동안 추가 디스크 공간도 요구한다.
삭제량이 크다는 이유만으로 바로 실행하면 서비스 요청이 오래 막힐 수 있다. 테이블과 인덱스 크기, 사용 가능한 여유 공간, 허용 가능한 잠금 시간, 복제 지연, 백업 일정까지 함께 봐야 한다. 운영 중 온라인 재구성이 필요하면 pg_repack 같은 별도 도구도 후보지만, 설치와 운영 제약을 검토해야 한다.
일반 VACUUM의 목표는 지속적인 공간 재사용과 통계·가시성 정보 유지이고, FULL의 목표는 물리 파일 재작성에 가깝다. 둘을 “약한 청소와 강한 청소” 정도로만 구분하면 잠금과 디스크 비용을 놓친다. 디스크를 당장 반환해야 하는 명확한 이유가 있는지부터 확인하는 편이 안전하다.
autovacuum은 선택 기능이 아니라 MVCC 유지 장치다
PostgreSQL의 autovacuum은 변경량을 보고 테이블별로 VACUUM과 ANALYZE를 실행한다. dead tuple 공간을 회수하고 플래너 통계를 갱신하는 역할뿐 아니라, 트랜잭션 ID wraparound를 막기 위한 동결 작업도 담당한다. 단순히 디스크 정리 스케줄러로만 보면 중요도를 과소평가하게 된다.
기본 임계값은 고정 수치와 테이블 크기에 비례한 scale factor의 조합으로 결정된다. 큰 테이블에서는 비율이 작은 변경도 절대 행 수로는 매우 클 수 있다. 반대로 작지만 갱신이 잦은 테이블은 고정 임계값 때문에 정리가 늦을 수 있다. 모든 테이블에 같은 설정이 알맞다고 가정하지 않는다.
조정 전에는 실제로 autovacuum이 늦는지 확인해야 한다. pg_stat_user_tables의 live/dead tuple 추정치, 마지막 vacuum과 autovacuum 시각, 실행 횟수를 보고 테이블별 패턴을 찾는다. 추정치는 정확한 실시간 행 수가 아니므로 한 번의 숫자보다 시간에 따른 증가 추세와 함께 본다.
SELECT relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
vacuum_count,
autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;오래 열린 트랜잭션이 정리 경계를 붙잡는다
VACUUM은 아직 어떤 트랜잭션이 볼 수 있는 행 버전을 제거하면 안 된다. 오래 열린 트랜잭션이 과거 스냅샷을 계속 유지하면, 이후 갱신으로 생긴 dead tuple 중 일부를 재사용 가능하다고 판단할 수 없다. autovacuum이 실행됐는데도 dead tuple이 줄지 않는다면 실행 주기만큼 오래된 스냅샷을 확인해야 한다.
pg_stat_activity에서는 트랜잭션 시작 시각과 상태를 볼 수 있다. 특히 idle in transaction 상태로 오래 남은 연결은 쿼리를 실행하지 않으면서도 트랜잭션을 붙잡을 수 있다. 애플리케이션이 연결을 반납하지 않았거나 수동 콘솔에서 BEGIN 후 방치한 경우가 여기에 해당할 수 있다.
SELECT pid,
usename,
state,
xact_start,
query_start,
wait_event_type,
wait_event,
left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;오래됐다는 이유만으로 세션을 즉시 종료해서는 안 된다. 백업, 마이그레이션, 분석 쿼리처럼 의도된 작업일 수 있다. 애플리케이션 이름, 사용자, 쿼리, 담당 작업을 확인한 뒤 종료 영향을 판단해야 한다. 근본적으로는 트랜잭션 범위를 짧게 유지하고 유휴 트랜잭션 타임아웃을 검토한다.
복제 슬롯과 피드백도 함께 확인한다
논리 복제 슬롯이 오래 소비되지 않으면 필요한 WAL이 보존되어 디스크 사용량이 커질 수 있다. 이것은 테이블 dead tuple과 같은 현상은 아니지만, “삭제했는데 디스크가 계속 늘어난다”는 관찰에서 함께 나타날 수 있다. 테이블 파일과 WAL 디렉터리 중 어디가 커지는지 먼저 구분해야 한다.
대기 복제본의 hot_standby_feedback 설정은 복제본 쿼리 취소를 줄이는 대신 주 서버가 일부 dead tuple을 제거하지 못하게 할 수 있다. 읽기 복제본에서 오래 실행되는 쿼리가 주 서버의 bloat에 영향을 줄 수 있다는 뜻이다. 복제 지연과 쿼리 취소, 주 서버 공간 사용의 교환을 이해하고 설정해야 한다.
따라서 디스크 원인을 볼 때는 테이블·인덱스 관계 크기, WAL 크기, 임시 파일과 로그를 나눠 측정한다. 전체 볼륨 사용률 하나만 보면 VACUUM 문제와 WAL 보존 문제를 혼동할 수 있다. PostgreSQL 내부 크기 함수와 운영체제 파일 시스템 관찰을 함께 사용한다.
테이블과 인덱스 크기를 따로 봤다
pg_total_relation_size는 테이블 본체뿐 아니라 인덱스와 TOAST까지 포함한 전체 크기를 보여 준다. pg_relation_size는 기본 관계 파일을, pg_indexes_size는 인덱스 합계를 확인할 때 사용할 수 있다. UPDATE가 잦은 열에 인덱스가 많다면 테이블보다 인덱스 bloat가 더 큰 부분을 차지할 수 있다.
SELECT pg_size_pretty(pg_relation_size('public.example')) AS table_only,
pg_size_pretty(pg_indexes_size('public.example')) AS indexes,
pg_size_pretty(pg_total_relation_size('public.example')) AS total;크기 숫자만으로 실제 bloat 비율을 정확히 계산할 수는 없다. 행 길이와 채우기 비율, TOAST, 페이지 내 빈 공간이 함께 영향을 준다. 확장이 허용된 환경에서는 pgstattuple로 더 자세히 볼 수 있지만 큰 테이블 전체 스캔 비용을 고려해야 한다.
UPDATE 패턴과 HOT의 관계
인덱스에 포함되지 않은 열을 갱신하고 같은 페이지에 새 버전을 둘 공간이 있으면 PostgreSQL은 HOT 업데이트를 사용할 수 있다. 이 경우 모든 인덱스에 새 항목을 추가하지 않아도 되어 인덱스 증가 비용을 줄일 수 있다. 하지만 페이지가 가득 차 있거나 갱신 열이 인덱스에 포함되면 조건이 달라진다.
fillfactor를 낮추면 페이지에 갱신용 여유를 남길 수 있지만, 읽기 밀도와 초기 디스크 사용을 희생한다. 갱신이 거의 없는 테이블에 일괄 적용할 이유는 없다. n_tup_upd와 n_tup_hot_upd의 비율, 실제 갱신 열과 인덱스를 보고 후보 테이블에 제한적으로 적용한다.
불필요한 인덱스는 INSERT와 UPDATE 때마다 새 엔트리 관리 비용을 만든다. 그렇다고 사용 횟수가 낮다는 이유만으로 삭제하면 월말이나 장애 대응에만 쓰는 중요한 쿼리를 망칠 수 있다. 인덱스 사용 통계, 쿼리 로그, 제약조건 역할을 확인하고 변경 전후 실행계획을 남겨야 한다.
작은 실험으로 확인하는 순서
개념을 확인하려면 운영 테이블이 아니라 별도 실험 테이블을 만든다. 충분한 행을 넣고 크기와 통계를 기록한 뒤 일부를 UPDATE하고 DELETE한다. 다른 세션에서 오래된 트랜잭션을 유지한 상태와 종료한 상태를 나눠 일반 VACUUM 뒤 dead tuple 추정치와 파일 크기를 비교한다.
실험할 때는 각 단계의 시간을 기록하고 ANALYZE가 통계에 미치는 영향도 구분한다. 단순히 SELECT 결과 행 수만 보면 MVCC 내부 공간 상태를 알 수 없다. 반대로 통계 추정치가 즉시 정확한 값이라고 가정하지도 않는다. 관찰 도구마다 보여 주는 범위를 적어 두는 편이 좋다.
VACUUM FULL 비교는 서비스 영향이 없는 환경에서만 한다. 실행 전후 관계 파일 번호와 크기, 걸린 시간, 잠금 대기, 추가 디스크 최대치를 본다. 이를 통해 일반 VACUUM의 공간 재사용과 FULL의 물리 재작성 차이를 숫자로 확인할 수 있다.
운영 점검 순서
첫째, 실제로 커진 것이 테이블인지 인덱스인지 WAL인지 구분한다. 둘째, 테이블 통계에서 dead tuple 추세와 마지막 autovacuum 시각을 본다. 셋째, 오래 열린 트랜잭션과 유휴 트랜잭션, 복제 슬롯과 복제본 쿼리를 확인한다. 넷째, autovacuum 로그와 진행 상황에서 작업이 시작되지 않는지, 시작했지만 따라가지 못하는지 나눈다.
그다음에야 테이블별 threshold와 scale factor, 작업 비용 제한, 동시 워커 수를 조정한다. 설정을 공격적으로 바꾸면 I/O가 몰려 애플리케이션 지연이 커질 수 있다. 한 테이블의 변경률이 특별하다면 전역값보다 테이블별 storage parameter로 좁게 조정하고 결과를 관찰한다.
긴급한 디스크 압박에서 FULL을 선택하더라도 백업과 롤백 계획, 잠금 시간, 여유 공간을 확인한다. 데이터 삭제를 더 실행하는 것은 이미 생긴 내부 빈 공간을 운영체제에 반환하지 못하며 오히려 WAL과 새 dead tuple을 늘릴 수 있다. 원인을 분리한 뒤 복구 방법을 고른다.
정리
PostgreSQL에서 DELETE 후 파일이 바로 줄지 않는 것은 MVCC와 공간 재사용 방식으로 설명할 수 있다. 옛 행 버전은 모든 스냅샷에서 불필요해진 뒤 정리할 수 있고, 일반 VACUUM은 그 공간을 같은 테이블이 다시 쓰게 만드는 데 초점을 둔다. 물리 파일 축소를 위한 FULL은 더 강한 잠금과 추가 공간을 요구한다.
따라서 해결 순서는 “VACUUM FULL부터 실행”이 아니라 무엇이 커졌는지, dead tuple이 왜 남는지, autovacuum이 따라가는지, 오래된 스냅샷과 복제가 정리를 막는지 확인하는 것이다. 이 글은 개념과 점검 쿼리를 정리한 노트이며, 실제 운영 조정값은 해당 데이터의 변경률과 관찰 결과로 결정해야 한다.