Case Study: Tối ưu câu truy vấn PostgreSQL từ 4.2s xuống 15ms cho bảng 20 triệu bản ghi

Bối cảnh sự cố nghẽn Database trong giờ cao điểm#

Hệ thống ghi nhận đơn hàng của bên mình có một bảng orders với hơn 20 triệu bản ghi. Vào khung giờ khuyến mãi 12h trưa, trang dashboard của bộ phận vận hành bắt đầu quay vòng tròn và xuất hiện hàng loạt cảnh báo CPU database chạm mốc 98%.

Khi kiểm tra pg_stat_activity, thủ phạm là câu truy vấn lọc danh sách đơn hàng theo tenant_id, status và sắp xếp theo created_at giảm dần. Thời gian thực thi trung bình lên tới 4.2 giây.

Phân tích Execution Plan bằng EXPLAIN ANALYZE#

Câu truy vấn ban đầu:

SELECT id, order_code, customer_id, total_amount, created_at
FROM orders
WHERE tenant_id = 1042 AND status = 'COMPLETED'
ORDER BY created_at DESC
LIMIT 20;

Kết quả EXPLAIN (ANALYZE, BUFFERS) chỉ ra nguyên nhân:

• Database thực hiện Seq Scan (quét toàn bộ bảng) vì index hiện có chỉ là idx_orders_tenant_id đơn lẻ.
• Tiếp theo là một bước Sort (quạt đĩa Disk-based Sort) cực kỳ nặng nề vì lượng bản ghi khớp điều kiện tenant_id lên tới 400.000 dòng.

Giải pháp: Composite Index kết hợp Include & Concurrently#

Thay vì tạo nhiều index đơn lẻ vô ích, giải pháp triệt để là tạo một Composite Index (Chỉ mục kết hợp) chuẩn theo thứ tự các trường trong WHERE và ORDER BY:

-- Tạo index an toàn không lock bảng trong giờ hoạt động
CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC)
INCLUDE (total_amount, order_code);

Tại sao lại hiệu quả vượt trội?#

1. Khớp trọn vẹn điều kiện lọc: Database nhảy trực tiếp đến đúng nhánh B-Tree chứa tenant_id = 1042 và status = 'COMPLETED'.
2. Khớp luôn thứ tự sắp xếp: Dữ liệu trong index đã được sắp xếp sẵn theo created_at DESC, loại bỏ hoàn toàn bước Sort!
3. Index-Only Scan: Nhờ mệnh đề INCLUDE, PostgreSQL lấy được luôn các trường dữ liệu cần hiển thị ngay trong index page mà không cần phải chạm vào bảng chính (Heap fetch).

Kết quả đo lường sau khi áp dụng#

• Thời gian truy vấn: Giảm từ 4.200ms xuống còn 15ms (nhanh hơn 280 lần!).
• Tải CPU Database: Hạ từ 98% xuống mức ổn định dưới 25%.
• Không xảy ra bất kỳ gián đoạn dịch vụ nào trong quá trình chạy CREATE INDEX CONCURRENTLY.

Góc thảo luận#

Anh em có thói quen dùng mệnh đề INCLUDE trong PostgreSQL để biến câu truy vấn thành Index-Only Scan không? Đã từng gặp case nào index bị phình to (index bloat) chưa?

Xem thêm nội dung liên quan:

• Học lộ trình Backend Developer
• Từ điển AI: Cơ sở dữ liệu Vector là gì?