PgBouncer transaction 모드에서 prepared statement 제대로 쓰는 법

왜 굳이 켜야 하나

PgBouncer 1.21에서 transaction pooling 모드에서도 prepared statement를 쓸 수 있게 된 게 벌써 2년 가까이 됐다. 우리 팀도 작년 가을에 1.22로 올리고 max_prepared_statements를 켰는데, 그동안 마주친 함정 몇 개가 있어서 정리해둔다. 비슷한 마이그레이션을 앞두고 있는 분들에게 도움이 됐으면 한다.

이 글은 "PgBouncer는 뭔가요"부터 시작하지 않는다. 이미 transaction 모드로 PgBouncer를 운영 중이고, 애플리케이션이 prepared statement를 쓰고 있거나 쓰고 싶은 상황을 가정한다.

JDBC, asyncpg, pgx, psycopg3 같은 모던 드라이버는 기본적으로 Parse/Bind/Execute로 분리된 extended query protocol을 쓴다. 매번 SQL 텍스트를 파싱하지 않고 plan을 재사용해서 빠르고, SQL injection 위험도 줄어든다. 그런데 transaction pooling을 쓰면 매 트랜잭션마다 다른 backend 커넥션에 매핑되니까, 한 커넥션에 prepare 해놓은 statement가 다음 트랜잭션에선 사라진다. 그래서 옛날엔 두 가지 중 하나를 선택해야 했다. session pooling을 쓰거나, prepared statement를 끄거나.

session pooling은 backend 커넥션을 트랜잭션 단위가 아니라 클라이언트 세션 단위로 잡는다. 풀이 안 도는 워크로드에선 그냥 PgBouncer를 안 쓰는 거랑 별 차이가 없어진다. 그래서 우리는 transaction 모드를 유지하면서 prepared statement를 살리는 쪽으로 가고 싶었다.

PgBouncer 1.21부터 추가된 max_prepared_statements 설정이 그 답이다. PgBouncer가 클라이언트의 Parse 메시지를 가로채서 자기가 캐시를 들고, 실제 backend 커넥션에 그 statement가 아직 prepare 안 돼있으면 자동으로 prepare 시킨다. 클라이언트는 transaction pooling을 쓰는지 알 필요가 없다.

설정 자체는 간단하다

pgbouncer.ini에 한 줄 추가하면 끝이다.

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

max_prepared_statements는 클라이언트 커넥션 하나당 PgBouncer가 추적할 prepared statement 개수의 상한이다. 0이 기본값이고, 0이면 기능이 꺼져있다. 우리 팀에서는 200으로 시작했다가 트래픽 패턴 보고 500까지 올렸다.

설정값을 정할 때 헷갈리지 말아야 할 게 두 가지 있다.

첫째, 이 값은 PgBouncer의 메모리에 들어가는 캐시 엔트리 수다. backend 커넥션 풀 크기랑은 다르다. backend는 그것대로 default_pool_size로 잡는다.

둘째, 이 값을 너무 작게 잡으면 LRU 방식으로 캐시가 밀려나면서 hit rate가 떨어진다. 애플리케이션이 100개의 unique한 query를 prepare 한다면 200~300이 적절하다. 우리 팀은 ORM이 자동 생성하는 statement까지 합치니까 300개를 넘어서 500으로 올렸다.

함정 1: 드라이버가 명시적 prepare를 안 쓰는 경우

이게 제일 첫 번째로 부딪힌 문제였다. JDBC는 prepareThreshold 설정으로 같은 SQL이 N번 실행되면 그제야 protocol-level prepared statement를 만든다. 기본값이 5다. 즉 처음 4번은 그냥 simple query고, 5번째부터 prepared가 된다.

문제는 PgBouncer 입장에선 명시적인 Parse 메시지가 와야 캐시를 만든다는 거다. 애플리케이션이 매번 simple query로 보내면 PgBouncer는 아무것도 안 캐시한다. 그래서 prepareThreshold를 1로 내리거나, 애초에 PreparedStatement를 직접 만드는 코드 패턴으로 가야 효과를 본다.

우리 팀은 JDBC URL에 이렇게 박았다.

jdbc:postgresql://pgbouncer:6432/app?prepareThreshold=1&preparedStatementCacheQueries=512

preparedStatementCacheQueries는 JDBC 클라이언트 쪽 캐시 크기다. PgBouncer의 max_prepared_statements보다 같거나 작게 맞추면 양쪽이 잘 동기화된다. 더 크게 잡으면 클라이언트는 캐시했다고 생각하는데 PgBouncer쪽은 LRU로 밀려나서 매번 다시 prepare 하는 상황이 벌어진다.

함정 2: SET 같은 세션 상태 변경

prepared statement는 살렸지만 SET timezone = 'Asia/Seoul' 같은 세션 변수는 여전히 transaction 모드에선 위험하다. 트랜잭션 밖에서 SET을 때리면 그 backend 커넥션에만 적용되고, 다음 트랜잭션은 다른 커넥션에 가니까 timezone이 또 다르다.

해결책은 두 가지다. 트랜잭션 안에서 SET LOCAL을 쓰거나, 그냥 매 connection 시작 시 PgBouncer가 자동 실행해주는 server_reset_query에 박아놓거나. 우리는 애플리케이션 connection string의 options 파라미터로 -c timezone=Asia/Seoul처럼 startup 시 한 번에 처리하게 바꿨다.

LISTEN/NOTIFY도 transaction 모드에선 동작하지 않는다. 이건 prepared statement랑은 별개 이슈인데, 같이 마이그레이션하다 보면 한 번씩 걸린다. 알림 받는 워크로드는 별도 PgBouncer 인스턴스를 session 모드로 띄우거나, Postgres에 직접 붙는 것 외엔 답이 없다.

함정 3: 모니터링 지표가 새로 필요하다

전에는 backend 커넥션 수랑 wait time만 봤는데, prepared statement를 켜고 나서는 hit/miss 비율을 봐야 한다. 안 그러면 max_prepared_statements가 너무 작아서 LRU에 계속 밀리는 상황을 못 잡는다.

PgBouncer 1.22부터 SHOW STATS 출력에 prepared statement 관련 컬럼이 추가됐다.

-- pgbouncer 콘솔 (보통 :6432 / pgbouncer DB)
SHOW STATS;

server_assignment_count랑 같이 보면서 prepared statement cache hit이 95% 이상으로 유지되는지 확인한다. 우리는 prometheus-pgbouncer-exporter에 이 지표를 추가하는 PR을 직접 올려서 쓰고 있다. 떨어지기 시작하면 max값을 올리거나 클라이언트 쪽 cache size를 줄인다.

단계적으로 켜는 걸 권한다

운영 중인 PgBouncer에서 갑자기 max_prepared_statements를 0에서 500으로 올리면, 즉시 모든 새 prepared statement가 PgBouncer 메모리를 먹기 시작한다. 메모리 사용량은 statement 텍스트 길이에 비례하는데, 우리 환경에선 statement 하나당 평균 2~3KB 정도 잡혔다.

또 한 가지, 라이브로 켜면 기존에 떠있던 backend 커넥션은 prepare를 다시 받지 않는다. 캐시는 새로 들어오는 트랜잭션부터 채워진다. 그래서 처음엔 cache hit이 0%에 가깝다가 몇 분에 걸쳐 올라가는 모양이 된다. 이걸 모르고 보면 "어 왜 안 빨라지지" 한다.

우리는 이렇게 했다.

1. 스테이지에서 일주일 운영하면서 hit rate랑 메모리 사용량 패턴을 본다
2. 운영에선 트래픽이 가장 적은 새벽 시간대에 reload
3. 30분간 cache 히트 채워질 때까지 지표 모니터링
4. 다음 날 아침 피크 시간에 latency 변화 측정

p95 query latency가 12% 정도 줄었고, Postgres쪽 pg_stat_statements의 plan time 비중도 눈에 띄게 떨어졌다. 가장 큰 효과는 사실 이 둘이 아니라 CPU 사용률이었다. parsing이 확연히 줄어드니까 Postgres 노드의 user CPU가 평균 8%포인트 빠졌다. 우린 그동안 prepared statement를 못 쓴다는 이유로 RDS 인스턴스를 한 단계 크게 잡고 있었는데, 이번에 다운사이즈도 같이 했다.

정리

PgBouncer transaction 모드에서 prepared statement는 이제 그냥 켜도 되는 기능이다. 다만 그냥 max_prepared_statements만 박는다고 효과가 나오진 않는다. 드라이버 쪽 prepareThreshold/cache size 맞추고, 세션 상태 변경 패턴 한 번 점검하고, hit rate 지표를 볼 수 있게 해놓는 것까지가 한 세트라고 보면 된다.

우리 팀에선 이걸 켜고 나서 RDS 인스턴스 크기 다운그레이드까지 같이 가서 비용도 같이 잡혔다. 더 큰 배포에선 어떻게 동작할지는 또 다른 문제일 수 있다. 혹시 max_prepared_statements를 1000 이상 잡고 운영 중인 분 있으면 어떻게 튜닝하셨는지 댓글 남겨주시면 감사하겠다.