PgBouncer 커넥션 풀링 운영 심화

글쓴이 /

PgBouncer란?

PgBouncer는 PostgreSQL 전용 경량 커넥션 풀러(connection pooler)입니다. PostgreSQL은 클라이언트 연결마다 별도 프로세스를 fork하는 구조라, 수백~수천 개 연결이 동시에 맺어지면 메모리와 컨텍스트 스위칭 비용이 급격히 증가합니다. PgBouncer는 클라이언트와 PostgreSQL 사이에서 소수의 실제 연결을 공유하여 이 문제를 해결합니다.

이 글에서는 PgBouncer의 3가지 풀링 모드, 운영 설정 튜닝, 트랜잭션 풀링의 함정, 모니터링, 그리고 Kubernetes 환경에서의 배치 전략까지 심층적으로 다룹니다. PostgreSQL pg_stat_statements 분석 가이드와 함께 읽으면 성능 최적화의 전체 그림을 이해할 수 있습니다.

풀링 모드 3가지

모드 연결 반환 시점 장점 제약
session 클라이언트 세션 종료 시 모든 PostgreSQL 기능 사용 가능 풀링 효과 최소
transaction 트랜잭션 완료 시 풀링 효율 최대 PREPARE, SET, LISTEN 등 세션 의존 기능 제한
statement 각 쿼리 완료 시 가장 공격적 풀링 멀티 스테이트먼트 트랜잭션 불가, autocommit만

실전 권장: 대부분의 웹 애플리케이션에는 transaction 모드가 최적입니다. 세션 변수나 Prepared Statement를 사용하지 않는다면 풀링 효율이 가장 높습니다.

핵심 설정 (pgbouncer.ini)

[databases]
# 데이터베이스 매핑
myapp = host=10.0.0.50 port=5432 dbname=myapp_production
# 읽기 전용 레플리카
myapp_ro = host=10.0.0.51 port=5432 dbname=myapp_production

[pgbouncer]
# === 풀링 모드 ===
pool_mode = transaction

# === 연결 수 제한 ===
# 클라이언트 → PgBouncer 최대 연결 수
max_client_conn = 1000

# PgBouncer → PostgreSQL 풀별 최대 연결 수
default_pool_size = 25

# 풀이 가득 찼을 때 추가 허용할 연결 수
reserve_pool_size = 5

# reserve_pool 사용 전 대기 시간 (초)
reserve_pool_timeout = 3

# 사용자별 DB별 최소 풀 크기 (미리 연결 유지)
min_pool_size = 5

# === 타임아웃 ===
# 클라이언트가 연결 대기하는 최대 시간
client_login_timeout = 60

# 쿼리 실행 최대 시간 (0=무제한)
query_timeout = 300

# 유휴 클라이언트 연결 최대 유지 시간
client_idle_timeout = 0

# 유휴 서버 연결 유지 시간
server_idle_timeout = 600

# 서버 연결 수명 (주기적 재연결)
server_lifetime = 3600

# === 네트워크 ===
listen_addr = 0.0.0.0
listen_port = 6432

# === 인증 ===
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# === 로깅 ===
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

# === 관리 ===
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

풀 사이즈 산정 공식

PostgreSQL의 최적 연결 수는 CPU 코어 수에 비례합니다. 과도한 연결은 오히려 성능을 저하시킵니다:

# PostgreSQL 권장 최대 연결 수
# max_connections ≈ (CPU 코어 수 × 2) + (디스크 수)
# 예: 8코어, SSD 1개 → 8 × 2 + 1 = 17개

# PgBouncer 풀 사이즈 산정
# 여러 앱 서버가 PgBouncer를 공유하는 경우:
# default_pool_size = PostgreSQL max_connections / (DB 수 × 사용자 수) - 여유분

# 예: PostgreSQL max_connections = 100
#     DB 2개, 사용자 2명
#     default_pool_size = 100 / (2 × 2) - 5 = 20

# 앱 서버별 HikariCP/Prisma 풀과의 관계
# [앱1: pool=10] ──┐
# [앱2: pool=10] ──┼── [PgBouncer: pool=20] ── [PostgreSQL: max_conn=100]
# [앱3: pool=10] ──┘
#
# 앱 풀 합계(30) > PgBouncer 풀(20) → PgBouncer가 큐잉하여 과부하 방지
# 이것이 PgBouncer의 핵심 가치!

transaction 모드의 함정

transaction 모드는 효율적이지만, 세션 단위 기능이 작동하지 않습니다. 실무에서 자주 겪는 함정들입니다:

# ❌ 함정 1: Prepared Statement
# 트랜잭션 A에서 PREPARE한 문이 트랜잭션 B에서 사라짐
PREPARE myquery AS SELECT * FROM users WHERE id = $1;
-- 트랜잭션 끝 → 다른 서버 연결로 전환됨
EXECUTE myquery(1);  -- ERROR: prepared statement "myquery" does not exist

# ✅ 해결: server_reset_query_always 또는 DEALLOCATE ALL
# pgbouncer.ini
server_reset_query_always = 1  # 매 트랜잭션 후 세션 리셋

# ❌ 함정 2: SET 명령어
SET statement_timeout = '5s';
-- 트랜잭션 끝 → 다른 연결로 전환 → SET 효과 사라짐
SELECT * FROM heavy_query;  -- 5초 타임아웃 적용 안됨

# ✅ 해결: SET LOCAL 사용 (트랜잭션 내에서만 유효)
BEGIN;
SET LOCAL statement_timeout = '5s';
SELECT * FROM heavy_query;
COMMIT;

# ❌ 함정 3: LISTEN/NOTIFY
LISTEN new_order;  -- 트랜잭션 끝나면 구독 해제됨

# ✅ 해결: LISTEN 전용으로 session 모드 연결 별도 구성
# pgbouncer.ini
[databases]
myapp = host=10.0.0.50 dbname=myapp pool_mode=transaction
myapp_listen = host=10.0.0.50 dbname=myapp pool_mode=session

# ❌ 함정 4: Advisory Lock
SELECT pg_advisory_lock(12345);
-- 트랜잭션 끝 → 다른 연결 → 락이 다른 세션에 남아있음!

# ✅ 해결: pg_advisory_xact_lock 사용 (트랜잭션 범위 락)
BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- 작업 수행
COMMIT;  -- 락 자동 해제

ORM별 설정 가이드

Prisma + PgBouncer

# .env
# ?pgbouncer=true → Prisma가 prepared statement 대신 텍스트 프로토콜 사용
DATABASE_URL="postgresql://user:pass@pgbouncer:6432/myapp?pgbouncer=true&connection_limit=10"

# schema.prisma
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")  # 마이그레이션용 직접 연결
}

# DIRECT_URL → PostgreSQL 직접 연결 (마이그레이션, Introspect 시 사용)
DIRECT_URL="postgresql://user:pass@postgres:5432/myapp"

TypeORM + PgBouncer

// typeorm.config.ts
export const dataSource = new DataSource({
  type: 'postgres',
  host: 'pgbouncer',
  port: 6432,
  database: 'myapp',
  // PgBouncer transaction 모드에서는 prepared statement 비활성화
  extra: {
    // node-postgres 옵션
    prepareThreshold: 0,  // prepared statement 비활성화
    max: 10,              // 앱 레벨 풀 크기 (PgBouncer보다 작게)
  },
});

Spring Boot (HikariCP) + PgBouncer

# application.yml
spring:
  datasource:
    url: jdbc:postgresql://pgbouncer:6432/myapp
    hikari:
      maximum-pool-size: 10        # PgBouncer default_pool_size보다 작게
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      # PgBouncer transaction 모드 호환
      connection-init-sql: ""      # 초기화 SQL 불필요
  jpa:
    properties:
      hibernate:
        # Prepared statement 캐시 비활성화 (transaction 모드)
        jdbc.use_scrollable_resultset: false

관리 콘솔 (SHOW 명령)

PgBouncer는 가상 데이터베이스 pgbouncer로 접속하여 관리 명령을 실행할 수 있습니다:

# 관리 콘솔 접속
psql -h localhost -p 6432 -U pgbouncer_admin pgbouncer

# === 핵심 모니터링 명령 ===

# 풀 상태 확인
SHOW POOLS;
# database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | pool_mode
# myapp    | app  | 15        | 2          | 10        | 15      | 0       | transaction
#
# cl_active: 활성 클라이언트 연결
# cl_waiting: 서버 연결 대기 중인 클라이언트 (이 값이 크면 풀 부족!)
# sv_active: 쿼리 실행 중인 서버 연결
# sv_idle: 유휴 서버 연결

# 통계 확인
SHOW STATS;
# database | total_xact_count | total_query_count | total_wait_time | avg_xact_time
# → total_wait_time이 크면 풀 사이즈 증가 필요

# 클라이언트 연결 목록
SHOW CLIENTS;
# → 어떤 앱에서 얼마나 연결하는지 확인

# 서버 연결 목록
SHOW SERVERS;
# → PostgreSQL과의 실제 연결 상태

# DNS 확인
SHOW DNS_HOSTS;

# 설정값 확인
SHOW CONFIG;

# === 관리 명령 ===
# 설정 재로드
RELOAD;

# 특정 DB 일시 중지 (유지보수 시)
PAUSE myapp;
RESUME myapp;

# 연결 끊기 (롤링 재시작 시)
KILL myapp;
RECONNECT myapp;

Prometheus 모니터링

# pgbouncer_exporter 배포 (Prometheus 메트릭 수집)
# docker-compose.yml
services:
  pgbouncer-exporter:
    image: prometheuscommunity/pgbouncer-exporter:latest
    environment:
      - PGBOUNCER_EXPORTER_CONNECTION_STRING=postgres://stats:pass@pgbouncer:6432/pgbouncer
    ports:
      - "9127:9127"

# 핵심 알림 규칙 (Prometheus)
groups:
  - name: pgbouncer
    rules:
      # 클라이언트 대기열 증가 알림
      - alert: PgBouncerClientWaiting
        expr: pgbouncer_pools_client_waiting_connections > 10
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "PgBouncer 클라이언트 대기 {{ $value }}개"

      # 서버 연결 포화도
      - alert: PgBouncerPoolSaturation
        expr: >
          pgbouncer_pools_server_active_connections
          / pgbouncer_pools_server_active_connections
          + pgbouncer_pools_server_idle_connections > 0.9
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "PgBouncer 풀 포화도 90% 초과"

      # 평균 대기 시간
      - alert: PgBouncerHighWaitTime
        expr: pgbouncer_stats_avg_wait_time_seconds > 0.5
        for: 3m
        labels:
          severity: warning

Kubernetes 배치 전략

K8s에서 PgBouncer를 배치하는 두 가지 패턴과 각각의 장단점입니다. K8s 네이티브 사이드카 컨테이너 가이드도 참고하세요:

# 패턴 1: 사이드카 (Pod마다 PgBouncer)
# 장점: 네트워크 홉 없음, 앱별 독립 설정
# 단점: 서버 연결 수 = Pod 수 × pool_size (스케일 시 주의)
apiVersion: apps/v1
kind: Deployment
metadata:
  name: api-server
spec:
  template:
    spec:
      initContainers:
        - name: pgbouncer
          image: bitnami/pgbouncer:1.22
          restartPolicy: Always
          ports:
            - containerPort: 6432
          env:
            - name: POSTGRESQL_HOST
              value: "postgres-primary.db.svc"
            - name: PGBOUNCER_POOL_MODE
              value: "transaction"
            - name: PGBOUNCER_DEFAULT_POOL_SIZE
              value: "5"  # 사이드카는 작게!
            - name: PGBOUNCER_MAX_CLIENT_CONN
              value: "50"
          startupProbe:
            tcpSocket:
              port: 6432
            periodSeconds: 2
            failureThreshold: 15
      containers:
        - name: api
          image: api-server:latest
          env:
            - name: DATABASE_URL
              value: "postgresql://user:pass@localhost:6432/myapp?pgbouncer=true"

---
# 패턴 2: 중앙 집중 (Deployment + Service)
# 장점: 서버 연결 수 통제 용이, 설정 일원화
# 단점: 네트워크 홉 추가, SPOF 가능성
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer
spec:
  replicas: 2  # HA 구성
  template:
    spec:
      affinity:
        podAntiAffinity:
          requiredDuringSchedulingIgnoredDuringExecution:
            - labelSelector:
                matchLabels:
                  app: pgbouncer
              topologyKey: kubernetes.io/hostname
      containers:
        - name: pgbouncer
          image: bitnami/pgbouncer:1.22
          resources:
            requests:
              cpu: 100m
              memory: 128Mi
            limits:
              cpu: 500m
              memory: 256Mi
---
apiVersion: v1
kind: Service
metadata:
  name: pgbouncer
spec:
  ports:
    - port: 6432
  selector:
    app: pgbouncer

운영 체크리스트

항목 확인 방법 조치
cl_waiting > 0 지속 SHOW POOLS default_pool_size 증가 또는 쿼리 최적화
avg_wait_time 증가 SHOW STATS 풀 포화 → 서버 연결 수 확인
sv_active 항상 max SHOW POOLS PostgreSQL 측 슬로우 쿼리 확인
max_client_conn 도달 SHOW CONFIG + 로그 max_client_conn 증가 또는 앱 연결 수 줄이기
DB 유지보수 – PAUSE → 작업 → RESUME (무중단)
PgBouncer 재시작 – SHUTDOWN 대신 RELOAD로 설정 적용

정리

PgBouncer는 PostgreSQL 운영에서 사실상 필수 컴포넌트입니다. transaction 풀링 모드로 소수의 서버 연결을 수천 클라이언트가 공유하면서도, Prepared Statement와 세션 변수의 함정만 주의하면 극적인 성능 향상을 얻을 수 있습니다. 특히 Kubernetes 환경에서 Pod 오토스케일링과 함께 사용할 때, PgBouncer 없이는 PostgreSQL max_connections가 금방 고갈되므로 반드시 도입을 검토하세요.

위로 스크롤
WordPress Appliance - Powered by TurnKey Linux