PostgreSQL 72000 오류 원인과 해결 방법 완벽 가이드

72000 snapshot too old 는?

PostgreSQL 에러 코드 72000은 “snapshot too old” 에러로, 트랜잭션이 오래된 스냅샷을 참조하려 할 때 발생합니다. PostgreSQL은 MVCC(Multi-Version Concurrency Control) 메커니즘을 통해 데이터의 여러 버전을 유지하는데, old_snapshot_threshold 설정값을 초과한 오래된 스냅샷을 사용하는 트랜잭션이 이미 정리(vacuum)된 데이터 버전에 접근하려 할 때 이 에러가 발생합니다. 특히 장시간 실행되는 분석 쿼리나 배치 작업에서 자주 나타나며, 데이터 정합성과 성능 간의 트레이드오프를 이해하는 것이 중요합니다.

주요 발생 원인

• old_snapshot_threshold 설정값 초과

PostgreSQL 9.6부터 도입된 old_snapshot_threshold 파라미터는 스냅샷의 유효 시간을 제한합니다. 이 값이 예를 들어 60min으로 설정된 경우, 60분 이상 경과한 스냅샷은 더 이상 유효하지 않다고 판단되어 해당 스냅샷을 사용하는 트랜잭션이 정리된 데이터 버전에 접근하려 할 때 에러가 발생합니다. 기본값은 -1(비활성화)이므로, 이 값이 명시적으로 설정된 운영 환경에서 주로 발생합니다.

• 장시간 실행되는 트랜잭션 (Long-Running Transactions)

대규모 데이터 분석 쿼리나 ETL 배치 작업처럼 트랜잭션이 수 시간 동안 열려 있는 경우, 그 사이에 VACUUM이 오래된 행 버전을 정리할 수 있습니다. 트랜잭션이 시작 시점의 스냅샷을 기준으로 데이터를 읽으려 할 때, 이미 vacuum된 데이터 버전을 참조하게 되어 에러가 발생합니다. 이는 OLAP 워크로드가 OLTP 데이터베이스와 혼재하는 환경에서 특히 빈번하게 발생합니다.

• VACUUM 및 autovacuum의 공격적 실행

autovacuum_vacuum_cost_delay, autovacuum_vacuum_scale_factor 등의 파라미터가 공격적으로 설정되어 있거나, 수동으로 VACUUM AGGRESSIVE를 실행한 경우, 오래된 행 버전이 빠르게 정리됩니다. 이로 인해 아직 열려 있는 장시간 트랜잭션의 스냅샷이 참조하는 데이터가 사라지게 되어 snapshot too old 에러가 촉발될 수 있습니다. 특히 테이블에 대량 UPDATE/DELETE 작업 이후 autovacuum이 즉시 실행되는 상황에서 문제가 악화됩니다.

해결 방법

원인 1: old_snapshot_threshold 값 조정

현재 설정값을 확인하고, 워크로드에 맞게 조정합니다.

-- 현재 old_snapshot_threshold 설정 확인
SHOW old_snapshot_threshold;

-- postgresql.conf에서 설정 변경 (재시작 필요)
-- old_snapshot_threshold = 120min  -- 2시간으로 늘리기

-- 또는 ALTER SYSTEM으로 동적 변경 후 reload
ALTER SYSTEM SET old_snapshot_threshold = '120min';
SELECT pg_reload_conf();

-- 변경 확인
SELECT name, setting, unit, context
FROM pg_settings
WHERE name = 'old_snapshot_threshold';

> ⚠️ 값을 너무 크게 설정하면 VACUUM이 오래된 버전을 정리하지 못해 테이블 bloat이 발생할 수 있으므로 신중하게 조정해야 합니다.

---

원인 2: 장시간 트랜잭션 감지 및 종료

현재 오래 실행 중인 트랜잭션을 찾아 조치합니다.

-- 5분 이상 실행 중인 트랜잭션 확인
SELECT
    pid,
    usename,
    application_name,
    state,
    now() - xact_start AS transaction_duration,
    now() - query_start AS query_duration,
    left(query, 100) AS query_preview
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY transaction_duration DESC;

-- 특정 PID의 쿼리를 안전하게 취소 (트랜잭션 유지)
SELECT pg_cancel_backend(12345);

-- 트랜잭션 자체를 강제 종료 (더 강력한 조치)
SELECT pg_terminate_backend(12345);

-- statement_timeout, idle_in_transaction_session_timeout 설정으로 예방
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
ALTER SYSTEM SET statement_timeout = '3600000'; -- 1시간 (밀리초 단위)
SELECT pg_reload_conf();

---

원인 3: VACUUM 전략 조정 및 스냅샷 충돌 회피

autovacuum 설정을 조정하여 오래된 스냅샷과의 충돌을 줄입니다.

-- 현재 autovacuum 관련 설정 확인
SELECT name, setting, unit
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;

-- 특정 테이블의 autovacuum 적극성을 낮추기 (bloat 위험 있음)
ALTER TABLE large_analytics_table SET (
    autovacuum_vacuum_scale_factor = 0.2,
    autovacuum_vacuum_cost_delay = 20
);

-- 장시간 쿼리에서 트랜잭션을 나누어 실행하는 방식 (커서 활용)
BEGIN;
DECLARE my_cursor CURSOR FOR
    SELECT * FROM large_table WHERE condition = true;

-- 일정량씩 fetch하여 스냅샷 노출 시간 최소화
FETCH 1000 FROM my_cursor;
-- ... 처리 후
FETCH 1000 FROM my_cursor;

CLOSE my_cursor;
COMMIT;

---

에러 발생 시 즉각적인 진단 쿼리

-- 현재 스냅샷 상태 및 오래된 트랜잭션 종합 진단
SELECT
    datname,
    usename,
    pid,
    state,
    now() - xact_start AS xact_age,
    now() - query_start AS query_age,
    wait_event_type,
    wait_event,
    left(query, 200) AS current_query
FROM pg_stat_activity
WHERE state != 'idle'
  AND xact_start IS NOT NULL
ORDER BY xact_start ASC
LIMIT 20;

-- 데이터베이스별 oldest transaction 확인
SELECT
    datname,
    age(datfrozenxid) AS db_age,
    pg_size_pretty(pg_database_size(datname)) AS db_size
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

예방 방법

• idle_in_transaction_session_timeout 및 statement_timeout 적극 활용

운영 환경에서는 반드시 트랜잭션 제한 시간을 설정해야 합니다. idle_in_transaction_session_timeout을 설정하면 트랜잭션이 시작된 상태로 아무 작업도 하지 않는 연결을 자동으로 끊어주며, statement_timeout은 단일 쿼리가 지나치게 오래 실행되는 것을 방지합니다. 이 두 파라미터의 조합은 snapshot too old뿐만 아니라 lock 경합, connection pool 고갈 등 다양한 운영 이슈를 예방하는 데 핵심적인 역할을 합니다.

“`sql

— 세션 레벨에서 즉시 적용 (테스트용)

SET idle_in_transaction_session_timeout = ‘5min’;

SET statement_timeout = ‘1h’;

— 전역 설정 (postgresql.conf 또는 ALTER SYSTEM)

ALTER SYSTEM SET idle_in_transaction_session_timeout = ‘300000’; — 5분

ALTER SYSTEM SET statement_timeout = ‘3600000’; — 1시간

SELECT pg_reload_conf();

“`

• OLAP 워크로드는 읽기 전용 복제본(Read Replica)으로 분리

장시간 실행되는 분석 쿼리는 운영 Primary 서버가 아닌 Streaming Replication 기반의 Hot Standby(읽기 전용 복제본)에서 실행하도록 아키텍처를 구성해야 합니다. 이렇게 하면 Primary 서버의 VACUUM 사이클에 영향을 주지 않으면서 대용량 분석 쿼리를 안전하게 실행할 수 있고, snapshot too old 에러 발생 가능성을 구조적으로 차단할 수 있습니다. 복제본에서는 old_snapshot_threshold를 더 넉넉하게 설정하거나 비활성화(-1)하는 것도 고려할 수 있습니다.

관련 에러

• 40001 (serialization_failure): SERIALIZABLE 격리 수준에서 트랜잭션 충돌 시 발생하는 에러로, MVCC 스냅샷 관리와 밀접한 관련이 있습니다.
• 55P03 (lock_not_available): 잠금 획득 실패 에러로, 장시간 트랜잭션이 lock을 보유하면서 snapshot too old와 함께 복합적으로 발생할 수 있습니다.
• 57014 (query_canceled): statement_timeout이나 lock_timeout 초과로 쿼리가 취소될 때 발생하며, snapshot too old 예방 설정과 함께 나타날 수 있는 에러입니다.
• XX001 (data_corrupted): 극단적인 경우 오래된 스냅샷으로 인한 데이터 접근 문제가 더 심각한 에러로 이어질 수 있어 함께 모니터링이 필요합니다.

---

본 포스트는 AI가 생성한 기술 가이드입니다. 운영 환경 적용 전 충분한 검토를 권장합니다.