session pooling에서 transaction pooling으로 갈아탄 후 3주간 벌어진 일

지난달에 결국 pgbouncer 풀 모드를 session에서 transaction으로 바꿨다. 결과부터 말하면 잘 됐다. 근데 그 3주 동안 두 번은 새벽에 잠에서 깼고, 한 번은 회의 도중에 대시보드 보고 얼굴이 하얘졌다. 기록 삼아 남긴다.

왜 갈아탔나

우리 서비스는 백엔드 20개 파드가 각각 커넥션 풀 20을 잡는다. 그러니까 idle 상태에서도 Postgres 쪽에는 400개 커넥션이 잡혀 있다는 얘기다. Aurora r6g.2xlarge 기준 max_connections가 900 남짓인데, 스케일 아웃 몇 번 하면 아슬아슬해진다.

그동안 pgbouncer를 session pooling으로 쓰고 있었는데, 이건 사실상 아무 이득이 없었다. 클라이언트 커넥션 하나가 백엔드 커넥션 하나를 트랜잭션 단위가 아니라 세션 종료 전까지 붙잡고 있으니까. 결국 pgbouncer는 그냥 커넥션 프록시 역할만 하고 있었다.

transaction pooling으로 바꾸면 백엔드 커넥션이 트랜잭션 종료 시점에 풀로 반납된다. 400개 클라이언트 커넥션을 백엔드 30~40개로 처리할 수 있다는 계산이 나왔다. 이론상 그렇다.

1주차: prepared statement 대참사

바꾼 다음날 아침 9시, 에러율이 급등했다.

ERROR: prepared statement "S_3" does not exist

이거 처음 보고 진짜 당황했다. 우리 앱은 asyncpg를 쓰는데, 얘가 기본적으로 prepared statement를 씁니다. transaction pooling 모드에서는 같은 백엔드 커넥션이 요청마다 바뀔 수 있으니까, 이전 커넥션에 PREPARE된 statement가 다음 커넥션에는 없다. 그래서 저 에러가 뜬다.

pgbouncer 1.21부터 transaction 모드에서도 protocol-level prepared statement를 지원한다는 걸 알고는 있었다. max_prepared_statements를 0이 아닌 값으로 세팅하면 pgbouncer가 prepared statement 캐시를 관리해준다. 근데 우리 pgbouncer 버전이 1.18이었다. 몰랐지... 몇 년 전에 헬름 차트 처음 깔 때 그대로였다.

버전 올리고 config에 이거 추가.

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

이거만 하면 될 줄 알았는데, asyncpg 쪽에서도 issue가 있었다. asyncpg가 statement name을 랜덤하게 생성해서, 같은 쿼리라도 prepared statement 캐시가 히트를 못했다. 결국 asyncpg 3.x로 올리고 statement_cache_size=0 옵션도 검토했지만, 성능 저하 걱정에 캐시 살리는 쪽으로 갔다.

SHOW STATS_TOTALS로 pgbouncer 캐시 히트율을 계속 봤다. 처음엔 30%까지 떨어졌다가, 앱 재배포 몇 번 거치고 나서 85% 수준으로 안착했다.

2주차: SET LOCAL 안 먹는 문제

이번엔 데이터팀이 슬랙에 물어봤다. "왜 우리 배치 잡의 statement_timeout이 안 먹지?"

원인은 이거였다. 배치 잡은 커넥션 열고 SET statement_timeout = '30min' 실행한 다음 여러 쿼리를 순차적으로 돌리는 구조였는데, transaction pooling 모드에서는 이 SET이 그 트랜잭션 안에서만 살아있다. 트랜잭션 끝나면 커넥션 반납되면서 SET 값도 날아간다.

해결책은 두 가지였다. SET LOCAL을 매 트랜잭션마다 다시 실행하거나, 아니면 커넥션 문자열에 options='-c statement_timeout=30min' 같은 걸 넣는 방법. 배치 팀은 후자를 택했다. 우리는 pgbouncer의 track_extra_parameters 옵션도 검토했는데, 결국 앱 코드에서 명시적으로 관리하는 게 낫다는 결론.

이 얘기는 사실 pgbouncer 문서에 다 나와있다. RESET ALL이 트랜잭션 끝날 때마다 실행된다는 것도. 근데 실제로 겪어야 아 이거구나 싶다.

3주차: 새벽 3시의 커넥션 폭증

세팅 다 끝나고 잘 돌아가나 싶었는데, 새벽 3시에 페이저가 울렸다. Aurora 커넥션 수가 max_connections 근처까지 튀었다.

원인 추적이 좀 걸렸다. pg_stat_activity를 봤더니 idle in transaction 상태 커넥션이 잔뜩 있었다. 오케이, 어떤 쿼리가 트랜잭션 열어놓고 안 닫고 있구나. 근데 대부분 앱 소스가 SQLAlchemy로 트랜잭션 관리하는데 어디서 새는지 모르겠더라.

결국 원인은 백업 파이프라인이었다. Airflow에서 도는 파이썬 잡이 psycopg2로 pgbouncer에 붙어서 SELECT ... 결과를 스트리밍으로 받고 있었는데, 로직 상 파이썬 쪽 처리가 오래 걸리는 동안 트랜잭션이 계속 열려 있었다. session pooling 시절에는 백엔드 커넥션 한 개만 잡고 있으면 됐는데, transaction pooling에서는 이 잡이 열려 있는 내내 백엔드 커넥션을 못 반환한다. 스케일 아웃된 워커가 5개면 백엔드 커넥션 5개가 통째로 잡혀 있는 셈.

임시로 이 배치는 pgbouncer를 우회해서 Aurora에 직접 붙게 했다. 근본 해결은 배치 잡 자체를 청크 단위로 트랜잭션 나누는 리팩터링. 그건 아직 진행 중이다.

지금 상태

• 백엔드 커넥션: 400 → 45개 수준
• pgbouncer prepared statement 캐시 히트율: 85%
• p99 레이턴시: 오히려 8% 개선 (백엔드 커넥션이 warm 상태로 유지되는 효과인 듯)
• 새로 배운 것: 문서를 진짜로 읽자

솔직히 다시 하라면 몇 가지는 미리 챙길 것 같다. pgbouncer 버전 최신화, SET LOCAL 감사, 그리고 pgbouncer를 우회할 필요가 있는 워크로드(장시간 배치, 리포트 쿼리, LISTEN/NOTIFY)를 사전에 분류하는 것. 특히 마지막은 카나리 배포로도 잘 안 잡힌다. 실제 배치 스케줄이 도는 새벽 시간대에나 문제가 드러났으니까.

앞으로 tigerdata의 자료도 좀 더 파볼 예정이다. 우리는 아직 RESET_QUERY 튜닝도 안 해봤고, server_reset_query_always도 default 그대로 쓰고 있다.

혹시 비슷한 마이그레이션 겪으신 분 있으면 어떤 함정 만나셨는지 궁금하다.