Đã đăng vào Saturday, 1:32 a.m. 8 phút đọc

PostgreSQL Bài 36: Kiến trúc Connection Pooling, vấn đề Max Connections và giải pháp với PgBouncer

Khi hệ thống backend bắt đầu mở rộng (scale-out) với hàng chục container Microservices chạy song song, một trong những điểm nghẽn đầu tiên đánh sập PostgreSQL không phải là CPU hay ổ cứng, mà là số lượng kết nối (Connections).

Khác với các hệ thống Web Server có thể xử lý hàng chục ngàn kết nối đồng thời dễ dàng, PostgreSQL có một kiến trúc cốt lõi khiến nó cực kỳ nhạy cảm với bài toán kết nối mạng. Bài học này sẽ giải phẫu nguyên nhân gốc rễ của lỗi FATAL: sorry, too many clients already và cách giải quyết triệt để bằng PgBouncer.

1. Kiến trúc Process-per-Connection và Bi kịch Max Connections

Hầu hết các RDBMS hiện đại (như MySQL, SQL Server) và các ứng dụng backend (Node.js, Go, Java) đều sử dụng mô hình Thread-per-Connection (một luồng xử lý một kết nối) hoặc mô hình Event-driven (chạy bất đồng bộ). Các luồng (threads) dùng chung không gian bộ nhớ nên rất nhẹ.

Tuy nhiên, PostgreSQL được thiết kế theo kiến trúc Process-per-Connection:

Mỗi khi có một Client kết nối thành công, máy chủ PostgreSQL sẽ thực hiện lệnh fork() ở tầng Hệ điều hành (Linux) để nhân bản ra một tiến trình con hoàn toàn độc lập (backend process).
Mỗi tiến trình con này tiêu tốn từ 2 MB đến 10 MB RAM bộ nhớ cấp phát riêng (work_mem, maintenance_work_mem), chưa kể chi phí khởi tạo kết nối (TCP handshake, xác thực).

Hậu quả khi số lượng kết nối tăng vọt:

Nếu bạn cấu hình tham số max_connections = 2000 trong postgresql.conf:

Cạn kiệt RAM: 2.000 kết nối $\times$ 5 MB/kết nối = 10 GB RAM bị bốc hơi chỉ để duy trì các tiến trình kết nối (ngay cả khi chúng đang ở trạng thái nhàn rỗi idle).
Nghẽn Context Switching: Hệ điều hành phải liên tục chuyển đổi ngữ cảnh (Context Switch) giữa hàng ngàn tiến trình, khiến CPU dành phần lớn thời gian để quản lý hệ thống thay vì thực thi câu lệnh SQL. Tốc độ truy vấn tụt dốc thảm hại.

Quy tắc vàng: Một máy chủ PostgreSQL chỉ hoạt động tối ưu nhất khi số lượng max_connections thực tế hoạt động cùng lúc xấp xỉ công thức: $\text{Số nhân CPU} \times 2 + \text{Số trục ổ đĩa}$ (Thường chỉ từ 100 đến 300 kết nối).

2. Connection Pooling là gì?

Thay vì mỗi request từ người dùng lại mở một kết nối mới tới Database rồi đóng lại, Connection Pooling (Hồ bơi kết nối) duy trì một nhóm các kết nối luôn mở sẵn.

Khi ứng dụng cần query, nó "mượn" một kết nối từ Pool, chạy xong thì "trả" lại vào Pool cho luồng khác dùng.

Có 2 vị trí để đặt Connection Pool:

Client-side Pooling (Tại ứng dụng): Sử dụng các thư viện như HikariCP (Java), database/sql (Go), Prisma, GORM. Nhược điểm: Nếu bạn chạy 20 pod (container) backend, mỗi pod giữ pool 20 kết nối, tổng cộng Database vẫn phải chịu tải $20 \times 20 = 400$ kết nối vật lý.
Server-side Pooling (Tại Database Middleware): Đặt một phần mềm Proxy đứng chắn giữa Application và Database. Ứng dụng cứ thoải mái mở hàng ngàn kết nối đến Proxy, Proxy sẽ nén (multiplex) chúng lại thành một số lượng kết nối vật lý cực nhỏ và cố định đến PostgreSQL.

Đại diện xuất sắc nhất cho mô hình Server-side chính là PgBouncer.

3. Kiến trúc và Sức mạnh của PgBouncer

PgBouncer là một phần mềm mã nguồn mở cực kỳ nhẹ (chỉ tốn vài MB RAM), đóng vai trò làm Router/Proxy kết nối chuyên dụng cho PostgreSQL.

[ BACKEND APPS ] (10,000 Kết nối logic)
       │
       ▼
[ PGBOUNCER ] (Connection Pooler)
       │
       ▼ (Chỉ duy trì 100 Kết nối vật lý)
[ POSTGRESQL ]

Lợi ích kép của PgBouncer:

Backend app không bao giờ gặp lỗi too many clients. Nếu số lượng request vượt ngưỡng, PgBouncer sẽ xếp hàng (Queue) các request đó trên RAM của nó một cách gọn nhẹ.
PostgreSQL chạy cực êm ái, chỉ phải phục vụ đúng 100 tiến trình, giữ cho CPU và bộ nhớ cache hoạt động ở mức hiệu suất cao nhất.

4. Ba chế độ Pool (Pooling Modes) của PgBouncer

Đây là phần cấu hình quan trọng nhất. PgBouncer điều phối kết nối dựa trên 3 chế độ (Modes), quyết định thời điểm nó đòi lại kết nối từ Client để chia sẻ cho người khác:

Pooling Mode	Thời điểm mượn kết nối	Thời điểm trả kết nối vào Pool	Mức độ chia sẻ (Concurrency)	Phù hợp cho
Session (Mặc định)	Khi Client bắt đầu kết nối	Khi Client đóng kết nối (Disconnect)	Thấp nhất	Ứng dụng legacy, công cụ phân tích dữ liệu dài hạn.
Transaction	Khi Client gửi lệnh `BEGIN` hoặc câu query đầu tiên	Ngay khi Client gọi `COMMIT` hoặc `ROLLBACK`	Cao nhất (Tối ưu)	Microservices, Web Apps, REST/GraphQL APIs.
Statement	Khi Client gửi 1 câu SQL bất kỳ	Ngay khi câu SQL đó chạy xong	Cực đoan	Auto-commit queries, không hỗ trợ Transaction block.

Thực chiến Production: Bạn gần như luôn luôn nên chọn Transaction pooling. Trong kiến trúc Web/API, một request HTTP thường kéo dài 200ms, nhưng thời gian thực thi các câu lệnh SQL bên trong transaction chỉ tốn 5ms. Ở chế độ Transaction, Client chỉ chiếm dụng kết nối vật lý đúng 5ms đó, 195ms còn lại kết nối vật lý được nhường cho hàng chục request khác!

5. Cấu hình PgBouncer cơ bản (`pgbouncer.ini`)

Dưới đây là file cấu hình tiêu chuẩn trên môi trường Linux/Docker:

Ini, TOML

[databases]
# Định tuyến kết nối: Ứng dụng gọi db 'my_db' sẽ được trỏ tới port 5432 của localhost
my_db = host=127.0.0.1 port=5432 dbname=my_db

[pgbouncer]
# Cổng mà PgBouncer lắng nghe (Application sẽ kết nối vào cổng này thay vì 5432)
listen_port = 6432
listen_addr = *

# CHẾ ĐỘ QUAN TRỌNG NHẤT
pool_mode = transaction

# Số lượng kết nối VẬT LÝ tối đa mở xuống PostgreSQL cho mỗi database
default_pool_size = 100

# Số lượng kết nối LOGIC tối đa từ Ứng dụng backend tới PgBouncer
max_client_conn = 10000

# Cấu hình xác thực (Đọc password từ file phân quyền riêng)
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

6. Cái giá phải trả: Những tính năng bị vô hiệu hóa khi dùng Transaction Pooling

Sử dụng pool_mode = transaction mang lại sức mạnh đồng thời khổng lồ, nhưng nó phá vỡ khái niệm Phiên làm việc (Session state).

Bởi vì hai câu lệnh truy vấn liên tiếp của cùng một Client có thể được PgBouncer đẩy xuống hai tiến trình kết nối vật lý hoàn toàn khác nhau của PostgreSQL. Do đó, bạn không thể sử dụng bất kỳ tính năng nào lưu trạng thái ở cấp độ Session:

Prepared Statements (Lệnh chuẩn bị trước): Mặc định, driver database thường dùng câu lệnh PREPARE (lưu template SQL trên session). PgBouncer sẽ làm hỏng tính năng này (gây lỗi prepared statement does not exist). Cách khắc phục: Vô hiệu hóa tính năng Prepared Statement ở driver ORM/Backend, hoặc kích hoạt cờ server_reset_query = DISCARD ALL trong PgBouncer.
Session Advisory Locks: Lệnh pg_advisory_lock() (đã học ở Bài 31) gắn liền với Session. Nếu dùng Transaction pool, khóa sẽ rớt sang một connection lạ và không thể gỡ ra. Cách khắc phục: Phải đổi sang dùng Transaction-level Advisory Locks (pg_advisory_xact_lock()).
Các biến SET cục bộ: Nếu bạn chạy lệnh SET timezone = 'UTC';, nó chỉ có tác dụng trên 1 kết nối vật lý đó. Request tiếp theo của Client có thể văng sang kết nối khác mang timezone mặc định.

7. Tóm tắt & Bài tiếp theo

Cố gắng tăng max_connections trong PostgreSQL lên hàng ngàn là một sai lầm kiến trúc, gây hao tổn RAM và làm nghẽn CPU do Context Switching.
PgBouncer là giải pháp Proxy Middleware đứng giữa, nhận hàng chục ngàn kết nối từ Backend và gom lại thành một số lượng kết nối nhỏ, ổn định xuống Database.
Chế độ Transaction Pooling là vũ khí tối thượng cho Microservices, tối đa hóa tỷ lệ tái sử dụng kết nối vật lý ngay khi một giao dịch COMMIT.
Cần chú ý tinh chỉnh code Backend (bỏ Session Lock, tắt Prepared Statements) để tương thích với chế độ Transaction của PgBouncer.

Bài 37 xem tiếp: Kiến trúc phân mảnh dữ liệu: Table Partitioning (Phân vùng) và Sharding — chúng ta sẽ giải quyết bài toán dung lượng khi một bảng vượt quá 100 triệu dòng hoặc 500GB. Cách chia cắt một bảng vật lý khổng lồ thành các bảng con (Partitions) theo thời gian (Range) hoặc danh mục (List) mà không làm thay đổi câu lệnh SQL của ứng dụng.

PostgreSQL