백엔드 커리큘럼 심화: PostgreSQL Timeout Budget, 쿼리·락·유휴 트랜잭션을 서로 다른 실패로 다루는 법

서비스가 느려질 때 DB timeout을 하나 추가하면 안전해 보인다. 하지만 statement_timeout = 500ms 같은 설정 하나는 세 가지 서로 다른 문제를 섞는다. SQL이 CPU·I/O 때문에 500ms 넘게 실행되는 경우, 빠른 SQL이 다른 트랜잭션 때문에 500ms 대기하는 경우, 트랜잭션만 열어 둔 채 애플리케이션이 아무 SQL도 보내지 않는 유휴 경우다. 실패 원인이 다르면 사용자에게 돌려줄 오류, 재시도 가능성, 고칠 대상도 다르다.

이 글의 목표는 timeout 값을 외우는 것이 아니라, 요청 전체 deadline에서 DB 예산을 역산하고 실패 유형마다 다른 PostgreSQL guardrail을 배치하는 것이다. Timeout/Retry/Backoff 설계의 상위 deadline 원칙, Connection Pool Sizing과 Saturation의 대기열 관점, Database Locking과 Contention의 lock 진단, JPA Transaction Boundary의 트랜잭션 범위를 함께 적용한다.

참고한 공식 문서:

• PostgreSQL Client Connection Defaults
• PostgreSQL SET
• PostgreSQL Transaction Isolation

이 글에서 얻는 것

• statement_timeout, lock_timeout, idle_in_transaction_session_timeout이 각각 어느 실패를 차단하는지 구분합니다.
• API deadline, JDBC/ORM timeout, PostgreSQL 세션 설정을 충돌 없이 정렬하는 방법을 익힙니다.
• 정상적인 긴 batch와 위험한 긴 transaction을 다른 role·다른 connection path로 분리할 수 있습니다.
• timeout 취소율, lock wait, connection acquire latency를 이용해 숫자를 조정하는 운영 기준을 만듭니다.

핵심 개념/이슈

1) timeout은 계층마다 한 개의 시간 예산을 공유해야 한다

사용자가 800ms 안에 응답을 받아야 하는 조회 API를 예로 들어 보자. 여기에 gateway timeout 800ms, 애플리케이션 future timeout 800ms, JDBC socket timeout 800ms, PostgreSQL statement_timeout 800ms를 각각 넣으면 안전망이 네 겹이 되는 것이 아니다. 연결을 빌리는 시간, JSON 직렬화, 네트워크 왕복, fallback 판단을 전혀 남기지 않아 가장 바깥 계층이 먼저 요청을 버리고 DB 작업은 계속될 수 있다.

실무에서는 바깥 deadline에서 안쪽 budget을 역산한다. 다음 수치는 출발점이며 서비스의 p95와 SLO에 맞춰 조정한다.

중요한 관계는 바깥 timeout > 안쪽 timeout이다. DB가 500ms에서 취소되고 앱이 600ms 안에 cleanup과 오류 분류를 마치는 편이, 앱이 먼저 500ms에 포기하고 DB가 800ms까지 실행되는 것보다 예측 가능하다. 긴 보고서 다운로드나 월말 정산을 온라인 API의 500ms 예산에 억지로 넣지 말고 비동기 job 또는 별도 read replica·role로 분리한다.

2) 세 PostgreSQL timeout은 관찰 대상이 다르다

PostgreSQL은 모두 세션 또는 트랜잭션 범위로 설정할 수 있지만, 중단하는 상태는 다르다.

statement_timeout은 SQL이 시작된 뒤의 전체 시간을 센다. 따라서 table scan, 느린 네트워크 스토리지, 다른 transaction의 lock 대기 모두 이 값에 닿을 수 있다. 반대로 lock_timeout은 lock을 기다리는 순간에만 의미가 있다. 주문 row를 갱신하는 SQL의 정상 실행은 20ms인데 간헐적으로 3초 wait가 생긴다면, 2초 statement timeout만 두기보다 150~300ms lock timeout으로 빠르게 충돌을 드러내고 blocker를 고치는 쪽이 보통 더 안전하다.

idle_in_transaction_session_timeout은 특히 과소평가된다. BEGIN 후 외부 결제 API를 호출하거나 화면 입력을 기다리면, 해당 세션은 SQL을 하지 않아도 snapshot·row lock·connection을 오래 잡을 수 있다. 이 상태는 autovacuum과 pool 모두에 해롭다. 긴 SQL을 10분 실행하는 batch와는 구분해야 한다. 전자는 active, 후자는 idle in transaction이라는 점부터 다르다.

3) 값은 전역 하나가 아니라 작업 성격별로 정한다

전역 default를 바꾸는 것은 가장 큰 blast radius를 가진다. 우선은 online read, online write, worker, migration을 나눈다.

-- 요청 트랜잭션 안에서만 적용한다. commit/rollback 뒤에는 사라진다.
BEGIN;
SET LOCAL statement_timeout = '500ms';
SET LOCAL lock_timeout = '200ms';

SELECT id, status, updated_at
FROM orders
WHERE customer_id = $1
ORDER BY updated_at DESC
LIMIT 50;
COMMIT;

SET LOCAL은 connection pool의 다음 사용자에게 설정이 새지 않게 하는 중요한 장치다. pool checkout 직후 일반 SET statement_timeout을 쓰면 reset 실패나 예외 경로에서 다른 endpoint가 같은 제한을 물려받을 수 있다. 반면 migration·reindex·대량 backfill처럼 일부러 긴 시간이 필요한 작업은 온라인 role의 예산을 올려서 해결하지 않는다. 별도 DB role, 별도 connection string, 명시된 maintenance window, 진행률·rollback 계획을 둔다.

초기 정책을 아래처럼 잡을 수 있다.

이 숫자는 보편 정답이 아니다. 예를 들어 200ms p99 API에서 1,500ms statement timeout은 사실상 사고를 숨긴다. 반대로 partition maintenance가 필요한 DB에서 500ms 전역 제한은 운영 절차를 깨뜨린다. 그래서 각 값에 owner와 적용 scope를 붙인다.

4) 취소는 성공도 실패도 아닌 ‘불확실성’을 만들 수 있다

PostgreSQL이 statement_timeout으로 statement를 취소하면 SQLSTATE 57014를 돌려준다. 하지만 애플리케이션이 받은 client-side timeout만으로는 DB가 rollback됐는지, 이미 commit했는지 알 수 없는 경우가 있다. 특히 write 요청을 재시도할 때 “응답이 없었으니 실패"라고 가정하면 중복 주문·중복 발송이 생긴다.

다음 순서로 복구 경로를 나눈다.

1. 읽기: 결과가 없어도 재시도가 안전한지, 같은 요청이 cache·replica에 부하를 더하지 않는지 확인한다.
2. 멱등 write: idempotency key나 unique business key로 결과를 먼저 조회한다. 이미 완료됐으면 이전 결과를 반환한다.
3. 비멱등 write: 자동 재시도하지 않는다. operation ledger나 수동 검토 상태로 넘긴다.
4. lock timeout: 재시도 전 blocker와 lock 순서를 확인한다. 같은 경쟁이 남아 있으면 retry는 대기열을 키운다.

이는 Timeout/Retry/Backoff 설계에서 말한 retry budget과 같은 원칙이다. timeout 비율이 1%에서 5%로 올랐을 때 재시도 2회를 모두 허용하면 DB 호출량이 얼마나 늘어나는지 계산하지 않으면, 보호 장치가 포화 가속기가 된다.

실무 적용

1) 관측표를 먼저 만들고 값을 바꾼다

설정 변경 전에 7일 기준선을 기록한다. query fingerprint마다 calls, p50/p95/p99, rows, shared/local block read, temporary file, SQLSTATE 57014 횟수를 수집한다. 동시에 pg_stat_activity에서 state, transaction 시작 시각, wait_event_type, wait_event를 확인하고, pool의 acquire p95와 active/idle connection 수를 같은 시각축에 둔다.

원인별로 첫 액션을 정해 둔다.

pg_cancel_backend를 수동으로 자주 쓰게 된다면 timeout 값이 없어서가 아니라 query ownership과 budget이 문서화되지 않았다는 신호일 수 있다. 취소한 세션 ID, SQL fingerprint, 호출 endpoint, 원인 분류를 남겨야 다음 alert가 같은 문제를 재현한다.

2) 10% canary에서 취소율과 사용자 결과를 같이 본다

처음에는 latency-sensitive read endpoint 하나에만 SET LOCAL statement_timeout을 적용한다. traffic의 10%로 30분 이상 관찰하고, 아래 abort 조건 중 하나라도 맞으면 값을 더 낮추지 않고 원인을 분석한다.

• 57014 cancel 비율이 0.1%를 넘거나 기준선의 2배가 된다.
• 해당 endpoint의 5xx 또는 fallback 비율이 기준선보다 0.2%p 이상 증가한다.
• pool acquire p95가 20% 이상 늘거나 active connection이 지속적으로 80%를 넘는다.
• 같은 query fingerprint의 p99가 낮아졌는데 retry count가 증가해 총 DB calls가 10% 이상 늘어난다.

성공은 평균 latency 하락이 아니다. p99, cancel, retry, pool, business success가 같이 안정적인 상태다. 이 다섯 가지가 안정된 뒤에 유사 endpoint로 확장한다. lock timeout은 hot row를 다루는 write에서 따로 canary하고, deadlock·serialization failure와 동일 오류로 집계하지 않는다.

3) 코드와 운영 예외를 분리한다

Spring/JPA 같은 ORM 환경에서는 transaction interceptor와 connection pool initialization SQL이 실제 설정 범위를 결정한다. request filter에서 deadline을 만들고 service method는 @Transactional 안에서 SET LOCAL을 적용한다. 다만 트랜잭션을 연 뒤 외부 HTTP 호출, 파일 업로드, message publish를 기다리면 idle guardrail을 피하려고 timeout을 올리는 대신 설계가 더 나빠진다. 데이터 변경을 commit한 뒤 outbox·worker로 후속 작업을 넘기거나, 외부 결과를 먼저 얻고 짧은 DB transaction을 연다.

DBA가 전역값을, 애플리케이션이 endpoint 예외를 각각 몰래 관리하면 회귀가 생긴다. 다음 네 가지를 하나의 config registry에 둔다.

• role/database 기본값과 변경 owner
• endpoint 또는 job별 SET LOCAL 값과 요청 deadline
• migration/maintenance의 별도 connection path와 만료 시각
• alert threshold, rollback 값, 예외 검토 일자

이 기록은 timeout을 “성능 튜닝 상수"가 아니라 운영 계약으로 만든다.

트레이드오프/주의점

첫째, 짧은 timeout은 대기열을 줄일 수 있지만 실제 서비스 용량을 만들지는 않는다. 인덱스 누락, 잘못된 join, hot row, 느린 스토리지가 원인이면 cancel 수만 늘고 사용자는 더 자주 실패한다. Query Plan Regression Guardrail처럼 계획과 데이터 분포를 먼저 점검해야 한다.

둘째, lock_timeout을 너무 낮추면 정상적인 짧은 경합도 오류가 되어 write 성공률이 떨어질 수 있다. 반대로 너무 길면 lock queue 뒤에 요청이 쌓인다. business operation의 허용 지연, 멱등성, blocker 제거 난이도로 결정한다.

셋째, cancel된 write의 결과는 앱이 모를 수 있다. HTTP 504를 받은 사용자에게 “실패"라고 단정하지 말고 idempotency ledger를 조회할 수 있어야 한다. timeout 수치만 바꾸고 결과 확인 경로를 만들지 않으면 신뢰성은 개선되지 않는다.

넷째, managed PostgreSQL 또는 proxy/PgBouncer 환경에서는 parameter 적용 scope와 reset 동작이 다를 수 있다. production 전에 SHOW로 실제 세션 값을 확인하고, transaction pooling에서 SET LOCAL이 commit/rollback 뒤 사라지는지 integration test로 검증한다.

체크리스트 또는 연습

• 사용자-facing API 하나의 최종 deadline에서 network, pool acquire, SQL, serialization 예산을 역산했다.
• statement_timeout, lock_timeout, idle_in_transaction_session_timeout의 owner·scope·rollback 값을 문서화했다.
• query p95/p99, lock wait, 57014, transaction age, pool acquire p95를 같은 dashboard에서 본다.
• online API와 migration/ETL이 다른 role 또는 connection path를 사용한다.
• timeout write는 idempotency key 또는 결과 ledger로 커밋 여부를 확인한다.
• 10% canary의 abort 조건과 30분 관측 지표를 배포 전에 합의했다.

연습으로 주문 조회 API 하나를 고른다. 현재 p99와 pool acquire p95를 측정한 뒤 800ms 전체 deadline을 각 계층에 나눠 보자. 다음으로 같은 DB에서 idle in transaction 세션을 하나 만들고, 실행 중인 10초 pg_sleep statement와 무엇이 다른지 pg_stat_activity에서 비교한다. 세 상태가 구분돼야 timeout 값도 안전하게 분리할 수 있다.

관련 글

• Timeout/Retry/Backoff 설계
• Connection Pool Sizing과 Saturation
• Database Locking과 Contention
• JPA Transaction Boundary
• Query Plan Regression Guardrail