PgBouncer Transaction Pooling: Giảm connection mà không làm hỏng ứng dụng

PgBouncer transaction pooling cho phép rất nhiều client chia sẻ một tập PostgreSQL connection nhỏ hơn: server connection chỉ gắn với client trong thời gian transaction rồi quay lại pool. Mô hình này giảm connection pressure rõ rệt, nhưng cố ý phá giả định “một client luôn ở cùng một database session”. Muốn dùng an toàn, ứng dụng phải loại session state, đặt transaction boundary rõ và sizing theo từng cặp database-user.

Bài viết đối chiếu tài liệu chính thức về pooling modes và feature compatibility cùng PgBouncer configuration. Pin phiên bản PgBouncer/PostgreSQL và test driver/ORM thực tế vì prepared statement support phụ thuộc protocol và cấu hình.

1. Ba pooling mode không chỉ khác hiệu năng

Transaction pooling phù hợp web/API có transaction ngắn. Nó không phải drop-in replacement cho workload dùng connection như một session dài.

2. Điều gì hỏng khi session không còn cố định?

Sau COMMIT, câu query tiếp theo có thể chạy trên backend connection khác. Vì thế không dựa vào:

• SET/RESET session tùy ý;
• temporary table cần tồn tại qua transaction;
• session-level advisory lock;
• LISTEN gắn với session;
• SQL PREPARE/DEALLOCATE truyền thống;
• cursor WITH HOLD hoặc state extension/session khác.

Dùng SET LOCAL trong transaction khi cần setting theo request; state tự kết thúc cùng transaction. Với tính năng bắt buộc session affinity, tạo endpoint/pool riêng ở session mode thay vì hy vọng transaction mode “thường vẫn chạy”.

3. Prepared statements cần phân biệt protocol và SQL

SQL-level PREPARE phụ thuộc session và không tương thích transaction pooling. Protocol-level named prepared statements có thể được PgBouncer hỗ trợ khi cấu hình max_prepared_statements khác 0 ở phiên bản phù hợp. Điều đó không đồng nghĩa mọi driver/ORM tự động an toàn.

Kiểm tra driver dùng unnamed hay named statement, cách deallocate, tên statement có xung đột không và behavior sau reconnect. Load test đúng query mix; nếu không chắc, tắt prepared statement phía client hoặc dùng mode tương thích rồi đo lại latency.

4. Transaction boundary là contract bắt buộc

Connection chỉ được trả về pool khi transaction kết thúc. Một request mở transaction rồi gọi HTTP chậm sẽ giữ PostgreSQL connection suốt thời gian chờ và làm mất lợi ích pooling.

• Không mở transaction trước I/O ngoài database.
• Commit/rollback trong finally.
• Đặt statement_timeout, lock_timeout và idle-in-transaction timeout phù hợp.
• Quan sát transaction age và query chờ lock.
• Không dùng autocommit mơ hồ cho chuỗi thao tác cần atomicity.

5. Sizing pool theo database và user

default_pool_size áp dụng cho mỗi pool, thường được phân tách theo database-user. Đặt 50 không có nghĩa toàn PgBouncer chỉ mở 50 PostgreSQL connections. Ước lượng tổng worst case từ số database, user, pool override, reserve pool và số instance PgBouncer.

db_connection_budget
  >= pgbouncer_instances
     × active_database_user_pools
     × effective_pool_size
     + admin_and_migration_headroom

Ngân sách phải thấp hơn max_connections sau khi chừa replication, monitoring, migration và kết nối khẩn cấp. Pool quá lớn chỉ dời queue từ PgBouncer vào PostgreSQL, nơi mỗi backend process tốn tài nguyên hơn.

6. max_client_conn không phải năng lực database

max_client_conn là số client PgBouncer chấp nhận, không phải số query có thể chạy đồng thời. Khi tăng, phải kiểm tra file descriptor của OS; tài liệu PgBouncer lưu ý FD tiềm năng còn gồm cả client và server connection.

Queue ở pooler là bình thường khi burst ngắn, nhưng chờ dài cần timeout rõ. query_wait_timeout ngăn request nằm trong queue vô hạn. App timeout phải dài/ngắn có chủ đích so với pool timeout để tránh request bỏ đi nhưng công việc vẫn chạy.

7. Cấu hình khởi đầu có chủ đích

[databases]
app = host=db.internal port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 40
min_pool_size = 5
reserve_pool_size = 10
reserve_pool_timeout = 3
query_wait_timeout = 10
server_idle_timeout = 600
server_lifetime = 3600
max_prepared_statements = 200
auth_type = scram-sha-256

Đây là ví dụ minh họa, không phải giá trị copy-paste. Support và tên setting thay đổi theo version. server_reset_query mặc định không chạy trong transaction mode vì client phải tự tránh session feature; bật reset cưỡng bức không biến app phụ thuộc session thành app tương thích.

8. Authentication, TLS và DNS/failover

• Dùng SCRAM và bảo vệ auth file/query; không để plaintext credential trong image.
• TLS cho client→PgBouncer và PgBouncer→PostgreSQL theo trust policy.
• Giới hạn admin console theo network và account riêng.
• Kiểm thử DNS change, failover, certificate rotation và password rotation.
• Không giả định PgBouncer tự quyết định primary đúng nếu discovery phía dưới sai.

Connection hiện hữu có thể tiếp tục trỏ endpoint cũ cho đến khi recycle. DNS TTL, server_lifetime và cơ chế failover phải được thiết kế cùng nhau.

9. Quan sát PgBouncer như một hàng đợi

Các lệnh admin SHOW POOLS, SHOW STATS, SHOW CLIENTS và SHOW SERVERS giúp thấy client active/waiting, server active/idle, transaction rate, query time và wait time.

Không auto-scale pool chỉ từ waiting clients: nguyên nhân có thể là lock, query regression hoặc dependency giữ transaction. Tăng connection khi database đã bão hòa thường làm sự cố nặng hơn.

10. Migration không big bang

1. Inventory SET, temp table, advisory lock, LISTEN, cursor và prepared statement.
2. Thêm test chạy qua PgBouncer transaction mode trong CI/staging.
3. Canary một service instance hoặc traffic slice.
4. So sánh error, pool wait, DB connection, latency và lock time.
5. Tách workload cần session mode sang endpoint riêng.
6. Mở rộng dần và giữ direct/session route cho migration/admin có kiểm soát.

Connection string nên cho biết rõ endpoint pooled hay direct. Không để migration tool vô tình chạy DDL dài qua pool được tối ưu cho request ngắn nếu behavior chưa được kiểm chứng.

Checklist production

• Đã inventory và loại/tách mọi session-dependent feature.
• Transaction không ôm network I/O ngoài database.
• Prepared statement behavior được test với driver/ORM thực.
• Tổng pool budget tính theo database × user × instance.
• Có headroom cho replication, migration, monitoring và admin.
• OS file descriptor phù hợp max_client_conn.
• Timeout app, PgBouncer và PostgreSQL được phối hợp.
• SCRAM/TLS, secret rotation và admin ACL đã cấu hình.
• Dashboard có waiting client, wait time, pool utilization và DB saturation.
• Failover, rolling restart và rollback sang session/direct đã được test.

Kết luận

Transaction pooling hiệu quả khi application coi connection là tài nguyên tạm thời của transaction, không phải nơi lưu state giữa các câu query. Rà đúng incompatibility, đóng transaction sớm, sizing toàn cục và quan sát queue giúp PgBouncer bảo vệ PostgreSQL trước connection storm mà không tạo lỗi ngẫu nhiên khó tái hiện.