Connection 관리: pgbouncer, transaction pooling, prepared statement 주의점

PostgreSQL 커넥션이 비싼 이유

PostgreSQL은 클라이언트 연결마다 OS 프로세스를 하나 포크한다. 스레드가 아닌 독립 프로세스다. 이 구조가 격리성과 안정성을 높이지만, 커넥션 비용도 함께 높인다.

유휴 상태의 커넥션 프로세스도 보통 5–10 MB의 메모리를 점유한다. 2,000개 동시 커넥션이면 커넥션 프로세스만으로 4–8 GB를 쓴다. work_mem 같은 쿼리 별 메모리는 그 위에 더해진다. max_connections를 올리면 PostgreSQL이 기동 시점에 공유 메모리를 더 많이 미리 할당하므로 사용 여부와 무관하게 자원이 소비된다.

커넥션 수가 늘어나면 컨텍스트 스위칭, 잠금 경쟁, CPU 스케줄러 부담도 증가한다. 실제 처리량은 커넥션 수가 일정 임계를 넘는 순간부터 오히려 떨어진다.

이 문제를 해결하는 표준 접근이 외부 커넥션 풀러다. 애플리케이션 쪽에 수천 개의 클라이언트 연결을 받아두고, PostgreSQL 쪽에는 소수의 백엔드 커넥션만 유지하면서 재사용한다.

PgBouncer 아키텍처와 세 가지 풀링 모드

PgBouncer는 단일 스레드, 이벤트 주도 방식으로 동작하는 경량 커넥션 풀러다. 자체 메모리는 2–5 MB 수준이다. 클라이언트 커넥션과 PostgreSQL 백엔드 커넥션 사이에서 프록시 역할을 한다.

풀링 모드는 세 가지다. 각각 백엔드 커넥션을 언제 반환하는지가 다르다.

Session pooling — 클라이언트가 연결해 있는 동안 백엔드 커넥션을 점유한다. 클라이언트가 끊어야 백엔드가 풀로 돌아온다. PostgreSQL 기능을 100% 사용할 수 있지만, 클라이언트 수 이상의 절약이 없다. 잠깐씩 연결하는 패턴이라면 효과가 있다.

Transaction pooling — 트랜잭션이 끝나면 백엔드 커넥션을 풀로 반환한다. BEGIN~COMMIT/ROLLBACK 단위로 재사용한다. 클라이언트 수가 백엔드 수보다 훨씬 많아도 된다. 대부분의 프로덕션 환경에서 선택하는 모드다.

Statement pooling — 문장 하나가 끝날 때마다 백엔드를 반환한다. 모든 문장이 autocommit으로 실행된다. 멀티 문장 트랜잭션이 불가능하므로 적용 범위가 매우 좁다.

Transaction Pooling의 제약: 세션 상태를 믿을 수 없다

Transaction pooling은 효율적이지만, 클라이언트가 트랜잭션 경계 밖에서 세션 상태를 유지한다고 가정하면 문제가 생긴다. 트랜잭션이 끝나는 순간 다른 클라이언트가 같은 백엔드 커넥션을 가져갈 수 있기 때문이다.

SET 명령 누출

-- 클라이언트 A가 트랜잭션 안에서 실행
SET work_mem = '256MB';
SELECT * FROM large_aggregation;
COMMIT;
-- 커넥션이 풀로 돌아감
-- 클라이언트 B가 이 커넥션을 받으면 work_mem = 256MB 상태 그대로

SET SESSION 또는 그냥 SET은 세션 전체에 걸린다. Transaction pooling에서 안전하게 쓰려면 SET LOCAL로 현재 트랜잭션 범위에만 적용해야 한다.

BEGIN;
SET LOCAL work_mem = '256MB';  -- 이 트랜잭션이 끝나면 원래 값으로 복구
SELECT * FROM large_aggregation;
COMMIT;

어드바이저리 락

pg_advisory_lock()은 세션 범위 락이다. 트랜잭션이 끝나도 세션에 남아 있다. Transaction pooling에서 커넥션이 재배정되면 다른 클라이언트가 의도치 않게 락을 보유하거나, 원래 클라이언트가 락을 잃는다. 해결책은 트랜잭션 범위 어드바이저리 락(pg_advisory_xact_lock())을 쓰거나, transaction pooling이 아닌 session pooling으로 전환하는 것이다.

LISTEN/NOTIFY

비동기 알림은 세션에 묶여 있다. Transaction pooling에서 커넥션이 재배정되면 LISTEN 구독이 끊긴다. LISTEN 경로는 별도 database 항목을 만들어 session pooling으로 분리하는 것이 현실적이다.

임시 테이블

CREATE TEMP TABLE은 세션이 살아 있는 동안 존재한다. Transaction pooling에서 트랜잭션이 끝나면 다른 클라이언트가 같은 백엔드를 받아도 임시 테이블에 접근할 수 없다. Transaction pooling 환경에서는 임시 테이블을 피하거나 CTE로 대체한다.

Prepared Statement와 PgBouncer 1.21의 해결

Transaction pooling에서 가장 많이 문제가 되었던 기능이 prepared statement다.

클라이언트 A가 PREPARE로 문장을 등록하고, 트랜잭션이 끝나 커넥션이 풀로 돌아간다. 클라이언트 B가 그 커넥션을 받으면 클라이언트 A가 등록한 prepared statement가 존재하지 않는다. 클라이언트 A가 나중에 다른 백엔드를 배정받아 EXECUTE를 시도하면 prepared statement does not exist 오류가 난다.

PgBouncer 1.21에서 해결됐다. max_prepared_statements 설정을 통해 PgBouncer가 프로토콜 수준 prepared statement를 추적하고 관리한다.

# pgbouncer.ini
max_prepared_statements = 200

이 값을 0이 아닌 값으로 설정하면, PgBouncer는 클라이언트별로 prepared statement 이름과 내용을 캐시한다. 클라이언트가 다른 백엔드 커넥션에 배정될 때 필요한 prepared statement를 해당 백엔드에 재등록한다. 애플리케이션 코드 변경 없이 투명하게 동작한다.

SQL PREPARE 문이 아닌 PostgreSQL 바이너리 프로토콜 수준 prepared statement(대부분의 ORM이 사용하는 방식)가 이 대상이다. SQLAlchemy, psycopg3의 prepared_statements 기능, JDBC prepareStatement 등이 해당된다.

Pool Size 계산

백엔드 커넥션 수는 무조건 많다고 좋지 않다. 실제 PostgreSQL이 병렬로 유용하게 처리할 수 있는 수가 한계다. 검증된 공식이 있다.

pool_size = (코어 수 × 2) + 유효 스핀들 수

• 코어 수: 하이퍼스레딩 스레드가 아닌 물리 코어
• 유효 스핀들 수: SSD/NVMe라면 0–1, 클라우드 블록 스토리지라면 1, 회전형 디스크 RAID라면 2–4

max_connections는 이것과 다른 개념이다. PgBouncer pool_size가 실제 동시 백엔드 커넥션 수이고, max_connections는 PostgreSQL이 허용하는 최대 커넥션의 상한이다. 보통 max_connections를 여유 있게 설정해두고 PgBouncer pool_size를 위 공식 기준으로 좁게 유지한다.

pgbouncer.ini 핵심 설정

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0

; 풀 크기 제어
max_client_conn = 5000         ; PgBouncer가 받는 클라이언트 최대 수
default_pool_size = 25         ; DB/사용자 쌍당 백엔드 커넥션 수
min_pool_size = 10             ; 유휴 시에도 유지할 최소 백엔드 수
reserve_pool_size = 5          ; 긴급 예비 커넥션
reserve_pool_timeout = 3       ; 예비 풀 사용 대기 시간(초)

; 풀링 모드
pool_mode = transaction

; Prepared statement 추적 (1.21+)
max_prepared_statements = 200

; 커넥션 반환 시 상태 초기화
server_reset_query = DISCARD ALL

; 타임아웃
client_idle_timeout = 3600     ; 유휴 클라이언트 끊기(초, 0=무제한)
server_idle_timeout = 600      ; 유휴 백엔드 끊기(초)
query_wait_timeout = 120       ; 백엔드 대기 최대 시간(초)

; 인증
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

[databases]
myapp = host=db.internal port=5432 dbname=myapp pool_size=25

[users]
"app_user" "md5해시값"

server_reset_query = DISCARD ALL은 백엔드 커넥션이 풀로 돌아올 때 세션 상태를 초기화한다. prepared statement, 임시 테이블, SET 값, 어드바이저리 락을 정리한다. Transaction pooling에서는 이 초기화가 매 트랜잭션마다 실행되는 것이 아니라, 커넥션 반환 후 재사용 전에 필요 시 실행된다. DISCARD ALL은 다소 무거운 명령이므로 session pooling에서는 주기적으로 비용이 발생할 수 있다.

모니터링: SHOW POOLS와 SHOW STATS

PgBouncer 관리 콘솔은 pgbouncer 데이터베이스에 접속해서 사용한다.

psql -p 6432 -U pgbouncer pgbouncer

SHOW POOLS — 가장 중요한 진단 뷰다.

SHOW POOLS;

cl_waiting이 계속 0이 아니면 pool_size가 너무 작거나 쿼리가 느려서 백엔드가 오래 점유된 것이다.

SHOW STATS — 처리량과 대기 시간 추이를 본다.

SHOW STATS;
-- total_xact_count: 누적 트랜잭션 수
-- total_query_time: 백엔드 연결 시간 (마이크로초)
-- total_wait_time:  백엔드를 기다린 시간 (마이크로초)

total_wait_time이 total_query_time의 10% 이상이라면 풀이 부족하거나 백엔드 쿼리가 느리다는 신호다.

SHOW CLIENTS / SHOW SERVERS — 개별 커넥션 상태를 확인할 때 쓴다. 특정 클라이언트가 오래 점유 중인지, 백엔드 커넥션이 어떤 상태인지 추적할 수 있다.

SHOW CLIENTS;   -- 클라이언트별 state: active | idle | wait_server
SHOW SERVERS;   -- 백엔드별 state: active | idle | used | tested

자주 겪는 운영 문제

cl_waiting이 0으로 떨어지지 않는다

풀 크기 부족이 가장 흔한 원인이다. SHOW POOLS에서 sv_idle이 0이면서 cl_waiting이 있다면 default_pool_size를 늘리거나, 느린 쿼리를 찾아 해결한다. sv_idle이 있는데도 cl_waiting이 있으면 PgBouncer 자체 처리 병목일 수 있다. PgBouncer는 단일 스레드이므로 매우 높은 연결 속도에서는 CPU 바운드 병목이 생길 수 있다.

커넥션 오류: prepared statement does not exist

PgBouncer 버전이 1.21 미만이고 transaction pooling에서 prepared statement를 쓸 때 발생한다. PgBouncer를 1.21 이상으로 올리고 max_prepared_statements = 200을 설정하면 해결된다. 즉각 업그레이드가 어려운 경우 ORM의 prepared statement 기능을 비활성화하거나 session pooling으로 임시 전환한다.

SCRAM 인증 오류

PgBouncer auth_type을 md5로 두면 내부적으로 백엔드가 SCRAM을 요구해도 자동으로 맞춰준다. auth_file에 MD5 해시 또는 평문 패스워드를 관리해야 한다. SCRAM 해시 그대로 auth_file에 넣으면 버전에 따라 인증 단계에서 실패할 수 있다.

query_wait_timeout 초과

백엔드 풀이 모두 점유된 상태에서 대기가 너무 길면 클라이언트에게 오류를 반환한다. 기본 120초는 애플리케이션 입장에서 너무 길게 느껴질 수 있다. SLA에 맞게 줄이고 알림을 두는 것이 낫다.

PgBouncer 외 선택지

Odyssey (Yandex 개발, C 멀티스레드) — 멀티코어를 활용하므로 매우 많은 동시 연결 환경에서 PgBouncer보다 높은 처리량을 낸다. 설정이 더 복잡하고 멀티테넌트 라우팅 기능이 강하다.

pgCat (Rust, 멀티스레드) — PgBouncer 호환 설정에 읽기 복제본 쿼리 라우팅, 샤딩 지원을 추가했다. 최근 빠르게 성장 중이다.

대부분의 운영 환경에서는 PgBouncer가 단순성과 안정성 면에서 가장 좋은 출발점이다.

운영 체크리스트

• PgBouncer pool_size를 (코어×2 + 스핀들) 공식으로 산정했는가?
• pool_mode = transaction 상태에서 SET, 임시 테이블, LISTEN을 쓰는 경로가 없는가?
• PgBouncer 1.21+ 에서 max_prepared_statements를 설정해 prepared statement 오류를 없앴는가?
• server_reset_query = DISCARD ALL이 설정되어 세션 상태 누출을 막고 있는가?
• SHOW POOLS에서 cl_waiting을 주기적으로 확인하는 알림이 있는가?
• query_wait_timeout이 서비스 SLA에 맞게 설정되어 있는가?
• client_idle_timeout, server_idle_timeout이 네트워크 장비의 idle connection 정책보다 짧은가?
• PgBouncer를 재시작할 때 기존 커넥션 drain 절차(-R 옵션 온라인 재시작 또는 graceful shutdown)가 있는가?

References

• PgBouncer Documentation, "Configuration" — https://www.pgbouncer.org/config.html
• PgBouncer Documentation, "Usage" — https://www.pgbouncer.org/usage.html
• PgBouncer GitHub Release, "1.21.0 — The one with prepared statements" — https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_21_0
• Crunchy Data Blog, "Prepared Statements in Transaction Mode for PgBouncer" — https://www.crunchydata.com/blog/prepared-statements-in-transaction-mode-for-pgbouncer
• Cybertec, "Estimating connection pool size with PostgreSQL database statistics" — https://www.cybertec-postgresql.com/en/estimating-connection-pool-size-with-postgresql-database-statistics/
• PostgreSQL Wiki, "Number Of Database Connections" — https://wiki.postgresql.org/wiki/Number_Of_Database_Connections
• pganalyze Blog, "PgBouncer prepared statements in transaction mode" — https://pganalyze.com/blog/5mins-postgres-pgbouncer-prepared-statements-transaction-mode
• Cybertec, "pgbouncer: Types of PostgreSQL Connection Pooling" — https://www.cybertec-postgresql.com/en/pgbouncer-types-of-postgresql-connection-pooling/
• Andres Freund, "Measuring the Memory Overhead of a Postgres Connection" — https://blog.anarazel.de/2020/10/07/measuring-the-memory-overhead-of-a-postgres-connection/
• PostgreSQL Announcement, "PgBouncer 1.22.0 released" — https://www.postgresql.org/about/news/pgbouncer-1220-released-2802/