Posted inDatabase

Tối ưu PostgreSQL: Dùng Partial và Covering Index để giảm 90% dung lượng, tăng tốc truy vấn

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Bối cảnh: Khi Index truyền thống trở thành gánh nặng

Trong quản trị cơ sở dữ liệu, chúng ta thường gặp một nghịch lý: Càng muốn truy vấn nhanh, ta càng ham tạo nhiều index. Tuy nhiên, index quá đà sẽ khiến thao tác INSERT/UPDATE chậm lại, đồng thời làm dung lượng lưu trữ phình to chóng mặt. Sau hơn 6 tháng vận hành hệ thống e-commerce với bảng orders lên tới 150 triệu record, mình nhận ra index B-Tree thông thường không còn đủ hiệu quả.

Trước đây, mình thường tạo index trên toàn bộ cột theo thói quen từ MySQL. Nhưng với PostgreSQL, khả năng tùy biến index linh hoạt hơn nhiều. Hai kỹ thuật “vàng” giúp mình giải quyết bài toán hiệu năng là Partial Indexes (Index một phần) và Covering Indexes (Index bao phủ).

Vấn đề thực tế mình đã đối mặt:

  • Lãng phí tài nguyên: Index cả những dữ liệu cũ, hiếm khi truy vấn (như đơn hàng đã hủy từ 3 năm trước).
  • Nghẽn I/O: Database tìm thấy key trong index nhưng vẫn phải tốn thêm một bước truy xuất vào bảng chính (Heap) để lấy dữ liệu, gây chậm trễ.

Partial Indexes: Chỉ Index những gì thực sự cần

Khái niệm và lợi ích

Partial Index cho phép bạn khoanh vùng dữ liệu cần index bằng mệnh đề WHERE. Thay vì đánh index cho toàn bộ 150 triệu dòng, bạn có thể chỉ tập trung vào 1-2% dữ liệu đang thực sự “nóng”.

Kỹ thuật này cực kỳ hiệu quả trong hai kịch bản:

  1. Dữ liệu không cân bằng (Skewed Data): Giả sử bảng users có 10 triệu dòng nhưng chỉ 1% là unverified. Nếu bạn chỉ cần tìm nhóm này để gửi mail nhắc nhở, hãy chỉ index những user chưa verify.
  2. Loại bỏ giá trị NULL: Tiết kiệm dung lượng cực lớn nếu cột chứa phần lớn là dữ liệu rỗng.

Triển khai thực tế

Xét bảng tasks. Mình chỉ quan tâm các task đang xử lý (processing), còn các task đã xong (finished) thì rất ít khi đụng tới.

-- Chỉ index các task đang xử lý để tiết kiệm bộ nhớ
CREATE INDEX idx_tasks_processing 
ON tasks (created_at) 
WHERE status = 'processing';

Khi chạy query, PostgreSQL sẽ tự động sử dụng index này:

SELECT * FROM tasks 
WHERE status = 'processing' 
ORDER BY created_at DESC;

Lưu ý quan trọng: Câu lệnh SQL của bạn bắt buộc phải có điều kiện WHERE status = 'processing'. Nếu thiếu, Planner của Postgres sẽ bỏ qua index và chuyển sang quét toàn bộ bảng (Seq Scan).

Covering Indexes: Đạt tới cảnh giới Index-Only Scan

Sức mạnh của từ khóa INCLUDE

Thông thường, index chỉ lưu các cột khóa (key columns). Khi bạn query một cột không nằm trong index, database phải thực hiện “Heap Fetch” để lấy dữ liệu từ bảng gốc. Bước này tốn tài nguyên I/O và làm chậm tốc độ phản hồi.

Covering Index (hỗ trợ từ PostgreSQL 11) cho phép đính kèm thêm dữ liệu vào index qua từ khóa INCLUDE. Mục tiêu là đạt được Index-Only Scan: lấy mọi thứ ngay tại index mà không cần chạm vào bảng chính.

Ví dụ thực tế

Mình từng tối ưu hệ thống log với tần suất truy vấn user_id và action_code dựa trên thời gian cực cao.

-- Covering Index với INCLUDE
CREATE INDEX idx_logs_time_covering 
ON logs (created_at) 
INCLUDE (user_id, action_code);

Tại sao cách này lại thông minh? Trong idx_logs_time_covering:

  • created_at dùng để sắp xếp và tìm kiếm (Search Key).
  • user_id và action_code chỉ là “hành lý” đi kèm (Payload).

Vì payload không dùng để sắp xếp, index sẽ nhỏ gọn hơn so với việc đưa cả 3 cột vào làm Key chính. Tốc độ INSERT cũng nhanh hơn đáng kể.

Kết hợp cả hai: Case study giảm 90% dung lượng

Trong dự án quản lý vận chuyển, mình kết hợp cả hai để lấy thông tin đơn hàng đang giao (shipping):

CREATE INDEX idx_orders_shipping_fast_track
ON orders (customer_id)
INCLUDE (total_amount, shipping_address)
WHERE status = 'shipping';

Kết quả thật bất ngờ. Dung lượng index giảm từ 12GB xuống còn 800MB. Tốc độ truy vấn từ 500ms lao dốc xuống dưới 10ms. Đây là những con số thực tế mình đo được trên môi trường production sau 6 tháng vận hành ổn định.

Giám sát và đo lường hiệu quả

Đừng vội tin index sẽ chạy ngay lập tức. Mình luôn dùng EXPLAIN ANALYZE để kiểm tra quyết định của Planner.

EXPLAIN ANALYZE 
SELECT total_amount FROM orders 
WHERE status = 'shipping' AND customer_id = 12345;

Nếu thấy dòng Index Only Scan, bạn đã thành công. Ngoài ra, hãy thường xuyên kiểm tra view pg_stat_user_indexes. Nếu một index có idx_scan bằng 0 sau một tuần, đừng ngần ngại “khai tử” nó để giải phóng tài nguyên.

SELECT relname, indexrelname, idx_scan 
FROM pg_stat_user_indexes 
WHERE schemaname = 'public';

Tối ưu database là một hành trình liên tục. Hiểu rõ Partial và Covering Indexes sẽ giúp bạn xử lý các bài toán hiệu năng hóc búa mà không cần tốn chi phí nâng cấp phần cứng.

Share:
Tags: