Cách tối ưu hiệu năng database PostgreSQL cho ứng dụng lớn

Tối ưu hiệu năng PostgreSQL cho ứng dụng lớn nên đi theo thứ tự: đo để tìm những truy vấn tốn thời gian nhất, đọc kế hoạch thực thi để hiểu vì sao chúng chậm, sửa bằng chỉ mục hoặc viết lại truy vấn, rồi mới tinh chỉnh cấu hình máy chủ, quản lý kết nối và cấu trúc bảng. Phần lớn cải thiện lớn đến từ truy vấn và chỉ mục, không phải từ thay đổi tham số cấu hình. Bài hướng dẫn này dành cho lập trình viên backend và người vận hành database đang gặp vấn đề tốc độ khi dữ liệu lớn dần.

Bước 1: Tìm truy vấn thực sự tốn kém

Bật extension pg_stat_statements (cần thêm vào shared_preload_libraries và khởi động lại). Extension này gom thống kê theo từng mẫu truy vấn: số lần gọi, tổng thời gian, thời gian trung bình, số dòng trả về. Sắp xếp theo tổng thời gian thực thi để thấy truy vấn nào chiếm nhiều tài nguyên nhất. Thường một truy vấn chạy nhanh nhưng gọi hàng triệu lần quan trọng hơn một báo cáo chậm chạy mỗi ngày một lần.

Bổ sung:

• Đặt log_min_duration_statement để ghi log truy vấn chạy quá một ngưỡng thời gian.
• Extension auto_explain tự ghi kế hoạch thực thi của truy vấn chậm.
• Theo dõi pg_stat_activity để thấy truy vấn đang chạy, đang chờ khóa.

Bước 2: Đọc EXPLAIN ANALYZE

Chạy EXPLAIN (ANALYZE, BUFFERS) với truy vấn cần tối ưu (lưu ý ANALYZE thực sự chạy truy vấn, cẩn thận với lệnh ghi). Những điểm cần chú ý:

• Seq Scan trên bảng lớn khi chỉ cần ít dòng: thường thiếu chỉ mục phù hợp.
• Ước lượng số dòng lệch xa thực tế (rows dự kiến so với actual rows): thống kê cũ hoặc điều kiện phức tạp; chạy ANALYZE bảng, cân nhắc tăng statistics target hoặc tạo thống kê mở rộng cho các cột tương quan.
• Nested Loop với nhiều vòng lặp trên bảng lớn: có thể do ước lượng sai.
• Sort hoặc Hash ghi ra đĩa: work_mem quá nhỏ cho thao tác đó.
• Buffers read lớn: đọc nhiều từ đĩa, dữ liệu không nằm trong bộ nhớ đệm.

Bước 3: Chọn chỉ mục đúng

Một số kỹ thuật hiệu quả:

• Chỉ mục nhiều cột: đặt cột lọc bằng trước, cột lọc khoảng hoặc sắp xếp sau. Chỉ mục (customer_id, created_at) phục vụ tốt "đơn hàng của khách X sắp theo thời gian".
• Chỉ mục một phần: chỉ đánh chỉ mục tập con hay truy vấn, ví dụ đơn chưa hoàn tất.
• Chỉ mục biểu thức: như lower(email) để tìm không phân biệt hoa thường.
• Covering index với INCLUDE: cho phép Index Only Scan, không cần đọc bảng.
• Tạo chỉ mục trên bảng đang chạy bằng CREATE INDEX CONCURRENTLY để không khóa ghi.

Đừng tạo chỉ mục tràn lan: mỗi chỉ mục làm chậm thao tác ghi và tốn dung lượng. Định kỳ kiểm tra pg_stat_user_indexes để xóa chỉ mục không được dùng.

Bước 4: Sửa truy vấn và cách ứng dụng dùng database

• N+1 query từ ORM: nạp danh sách rồi truy vấn từng phần tử; dùng join hoặc nạp theo lô.
• SELECT * lấy cột lớn không cần thiết, nhất là cột JSONB, text dài.
• Phân trang bằng OFFSET lớn: chậm dần khi trang càng xa; dùng phân trang theo khóa (keyset) với điều kiện "lớn hơn giá trị cuối cùng".
• Hàm trên cột trong WHERE làm mất chỉ mục, ví dụ lọc theo date(created_at); thay bằng điều kiện khoảng.
• Transaction kéo dài: giữ khóa và cản VACUUM dọn dữ liệu cũ.

Thiết kế API phân trang và lọc hợp lý từ đầu giúp tránh nhiều truy vấn tốn kém; xem bài xây dựng hệ thống API RESTful chuẩn.

Bước 5: VACUUM và hiện tượng phình bảng

PostgreSQL dùng MVCC: cập nhật và xóa không ghi đè ngay mà tạo phiên bản mới, phiên bản cũ thành dòng chết. VACUUM dọn các dòng chết để tái sử dụng chỗ, autovacuum làm việc này tự động. Với bảng cập nhật nhiều:

• Theo dõi số dòng chết và lần autovacuum gần nhất qua pg_stat_user_tables.
• Điều chỉnh tham số autovacuum riêng cho bảng lớn, nóng để chạy thường xuyên hơn.
• Tránh transaction mở quá lâu ngăn VACUUM dọn dẹp.
• Theo dõi tuổi transaction ID để phòng tránh tình trạng wraparound.

Bước 6: Cấu hình bộ nhớ và máy chủ

Chỉ tinh chỉnh khi đã xử lý truy vấn. Các tham số thường được xem xét (giá trị cụ thể tùy RAM, tải và nên thử nghiệm):

• shared_buffers: bộ đệm dùng chung; điểm khởi đầu phổ biến là khoảng một phần tư RAM trên máy chủ chuyên dụng.
• effective_cache_size: ước lượng bộ nhớ đệm của hệ điều hành, giúp bộ lập kế hoạch chọn chỉ mục.
• work_mem: bộ nhớ cho mỗi thao tác sắp xếp, hash; nhân với số kết nối và số thao tác nên không đặt quá cao.
• maintenance_work_mem: cho VACUUM, tạo chỉ mục.
• random_page_cost: giảm trên ổ SSD để bộ lập kế hoạch ưu tiên chỉ mục hợp lý hơn.

Các công cụ gợi ý cấu hình theo phần cứng có thể làm điểm xuất phát, sau đó điều chỉnh theo đo lường thực tế.

Bước 7: Quản lý kết nối

Mỗi kết nối PostgreSQL là một tiến trình riêng, tốn bộ nhớ. Ứng dụng có nhiều instance, mỗi instance mở hàng chục kết nối, sẽ nhanh chóng làm quá tải máy chủ. Dùng connection pooler như PgBouncer ở chế độ transaction pooling để nhiều kết nối ứng dụng dùng chung ít kết nối database. Lưu ý một số tính năng phiên (prepared statement kiểu cũ, biến phiên) cần cấu hình phù hợp với chế độ này.

Bước 8: Mở rộng khi dữ liệu rất lớn

• Phân vùng bảng (declarative partitioning) theo thời gian hoặc theo khóa cho bảng hàng trăm triệu dòng; truy vấn chỉ quét phân vùng liên quan, xóa dữ liệu cũ bằng cách gỡ phân vùng.
• Read replica cho báo cáo và truy vấn đọc nặng, chấp nhận độ trễ đồng bộ.
• Cache ở tầng ứng dụng cho dữ liệu đọc nhiều, ít đổi.
• Tách tải phân tích sang kho dữ liệu riêng thay vì chạy báo cáo nặng trên database giao dịch.
• Sharding (ví dụ với extension Citus) chỉ khi các cách trên không đủ, vì tăng độ phức tạp đáng kể.

Giám sát liên tục

Đưa các chỉ số như số kết nối, tỷ lệ cache hit, độ trễ truy vấn, số dòng chết, độ trễ replica, dung lượng đĩa vào hệ thống giám sát và đặt cảnh báo. Khi chạy PostgreSQL trên cloud, dịch vụ được quản lý giảm công sao lưu, nâng cấp; xem bài so sánh dịch vụ cloud hosting và VPS để cân nhắc phương án hạ tầng. Chạy PostgreSQL trong container cho môi trường phát triển giúp mọi thành viên có cùng phiên bản với production, như trình bày trong bài sử dụng Docker container hóa ứng dụng.