PostgreSQL: PgBouncer

※ PostgreSQL: PgBouncer.
 
안녕하세요. 듀스트림입니다.
 
오늘 포스팅은 PostgreSQL의 커넥션 풀러 중 하나인 PgBouncer의 개요부터 설치, 튜닝까지의 내용입니다.
 
커넥션 풀에 대한 개념 및 내용은 아래 포스팅을 참고 부탁드립니다.

PostgreSQL: 커넥션 풀

---

1. PgBouncer ?

PgBouncer는 PostgreSQL 전용의 초경량(Connection Pooler) 미들웨어로, 애플리케이션과 PostgreSQL 사이에 위치하여 데이터베이스 연결 수를 효율적으로 관리해 주는 컴포넌트입니다.
PostgreSQL은 접속 1개당 백엔드 프로세스 1개를 생성하는 구조이기 때문에, 연결이 폭증하면 CPU/메모리 부담이 증가하고 성능 저하가 발생할 수 있습니다.
이때 PgBouncer가 중간에서 연결을 대신 관리해 주어 DB 서버 부담을 줄입니다.
 
클라이언트 애플리케이션은 실제 DB에 직접 연결하지 않고 PgBouncer와 연결합니다.
PgBouncer는 내부에서 이미 만들어둔 PostgreSQL 연결을 재사용하여 성능을 높입니다.

특히 다음 상황에서 효과가 큽니다.

• 웹 애플리케이션처럼 짧고 빈번한 쿼리가 많을 때
• 접속 수가 급증하는 환경(대규모 API 서버 등)
• Lambda, Cloud Function처럼 DB 커넥션 생성이 비싼 환경

 

1.1 PgBouncer Pooling Mode

PgBouncer는 아래 3가지 방식으로 접속을 관리합니다.
 
1.1.1 Session pooling (Default)

• 한 클라이언트가 연결을 잡으면, 그 세션 동안 PostgreSQL 연결을 독점합니다.
• 세션이 끝나야 다른 클라이언트가 해당 연결을 사용할 수 있습니다.
  → 대부분의 시스템에서 가장 많이 사용
  → 트랜잭션 저장 변수, prepare statement 유지 가능

1.1.2 Transaction pooling

• 트랜잭션이 끝날 때마다 PostgreSQL 연결을 반납하고 다른 클라이언트에게 할당합니다.
  → 가장 높은 효율/성능
  → 단, 세션 레벨 기능 사용 불가 (예: temp table, session variable, cursor 등)

1.1.3 Statement pooling

• 쿼리 한 개만 수행하고 바로 연결을 반환합니다.
  → 성능 최강이지만 제약이 매우 많아서 실제 운영에서는 거의 사용 안 함

❗Pgpool-II는 클라이언트 ↔ Pgpool-II ↔ PostgreSQL 구조에서 클라이언트 커넥션은 계속 유지하지만, PostgreSQL 백엔드 연결은 필요한 순간에만 잡아 사용한 뒤 다시 풀에 반환합니다.

---

1.2 PgBouncer를 사용하는 이유

문제	PgBouncer 사용 시
PostgreSQL의 높은 연결 생성 비용	연결 재사용으로 오버헤드 감소
애플리케이션에서 커넥션 폭증	DB 연결 수 제한 가능(max_client_conn, pool_size)
연결을 오래 붙잡는 클라이언트 문제	pooling 모드로 제약 가능
DB 서버 메모리 과다 사용	백엔드 프로세스 수 줄어들어 메모리 절감

PostgreSQL은 특히 연결 생성 비용이 높은 편이라, Connection Poooler 없이 OLTP 운영은 많은 리스크가 존재합니다.

---

1.3 PgBouncer의 주요 특징

1. 매우 가벼움
2. 단일 스레드 기반
3. HAProxy와 매우 잘 어울림
4. TLS 지원
5. 인증 캐싱 지원
6. 다양한 Pool 모드 제공(session / tx / statement)
7. ini 형태의 설정 파일

---

2. PgBouncer 설치

▸ 패키지 설치

# root 또는 sudo 권한이 있는 User로 진행
dnf install -y epel-release
dnf groupinstall -y "Development Tools"
dnf install -y autoconf automake libtool pkgconfig libevent libevent-devel openssl openssl-devel readline readline-devel postgresql-devel gcc make pkgconfig python3
dnf --enablerepo=epel install -y pandoc

 
▸ PgBouncer 다운로드

cd ~
wget https://www.pgbouncer.org/downloads/files/1.25.1/pgbouncer-1.25.1.tar.gz
tar zxvf pgbouncer-1.25.1.tar.gz

 
▸ PgBouncer 설치

cd pgbouncer-1.25.1
./configure --prefix=/usr/local/pgbouncer --with-libevent=/usr --with-openssl
make & make install

 
▸ 디렉터리 구조 생성

mkdir -p /etc/pgbouncer
mkdir -p /var/log/pgbouncer
mkdir -p /var/run/pgbouncer
chown -R postgres:postgres /var/log/pgbouncer /var/run/pgbouncer

 
▸ pgbouncer.ini 생성

vi /etc/pgbouncer/pgbouncer.ini

 
→ 아래 내용 추가

[databases]
; 여러 DB가 있으면 모두 추가
; DB name = host=127.0.0.1 port=5432 dbname=postgres
postgres = host=127.0.0.1 port=5432 dbname=postgres
test = host=127.0.0.1 port=5432 dbname=test

[pgbouncer]
; PgBouncer가 클라이언트를 받는 IP/포트
listen_addr = 0.0.0.0
listen_port = 6432

; 소켓 디렉토리 (로컬 유닉스 소켓도 사용할 경우)
unix_socket_dir = /var/run/pgbouncer

; 인증 방식
; PostgreSQL이 scram-sha-256이면, pgbouncer도 scram 사용 가능(md5도 가능)
; auth_type = scram-sha-256
auth_type = md5

; PostgreSQL의 pg_authid 내용을 export한 auth_file 사용
auth_file = /etc/pgbouncer/userlist.txt

; 풀링 모드: session / transaction / statement
; 처음에는 session 으로 테스트, 이후 transaction으로 변경 고려
pool_mode = transaction

; 클라이언트/서버 커넥션 수 관련
max_client_conn = 5000
default_pool_size = 200
min_pool_size = 20

; PostgreSQL 세션당 최대 커넥션 수 제한
; 노드당 100~300 수준부터 시작 추천
reserve_pool_size = 20
reserve_pool_timeout = 5

; 로깅
logfile = /var/log/pgbouncer/pgbouncer.log
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; 관리용
admin_users = postgres, pgbouncer
stats_users = postgres, pgbouncer

; 기타 타임아웃 (기본값에서 필요 시 조절)
query_timeout = 0
client_idle_timeout = 0
server_idle_timeout = 600

 
▸ 인증 설정

-- psql
SELECT usename, passwd FROM pg_shadow;

-- 이 값을 토대로 리스트 작성

 

vi /etc/pgbouncer/userlist.txt

 
→ 아래 내용 추가

# 아래 형식으로 추가
"postgres" "SCRAM-SHA-256$4096:...."
"app_user" "SCRAM-SHA-256$4096:...."

 
▸ systemd 서비스 등록

vi /etc/systemd/system/pgbouncer.service

 
→ 아래 내용 추가

[Unit]
Description=PgBouncer Connection Pooler
After=network.target

[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/local/pgbouncer/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID
Restart=on-failure
PIDFile=/var/run/pgbouncer/pgbouncer.pid

[Install]
WantedBy=multi-user.target

 
▸ Daemon Reload 및 Start

systemctl daemon-reload
systemctl enable pgbouncer
systemctl start pgbouncer
systemctl status pgbouncer

 
▸ 접속 테스트

psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=postgres"

• dbname=pgbouncer로 테스트 후 dbname 변경해서 테스트

 
→ 아래 명령어들로 상태 확인

-- psql: dbname=pgbouncer 전용 명령어
SHOW DATABASES;
SHOW POOLS;
SHOW CLIENTS;
SHOW SERVERS;
SHOW STATS;

---

3. 튜닝 방법

예를 들어 max_connections=1000 × 3 Node → PgBouncer 도입 시 튜닝 방향에 대해 설명하겠습니다.
 
현재는 각 노드 max_connections = 1000 이라면,

• 애플리케이션 총 커넥션이 늘수록
  → PostgreSQL 프로세스(백엔드) 개수가 매우 많아지고
  → context switch, 메모리 사용량, connection overhead가 커짐

PgBouncer 도입 후에는 일반적으로 아래처럼 수정하고 많은 클라이언트 → 상대적으로 적은 DB 커넥션 구조로 만드는 것이 목표입니다.

1. PostgreSQL max_connections를 줄이고 (300~500 수준부터 시작)
2. PgBouncer에서 max_client_conn은 크게 (3000~5000),
3. default_pool_size는 작게 설정 (DB당 50~200)

 
예를 들어, 노드당 아래처럼 베이스라인으로 잡고 실제 워크로드(접속수, TPS, latency, idle session 패턴)를 보면서 늘리거나 줄이면 됩니다.

• PostgreSQL: max_connections = 400
• PgBouncer:
  • max_client_conn = 5000
  • default_pool_size = 100 (DB당 100개 서버 커넥션)
  • 필요하면 reserve_pool_size로 버스트 대응

---

오늘은 여기까지~