pgbouncer transaction 모드로 바꿨더니 SET 이 사라진다

커넥션을 아끼려고 pool_mode 를 session 에서 transaction 으로 바꿨다. 커넥션 수는 확실히 줄었다. 그런데 앱이 이상해졌다.

어떤 요청은 되고, 어떤 요청은 prepared statement 관련 에러가 난다. SET 으로 걸어 둔 설정은 다음 쿼리에서 사라진다. 재현이 안 되니 더 답답하다. 같은 요청을 다시 보내면 이번엔 성공하니까.

멀티테넌트 서비스라면 얘기가 더 무겁다. search_path 로 테넌트를 가르고 있었다면, 어느 순간 A 회사 사용자가 B 회사 데이터를 보게 된다. 에러도 없이. 잘못된 데이터가 조용히 나간다.

세션이 더 이상 내 것이 아니다

transaction 모드는 트랜잭션이 끝나는 순간 백엔드 커넥션을 풀에 돌려준다. 다음 트랜잭션은 전혀 다른 백엔드를 받을 수도 있다. 그러니 세션에 붙여 둔 건 전부 깨진다. 세션이 더 이상 내 것이 아니다.

무서운 건 깨지는 모양이다. 대부분 예외를 던지며 멈추지 않는다. 방금 설정한 게 그냥 없어진다. SET search_path 를 하고 다음 쿼리를 보내면, 그 쿼리는 기본 search_path 를 가진 다른 백엔드에서 돈다. 에러 대신 틀린 결과가 나온다.

게다가 이게 트래픽에 비례한다. 커넥션이 한가할 땐 돌려줘도 같은 백엔드를 다시 받을 확률이 높다. 그래서 개발 환경과 새벽에는 멀쩡하다. 동시 요청이 몰려서 백엔드가 실제로 섞이기 시작하면 그때부터 터진다. "재현이 안 된다"의 정체가 이거였다.

session 모드는 클라이언트가 접속을 끊을 때까지 백엔드를 붙잡고 있다. 안전하지만 커넥션을 아끼는 효과는 거의 없다. statement 모드는 문장 단위라 더 극단적이다. 여러 문장짜리 트랜잭션을 아예 못 쓴다.

깨지는 것과 버티는 것

어떤 게 깨지고 어떤 게 버티는지 나란히 놓으면 이렇다.

· ── 깨진다: 세션에 붙는 모든 것 ──
✕ SET search_path = tenant_a;      -- 다음 쿼리에서 사라진다
✕ CREATE TEMP TABLE tmp (...);     -- 다른 백엔드엔 없다
✕ PREPARE s AS SELECT ...;         -- 1.21 미만이면 못 찾는다
✕ SELECT pg_advisory_lock(1);      -- 세션 락은 반납 시 풀린다
✕ LISTEN ch;                       -- 알림을 못 받는다
✕ DECLARE c CURSOR WITH HOLD ...;  -- 트랜잭션 밖에서 못 산다

· ── 된다: 트랜잭션 안에 가둔 것 ──
✓ BEGIN;
✓ SET LOCAL search_path = tenant_a;
✓ SELECT pg_advisory_xact_lock(1);   -- 트랜잭션 락
✓ ...
✓ COMMIT;

· 1.21+ 라면 프리페어드는 이걸로 살릴 수 있다
✓ max_prepared_statements = 200   # 기본값 0 이면 꺼져 있다

✕ 문제 지점 · ✓ 해법 · 그대로 돌리기 전에 자기 환경에 맞게 고친다.

"우리는 prepared statement 안 쓰는데요"

제일 흔한 사고는 ORM 에서 난다. 드라이버 상당수가 기본으로 서버 사이드 프리페어드 스테이트먼트를 쓴다. 개발자가 PREPARE 를 직접 쓴 적이 한 번도 없어도 그렇다. 그래서 "우리는 prepared statement 안 쓰는데요" 하면서 그 에러를 만난다.

pgbouncer 1.21(2023년 10월)부터는 transaction 모드에서도 프리페어드 스테이트먼트를 지원한다. 클라이언트의 PREPARE 를 pgbouncer 가 가로채 내부 이름을 붙여 두고, 어느 백엔드에 앉든 그 백엔드에 없으면 그 자리에서 준비한 뒤 실행을 넘긴다. 다만 max_prepared_statements 를 0 이 아닌 값으로 켜야 동작한다. 기본값이 0 이다. 버전만 올리고 이걸 안 켜면 달라지는 게 없다.

멀티테넌트에서 search_path 를 쓰는 구조는 특히 위험하다. 조용히 깨지면서 결과가 틀리기 때문이다. SET LOCAL 로 바꾸면 트랜잭션 안에서만 유효해서 안전하다. 그런데 트랜잭션 밖에서 도는 조회가 하나라도 남아 있으면, 그건 여전히 기본 스키마를 본다. 그래서 이 구조는 pgbouncer 를 넣기 전에 먼저 걷어내는 편이 낫다.

어드바이저리 락도 자주 물린다. pg_advisory_lock 은 세션 락이라 커넥션을 반납하는 순간 풀려 버린다. 트랜잭션 범위인 pg_advisory_xact_lock 으로 바꾸면 되는데, 이름이 비슷해서 코드 리뷰에서 잘 안 걸러진다. 솔직히 눈으로 잡아내기 어려운 차이다.

transaction 모드로 가기 전에 할 일

1. 세션 설정은 전부 SET LOCAL 로 바꾼다. 트랜잭션 안에 가둬 두면 반납할 때 알아서 사라지니 안전하다.
2. 어드바이저리 락은 pg_advisory_xact_lock 계열로 옮겨야 한다. 세션 락은 transaction 모드에서 아무 의미가 없다.
3. 프리페어드 스테이트먼트는 길이 둘이다. pgbouncer 를 1.21 이상으로 올리고 max_prepared_statements 를 켜거나, 드라이버에서 서버 사이드 프리페어를 끄거나. JDBC 는 prepareThreshold=0, psycopg 는 prepare_threshold=None 식이다.
4. 세션이 꼭 필요한 작업이 있다면 별도 풀을 session 모드로 따로 두면 된다. 대량 임포트, COPY, LISTEN/NOTIFY 를 쓰는 워커는 그 풀로 보낸다. pgbouncer 하나에서도 pool_mode 를 데이터베이스별로 다르게 줄 수 있다.
5. 바꾸기 전에 세션 상태에 기대는 코드부터 찾자. SET, CREATE TEMP, advisory lock, LISTEN, WITH HOLD 커서를 코드베이스에서 문자열로 검색하기만 해도 대부분 나온다.
6. pool_size 를 1~2 로 극단적으로 줄이면 백엔드가 섞이는 상황이 바로 재현된다. 스테이징에서 커넥션을 일부러 섞어 놓고 테스트해 볼 만하다.

풀이 실제로 어떻게 돌고 있나

관리 콘솔과 서버 쪽에서 이걸 보면 된다.

· pgbouncer 관리 콘솔
psql -p 6432 -U pgbouncer pgbouncer

SHOW POOLS;
✕ cl_waiting 이 쌓이면 풀이 작거나
✕ 트랜잭션을 오래 잡고 있는 것이다
· sv_active 대비 sv_idle 비율을 같이 본다

SHOW CONFIG;
· pool_mode · max_prepared_statements 확인

· 앱이 트랜잭션을 얼마나 오래 잡나 (서버 쪽)
SELECT state, count(*),
       max(now() - xact_start) AS longest
  FROM pg_stat_activity
 GROUP BY state;
✕ idle in transaction 이 길면
✕ transaction 모드의 이득이 사라진다

핵심 한 줄transaction 모드에서는 세션에 아무것도 붙여 둘 수 없다.