PgBouncer – Giải pháp Pooling kết nối cơ sở dữ liệu PostgreSQL hiệu quả và mở rộng

PgBouncer – Giải pháp Pooling kết nối cơ sở dữ liệu PostgreSQL hiệu quả và mở rộng

PgBouncer là một công cụ quản lý pooling kết nối cơ sở dữ liệu, đóng vai trò trung gian giữa ứng dụng và PostgreSQL. Nó giúp gom nhiều kết nối từ ứng dụng vào một vài kết nối thực sự đến database, giúp giảm tải, tiết kiệm bộ nhớ và nâng cao hiệu năng trong các hệ thống lớn.

Tại sao cơ sở dữ liệu PostgreSQL bị hết kết nối?

PostgreSQL xử lý mỗi kết nối với một process riêng trên hệ điều hành. Ví dụ, lúc có 100 kết nối thì máy chủ chạy 100 process, lúc 500 kết nối, bộ nhớ bị cạn kiệt, dẫn đến tốc độ truy vấn rất chậm.

Điều này đặc biệt nan giải cho các ứng dụng Node.js hoặc các kiến trúc serverless, nơi mỗi instance hoặc function có xu hướng yêu cầu một pool kết nối riêng biệt.

PgBouncer vận hành như thế nào?

PgBouncer đứng giữa ứng dụng và PostgreSQL, gom nhiều kết nối ứng dụng (ví dụ 500 kết nối) chia sẻ số lượng nhỏ kết nối thực với cơ sở dữ liệu (ví dụ giới hạn 20 kết nối).

Ví dụ mô hình:

500 kết nối ứng dụng → PgBouncer → 20 kết nối PostgreSQL

Các chế độ pooling của PgBouncer

• Session Mode
  Mỗi kết nối ứng dụng tương ứng 1 kết nối database riêng. Tương tự như không dùng pooling. Chỉ dùng cho mục đích tương thích, không mang lại lợi ích tối ưu.

• Transaction Mode
  Kết nối database được trả lại pool ngay sau mỗi transaction. Đây là mode được khuyến khích sử dụng, giúp nhiều kết nối ứng dụng có thể chia sẻ ít kết nối database thực tế.

  Hạn chế: các câu lệnh prepared statement và biến thiết lập (SET variables) không duy trì qua transaction, vì gắn liền kết nối cụ thể. Một số ORM như Prisma hỗ trợ xử lý hạn chế này.

• Statement Mode
  Kết nối đóng lại sau mỗi câu lệnh thực thi. Chỉ phù hợp với các truy vấn đơn giản, không dùng cho transaction.

Cấu hình PgBouncer cơ bản

Tập tin cấu hình /etc/pgbouncer/pgbouncer.ini có thể như sau:

[databases]
myapp = host=postgres-primary port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000       # Số kết nối tối đa đến PgBouncer từ ứng dụng
default_pool_size = 20       # Số kết nối tối đa đến PostgreSQL chuyên biệt cho mỗi user/db
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5

server_idle_timeout = 600    # Đóng các kết nối database không hoạt động sau 10 phút
client_idle_timeout = 0      # Không đóng kết nối ứng dụng không hoạt động

log_connections = 0
log_disconnections = 0

Tệp userlist.txt chứa thông tin người dùng với mật khẩu mã hóa:

"myapp_user" "scram-sha-256$..."

Triển khai PgBouncer với Docker

Ví dụ file docker-compose.yml kết hợp PostgreSQL và PgBouncer:

services:
  pgbouncer:
    image: bitnami/pgbouncer:latest
    environment:
      POSTGRESQL_HOST: postgres
      POSTGRESQL_PORT: 5432
      POSTGRESQL_DATABASE: myapp
      POSTGRESQL_USERNAME: myapp_user
      POSTGRESQL_PASSWORD: ${DB_PASSWORD}
      PGBOUNCER_POOL_MODE: transaction
      PGBOUNCER_MAX_CLIENT_CONN: 1000
      PGBOUNCER_DEFAULT_POOL_SIZE: 20
    ports:
      - "6432:6432"
    depends_on:
      - postgres

  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: myapp_user
      POSTGRES_PASSWORD: ${DB_PASSWORD}
    volumes:
      - pgdata:/var/lib/postgresql/data

Ứng dụng cần kết nối tới PgBouncer thay vì tới PostgreSQL trực tiếp, ví dụ trong Prisma:

// DATABASE_URL trỏ tới PgBouncer
const DATABASE_URL = process.env.DATABASE_URL;
// Ví dụ: postgresql://myapp_user:password@pgbouncer:6432/myapp

// Với Prisma, vô hiệu hóa prepared statements để tương thích transaction mode
// DATABASE_URL="?pgbouncer=true&connection_limit=1"

Trong file schema Prisma:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_DATABASE_URL") // Bỏ qua PgBouncer khi migrate
}

Giám sát kết nối PgBouncer

Bạn có thể kết nối vào bảng điều khiển admin của PgBouncer:

psql -p 6432 -U pgbouncer pgbouncer
SHOW POOLS;
SHOW CLIENTS;
SHOW CONFIG;

Nếu cl_waiting > 0 nghĩa là có kết nối đang chờ, bạn cần tăng default_pool_size hoặc tối ưu truy vấn.

Các nền tảng quản lý PostgreSQL hỗ trợ pooling

Nhiều nhà cung cấp dịch vụ cloud đã tích hợp trang bị sẵn connection pooling:

• Supabase có endpoint riêng cho pooled connections
• Neon cung cấp pooling serverless
• PlanetScale hỗ trợ pooling cho MySQL

Với các nền tảng quản lý này, bạn nên sử dụng endpoint pooling do họ cung cấp. Còn với hệ thống PostgreSQL tự quản lý, đặc biệt khi có hơn 50 instance ứng dụng, PgBouncer là một công cụ không thể thiếu để đảm bảo hệ thống ổn định và hiệu quả.

---

PgBouncer là một giải pháp đơn giản, nhẹ nhưng rất hiệu quả cho bài toán quản lý kết nối PostgreSQL quy mô lớn, rất thích hợp với các hệ thống backend hiện đại sử dụng Node.js hoặc kiến trúc serverless.

Việc thiết lập PgBouncer không quá phức tạp, đặc biệt khi tích hợp với Docker, giúp các đội phát triển có thể dễ dàng triển khai và tối ưu vận hành database.