Cài PgBouncer Trên VPS Để Giảm Kết Nối PostgreSQL Và Ổn Định Ứng Dụng

Một PostgreSQL server có thể xử lý rất tốt hàng nghìn query mỗi giây nhưng vẫn chậm nếu ứng dụng mở quá nhiều connection đồng thời. Mỗi connection PostgreSQL không miễn phí: nó cần backend process, memory và bookkeeping riêng.

PgBouncer giải quyết bài toán này bằng cách đứng giữa client và PostgreSQL:

Ứng dụng → PgBouncer → PostgreSQL

Nhiều client connection có thể dùng chung một nhóm nhỏ server connection phía sau.

Nếu bạn đang tối ưu query chậm, xem bài PostgreSQL chậm trên VPS. PgBouncer giải quyết connection pressure, không thay thế index hay query tuning.

Khi nào nên dùng PgBouncer?

PgBouncer đặc biệt hữu ích khi:

• app server có nhiều worker;
• serverless/job tạo nhiều connection ngắn;
• PHP/Python/Node.js mở connection thường xuyên;
• PostgreSQL có max_connections cao nhưng CPU/RAM bắt đầu chịu áp lực;
• nhiều service cùng truy cập một database;
• traffic có burst lớn.

Nếu chỉ có một app nhỏ với connection pool tốt sẵn trong framework, PgBouncer chưa chắc cần thiết.

PgBouncer có những pool mode nào?

PgBouncer hỗ trợ ba mode chính.

Session pooling

Một server connection được gắn cho client trong suốt client session.

Ưu điểm:

• tương thích nhiều PostgreSQL feature nhất;
• ít thay đổi hành vi ứng dụng.

Nhược điểm:

• tiết kiệm connection ít hơn transaction pooling.

Transaction pooling

Server connection chỉ được giữ trong thời gian transaction. Khi transaction kết thúc, connection được trả lại pool cho client khác.

Đây là mode phổ biến khi muốn tối ưu connection mạnh hơn.

Nhưng nó phá vỡ một số session-based feature như:

• SET state không được track đầy đủ;
• session-level advisory lock;
• LISTEN;
• một số temp table/session behavior;
• SQL-level prepared statement tùy cách dùng.

Ứng dụng phải tương thích.

Statement pooling

Connection được trả lại pool sau mỗi statement. Multi-statement transaction không được hỗ trợ.

Đây là mode rất mạnh tay và hiếm khi phù hợp ứng dụng web thông thường.

Cài PgBouncer trên Ubuntu

sudo apt update
sudo apt install -y pgbouncer

Kiểm tra:

pgbouncer --version
sudo systemctl status pgbouncer

Các file thường nằm dưới:

/etc/pgbouncer/

Kiểm tra package thực tế:

dpkg -L pgbouncer | grep -E 'ini|userlist'

Cấu hình database

Mở:

sudo nano /etc/pgbouncer/pgbouncer.ini

Ví dụ:

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pool_mode = transaction
max_client_conn = 300
default_pool_size = 30
reserve_pool_size = 10
reserve_pool_timeout = 5

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

admin_users = postgres
stats_users = postgres

Các giá trị trên chỉ là ví dụ. Không nên copy max_client_conn=300 hay default_pool_size=30 mà không đo workload.

Ý nghĩa max_client_conn

max_client_conn là số client connection tối đa PgBouncer chấp nhận.

Ví dụ:

max_client_conn = 300

PgBouncer Docs lưu ý khi tăng giá trị này, giới hạn file descriptor của hệ điều hành cũng có thể cần tăng.

Đừng nghĩ 1.000 client connections đồng nghĩa PostgreSQL phải nhận 1.000 connection. Đó chính là vai trò của pooling.

default_pool_size là gì?

Đây là số server connection tối đa mặc định cho mỗi user/database pair.

Ví dụ:

default_pool_size = 30

Nếu có 200 client connection nhưng pool size là 30, PgBouncer sẽ queue phần còn lại khi 30 server connection đang bận.

Pool quá nhỏ → query chờ nhiều.

Pool quá lớn → mất lợi ích giảm connection pressure.

Reserve pool

reserve_pool_size = 10
reserve_pool_timeout = 5

Reserve pool cho phép thêm một lượng connection tạm thời khi client phải chờ quá lâu.

Nó giúp xử lý burst, nhưng không nên dùng để che cấu hình default_pool_size quá thấp kéo dài.

Authentication

Một cách đơn giản là dùng userlist.txt.

Ví dụ:

"appuser" "SCRAM-SHA-256$..."

Không nên lưu plaintext password trong Git repository.

PgBouncer cũng hỗ trợ các mô hình authentication khác như auth_query, nhưng cấu hình phức tạp hơn và cần thiết kế quyền cẩn thận.

Lấy SCRAM verifier từ PostgreSQL

Bạn có thể xem verifier từ catalog nếu user có quyền phù hợp:

SELECT rolname, rolpassword
FROM pg_authid
WHERE rolname = 'appuser';

pg_authid chứa thông tin nhạy cảm và chỉ superuser đọc được. Đừng log hoặc chia sẻ output này.

Quyền file userlist.txt

sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txt

User/group thực tế chạy PgBouncer có thể khác tùy package. Kiểm tra bằng:

systemctl cat pgbouncer

trước khi đổi ownership.

Khởi động lại và kiểm tra

sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer

Kiểm tra port:

sudo ss -tulnp | grep 6432

Nếu app và PgBouncer cùng VPS, bind 127.0.0.1 là lựa chọn an toàn đơn giản.

Nếu PgBouncer nằm trên server khác, ưu tiên private network/VPN và firewall allow đúng nguồn.

Kết nối qua PgBouncer

Thay vì:

postgresql://appuser:password@127.0.0.1:5432/appdb

app kết nối:

postgresql://appuser:password@127.0.0.1:6432/appdb

Test:

psql -h 127.0.0.1 -p 6432 -U appuser appdb

Admin console

PgBouncer có database ảo tên pgbouncer.

Kết nối:

psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer

Một số lệnh hữu ích:

SHOW POOLS;
SHOW CLIENTS;
SHOW SERVERS;
SHOW STATS;
SHOW DATABASES;
SHOW CONFIG;

SHOW POOLS giúp xem:

• client active/waiting;
• server active/idle;
• pool nào đang queue.

Cách biết pool size quá nhỏ

Dấu hiệu:

• cl_waiting tăng;
• query latency tăng nhưng PostgreSQL chưa dùng hết CPU;
• nhiều client chờ server connection;
• traffic burst làm queue dài.

Khi đó có thể tăng pool size từng bước và đo lại.

Đừng tăng thẳng lên gần max_connections của PostgreSQL.

Cách biết pool size quá lớn

Dấu hiệu:

• PostgreSQL vẫn có quá nhiều backend;
• RAM per connection tăng;
• context switching nhiều;
• database CPU cao dù client không quá tải.

Mục tiêu là đủ connection để PostgreSQL khai thác CPU/I/O tốt nhưng không bị connection overhead.

Transaction pooling và prepared statements

PgBouncer hiện hỗ trợ protocol-level named prepared statement trong transaction pooling nếu cấu hình max_prepared_statements khác 0.

Tuy nhiên ứng dụng/framework có thể dùng prepared statement theo nhiều cách khác nhau.

Trước khi đổi từ session sang transaction pooling:

1. đọc driver docs;
2. test staging;
3. kiểm tra migration tool;
4. kiểm tra session variable;
5. chạy integration test.

Không đổi trực tiếp production chỉ vì transaction pooling “nhanh hơn”.

Transaction pooling và SET

Session-based SET không hoạt động như kỳ vọng vì transaction kế tiếp có thể dùng server connection khác.

Nếu ứng dụng phụ thuộc:

SET search_path ...;
SET ROLE ...;

trong session, cần test kỹ.

Một số startup parameter được PgBouncer track, nhưng không phải mọi session state đều tương thích.

PgBouncer có tăng tốc query không?

Không trực tiếp.

Nếu query mất 5 giây vì thiếu index, PgBouncer không biến nó thành 50 ms.

PgBouncer giúp:

• giảm thời gian mở connection;
• giảm số backend PostgreSQL;
• hấp thụ concurrency tốt hơn;
• queue client khi pool bận.

Query tuning vẫn cần pg_stat_statements và EXPLAIN ANALYZE.

Kết hợp với application pool

Nhiều framework đã có pool nội bộ.

Ví dụ:

• 10 app instance;
• mỗi instance pool 20 connection;
• tổng có thể là 200 client connection tới PgBouncer.

PgBouncer có thể pool xuống 30–50 server connection phía PostgreSQL tùy workload.

Không nên đặt app pool quá lớn chỉ vì phía trước có PgBouncer. Queue ở application đôi khi tốt hơn tạo hàng nghìn client connection.

Reload cấu hình không downtime

PgBouncer admin console hỗ trợ:

RELOAD;

Một số config có thể reload online.

Sau khi sửa:

sudo -u postgres psql -h 127.0.0.1 -p 6432 pgbouncer

rồi:

RELOAD;

Hoặc dùng signal/service theo package và tài liệu phiên bản.

Security

• không public 6432 ra Internet nếu không cần;
• dùng SCRAM khi phù hợp;
• firewall đúng nguồn;
• bảo vệ userlist.txt;
• không dùng superuser cho application;
• log connection nhưng tránh lộ password;
• update PgBouncer định kỳ.

Nếu quản trị database từ xa, nên đi qua private network hoặc bastion thay vì mở PostgreSQL/PgBouncer cho toàn Internet.

Monitoring PgBouncer

Theo dõi:

• client active/waiting;
• server active/idle;
• transaction rate;
• query rate;
• average wait time;
• pool saturation.

Có exporter cho Prometheus trong ecosystem, hoặc bạn có thể thu SHOW STATS theo cách phù hợp.

Nếu đã dùng Grafana, xem bài Giám sát VPS bằng Prometheus và Grafana.

Sai lầm thường gặp

Chọn transaction pooling mà app dùng session state

Ứng dụng có thể lỗi ngẫu nhiên rất khó debug.

Đặt default_pool_size quá cao

Bạn gần như mất lợi ích pooling.

Public PgBouncer ra Internet

Pooler không nên thay firewall/private network.

Dùng PgBouncer để chữa slow query

Nó không thay index và query optimization.

Không theo dõi client waiting

Pool quá nhỏ có thể biến PgBouncer thành bottleneck mới.

Checklist

• Xác định lý do cần pooling.
• Chọn pool mode phù hợp.
• App tương thích transaction mode nếu dùng.
• max_client_conn hợp lý.
• default_pool_size dựa trên benchmark.
• Authentication an toàn.
• Port 6432 không public dư thừa.
• Test SHOW POOLS và SHOW STATS.
• Theo dõi queue/wait time.
• Query tuning vẫn được thực hiện riêng.

FAQ

PgBouncer có thay PostgreSQL connection pool trong app không?

Không nhất thiết. Hai lớp có thể cùng tồn tại nhưng phải sizing hợp lý để tránh quá nhiều client connection.

Transaction pooling có an toàn cho mọi ứng dụng không?

Không. Nó phá một số session-based PostgreSQL feature. Cần test driver/framework và workload thật.

PgBouncer dùng port nào?

Ví dụ phổ biến là 6432, nhưng bạn có thể đổi trong cấu hình.

Có nên đặt PgBouncer trên cùng VPS với PostgreSQL?

Có thể, đặc biệt với mô hình nhỏ. Với kiến trúc lớn hơn, có thể tách theo HA/network design.

PgBouncer có giúp khi database CPU 100% không?

Có thể giảm connection overhead, nhưng nếu CPU cao do query nặng thì cần tối ưu query trước.

Kết luận

PgBouncer là một lớp nhỏ nhưng rất hữu ích giữa ứng dụng và PostgreSQL khi connection concurrency tăng. Giá trị lớn nhất của nó là biến hàng trăm client connection thành một pool server connection có kiểm soát.

Hãy bắt đầu với session pooling nếu cần tương thích cao, sau đó chỉ chuyển sang transaction pooling khi đã kiểm thử rõ ràng. Theo dõi SHOW POOLS, queue time và PostgreSQL backend count để sizing dựa trên dữ liệu thật.

Nếu PostgreSQL workload của bạn tăng ổn định sau khi đã tối ưu query và pooling, có thể tham khảo các gói VPS tại HUONG CLOUD và chọn CPU/RAM theo metric thực tế.

Nguồn tham khảo

• PgBouncer — Configuration: https://www.pgbouncer.org/config
• PgBouncer — Usage: https://www.pgbouncer.org/usage
• PgBouncer — Features and pooling modes: https://www.pgbouncer.org/features.html