Connection pooling và PgBouncer: 400 client, PostgreSQL nhận 100 Trung cấp

Vấn đề

API đặt hàng Node.js chạy 20 pod, mỗi pod có connection pool pg với max: 20. Lúc tải cao, cả cụm cần 400 connection.

PostgreSQL để max_connections = 100. Khi autoscaler thêm pod, log có FATAL: sorry, too many clients already, SQLSTATE 53300 (too_many_connections), dù CPU database còn rảnh.

Tăng max_connections lên 500 có đúng không? Nếu không, pool đặt ở đâu, cỡ bao nhiêu, và phải từ bỏ gì?

Chọn gì? (tóm tắt trước)

Tình huống	Chọn	Điều kiện
Tổng max các pool (theo số pod tối đa) nằm dưới số slot	Chỉ pool trong app	Còn chừa slot cho migration, monitoring
Nhiều pod, client phần lớn thời gian rảnh	PgBouncer transaction mode	App không dựa vào trạng thái session
Cần SET session, LISTEN, session advisory lock	PgBouncer session mode hoặc connection riêng	Chấp nhận mỗi client mở giữ một backend

Khái niệm cốt lõi

Vì sao connection PostgreSQL đắt

PostgreSQL fork một process cho mỗi connection. Process sống đến khi client ngắt, kể cả lúc connection chỉ nằm rảnh trong pool.

Một số tài nguyên, gồm shared memory, được cấp theo max_connections; đổi phải restart.

Theo wiki PostgreSQL, vượt điểm bão hoà, connection hoạt động thêm chỉ tranh CPU, lock, work_mem, nên throughput giảm. 400 client cần ít backend hơn nhiều.

Hai tầng pool

	Pool trong app (pg.Pool)	PgBouncer
Gom được	Request trong một pod	Client từ mọi pod
Backend PostgreSQL	Mỗi connection pool = một backend	Nhiều client dùng chung ít backend
Default	max = 10	default_pool_size = 20 mỗi cặp user/database

Hai tầng dùng cùng nhau: pod giữ pg.Pool nhưng trỏ vào PgBouncer.

Đang tải diagram…

Ba pooling mode

Khác biệt là server connection gắn với client bao lâu:

Mode	Gắn đến khi	Tính năng PostgreSQL
session (mặc định)	Client ngắt connection	Đầy đủ
transaction	Transaction kết thúc	Mất các tính năng gắn session
statement	Câu lệnh kết thúc	Như transaction, cấm transaction nhiều câu

Statement mode hiếm dùng; PgBouncer nêu mục tiêu chính là PL/Proxy. Ở session mode, 400 client cùng mở vẫn cần 400 backend.

Cách hoạt động

3 client dùng chung 2 backend thế nào, và cái gì rò giữa họ?

Transaction mode: server connection chỉ thuộc client trong một transaction, rồi về pool cho client khác

◀ Trước▶ PhátTiếp ▶↺ Từ đầuBước 1/6

Client A mở transaction: PgBouncer gán server S1 cho A. Pool còn S2 rảnh.

Trạng thái sau từng bước:

1. A, B BEGIN: S1 thuộc A, S2 thuộc B; hết server rảnh.
2. C gửi câu lệnh: cl_waiting = 1. PostgreSQL không biết C tồn tại.
3. A COMMIT: S1 về pool; connection A → PgBouncer vẫn mở.
4. C nhận S1, cùng mọi thứ A để lại trên session đó (SET, session advisory lock, PREPARE).
5. Transaction tiếp theo của A nhận server rảnh trước, không bảo đảm là S1.

Ở transaction mode chỉ phạm vi transaction là đáng tin. PgBouncer liệt kê tính năng không dùng được:

Tính năng	Transaction mode	Thay bằng
SET/RESET	Không	SET LOCAL trong transaction
LISTEN	Không	Connection riêng (NOTIFY vẫn được)
Cursor WITH HOLD	Không	Cursor trong transaction, phân trang keyset
PREPARE/DEALLOCATE bằng SQL	Không	Prepared statement mức protocol
Session advisory lock	Không	pg_advisory_xact_lock, xem advisory locks
Temp table PRESERVE/DELETE ROWS	Không	ON COMMIT DROP

Prepared statement mức protocol (query có name trong pg) dùng được ở transaction mode từ PgBouncer 1.21.0 khi max_prepared_statements khác 0; từ 1.24.0 mặc định là 200.

PgBouncer tự đặt lại vài tham số startup cho từng client (application_name, TimeZone, client_encoding…), nhưng mặc định không gồm search_path hay statement_timeout (thêm được qua track_extra_parameters).

Chi tiết vận hành

Tham số PgBouncer:

• pool_mode: mặc định session.
• default_pool_size: server connection tối đa mỗi cặp user/database, mặc định 20.
• max_client_conn: client tối đa, mặc định 100; nâng thì kiểm tra cả giới hạn file descriptor.
• server_idle_timeout: đóng server rảnh quá 600 giây (mặc định).
• max_db_connections: trần mỗi database bất kể user, mặc định 0 (không giới hạn).
• server_reset_query (DISCARD ALL) chỉ chạy ở session mode, trừ khi bật server_reset_query_always.

PostgreSQL 17: superuser_reserved_connections mặc định 3, nên role thường dùng tối đa 97 slot. Chạm ngưỡng đó nhận remaining connection slots are reserved for roles with the SUPERUSER attribute; hết hẳn slot thì sorry, too many clients already. Cả hai đều 53300 (kiểm tra qua log verbose).

Chọn kích thước pool

Không có công thức chung. Wiki PostgreSQL gợi ý số connection đang làm việc quanh (core_count * 2) + effective_spindle_count, kèm cảnh báo: công thức cũ, chưa phân tích với SSD, phải đo trên hệ thật.

Bắt đầu nhỏ, tăng dần và đo latency. Tổng mọi pool (mỗi instance PgBouncer có pool riêng) cộng connection quản trị phải dưới số slot của role thường.

Quan sát bằng admin console (database pgbouncer):

• SHOW POOLS: cl_waiting > 0 kéo dài và maxwait tăng dần nghĩa là server quá tải hoặc pool quá nhỏ. Xem CPU và lock trước khi tăng pool.
• sv_idle luôn cao: pool thừa. SHOW STATS: avg_wait_time (micro giây chờ được gán server).

Ví dụ thực tế

20 pod đặt hàng qua một PgBouncer

20 pod trỏ vào PgBouncer, giữ max: 20. PgBouncer: pool_mode = transaction, max_client_conn = 500, default_pool_size = 40. PostgreSQL giữ max_connections = 100: 40 cho app, phần còn lại dự phòng.

Số 40 là điểm xuất phát, đo lại theo cl_waiting và latency.

Demo: PostgreSQL 17 (max_connections = 20) + PgBouncer 1.25.2, transaction mode, default_pool_size = 5

examples/compose.connection-pooling-pgbouncer.yml chạy PostgreSQL riêng và edoburu/pgbouncer:v1.25.2-p0 (host port 6433). Script pgbench:

# trong container postgres; đã tạo bảng demo_connection_pooling.orders
printf "BEGIN;\nINSERT INTO demo_connection_pooling.orders(total) VALUES (100);\nSELECT pg_sleep(0.05);\nCOMMIT;\n" > /tmp/tx.sql
pgbench -h pgbouncer -U hub -d hub -n -f /tmp/tx.sql -c 50 -j 4 -T 10

1. 50 client, 5 backend (SHOW POOLS và pg_stat_activity giữa lúc chạy):

 database | cl_active | cl_waiting | sv_active | sv_idle | pool_mode
 hub      |         5 |         45 |         5 |       0 | transaction
 usename | application_name | count   -- trên PostgreSQL
 hub     | pgbench          |     5
number of failed transactions: 0 (0.000%)
tps = 94.028315 (without initial connection time)

5 backend, mỗi transaction giữ khoảng 50 ms: trần cỡ 100 tps. Số đo đổi mỗi lần chạy.

2. SET rò sang client khác (hai lần gọi psql riêng):

-- client A
SELECT pg_backend_pid();                 -- 111
SET search_path TO demo_connection_pooling;
SET statement_timeout = '1ms';
-- client B, connection mới
SELECT pg_backend_pid();                 -- 111
SHOW statement_timeout;                  -- 1ms
SELECT pg_sleep(0.01);
ERROR:  canceling statement due to statement timeout

Khi client khác đang giữ S1, câu kế tiếp của A chạy trên backend 110 và thấy search_path mặc định: A mất thiết lập của mình.

3. Vượt max_connections (đã có 5 backend của PgBouncer):

$ pgbench -h localhost ... -c 30      # nối thẳng PostgreSQL
FATAL:  sorry, too many clients already
pgbench: error: could not create connection for client 15
$ pgbench -h pgbouncer ... -c 100     # qua PgBouncer
number of failed transactions: 0 (0.000%)

Dọn: DROP SCHEMA demo_connection_pooling CASCADE, rồi down -v.

Trade-offs

Ưu điểm

• Hàng trăm client dùng chung vài chục backend.

• Hàng chờ nằm ở PgBouncer (cl_waiting) thay vì lỗi 53300.

< slot="cons">

• Transaction mode bỏ tính năng session; lỗi rò trạng thái thường chỉ lộ khi có tải.

• Thêm một hop mạng và một thành phần cần HA.

• Transaction dài hoặc idle in transaction giữ server connection, cả pool phải chờ.

Nhược điểm

Checklist production

1. Cộng số pod tối đa × max của pool, so với slot của role thường.
2. Rà code trước transaction mode: SET (đổi sang SET LOCAL), LISTEN, session advisory lock, PREPARE, WITH HOLD, temp table.
3. Kiểm tra PgBouncer ≥ 1.21 và max_prepared_statements nếu driver dùng named prepared statement.
4. Nâng max_client_conn theo số client thật; đặt default_pool_size/max_db_connections để tổng dưới max_connections trừ dự phòng.
5. Job cần session (advisory lock, LISTEN) đi connection riêng.
6. Đặt idle_in_transaction_session_timeout.
7. Alert khi cl_waiting > 0 kéo dài hoặc maxwait tăng.

Sai lầm thường gặp

Lỗi khó thấy nhất:

SET qua transaction mode: rò sang client khác hoặc mất

✕ Hay gặp

await pool.query("SET statement_timeout = '2s'");
await pool.query('SELECT * FROM orders WHERE id = $1', [id]);

✓ Nên viết

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query("SET LOCAL statement_timeout = '2s'");
  await client.query('SELECT * FROM orders WHERE id = $1', [id]);
  await client.query('COMMIT');
} catch (e) { await client.query('ROLLBACK'); throw e; }
finally { client.release(); }

Vì sao

SET LOCAL hết hiệu lực khi transaction kết thúc, đúng lúc PgBouncer thu server connection về.

Bẫy thứ hai:

Tăng max_connections lên 500 để hết lỗi 53300

Lỗi biến mất, nhưng tới 500 process tranh CPU, lock và work_mem; lúc tải đỉnh mọi query chậm theo. Hãy gom client bằng pooler.

Cách hiểu sai	Hệ quả	Cần làm
Session mode giảm số backend	400 client mở vẫn cần 400 backend	Transaction mode
default_pool_size là trần toàn PgBouncer	Nhiều cặp user/database vượt slot	max_db_connections
cl_waiting cao thì tăng pool	Server bão hoà càng chậm	Xem CPU, lock, query chậm trước

Tự kiểm tra

1. Vì sao client B thấy statement_timeout = 1ms dù chưa từng SET?
2. Demo 50 client chỉ đạt khoảng 94 tps. Đổi gì để tăng, và khi nào không giúp?
3. Hai user cùng database, default_pool_size = 60, max_connections = 100. Chuyện gì xảy ra khi cả hai pool đầy?

Tóm tắt

Tóm tắt

• Mỗi connection PostgreSQL là một process; quá nhiều backend hoạt động làm throughput giảm.
• Pool trong app chỉ gom trong một pod; nhiều pod cần pooler ngoài như PgBouncer.
• Transaction mode gán server theo transaction: SET, LISTEN, session lock, WITH HOLD không dùng được.
• Pool size chọn bằng đo; theo dõi cl_waiting, maxwait.

Tham khảo

• PostgreSQL 17 — Architectural Fundamentals: một process cho mỗi connection.
• PostgreSQL 17 — Connections and Authentication: max_connections, slot dự phòng.
• PgBouncer Features, Configuration, Usage, Changelog: mode, tính năng, default, cột SHOW POOLS, mốc version.
• PostgreSQL Wiki — Number Of Database Connections: vì sao nhiều connection làm chậm.