Connection Pooling ด้วย PgBouncer: คู่มือตั้งค่าและปรับแต่งสำหรับ PostgreSQL

PostgreSQL สร้าง Process ใหม่ทุกครั้งที่มีการเชื่อมต่อเข้ามา ซึ่งใช้ทรัพยากรทั้ง Memory และ CPU ในการ Fork Process เมื่อแอปพลิเคชันมีผู้ใช้จำนวนมากและเปิดปิดการเชื่อมต่อบ่อย ๆ เซิร์ฟเวอร์จะรับภาระหนักจนทำให้ประสิทธิภาพลดลง

Connection Pooling เป็นเทคนิคที่แก้ปัญหานี้โดยสร้างกลุ่มการเชื่อมต่อสำเร็จรูปไว้ล่วงหน้า แล้วนำกลับมาใช้ซ้ำแทนการสร้างใหม่ทุกครั้ง PgBouncer เป็นเครื่องมือ Connection Pooler ที่ได้รับความนิยมสูงสุดสำหรับ PostgreSQL เพราะเบา เร็ว และตั้งค่าง่าย บทความนี้จะอธิบายหลักการทำงาน วิธีติดตั้ง ตั้งค่า และปรับแต่ง PgBouncer สำหรับ Production

ทำไมต้อง Connection Pooling

PostgreSQL ใช้สถาปัตยกรรม Process-per-Connection หมายความว่าทุกการเชื่อมต่อจะมี Backend Process ของตัวเอง แต่ละ Process ใช้ Memory ประมาณ 5-10 MB ขึ้นอยู่กับ work_mem และ shared_buffers ที่ตั้งไว้ ถ้ามี 500 การเชื่อมต่อพร้อมกัน เซิร์ฟเวอร์อาจใช้ Memory เพิ่มอีก 2.5-5 GB เฉพาะสำหรับ Backend Process

ปัญหาที่พบบ่อยเมื่อไม่ใช้ Connection Pooling ได้แก่ การสร้างและทำลาย Process ซ้ำ ๆ ทำให้เกิด Overhead สูง, Memory ถูกใช้โดยการเชื่อมต่อที่ Idle ไม่ได้ทำงานจริง, เมื่อจำนวนการเชื่อมต่อเกิน max_connections ฐานข้อมูลจะปฏิเสธการเชื่อมต่อใหม่ทันที และ Context Switching ระหว่าง Process จำนวนมากทำให้ CPU ทำงานไม่มีประสิทธิภาพ

PgBouncer คืออะไร

PgBouncer เป็น Lightweight Connection Pooler สำหรับ PostgreSQL ทำหน้าที่เป็นตัวกลางระหว่างแอปพลิเคชันกับฐานข้อมูล โดยรับการเชื่อมต่อจาก Client แล้วจัดสรร Backend Connection ที่มีอยู่ให้ แทนที่จะสร้างใหม่ทุกครั้ง

จุดเด่นของ PgBouncer คือใช้ Memory น้อยมาก (ประมาณ 2 KB ต่อ 1 การเชื่อมต่อ), รองรับ Client หลายพัน Connection พร้อมกัน, ตั้งค่าง่ายด้วยไฟล์ Configuration เดียว และมี Admin Console สำหรับตรวจสอบสถานะแบบ Real-time

ติดตั้ง PgBouncer

ติดตั้งบน Ubuntu/Debian

sudo apt update
sudo apt install pgbouncer

# ตรวจสอบเวอร์ชัน
pgbouncer --version

# ไฟล์ Configuration หลัก
ls /etc/pgbouncer/
# pgbouncer.ini  — ไฟล์ตั้งค่าหลัก
# userlist.txt   — รายชื่อผู้ใช้และรหัสผ่าน

ติดตั้งบน CentOS/RHEL

sudo dnf install pgbouncer

# หรือจาก PostgreSQL Repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install pgbouncer

ตั้งค่า PgBouncer เบื้องต้น

ไฟล์ pgbouncer.ini

[databases]
# ชื่อฐานข้อมูลที่ Client เห็น = การเชื่อมต่อจริงไปยัง PostgreSQL
myapp = host=127.0.0.1 port=5432 dbname=myapp_production

# เชื่อมต่อไปยังเซิร์ฟเวอร์อื่น
analytics = host=10.0.1.50 port=5432 dbname=analytics_db

# ใช้ชื่อเดิมเชื่อมต่อฐานข้อมูลเดิม
* = host=127.0.0.1 port=5432

[pgbouncer]
# ที่อยู่และพอร์ตที่ PgBouncer รับฟัง
listen_addr = 0.0.0.0
listen_port = 6432

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

# Pool Mode (สำคัญมาก!)
pool_mode = transaction

# ขนาด Pool
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

# จำนวน Client สูงสุด
max_client_conn = 1000
max_db_connections = 50

# Timeout
server_idle_timeout = 600
client_idle_timeout = 0
client_login_timeout = 60
query_timeout = 0
query_wait_timeout = 120

# Logging
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

# Admin
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

ไฟล์ userlist.txt

# รูปแบบ: "username" "password"
# รหัสผ่านเป็น MD5 หรือ SCRAM-SHA-256 hash

# ดึง hash จาก PostgreSQL
psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename = 'myapp_user';"

# เพิ่มใน userlist.txt
"myapp_user" "SCRAM-SHA-256$4096:salt$stored_key:server_key"
"pgbouncer_admin" "SCRAM-SHA-256$4096:salt$stored_key:server_key"

# หรือใช้ auth_query แทน (แนะนำสำหรับ Production)
# ตั้งค่าใน pgbouncer.ini:
# auth_type = scram-sha-256
# auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

Pool Mode: เลือกให้เหมาะกับแอปพลิเคชัน

Pool Mode เป็นการตั้งค่าที่สำคัญที่สุดของ PgBouncer เพราะกำหนดว่า Backend Connection จะถูกแชร์อย่างไร มี 3 โหมด

Session Mode

Client ได้ Backend Connection ตลอดระยะเวลาที่เชื่อมต่อ เหมือนเชื่อมต่อ PostgreSQL โดยตรง รองรับทุก Feature เช่น Prepared Statement, SET, LISTEN/NOTIFY แต่ประหยัดทรัพยากรน้อยที่สุดเพราะ Connection ไม่ได้ถูกแชร์

Transaction Mode

Client ได้ Backend Connection เฉพาะระหว่างทำ Transaction เท่านั้น เมื่อ Transaction จบ Connection จะถูกคืนกลับ Pool ให้ Client อื่นใช้ต่อ โหมดนี้เหมาะกับแอปพลิเคชันส่วนใหญ่และประหยัดทรัพยากรได้มาก แต่ไม่รองรับ Feature ที่ต้องใช้ข้าม Transaction เช่น Prepared Statement (ใน PostgreSQL เวอร์ชันเก่า), SET parameter, LISTEN/NOTIFY

Statement Mode

Client ได้ Backend Connection เฉพาะระหว่างรัน Query เดียว ไม่รองรับ Multi-Statement Transaction (BEGIN/COMMIT) เหมาะเฉพาะ Autocommit Workload เท่านั้น ประหยัดทรัพยากรมากที่สุดแต่ข้อจำกัดเยอะที่สุด

คุณสมบัติ	Session	Transaction	Statement
ประหยัดทรัพยากร	น้อย	มาก	มากที่สุด
Prepared Statement	รองรับ	รองรับ (PG 14+)	ไม่รองรับ
SET/RESET	รองรับ	ไม่รองรับ	ไม่รองรับ
Multi-Statement TX	รองรับ	รองรับ	ไม่รองรับ
LISTEN/NOTIFY	รองรับ	ไม่รองรับ	ไม่รองรับ
เหมาะกับ	Legacy App	Web App ทั่วไป	Simple Query

เริ่มใช้งาน PgBouncer

# เริ่ม PgBouncer
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

# ตรวจสอบสถานะ
sudo systemctl status pgbouncer

# เชื่อมต่อผ่าน PgBouncer (พอร์ต 6432 แทน 5432)
psql -h 127.0.0.1 -p 6432 -U myapp_user -d myapp

# ทดสอบว่าเชื่อมต่อผ่าน PgBouncer จริง
# ใน Application เปลี่ยนพอร์ตจาก 5432 เป็น 6432
# DATABASE_URL=postgresql://myapp_user:[email protected]:6432/myapp

Admin Console: ตรวจสอบสถานะ Pool

# เชื่อมต่อ Admin Console
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer

# ดูสถานะ Pool ทั้งหมด
SHOW POOLS;
# database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | pool_mode
# myapp    | app  | 45        | 0          | 10        | 10      | 0       | transaction

# ดูการเชื่อมต่อทั้งหมด
SHOW CLIENTS;
SHOW SERVERS;

# ดูสถิติ
SHOW STATS;
# total_xact_count — จำนวน Transaction ทั้งหมด
# total_query_count — จำนวน Query ทั้งหมด
# avg_xact_time — เวลาเฉลี่ยต่อ Transaction (microseconds)

# ดูค่าตั้งค่าปัจจุบัน
SHOW CONFIG;

# Reload Configuration โดยไม่ต้อง Restart
RELOAD;

# หยุดรับ Client ใหม่ (Graceful)
PAUSE myapp;
RESUME myapp;

# ตัด Connection ที่ค้างอยู่
KILL myapp;

ปรับแต่งขนาด Pool สำหรับ Production

การกำหนดขนาด Pool ที่เหมาะสมขึ้นอยู่กับจำนวน CPU Core ของเซิร์ฟเวอร์ ลักษณะ Workload และ max_connections ของ PostgreSQL สูตรเริ่มต้นที่แนะนำคือ

# สูตรคำนวณ default_pool_size
# default_pool_size = (CPU cores * 2) + effective_spindle_count
# ตัวอย่าง: เซิร์ฟเวอร์ 8 cores, SSD
# default_pool_size = (8 * 2) + 1 = 17 → ปัดเป็น 20

# postgresql.conf
max_connections = 100    # จำกัดที่ PostgreSQL

# pgbouncer.ini
[pgbouncer]
default_pool_size = 20       # Backend Connection ต่อ user/database pair
min_pool_size = 5            # สร้าง Connection ล่วงหน้าอย่างน้อยนี้
reserve_pool_size = 5        # Connection สำรองเมื่อ Pool เต็ม
reserve_pool_timeout = 3     # รอกี่วินาทีก่อนใช้ Reserve

max_client_conn = 2000       # Client สูงสุดที่ PgBouncer รับได้
max_db_connections = 60      # Backend Connection สูงสุดต่อฐานข้อมูล
max_user_connections = 0     # 0 = ไม่จำกัดต่อ User

# ข้อสำคัญ:
# max_db_connections ต้องน้อยกว่า max_connections ของ PostgreSQL
# เผื่อ Connection สำหรับ Admin, Monitoring, Replication

ตั้งค่า Timeout ที่เหมาะสม

[pgbouncer]
# Server-side Timeout
server_idle_timeout = 600        # ปิด Backend Connection ที่ Idle เกิน 10 นาที
server_lifetime = 3600           # Recycle Backend Connection ทุก 1 ชั่วโมง
server_connect_timeout = 15      # Timeout สำหรับการเชื่อมต่อไปยัง PostgreSQL
server_login_retry = 15          # รอกี่วินาทีก่อน Retry เชื่อมต่อ

# Client-side Timeout
client_idle_timeout = 0          # 0 = ไม่ตัด Client ที่ Idle (ให้ App จัดการ)
client_login_timeout = 60        # Timeout สำหรับ Authentication
query_timeout = 0                # 0 = ไม่มี Timeout สำหรับ Query
query_wait_timeout = 120         # Client รอ Backend Connection ได้นานสุด 2 นาที

# สำหรับ Web Application ที่ต้องการ Response เร็ว
# query_wait_timeout = 30        # ลดเหลือ 30 วินาที
# client_idle_timeout = 300      # ตัด Client ที่ Idle เกิน 5 นาที

Application-Level Connection Pooling กับ PgBouncer

หลายเฟรมเวิร์กมี Connection Pool ในตัวอยู่แล้ว เช่น Django, Rails, Spring Boot เมื่อใช้ร่วมกับ PgBouncer ควรปรับค่าให้สอดคล้องกัน

# ตัวอย่าง: Django + PgBouncer
# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': '127.0.0.1',
        'PORT': '6432',          # พอร์ต PgBouncer
        'NAME': 'myapp',
        'USER': 'myapp_user',
        'PASSWORD': 'secret',
        'CONN_MAX_AGE': 0,       # ปิด Django Pool (ให้ PgBouncer จัดการ)
        'DISABLE_SERVER_SIDE_CURSORS': True,  # จำเป็นสำหรับ Transaction Mode
    }
}

# ตัวอย่าง: Node.js (pg library) + PgBouncer
# const pool = new Pool({
#   host: '127.0.0.1',
#   port: 6432,
#   max: 10,                     # Pool ของ App (น้อยกว่า PgBouncer)
#   idleTimeoutMillis: 30000,
# })

# ตัวอย่าง: Go (pgx) + PgBouncer
# config.MaxConns = 10           # Pool ของ App
# config.PreferSimpleProtocol = true  # จำเป็นสำหรับ Transaction Mode

หลักการสำคัญคือ Application Pool ควรมี Connection น้อยกว่า PgBouncer Pool เพื่อไม่ให้เกิดคอขวดที่ PgBouncer และปิด Feature ที่ไม่รองรับใน Transaction Mode เช่น Server-side Cursor

High Availability: PgBouncer กับ HAProxy

ในระบบ Production ที่ต้องการความพร้อมใช้งานสูง สามารถวาง PgBouncer หลายตัวหลัง HAProxy เพื่อกระจายโหลดและรองรับ Failover

# haproxy.cfg — Load Balance PgBouncer
frontend pgbouncer_front
    bind *:6432
    default_backend pgbouncer_back

backend pgbouncer_back
    balance roundrobin
    option tcp-check
    server pgb1 10.0.1.10:6432 check
    server pgb2 10.0.1.11:6432 check

Monitoring PgBouncer

# Script ตรวจสอบสถานะ PgBouncer
#!/bin/bash

# ดูจำนวน Client ที่รอ Backend Connection
WAITING=$(psql -h 127.0.0.1 -p 6432 -U pgbouncer_stats -d pgbouncer \
  -t -c "SHOW POOLS;" | awk -F'|' '{sum += $4} END {print sum}')

if [ "$WAITING" -gt 10 ]; then
    echo "WARNING: $WAITING clients waiting for connections"
fi

# ดูอัตราการใช้ Pool
psql -h 127.0.0.1 -p 6432 -U pgbouncer_stats -d pgbouncer \
  -c "SHOW POOLS;" \
  -c "SHOW STATS_TOTALS;"

# Prometheus Exporter สำหรับ PgBouncer
# ใช้ pgbouncer_exporter เพื่อส่ง Metrics ไปยัง Prometheus/Grafana
# https://github.com/prometheus-community/pgbouncer_exporter

Metrics สำคัญที่ควรเฝ้าดูได้แก่ cl_waiting (Client ที่รอ Backend Connection ควรเป็น 0), sv_active กับ sv_idle (สัดส่วนของ Backend Connection ที่ทำงานและว่าง), avg_xact_time (เวลาเฉลี่ยต่อ Transaction) และ total_wait_time (เวลาที่ Client รอรวม ถ้าสูงแสดงว่า Pool เล็กเกินไป)

Troubleshooting ปัญหาที่พบบ่อย

ปัญหาแรกที่พบบ่อยคือ Prepared Statement ไม่ทำงานใน Transaction Mode ซึ่งแก้ได้โดยอัปเกรด PostgreSQL เป็นเวอร์ชัน 14 ขึ้นไปที่รองรับ Protocol-level Prepared Statement ข้าม Connection หรือตั้ง max_prepared_statements ใน PgBouncer 1.21+

# PgBouncer 1.21+ รองรับ Prepared Statement ใน Transaction Mode
[pgbouncer]
max_prepared_statements = 100    # จำนวน Prepared Statement สูงสุดต่อ Connection

# ถ้าใช้เวอร์ชันเก่า ให้ App ปิด Prepared Statement
# Rails: prepared_statements: false
# Django: DISABLE_SERVER_SIDE_CURSORS = True

ปัญหาที่สองคือ Authentication Failed ซึ่งมักเกิดจาก Password Hash ใน userlist.txt ไม่ตรงกับ PostgreSQL แก้โดยใช้ auth_query แทน userlist.txt เพื่อดึง Password จากฐานข้อมูลโดยตรง

# ใช้ auth_query แทน userlist.txt
[pgbouncer]
auth_type = scram-sha-256
auth_user = pgbouncer_auth        # User ที่มีสิทธิ์อ่าน pg_shadow
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

# สร้าง User สำหรับ auth_query
CREATE USER pgbouncer_auth WITH PASSWORD 'auth_password';
GRANT SELECT ON pg_shadow TO pgbouncer_auth;

สรุป

PgBouncer เป็นเครื่องมือที่จำเป็นสำหรับ PostgreSQL ใน Production โดยเฉพาะเมื่อมี Client จำนวนมาก เลือก Transaction Mode เป็นค่าเริ่มต้นเพราะให้ความสมดุลระหว่างประสิทธิภาพและความเข้ากันได้ดีที่สุด กำหนดขนาด Pool ตามจำนวน CPU Core ตั้ง Timeout ให้เหมาะกับ Workload และเฝ้าดู cl_waiting อย่าให้สูงเกินไป การใช้ Connection Pooler ช่วยให้ฐานข้อมูลรองรับ Client ได้มากขึ้นโดยใช้ทรัพยากรน้อยลงอย่างมีนัยสำคัญ

แนะนำบริการ DE

การรัน PgBouncer ร่วมกับ PostgreSQL ต้องการเซิร์ฟเวอร์ที่ควบคุมได้เต็มที่เพื่อติดตั้งและตั้งค่าได้อย่างอิสระ Cloud VPS ของ DE ให้ Root Access พร้อม SSD NVMe ที่มี Latency ต่ำ เหมาะสำหรับวาง PgBouncer ไว้บนเซิร์ฟเวอร์เดียวกับฐานข้อมูลเพื่อลด Network Overhead

สำหรับผู้ที่ต้องการโฮสต์เว็บแอปพลิเคชันโดยไม่ต้องจัดการเซิร์ฟเวอร์เอง Cloud Hosting ของ DE มีระบบจัดการฐานข้อมูลพร้อมใช้งานที่ดูแลเรื่อง Performance ให้โดยอัตโนมัติ