PgBouncer transaction pool에서 prepared statement 쓰기, 이젠 진짜 된다

IT/DB 운영

PgBouncer transaction pool에서 prepared statement 쓰기, 이젠 진짜 된다

gfrog 2026. 8. 18. 09:29

thumbnail

Django, SQLAlchemy, node-postgres 같은 걸 쓰다가 PgBouncer 뒤에 붙이면 한 번쯤 만나는 에러가 있다. prepared statement "S_1" does not exist. 그 순간 대부분의 팀은 두 갈래로 갈라진다. 하나는 transaction pool을 포기하고 session pool로 내리는 쪽. 다른 하나는 앱단에서 prepared statement를 아예 끄는 쪽. 어느 쪽이든 성능을 반쯤 반납하는 거였다.

PgBouncer 1.21에서 transaction pool에서도 protocol-level prepared statement를 쓸 수 있게 됐고, 최근 1.22가 나오면서 DISCARD ALL / DEALLOCATE ALL까지 다뤄져서 실무에서 쓰기가 훨씬 편해졌다. 이번 글은 이걸 실제로 켜는 절차, 그리고 켠 뒤에 놓치기 쉬운 부분을 정리한다.

왜 이게 문제였나

PgBouncer의 transaction pool은 트랜잭션 경계마다 클라이언트-서버 매핑이 바뀐다. 그런데 Postgres의 prepared statement는 세션 로컬 객체다. 클라이언트 A가 PREPARE S_1을 백엔드 X에서 등록해두면, 다음 쿼리에서 백엔드 Y로 배정될 때 Y는 S_1을 모른다. 그래서 앱이 protocol-level prepare를 쓰는 순간(대부분의 드라이버 기본값) 무조건 깨진다.

session pool로 내리면 이 문제가 사라지지만, 커넥션 재사용률이 뚝 떨어져서 굳이 PgBouncer를 쓸 이유가 애매해진다. 우리 팀도 이 이유로 한동안 prepareThreshold=0 같은 걸로 앱에서 강제로 꺼두고 살았다.

준비물

PgBouncer 1.22 이상 (1.21도 되지만 1.22의 DISCARD/DEALLOCATE 처리 개선이 실무에선 크다)
Postgres 클라이언트는 extended query protocol로 prepared statement를 쓰는 놈이어야 한다. PREPARE ... AS SELECT ... 같은 SQL 명령으로 하는 건 여전히 안 된다.

드라이버별 지원 범위는 이 정도로 정리하면 된다. libpq(PQprepare), psycopg3(server-side prepared statements), pgx v5, node-postgres의 named statement, JDBC의 서버-사이드 prepared는 다 protocol-level이라 OK다. 반면 SQL 문자열로 PREPARE를 직접 쏘는 코드가 있다면 이 기능과는 별개다.

설정

pgbouncer.ini에 두 줄이면 된다.

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

max_prepared_statements는 기본이 0이고, 0이면 이 기능 자체가 꺼져 있다. 값은 클라이언트 커넥션 하나당 캐시할 prepared statement 개수다. 앱이 실제로 쓰는 서로 다른 SQL 개수보다 조금 크게 잡으면 된다. 우리 팀은 대부분의 서비스에서 128~256 사이로 잡고 있다. 지나치게 크게 잡으면 PgBouncer의 메모리가 늘어난다. 서비스마다 상황이 다르니 실제 사용 SQL 종류 수를 한 번 세보고 시작하는 걸 권한다.

Postgres 쪽 설정은 건드릴 게 없다. 여기까지 하고 reload.

pgbouncer -R -d /etc/pgbouncer/pgbouncer.ini

동작 확인

간단하게 psycopg3로 확인해보자.

import psycopg

with psycopg.connect("postgresql://app@pgbouncer:6432/app", prepare_threshold=1) as conn:
    with conn.cursor() as cur:
        for i in range(5):
            cur.execute("SELECT id, name FROM users WHERE id = %s", (i,))
            print(cur.fetchone())

prepare_threshold=1이면 두 번째 실행부터 서버 사이드 prepared를 태운다. PgBouncer admin console에서 확인.

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
SHOW STATS_TOTALS;
SHOW SERVERS;

SHOW SERVERS에 prep_stmts 컬럼이 있다. 여기 숫자가 0이 아니라 늘어나면 실제로 캐시되고 있다는 뜻이다. 캐시가 잡히면 Parse 왕복이 사라지니 latency가 눈에 띄게 준다. 우리 조회 heavy 서비스에서 P50 응답이 8ms → 5ms로 내려간 걸 확인했다.

놓치기 쉬운 것들

첫 번째, 리셋 쿼리와 충돌. server_reset_query에 DISCARD ALL이 들어 있으면 매 트랜잭션 끝날 때 prepared statement가 다 날아간다. 그럼 이 기능을 켠 의미가 없다. 1.22부터는 PgBouncer가 이 상황을 인식해서 캐시를 유지하도록 처리하지만, 확실하게 하고 싶으면 server_reset_query를 비우거나 DISCARD ALL 대신 좀 더 좁은 걸 쓰는 걸 권한다. 이걸 놓쳐서 "기능은 켜는데 왜 latency 그대로냐"고 삽질한 적이 있다.

두 번째, temp table이나 SET 명령을 자주 쓰는 앱. transaction pool에서 원래도 문제였지만, prepared statement까지 얹으면 원인 파악이 더 꼬인다. 세션 상태가 남는 SQL을 트랜잭션 밖에서 날리는 코드가 있으면 이 기능 켜기 전에 먼저 정리해야 한다.

세 번째, 애플리케이션 커넥션 풀 크기. 앱단 풀(HikariCP, SQLAlchemy pool 등)의 커넥션 하나당 PgBouncer 내부에 prepared statement 캐시가 하나씩 생긴다. 앱 풀이 클수록 캐시 메모리 사용량이 앱 풀 크기 × max_prepared_statements × 쿼리 평균 길이로 곱해진다는 걸 염두에 둔다. 문제될 만한 수치는 아니지만 인지는 하고 있어야 한다.

네 번째, 롤링 재시작. max_prepared_statements를 바꾸려면 PgBouncer 재시작이 필요하다(reload로는 반영 안 되는 항목). 트래픽이 있는 시간대에는 앞단에 두 대 이상을 두고 하나씩 내리는 방식으로 해야 커넥션이 튀지 않는다. 우리는 keepalived + 두 대 구성으로 이걸 다룬다.

언제 켜지 말아야 하나

앱이 SQL로 PREPARE를 직접 쏘는 방식이면 이 기능은 의미가 없다. 앱 코드부터 protocol-level로 바꾼 뒤에 켜자.
이미 session pool로 잘 돌아가고 있고 커넥션 재사용률에 여유가 있다면 굳이 transaction pool로 내려올 이유는 없다. 이 기능은 "transaction pool을 쓰고 싶은데 prepared까지 살리고 싶다"는 상황에 대한 답이다.
Aurora, RDS Proxy 같은 매니지드 프록시를 쓰고 있다면 각자 다른 방식으로 이걸 다룬다. RDS Proxy는 아직 prepared statement pinning 이슈가 남아있고, Aurora는 별개 이야기다. 각자 문서를 확인해야 한다.

마무리

이 기능이 나오기 전까지는 "prepared statement 켤 거면 session pool, 아니면 앱단에서 끄기" 둘 중 하나였다. 이제는 둘 다 챙길 수 있다. 우리 팀에서 새로 만드는 서비스는 기본으로 이 조합을 쓰기로 정리했고, 기존 서비스도 하나씩 옮기고 있다. 옮기는 과정에서 위에 적은 함정들에 몇 번 걸렸는데 그 얘기는 다음 글에서 좀 더 정리해보려고 한다.