Bối cảnh: Khi PostgreSQL bắt đầu “thở nặng”
Sau khi migrate database 100GB từ MySQL sang PostgreSQL — mình mất 3 ngày planning và 1 ngày thực thi — hệ thống chạy ổn định được khoảng 2 tuần thì bắt đầu có triệu chứng lạ. Số connection đồng thời tăng dần theo từng lần deploy thêm app instance, response time API leo lên 800ms–1s vào giờ cao điểm. Đôi khi app trả thẳng lỗi too many connections.
Check pg_stat_activity thì thấy hàng trăm connection đang idle — không làm gì, chỉ ngồi chờ query mới. Vấn đề nằm ở đây: mỗi connection đến PostgreSQL là một OS process riêng, chiếm RAM và CPU thật sự. 180 connection idle đồng nghĩa với 180 process đang chạy không việc gì.
Khác với MySQL dùng thread-based model, PostgreSQL spawn một OS process riêng cho mỗi connection — gọi là process-per-connection. Scale-out thêm app instance? Mỗi instance giữ connection pool riêng, tổng connection tăng theo cấp số nhân mà không ai kiểm soát được.
Connection pooling ở application layer (HikariCP, psycopg2 pool) chỉ giải quyết trong phạm vi 1 process. Muốn gom connection từ nhiều instance lại, cần một lớp proxy ngồi giữa app và database. PgBouncer làm đúng việc đó.
PgBouncer hoạt động như thế nào
PgBouncer là một lightweight proxy — tiêu thụ khoảng 1–2MB RAM — ngồi giữa app và PostgreSQL. Nó nhận connection từ nhiều app instance, tái sử dụng một tập nhỏ connection thực đến PostgreSQL server. App tưởng mình đang connect thẳng PostgreSQL, nhưng thực ra đang nói chuyện với PgBouncer.
PgBouncer có 3 pool mode:
- session mode: Giữ 1 server connection suốt session của client — an toàn nhất nhưng tiết kiệm ít nhất
- transaction mode: Chỉ giữ server connection trong duration của 1 transaction — tiết kiệm nhất, phù hợp đa số app hiện đại
- statement mode: Cực kỳ aggressive, chỉ giữ trong 1 statement — không dùng được với multi-statement transaction
Trong production mình dùng transaction mode. Với 500 client connection vào PgBouncer, chỉ cần ~25 connection thực đến PostgreSQL. Đó là điểm mấu chốt của connection pooling.
Cài đặt PgBouncer
Trên Ubuntu/Debian:
sudo apt update
sudo apt install -y pgbouncer
# Kiểm tra version
pgbouncer --version
# PgBouncer 1.21.0Sau khi cài, PgBouncer chưa start được vì cần cấu hình trước. File config nằm ở /etc/pgbouncer/.
Cấu hình chi tiết
File pgbouncer.ini
Chỉnh sửa /etc/pgbouncer/pgbouncer.ini:
[databases]
; Alias mà app connect đến = connection string thực đến PostgreSQL
myapp_db = host=127.0.0.1 port=5432 dbname=myapp_production
[pgbouncer]
; PgBouncer lắng nghe
listen_port = 6432
listen_addr = 127.0.0.1
; Authentication
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; Pool mode
pool_mode = transaction
; Client-side: app connect vào PgBouncer
max_client_conn = 1000
; Server-side: connection thực đến PostgreSQL (per database-user pair)
default_pool_size = 25
; Reserve khi pool đầy
reserve_pool_size = 5
reserve_pool_timeout = 3
; Đóng idle server connection sau 10 phút
server_idle_timeout = 600
client_idle_timeout = 0
; Log
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
; Admin access
admin_users = pgbouncer_admin
stats_users = pgbouncer_statsThông số quan trọng cần hiểu:
default_pool_size = 25: tổng server connection đến PostgreSQL per database-user pair. Con số này phụ thuộcmax_connectionstrong PostgreSQL và số database bạn pool. Với mình, PostgreSQL configmax_connections = 100, dùng 2 database → mỗi pool để 25 là hợp lý.max_client_conn = 1000: số client connect vào PgBouncer — con số này lớn cũng không sao vì PgBouncer cực nhẹ (mỗi client chỉ tốn vài KB).server_idle_timeout = 600: tránh giữ server connection idle lâu không cần thiết.
File userlist.txt
Lấy password hash từ PostgreSQL rồi điền vào file auth:
# Lấy hash password của user
sudo -u postgres psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename='myapp_user';"Tạo file /etc/pgbouncer/userlist.txt:
"myapp_user" "md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
"pgbouncer_admin" "md5yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy"Phân quyền đúng để PgBouncer đọc được:
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txtKhởi động và kết nối
sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
sudo systemctl status pgbouncerApp giờ connect đến port 6432 thay vì 5432. Với Python:
import psycopg2
# Trước: connect thẳng PostgreSQL port 5432
# conn = psycopg2.connect("host=localhost port=5432 dbname=myapp_production user=myapp_user")
# Sau: qua PgBouncer port 6432
conn = psycopg2.connect(
host="localhost",
port=6432, # PgBouncer port
dbname="myapp_db", # Alias trong [databases] section
user="myapp_user",
password="your_password"
)Lưu ý khi dùng transaction mode
Transaction mode không hỗ trợ một số tính năng session-level của PostgreSQL:
SET/RESETsession variablesLISTEN/NOTIFY- Advisory locks ở session level
- Prepared statements (cần config thêm hoặc dùng
server_reset_query_always = 1)
App Django/FastAPI thông thường? Thường không gặp vấn đề gì. Nhưng nếu code đang dùng prepared statements tường minh, test kỹ trước khi lên production — hoặc chuyển sang session mode.
Kiểm tra & Monitoring
PgBouncer admin console
Connect vào admin console:
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncerCác lệnh cần biết:
-- Trạng thái các pool
SHOW POOLS;
-- Thống kê request/s, latency
SHOW STATS;
-- Danh sách server connection (connection thực đến PostgreSQL)
SHOW SERVERS;
-- Reload config không cần restart
RELOAD;Output SHOW POOLS trông như này:
database | user | cl_active | cl_waiting | sv_active | sv_idle | maxwait
-----------+------------+-----------+------------+-----------+---------+---------
myapp_db | myapp_user | 45 | 0 | 20 | 5 | 0Cột cần để ý nhất là cl_waiting. Con số này > 0 thường xuyên là tín hiệu pool đang quá tải — client phải xếp hàng chờ. Giải pháp: tăng default_pool_size và tăng max_connections trong PostgreSQL tương ứng.
Kiểm tra từ PostgreSQL side
-- Đếm connection thực từ PgBouncer vào PostgreSQL
SELECT count(*), state
FROM pg_stat_activity
WHERE usename = 'myapp_user'
GROUP BY state;
-- Xem max_connections hiện tại
SHOW max_connections;Script alert đơn giản
#!/bin/bash
# /opt/scripts/check-pgbouncer.sh
# Chạy cron mỗi 5 phút: */5 * * * * /opt/scripts/check-pgbouncer.sh
WAITING=$(psql -h 127.0.0.1 -p 6432 -U pgbouncer_stats pgbouncer \
-t -c "SHOW POOLS;" 2>/dev/null | awk -F'|' '{sum += $4} END {print int(sum)}')
if [ "${WAITING:-0}" -gt 10 ]; then
echo "ALERT: PgBouncer có $WAITING client đang chờ connection!" | \
mail -s "[PgBouncer] Pool exhausted" [email protected]
fiKết quả thực tế sau 6 tháng
Số liệu cụ thể sau khi deploy PgBouncer:
- Server connection đến PostgreSQL giảm từ ~180 xuống ổn định ~25
- RAM PostgreSQL giảm khoảng 15% do ít process hơn
- Response time API giờ cao điểm ổn định dưới 300ms (trước là 800ms–1s)
- Không còn lỗi
too many connections
Điều rút ra sau 6 tháng chạy production: PostgreSQL rất mạnh, nhưng connection model của nó cần được quản lý chủ động hơn MySQL. PgBouncer không phải silver bullet — query chậm thì vẫn phải optimize query. Nhưng với bài toán connection overhead ở tầng infrastructure, đây là giải pháp rõ ràng nhất.
Nếu PostgreSQL đang có connection tăng bất thường hoặc app hay báo lỗi timeout — thêm PgBouncer trước khi tính đến nâng cấp server. Thường giải quyết được vấn đề mà không cần đụng đến phần cứng.
