Aurora PostgreSQL 장기 실행 쿼리가 Vacuum과 테이블 팽창에 미치는 영향 분석

Aurora PostgreSQL을 운영하다 보면 데이터 사용량은 크게 변하지 않았는데 테이블과 인덱스의 저장 공간이 계속 증가하거나, Autovacuum이 실행됐음에도 죽은 행이 줄어들지 않는 현상을 만날 수 있다. 이런 문제는 단순히 Autovacuum 성능이 부족해서 발생하는 것이 아니다. 장기 실행 쿼리나 종료되지 않은 트랜잭션이 오래된 행 버전을 계속 필요로 하면 Vacuum은 해당 행을 안전하게 제거할 수 없다.

특히 대규모 조회, 배치 작업, 잘못 관리된 데이터베이스 세션이 동시에 존재하는 환경에서는 테이블 팽창이 빠르게 진행될 수 있다. 저장 공간 증가뿐만 아니라 쿼리 성능 저하, 캐시 효율 감소, I/O 부하 상승으로 이어질 수 있으므로 장기 실행 쿼리와 Vacuum의 관계를 정확하게 이해해야 한다.

PostgreSQL MVCC와 Vacuum의 작동 원리

PostgreSQL은 MVCC(Multi-Version Concurrency Control)를 사용해 조회와 변경 작업이 서로를 과도하게 차단하지 않도록 한다. UPDATE가 실행되면 기존 행을 직접 덮어쓰는 대신 새로운 행 버전을 생성하고, 기존 버전은 더 이상 사용되지 않는 죽은 행(Dead Tuple)로 남는다. DELETE 역시 행을 즉시 물리적으로 제거하지 않고 삭제된 것으로 표시한다.

이 구조 덕분에 한 트랜잭션이 데이터를 변경하는 동안 다른 트랜잭션은 자신이 시작한 시점의 일관된 데이터를 조회할 수 있다. 그러나 변경 전 행 버전은 모든 관련 트랜잭션에서 더 이상 필요하지 않다는 사실이 확인될 때까지 제거할 수 없다.

Vacuum은 오래된 행 버전을 정리해 공간을 재사용할 수 있도록 만들고, Visibility Map과 통계 정보를 관리한다. 일반 Vacuum은 운영체제에 파일 공간을 바로 반환하는 작업이 아니라 테이블 내부에서 해당 공간을 다시 사용할 수 있도록 정리하는 작업이다. 따라서 Vacuum이 정상적으로 수행돼도 테이블 파일 크기가 즉시 감소하지 않을 수 있다.

Autovacuum은 테이블별 변경량이 설정된 기준을 넘으면 자동으로 실행된다. 하지만 Autovacuum 프로세스가 실행됐다는 사실과 죽은 행을 실제로 제거했다는 것은 서로 다른 의미다. 오래된 스냅샷을 유지하는 트랜잭션이 있으면 Vacuum은 해당 트랜잭션에서 보일 가능성이 있는 행 버전을 그대로 남겨야 한다.

장기 실행 쿼리가 죽은 행 정리를 방해하는 과정

트랜잭션이 시작되면 PostgreSQL은 현재 데이터 상태를 판단하기 위한 스냅샷을 사용한다. 이후 다른 세션에서 UPDATE나 DELETE가 반복되더라도 장기 트랜잭션은 자신의 스냅샷을 기준으로 데이터를 확인해야 한다. 이 때문에 Vacuum은 장기 트랜잭션보다 새로운 시점에 발생한 죽은 행을 바로 제거하지 못할 수 있다.

문제는 SQL 실행 시간이 긴 경우에만 발생하지 않는다. 애플리케이션이 트랜잭션을 시작한 뒤 COMMIT이나 ROLLBACK을 수행하지 않은 idle in transaction 세션도 오래된 스냅샷과 트랜잭션 ID를 유지할 수 있다. 실제 운영에서는 몇 시간 동안 실행된 분석 쿼리보다 커넥션 풀에 방치된 idle in transaction 세션이 원인인 경우도 많다.

다음 SQL을 사용하면 Writer 인스턴스에서 장시간 유지되는 트랜잭션을 확인할 수 있다.

SELECT pid,
       usename,
       application_name,
       client_addr,
       state,
       xact_start,
       query_start,
       now() - xact_start AS transaction_duration,
       now() - query_start AS query_duration,
       wait_event_type,
       wait_event,
       LEFT(query, 200) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

여기서 query_start만 확인하면 원인을 놓칠 수 있다. 현재 SQL은 짧게 실행되고 있어도 xact_start가 오래됐다면 동일 트랜잭션이 장시간 유지되고 있을 가능성이 있다. 따라서 쿼리 실행 시간과 트랜잭션 유지 시간을 함께 비교해야 한다.

Aurora Replica에서 실행되는 장기 조회는 Writer의 장기 트랜잭션과 동일한 방식으로 단정해서는 안 된다. Replica의 쿼리 충돌 처리와 피드백 관련 설정에 따라 오래된 행 정리와 복제 지연에 미치는 영향이 달라질 수 있다. Replica 쿼리가 필요한 행을 Writer의 Vacuum이 제거하면 해당 조회가 복구 충돌로 취소될 수 있으며, 피드백을 통해 정리를 늦추는 구성에서는 Writer 측 죽은 행 정리가 지연될 가능성도 있다. 따라서 Reader에서 장기 조회가 빈번하다면 쿼리 취소, Replica Lag, Vacuum 진행 상태를 함께 점검해야 한다.

테이블 팽창과 성능 저하를 진단하는 방법

Vacuum이 제거하지 못한 죽은 행이 계속 누적되면 테이블 팽창이 발생한다. 동일한 유효 데이터 건수를 저장하는 데 더 많은 데이터 페이지가 필요해지고, 순차 스캔과 인덱스 스캔에서 읽어야 하는 블록 수도 증가한다. Shared Buffers에 불필요한 페이지가 올라오면서 캐시 효율이 낮아지고 스토리지 I/O도 증가할 수 있다.

먼저 다음 SQL로 죽은 행이 많은 테이블을 확인할 수 있다.

SELECT schemaname,
       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
LIMIT 30;

n_dead_tup은 통계 기반 추정값이므로 실제 수치와 차이가 날 수 있다. 단순히 죽은 행 개수만 보지 말고 유효 행 대비 비율, 최근 Autovacuum 실행 시간, UPDATE·DELETE 발생량을 함께 확인해야 한다.

테이블과 인덱스 크기는 다음과 같이 조회할 수 있다.

SELECT schemaname,
       relname,
       pg_size_pretty(pg_table_size(relid)) AS table_size,
       pg_size_pretty(pg_indexes_size(relid)) AS index_size,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 30;

Autovacuum 실행 여부는 pg_stat_progress_vacuum에서 확인할 수 있다.

SELECT pid,
       datname,
       relid::regclass AS table_name,
       phase,
       heap_blks_total,
       heap_blks_scanned,
       heap_blks_vacuumed,
       index_vacuum_count
FROM pg_stat_progress_vacuum;

Autovacuum이 반복적으로 실행되는데 n_dead_tup과 테이블 크기가 계속 증가한다면 작업 횟수만 늘리는 방식으로 접근해서는 안 된다. 오래된 트랜잭션, 높은 변경량, Vacuum 처리 속도, 테이블별 설정을 함께 분석해야 한다.

또한 pg_stat_user_tables의 통계는 초기화될 수 있으므로 장기 추세 분석에는 CloudWatch 지표와 별도 수집한 테이블 통계를 사용하는 것이 좋다. 일정 시간마다 테이블 크기, 죽은 행 수, 마지막 Autovacuum 시간, 장기 트랜잭션 수를 저장하면 팽창이 시작된 시점과 배포·배치 작업의 연관성을 찾기 쉬워진다.

실무 트러블슈팅과 안전한 해결 방법

비슷한 현상을 점검할 때 가장 먼저 확인할 항목은 Autovacuum 파라미터가 아니라 오래된 트랜잭션이다. Autovacuum 관련 값을 공격적으로 변경해도 Vacuum이 제거할 수 없는 행은 그대로 남기 때문이다. 실제로 idle in transaction 세션을 정리한 뒤 다음 Vacuum부터 죽은 행이 빠르게 감소하는 경우가 있다.

장기 트랜잭션을 발견했다면 해당 세션이 정상적인 배치인지, 장애로 방치된 연결인지 먼저 확인해야 한다. 세션을 무조건 종료하면 처리 중인 작업이 롤백되거나 서비스 오류가 발생할 수 있다. 애플리케이션 담당자와 영향도를 확인한 후 필요한 경우 다음 함수를 사용해 쿼리 취소 또는 세션 종료를 수행한다.

SELECT pg_cancel_backend(대상_PID);

SELECT pg_terminate_backend(대상_PID);

pg_cancel_backend()는 현재 실행 중인 SQL을 취소하고 연결은 유지한다. pg_terminate_backend()는 세션 자체를 종료하므로 트랜잭션이 롤백된다. 대규모 변경 트랜잭션은 롤백에도 상당한 시간이 필요할 수 있으므로 긴급 상황에서도 영향도를 확인해야 한다.

원인 세션을 정리한 뒤에는 대상 테이블에 일반 Vacuum을 수행하고 결과를 확인할 수 있다.

VACUUM (VERBOSE, ANALYZE) 스키마명.테이블명;

일반 Vacuum은 테이블 내부 공간을 재사용 가능하게 하지만 이미 커진 파일을 대부분 운영체제에 돌려주지는 않는다. 파일 크기를 실제로 축소하려면 VACUUM FULL, 테이블 재작성 또는 온라인 재구성 방식을 검토해야 한다. VACUUM FULL은 강한 잠금을 요구하고 추가 작업 공간과 운영 중단 가능성이 있으므로 대형 운영 테이블에 즉시 실행하는 것은 위험하다.

재발 방지를 위해서는 애플리케이션 트랜잭션 범위를 줄이고 반드시 COMMIT 또는 ROLLBACK이 수행되도록 설계해야 한다. idle_in_transaction_session_timeout을 적용하면 일정 시간 이상 방치된 트랜잭션 세션을 자동 종료할 수 있지만, 정상 배치나 관리 작업까지 종료하지 않도록 워크로드별 기준을 정해야 한다.

변경량이 많은 테이블은 전역 Autovacuum 설정만으로 관리하기보다 테이블별 설정을 적용하는 편이 효과적이다.

ALTER TABLE 스키마명.테이블명
SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.01
);

위 값은 예시일 뿐이며 테이블 크기, 초당 변경량, 인스턴스 자원과 Vacuum 소요 시간을 기준으로 조정해야 한다. 너무 낮게 설정하면 Autovacuum이 과도하게 실행돼 CPU와 I/O 경쟁이 증가할 수 있다.

개인적인 운영 경험상 가장 효과적인 대응 순서는 장기 트랜잭션 확인, 원인 애플리케이션 식별, 안전한 세션 정리, 일반 Vacuum 수행, 팽창 정도 재측정, 테이블별 Autovacuum 조정 순서다. 처음부터 VACUUM FULL을 선택하기보다 죽은 행이 쌓인 근본 원인을 제거해야 같은 문제가 반복되지 않는다.

마치며

Aurora PostgreSQL에서 테이블 팽창이 발생하면 Autovacuum이 작동하지 않는다고 판단하기 쉽다. 그러나 실제로는 장기 실행 쿼리나 idle in transaction 세션이 오래된 행 버전을 필요로 해 Vacuum의 정리를 제한하는 경우가 많다.

테이블 팽창은 저장 공간만의 문제가 아니다. 읽어야 하는 데이터 페이지가 증가하고 인덱스 효율과 메모리 캐시 효율이 낮아지면서 전체 데이터베이스 응답 시간에도 영향을 줄 수 있다. 장기 트랜잭션 시간, 죽은 행 증가 추세, 테이블 크기, Autovacuum 진행 상태, Replica Lag을 함께 관찰해야 정확한 원인을 찾을 수 있다.

무엇보다 중요한 것은 응급조치보다 재발 방지다. 애플리케이션의 트랜잭션 범위를 짧게 유지하고, 방치 세션에 대한 제한을 설정하며, 변경량이 많은 핵심 테이블에는 개별 Autovacuum 정책을 적용해야 한다. 이러한 관리 체계를 갖추면 Aurora PostgreSQL의 저장 공간 증가와 예기치 않은 성능 저하를 사전에 발견하고 안정적으로 대응할 수 있다.