PostgreSQL 오류: canceling statement due to conflict with recovery

postgresql error canceling statement due to conflict with recovery

리드 리플리카(Hot Standby)에서 “읽기”가 길어질수록, “복구(WAL 적용)”가 우선권을 가져 쿼리가 취소될 수 있다.

개요

이 오류는 주로 Streaming Replication 환경의 Standby(읽기 전용 리플리카)에서 발생한다. Standby는 Primary에서 전달된 WAL을 빠르게 적용해야 하는데, 동시에 실행 중인 SELECT가 WAL 적용 과정(특히 VACUUM/정리로 인해 필요해진 튜플 제거, 페이지 변경)을 방해하면 PostgreSQL이 쿼리를 강제로 취소해 복구(리플레이)를 진행한다.

실무 기준으로 보면
“쿼리 성능 문제”처럼 보여도, 본질은 리플리카가 지연되지 않도록 복구가 쿼리를 밀어내는 정책이다.
단기 해결은 설정 조정이지만, 근본 해결은 장시간 읽기 쿼리/트랜잭션을 줄이는 설계다.

환경

• PostgreSQL: Hot Standby(Streaming Replication) 구성
• 발생 위치: Standby(읽기 전용) 노드에서 SELECT 수행 중
• 동시 상황: Primary에서 VACUUM, UPDATE/DELETE, DDL, 대량 배치 등 WAL 생성/적용이 활발

운영 환경에서는 “리드 리플리카를 리포팅/배치 조회에 쓰는 패턴”에서 특히 잘 터진다. 리포팅 쿼리가 오래 걸리면, 그 사이 Primary의 변경분이 누적되고 Standby는 WAL 적용을 밀어붙이려 하면서 충돌이 생긴다.

증상

Standby에서 다음과 같은 메시지가 로그/애플리케이션 에러로 나타난다.

ERROR:  canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.

또는 아래 유형이 함께 보일 수 있다.

ERROR:  canceling statement due to conflict with recovery
DETAIL: Canceling statement due to conflict with recovery.
HINT: In a moment you might be able to reconnect to the database and repeat your command.

포인트는 “Standby가 읽기 쿼리를 살리느냐” vs “복구(WAL 적용)를 살리느냐”의 선택에서, 기본적으로 복구가 우선권을 가져 쿼리가 취소된다는 점이다.

1차 점검

1) 발생 노드가 Standby인지 확인

SELECT pg_is_in_recovery();

결과가 true면 Standby에서 발생한 케이스로 분류해도 된다.

2) 리플리케이션 지연 확인

-- Standby에서 대략적인 리플레이 지연(시간)
SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag;

3) 장시간 실행 쿼리/트랜잭션 확인

SELECT pid, usename, state, now() - query_start AS runtime, left(query, 200) AS q
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY runtime DESC;

체크 기준
runtime이 길고(예: 수십 초~수분), 리포팅/집계/대량 스캔 성격이면 충돌 확률이 급증한다.
특히 “트랜잭션을 열어둔 채 오래 읽는 패턴”은 Standby에서 가장 위험하다.

심화 분석

왜 VACUUM/정리가 Standby의 SELECT를 깨는가

Primary에서 UPDATE/DELETE가 반복되면 MVCC 특성상 “죽은 튜플(dead tuples)”이 쌓이고, VACUUM이 이를 정리한다. Standby는 WAL을 적용하면서 그 정리 작업을 재현해야 한다. 그런데 Standby에서 어떤 SELECT가 오래 걸리며 과거 스냅샷을 유지하고 있으면, WAL 적용은 “지워야 하는 튜플을 아직 쿼리가 볼 수도 있다”라고 판단한다. 그 순간 복구를 멈출 수 없으니, PostgreSQL은 해당 SELECT를 취소한다.

대표적으로 충돌을 키우는 패턴

• Standby에서 장시간 수행되는 대용량 리포트/집계 쿼리
• 커서/페이징을 트랜잭션으로 길게 묶어 스냅샷을 오래 유지
• ORM이 읽기 트랜잭션을 길게 잡고 연결을 유지하는 구성
• Primary에서 대량 UPDATE/DELETE, VACUUM aggressive, DDL 작업이 잦음

복구

A) 단기: 쿼리 재시도/타임아웃 정책

이 오류는 Standby의 정상 동작 정책으로도 발생할 수 있으므로, 애플리케이션 레벨에서 재시도(Backoff 포함)를 넣으면 사용자 영향이 줄어든다. 다만 재시도만으로 해결하려 하면 같은 쿼리가 반복 취소될 수 있다.

B) 단기: Standby 지연 허용(쿼리를 더 살리기)

Standby에서 “복구가 기다려줄 수 있는 최대 시간”을 늘리는 방식이다. 대표적으로 아래 파라미터가 연결된다.

-- standby가 스트리밍 WAL 적용을 얼마나 기다릴지(예: 30s, 2min 등)
-- postgresql.conf (standby)
max_standby_streaming_delay = '30s'
max_standby_archive_delay   = '30s'

값을 늘리면 SELECT가 덜 취소되는 대신, Standby의 리플레이 지연이 커질 수 있다. “리포팅은 되는데 리플리카가 몇 분 늦는” 상태를 허용할 수 있는지부터 결정해야 한다.

C) 단기~중기: hot_standby_feedback 사용

Standby가 Primary에게 “내가 아직 이 스냅샷을 보고 있으니, 너무 공격적으로 정리(VACUUM)하지 말아달라”는 힌트를 주는 설정이다.

-- postgresql.conf (standby)
hot_standby_feedback = on

주의
hot_standby_feedback는 Standby의 SELECT를 살리는 데 도움 되지만,
Primary에서 VACUUM이 정리를 못해 테이블/인덱스 bloat(팽창)가 커질 수 있다.
“쿼리 취소 vs bloat 증가”의 트레이드오프를 운영적으로 관리해야 한다.

D) 근본: 장시간 읽기 쿼리/트랜잭션 줄이기

• 리포팅 쿼리 최적화: 필요한 컬럼만, 필터/인덱스 점검, 불필요한 전체 스캔 제거
• 쿼리 분할: 날짜/ID 범위로 쪼개 배치 실행(각 실행 시간을 짧게)
• 커서/페이징 개선: 긴 트랜잭션 기반 페이징 대신 Keyset pagination 고려
• 연결/트랜잭션 관리: read-only 트랜잭션을 오래 붙잡지 않도록 코드/ORM 설정 점검
• 워크로드 분리: 무거운 분석은 별도 분석용 리플리카/웨어하우스로 이동

재발 방지

1) 운영 기준선 정하기

• Standby 최대 허용 지연(예: 10초/30초/1분)을 숫자로 합의
• 리포팅 쿼리 95퍼센타일 목표 시간(예: 5초/10초)을 합의
• 취소 오류 발생률/시간대(배치 창) 모니터링 기준 설정

2) 파라미터 조합의 “의도”를 문서화

max_standby_streaming_delay를 늘리면 쿼리는 살지만 지연이 늘고, hot_standby_feedback를 켜면 쿼리는 살지만 Primary bloat가 늘 수 있다. 운영 환경에서는 “왜 이 값을 선택했는지”가 사후 점검에서 핵심 근거가 된다.

3) 모니터링 포인트

• Standby replay lag(지연) 추세
• 오류 로그에서 conflict with recovery 발생 빈도
• Primary의 bloat 징후(테이블/인덱스 팽창), autovacuum 동작
• 장시간 트랜잭션/쿼리 상위 N

현장 팁
“리포팅 쿼리가 길어지는 시간대”와 “Primary에서 대량 변경이 일어나는 시간대”가 겹치면 거의 확정적으로 터진다.
배치 창 조정(시간 분리)만으로도 오류 빈도가 확 줄어드는 경우가 많다.

한 번에 정리

• 원인: Standby에서 장시간 SELECT가 WAL 적용(복구)과 충돌해 PostgreSQL이 쿼리를 취소
• 즉시 대응: 재시도/백오프 + 장시간 쿼리 확인(쿼리/트랜잭션 줄이기)
• 설정 옵션: max_standby_*_delay로 지연을 허용하거나, hot_standby_feedback로 쿼리 취소를 줄임
• 주의점: 지연 허용은 최신성 저하, feedback은 Primary bloat 증가 가능
• 근본 해결: 리포팅 쿼리 최적화·분할·워크로드 분리로 “긴 스냅샷 유지”를 제거