Tổng quan Pattern Production

Trong các dự án backend thực tế, hiệu năng PostgreSQL hiếm khi chỉ phụ thuộc vào một truy vấn chậm duy nhất. Mà nó là sự kết hợp giữa cách ứng dụng kết nối, cách truy vấn, và cách schema phát triển theo thời gian. Bài học này đề cập bốn pattern production có ảnh hưởng trực tiếp đến hiệu năng end-to-end: connection pooling với PgBouncer, chiến lược phân trang cho các tập kết quả lớn, quy trình schema migration sử dụng các công cụ như sqitch hoặc Flyway, và một phương pháp tối ưu hiệu năng có hệ thống bắt đầu từ truy vấn và đi sâu xuống cấu hình database.

Mục tiêu là chuyển từ "chạy được trên máy tôi" sang hành vi có thể dự đoán được dưới tải thực tế.

Connection Pooling với PgBouncer

Mỗi kết nối đến PostgreSQL sẽ fork một backend process, tiêu tốn memory và CPU. Một backend có hàng trăm request đồng thời sẽ nhanh chóng cạn kiệt connection limit. PgBouncer nằm giữa ứng dụng và PostgreSQL, ghép nhiều kết nối từ ứng dụng vào một pool kết nối database thực nhỏ hơn.

Chế độ production phổ biến nhất là transaction pooling, trong đó kết nối chỉ được gán cho client trong suốt thời gian diễn ra một transaction:

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Trong ứng dụng, bạn trỏ database host đến port 6432 thay vì 5432. Một lưu ý quan trọng với transaction pooling: prepared statements, các lệnh `SET`, và các tính năng ở cấp session như `LISTEN/NOTIFY` không hoạt động đáng tin cậy vì kết nối bên dưới có thể thay đổi giữa các transaction. Chỉ sử dụng server-side prepared statements khi chạy ở session pooling mode.

Chiến lược Phân trang

Phân trang bằng offset với `LIMIT ... OFFSET ...` tiện lợi nhưng hoạt động kém trên các bảng lớn vì PostgreSQL vẫn phải quét và bỏ qua tất cả các row được nhảy qua. Trên một bảng có hàng triệu row, trang 1000 sẽ chậm hơn đáng kể so với trang 1.

Phân trang keyset (còn gọi là phân trang con trỏ) tránh hoàn toàn việc dùng offset bằng cách sử dụng giá trị cuối cùng đã thấy từ một cột có đánh chỉ mục:

-- Bad: offset pagination
SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

-- Good: keyset pagination
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Câu truy vấn thứ hai dùng chỉ mục kết hợp trên `(created_at, id)` và có thể nhảy trực tiếp đến đúng vị trí. Nó cũng trả về kết quả ổn định ngay cả khi có hàng mới được chèn vào giữa các lần tải trang, điều này rất cần thiết cho feed và cuộn vô hạn.

Schema Migrations

Trong môi trường production, các thay đổi schema phải có khả năng lặp lại, có thể review và đảo ngược được. Các công cụ như Flyway, Liquibase hoặc sqitch lưu trữ migration dưới dạng các file SQL có phiên bản, chạy theo thứ tự, với một bảng theo dõi ghi lại các migration đã được áp dụng.

Một file migration điển hình có dạng như sau:

-- V002__add_user_email_index.sql
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Có ba nguyên tắc quan trọng nhất. Thứ nhất, luôn dùng `CREATE INDEX CONCURRENTLY` trên các bảng đang tồn tại để không khóa các thao tác ghi trong quá trình tạo chỉ mục. Thứ hai, không bao giờ trộn lẫn các thay đổi phá hủy (xóa cột) với tính năng mới trong cùng một migration, để có thể rollback. Thứ ba, hãy test migration trên một bản sao dữ liệu production, vì số liệu thống kê planner của PostgreSQL có thể phát hiện các vấn đề mà cơ sở dữ liệu dev nhỏ không bao giờ thể hiện.

End-to-End Performance Tuning

Tuning tuân theo một cách tiếp cận theo từng lớp. Thứ nhất, xác định câu truy vấn chậm bằng `pg_stat_statements` hoặc logging ở cấp ứng dụng. Thứ hai, chạy `EXPLAIN (ANALYZE, BUFFERS)` để xem execution plan thực tế, số lượng hàng và buffer hit so với disk read. Thứ ba, khắc phục nguyên nhân gốc rễ, thường là thiếu chỉ mục, thứ tự join sai, hoặc truy vấn lấy nhiều dữ liệu hơn mức cần thiết.

Chỉ sau khi tầng truy vấn đã sạch thì bạn mới nên động đến các tham số database. Các cải thiện phổ biến bao gồm tăng `shared_buffers` lên khoảng 25 phần trăm RAM, đặt `effective_cache_size` vào khoảng 75 phần trăm, và tinh chỉnh `work_mem` cho mỗi truy vấn nếu các phép sort hoặc hash lớn đang tràn ra disk. Nhưng hãy nhớ: cấu hình không thể cứu một câu truy vấn scan toàn bộ bảng trên mỗi request.

-- Finding the top 10 slowest queries by total execution time
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Tổng kết

Hiệu năng PostgreSQL trong production là một thuộc tính của hệ thống, không phải một nút vặn đơn lẻ. Hãy pool connection bằng PgBouncer để xử lý concurrency mà không làm cạn kiệt database, thay offset pagination bằng keyset pagination trên các bảng lớn, quản lý thay đổi schema thông qua các công cụ migration có phiên bản, và tuân theo quy trình tuning ưu tiên truy vấn trước khi động đến cấu hình server. Cùng nhau, các pattern này mang lại cho bạn latency dự đoán được và một lộ trình rõ ràng khi tải tăng lên.