PostgreSQL: ERROR [40001]: canceling statement due to conflict with recovery

※ ERROR: canceling statement due to conflict with recovery

※ DETAIL: User was holding shared buffer pin for too long.

 

안녕하세요. 듀스트림입니다.

 

오늘 포스팅은 PostgerSQL HA 환경을 운영하다 보면 자주 만나게 되는 복구 충돌 오류(recovery conflict)에 대한 내용입니다.

---

1. 발생 원인 요약

Hot Standby 환경에서의 Read 쿼리 충돌

• 슬레이브(standby) 노드에서 SELECT 같은 읽기 쿼리를 실행 중일 때, 동시에 마스터 노드에서는 해당 페이지를 변경하는 WAL 로그를 생성 및 전송합니다.
• 슬레이브는 WAL을 즉시 replay해야 하지만, 해당 페이지가 읽히고 있어서 shared buffer pin이 잠겨 있는 이유로 redo를 시킬 수 없습니다.
• PostgreSQL은 데이터 일관성을 보장하기 위해 해당 세션을 강제로 취소(cancel) 시킵니다.

"Standby에서 WAL을 적용할 때, 어떤 쿼리 때문에 적용이 막히면 최대 max_standby_streaming_delay까지만 기다리고, 그 이후에도 안 풀리면 쿼리를 죽여라"라고 설정되어 있다고 이해하시면 됩니다.

---

2. 에러 구조 이해

항목	설명
shared buffer pin	PostgreSQL이 메모리 버퍼에서 특정 페이지를 "고정"하여 읽기/쓰기 작업 중에 다른 프로세스가 변경하지 못하게 하는 잠금 메커니즘
for too long	해당 핀을 너무 오래 잡고 있어 WAL replay를 지연시켰다는 의미
conflict with recovery	WAL replay(복구)와 사용자 쿼리 간의 충돌이 발생함

---

3. 해결 방안

3.1 max_standby_streaming_delay 조정

ALTER SYSTEM SET max_standby_streaming_delay = '5min';
SELECT pg_reload_conf();

• 리플레이 지연을 최대 5분까지 허용 (이 시간을 넘길 때까지 충돌을 해소하지 못한 Standby의 쿼리는 취소됩니다.)
• 복제 지연(latency)이 약간 늘어나지만, 쿼리 강제 취소 빈도를 줄일 수 있음

---

3.2 애플리케이션 측 리트라이 처리

• 읽기 전용(Standby)에서 SELECT를 수행하는 애플리케이션은 이 오류를 감지하면 자동 재시도(retry)하도록 구현
  (예: JDBC에서 SQLState = 40001 또는 57014 처리)

---

+ hot_standby_feedback = on

ALTER SYSTEM SET hot_standby_feedback = on;
SELECT pg_reload_conf();

• hot_standby_feedback은 Vacuum으로 인한 충돌만 막아줍니다.
• 슬레이브가 자신이 참조 중인 xmin 정보를 마스터에 전달 → Vacuum이 해당 튜플 삭제를 지연시킵니다.
• 단점으로 bloat (테이블 및 인덱스 팽창) 가능성 있음 → 장기 실행 쿼리가 많을수록 bloat 위험이 커집니다.

이때 발생하는 에러의 디테일은 아래와 같습니다.
DETAIL: User query might have needed to see row versions that must be removed.

---

꽤 자주보이는 오류입니다. 알아두면 좋겠죠?

오늘은 여기까지~