idle_in_transaction_session_timeout, 이거 하나로 커넥션 풀이 살아난다

오늘 알게 된 건 아니고 예전부터 알고는 있었는데, 최근에 신규 서비스 튜닝 도우면서 "아 이거 진짜 모르는 팀 아직도 많구나" 싶어서 짧게 남긴다.

PostgreSQL 파라미터 중에 idle_in_transaction_session_timeout 라는 게 있다. 이름 그대로 트랜잭션 시작해놓고 아무것도 안 하는 세션을 강제 종료해주는 값이다. 기본값은 0, 즉 비활성이다.

왜 이게 문제가 되나

전형적인 시나리오는 이렇다.

앱에서 BEGIN 날려서 트랜잭션 열어놓고, 그 사이에 외부 API 호출한다. 근데 그 API가 30초쯤 걸린다. 그동안 이 커넥션은 어떤 상태냐면 idle in transaction. 살아는 있는데 아무 일도 안 하고, 그런데 락은 잡고 있을 수도 있다.

이런 커넥션이 몇 개 쌓이면 pg_stat_activity 볼 때 이렇게 나온다.

SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'app'
GROUP BY state;

-- state                | count
-- ---------------------|-------
-- active               |    12
-- idle                 |    34
-- idle in transaction  |    47  <-- 이게 늘어난다

이게 늘어나면 뭐가 문제냐. 커넥션 풀 max에 걸린다. PgBouncer 앞에 둬도 마찬가지다. PgBouncer의 transaction 풀 모드도 트랜잭션이 열려 있는 동안엔 서버 커넥션을 놓지 못한다. 그래서 앱 커넥션은 남아도는데 실제 백엔드에서는 커넥션이 부족해지는 이상한 상황이 온다.

그래서 얼마로 설정하나

우리 팀에서는 이렇게 걸어둔다.

ALTER DATABASE app SET idle_in_transaction_session_timeout = '30s';

30초는 좀 짧지 않냐 싶을 수 있는데, 정상적인 앱 로직이라면 트랜잭션 안에서 30초 넘게 대기하는 경우는 거의 없다. 있으면 그게 버그다. 트랜잭션 안에서 외부 호출 하지 말라는 얘기를 코드 리뷰로 다 잡을 수는 없으니 DB 레벨에서 방어선 하나 치는 셈이다.

전역으로 postgresql.conf에 걸어도 되지만, 특정 워크로드(예: ETL, 마이그레이션 잡)에서는 오래 걸리는 트랜잭션이 정상일 수 있어서 데이터베이스나 롤 단위로 거는 게 안전하다.

-- 배치용 롤은 예외
ALTER ROLE batch_worker SET idle_in_transaction_session_timeout = 0;

주의할 점

PgBouncer 문서에도 나오지만, 미들웨어 뒤에 있으면 커넥션이 갑자기 끊길 때 앱이 이상하게 반응할 수 있다. 그래서 앱단에서 재시도 로직이 있는지 먼저 확인하고 걸어야 한다. 우리는 처음 도입할 때 스테이징에서 5s로 걸어놓고 하루 관찰했다. 예상 못했던 배치 스크립트가 하나 죽어서 그건 롤 단위로 제외했다.

statement_timeout, lock_timeout 하고 헷갈리기 쉬운데 셋 다 성격이 다르다. 짧게 정리하면:

• statement_timeout: 쿼리 한 방이 이 시간 넘으면 종료
• lock_timeout: 락 획득 대기가 이 시간 넘으면 종료
• idle_in_transaction_session_timeout: 트랜잭션 안에서 놀고 있으면 종료

세 개 다 걸어두는 게 마음이 편하다.

혹시 다른 방법 쓰시는 분 있으면 알려주세요. 우리는 아직 이걸로 큰 문제는 없었는데 대규모 환경에선 또 다른 이슈가 있을지도 모르겠다.