API는 504, replica DB는 EOF — PostgreSQL Hang 쿼리의 후폭풍

TL;DR
- 증상: 한 서비스에선 504 Gateway Timeout, 다른 서비스에선 replica DB 커넥션 강제 종료(EOF)
- 처음 본 단서: pg_stat_activity에 13시간 이상 active 상태인 SELECT 쿼리 2개
- 진짜 원인: 13시간 hang 쿼리가 오래된 XID를 잡고 있어서 autovacuum 무력화
          → dead tuple 누적 (일부 테이블 dead_pct 5,704%)
          → 테이블 bloat → 일반 쿼리 성능 급락 → 504
          → primary가 vacuum WAL을 replica에 보내면 lock conflict
          → max_standby_streaming_delay 초과 → replica가 backend 강제 종료 → EOF
- 해결: pg_terminate_backend()로 hang 쿼리 종료 → autovacuum 정상 재개

이런 분께: PostgreSQL primary/replica 환경에서 504 + replica EOF가 함께 보이는 분

상황 (Context)

• 환경: AWS RDS PostgreSQL (primary + read replica), Spring Boot + HikariCP 백엔드
• 트래픽 패턴: 시계열 가격 테이블에 대한 read-heavy 워크로드 (price_latest, price_hour, price_minute 등 시계열 파티션 테이블)
• 시점: 평소 안정적이던 서비스가 어느 오후 갑자기 두 가지 다른 증상으로 동시 장애

마주한 문제

1. 일부 API에서 504 Gateway Timeout

GatewayTimeout: [504 Gateway Timeout] during [GET] to
[http://backend-price-svc.../v1/price/with-market-status?ric=...]
[upstream request timeout]

2. Replica DB 커넥션이 갑자기 끊김

HikariPool-2 - Connection marked as broken because of SQLSTATE(08006), ErrorCode(0)
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
Caused by: java.io.EOFException: null

• SQLSTATE(08006) = connection_failure — DB가 연결을 강제 종료
• EOFException = 소켓이 예고 없이 닫힘 — replica 쪽에서 backend를 강제 kill했다는 신호

두 증상은 다른 서비스에서 동시에 발생. 504는 read 쿼리가 느려서 timeout, EOF는 커넥션 자체가 끊긴 것. 직관적으론 둘이 무슨 관계인지 안 보였다.

가설과 시도 — what didn't work

가설 1. 일시적 트래픽 burst

→ 탈락. CloudWatch 메트릭상 RDS CPU/IOPS 모두 평소 수준. 일부 쿼리만 느림.

가설 2. HikariPool 설정 문제

EOF가 났으니 커넥션 풀 max-lifetime이나 idle timeout 문제?

→ 탈락. EOF는 서버 쪽에서 끊은 것. 클라이언트 풀 설정으론 발생 안 함. 서버에서 강제 종료한 이유를 따로 찾아야 함.

가설 3. Replica replication lag

WAL 적용이 늦어졌나?

→ 단서. lag 자체보다 왜 lag가 났는지가 핵심이었다. 이 시점에 pg_stat_activity를 들여다봤다.

원인 분석

단서 — 13시간 동안 active한 SELECT 쿼리

SELECT pid, usename, state, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

pid usename state duration

3298	read_user	active	13시간 40분 31초
3395	read_user	active	13시간 38분 47초

같은 패턴의 SELECT 두 개가 13시간 넘게 살아있었다. 쿼리 형태:

SELECT v.ric, v.pub_dt, p.close_price
FROM (VALUES
    ($1::text, $2::timestamptz),
    ($3::text, $4::timestamptz),
    -- ... 130+ 파라미터 바인딩 쌍
) AS v(ric, pub_dt)
JOIN price p ON p.ric = v.ric AND p.pub_dt = v.pub_dt

평소엔 빠르게 끝나야 할 쿼리. 그런데 한 번 hang에 빠지면서 13시간 동안 살아있었다.

메커니즘 — Hang 쿼리가 Autovacuum을 무력화

PostgreSQL은 UPDATE/DELETE 시 기존 행을 즉시 삭제하지 않고 dead tuple로 남겨둔다 (MVCC). Autovacuum이 주기적으로 dead tuple을 회수해서 공간을 재사용.

여기서 함정: PostgreSQL은 오래된 트랜잭션이 살아있는 동안 그 트랜잭션보다 최신의 dead tuple을 정리할 수 없다. 트랜잭션 snapshot에 여전히 "보여야 할" 버전일 수 있기 때문.

hang 쿼리 (13시간 active)
  └→ 오래된 트랜잭션 XID가 살아있음
       └→ autovacuum이 돌긴 하지만 그 XID 이전 시점 dead tuple은 정리 불가
            └→ dead tuple 계속 누적
                 └→ 테이블 bloat → 다른 쿼리 성능 급락

Dead Tuple 누적 현황 (hang 쿼리 살아있는 시점)

relname n_dead_tup n_live_tup dead_pct

price_latest	4,676,013	81,977	5,704%
price_day_tmp	11,863,541	2,021,746	587%
price_hour	21,071,960	22,611,878	93%
price_minute	17,476,637	124,554,459	14%

dead_pct = n_dead_tup / n_live_tup × 100. 100% 초과는 dead tuple이 live tuple보다 많다는 뜻. price_latest는 live row 8만 개에 dead row 467만 개 — 한마디로 폐허.

당연히 이런 테이블에 가는 쿼리는 모두 느려졌다. 백엔드 API에서 504가 난 이유.

두 번째 메커니즘 — Replica Recovery Conflict로 인한 EOF

Primary에서 dead tuple 정리 시도 → vacuum WAL이 replica에 전송 → replica가 같은 테이블에 lock 잡으려고 시도 → 그런데 replica에서도 그 테이블을 read 쿼리가 사용 중.

[Primary]
  vacuum 시도 → vacuum WAL 생성 (RowExclusiveLock 포함)

[Replica]
  WAL 수신 → 적용 위해 테이블 lock 시도
    └→ replica의 read 쿼리가 같은 테이블 참조 중
         └→ lock conflict → max_standby_streaming_delay 동안 대기
              └→ timeout 초과 → replica가 충돌하는 backend를 강제 종료 (EOF)

이게 backend에서 EOFException + SQLSTATE(08006) 으로 보이는 원인. 클라이언트가 읽고 쓰는 도중 서버가 socket을 끊어버린 것.

504와 EOF가 동시에 발생한 이유: 둘 다 같은 hang 쿼리에서 출발한 두 갈래 후폭풍.

해결

-- 1. hang 쿼리 식별
SELECT pid, usename, state, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '10 minutes'
ORDER BY duration DESC;

-- 2. hang 쿼리 종료
SELECT pg_terminate_backend(3298);
SELECT pg_terminate_backend(3395);

종료 후 자동으로 일어난 회복:

1. 오래된 XID 해소 → autovacuum이 dead tuple 정리 재개
2. Replica recovery conflict 해소 → WAL 정상 적용
3. HikariPool 끊겼던 커넥션 재연결 → DB 조회 정상화
4. backend 응답 정상 → 504 해소

Before / After (대표 테이블)

relname dead_pct (before) dead_pct (after)

price_latest	5,704%	(목록에서 제거)
price_day_tmp	587%	(목록에서 제거)
price_hour	93%	(목록에서 제거)

상위 5개 테이블이 autovacuum 정상 재개로 dead_pct 상위 목록에서 빠짐.

재발 방지

근본 해결책은 long-running query 자체를 막는 것. 4가지를 같이 가야 안전:

1. Statement Timeout (가장 효과적)

특정 계정/DB의 쿼리가 일정 시간 이상 실행되면 자동 kill.

ALTER ROLE read_user SET statement_timeout = '300000'; -- 5분

-- 또는 DB 전체
ALTER DATABASE <dbname> SET statement_timeout = '300000';

2. Idle in Transaction Timeout

트랜잭션을 열고 idle 상태로 방치되는 커넥션도 비슷한 함정 — 자동 종료 설정.

ALTER ROLE read_user SET idle_in_transaction_session_timeout = '60000'; -- 1분

3. Long-Running Query 모니터링·알림

SELECT pid, usename, state, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

CloudWatch / Grafana 대시보드에 5분 초과 active 쿼리 메트릭 + Slack 알림.

4. Dead Tuple 비율 모니터링

SELECT schemaname, relname,
       n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / nullif(n_dead_tup + n_live_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY dead_pct DESC;

dead_pct 10% 이상 테이블은 알림 대상.

배운 점

1. 504와 replica EOF가 같은 시점에 보이면 동일 근본 원인을 의심하라.
직관적으론 따로 보이는 증상이지만, PostgreSQL primary/replica 환경에서는 autovacuum 정체 → dead tuple 누적 → 1) 일반 쿼리 성능 저하 (504), 2) replica recovery conflict (EOF) 두 갈래로 갈 수 있다. 두 증상이 동시에 보이면 hang 쿼리부터 의심.

2. pg_stat_activity는 첫 번째 진단 도구.
"DB 느리다", "커넥션이 끊긴다" 같은 증상에서 출발할 때 가장 먼저 봐야 할 view. state = 'active'이면서 query_start가 오래된 row를 찾는 게 30초면 끝나는 진단.

3. PostgreSQL Autovacuum은 오래된 트랜잭션이 있으면 무력화된다.
Autovacuum이 돌고 있다고 해서 dead tuple이 정리되고 있다는 보장이 아니다. 활성 hang 쿼리/idle in transaction 트랜잭션이 있으면 그 XID 이전 시점은 정리 불가. dead_pct가 비정상적으로 높은데 last_autovacuum 시각이 최근이면 → 100% hang 쿼리 의심.

4. Replica의 EOF는 클라이언트 잘못이 아니다.
HikariPool max-lifetime이나 클라이언트 idle 설정을 의심하기 쉬운데, EOF는 서버가 끊은 것. PostgreSQL replica에서는 max_standby_streaming_delay 초과 시 충돌하는 backend를 강제 종료한다 — 클라이언트 풀 튜닝으로는 못 막음. 원인은 항상 primary/replica 동기화 충돌.

5. statement_timeout은 PostgreSQL 운영의 안전벨트.
운영 DB에는 read 계정 단위로라도 반드시 걸어두자. 최악의 경우 일부 쿼리가 timeout으로 실패하지만, 그게 13시간 hang으로 전체 DB가 폐허가 되는 것보단 훨씬 낫다.

참고

• PostgreSQL — Routine Vacuuming
• PostgreSQL — Hot Standby Recovery Conflicts
• PostgreSQL — Statement Timeout
• HikariCP — SQLSTATE 08006 / connection failure