Index trong PostgreSQL: những điều mình ước được biết sớm hơn

1. Vấn đề "Full Table Scan" khi bảng lớn

Giả sử ta có bảng orders của một shop online, khoảng 5 triệu đơn hàng:

-- Câu query phổ biến: tìm các đơn hàng chưa xử lý của khách hàng ID = 1042
SELECT * FROM orders 
WHERE user_id = 1042 AND status = 'PENDING' 
ORDER BY created_at DESC;

Nếu không có chỉ mục, PostgreSQL buộc phải đọc qua 5 triệu dòng dữ liệu trên đĩa cứng, mất khoảng 1500ms - 2500ms.

2. Giải pháp: Composite Index (Chỉ mục kết hợp)

Một sai lầm phổ biến là tạo 2 index rời rạc trên cột user_id và status. Thay vào đó, hãy tạo 1 Composite Index đúng thứ tự:

-- Tạo Composite Index tối ưu cho câu query trên
CREATE INDEX idx_orders_user_status_date 
ON orders (user_id, status, created_at DESC);

Kết quả (minh hoạ)

• Thời gian thực thi có thể giảm từ cỡ vài giây xuống chỉ vài mili-giây. Con số cụ thể tuỳ phần cứng và dữ liệu, nên bạn hãy tự chạy EXPLAIN ANALYZE trước và sau khi tạo index để thấy khác biệt trên máy mình.
• PostgreSQL dùng Index Scan trên index mới thay vì quét toàn bảng. Vì câu query dùng SELECT * nên Postgres vẫn phải đọc dòng dữ liệu trong bảng; muốn có Index Only Scan thì chỉ chọn các cột nằm trong index.

3. Partial Index: chỉ đánh chỉ mục phần dữ liệu cần dùng

Trong hệ thống E-commerce, 95% đơn hàng đã ở trạng thái COMPLETED hoặc CANCELLED. Các query của nhân viên vận hành thường chỉ quan tâm đến đơn hàng PENDING hoặc PROCESSING.

Tạo Partial Index chỉ đánh chỉ mục cho các đơn chưa hoàn thành:

CREATE INDEX idx_orders_active_only 
ON orders (user_id, created_at DESC) 
WHERE status IN ('PENDING', 'PROCESSING');

Lợi ích

• Index nhỏ hơn nhiều (trong ví dụ này chỉ khoảng 5% số dòng được đánh chỉ mục), nên dễ nằm gọn trong RAM.
• Chi phí cập nhật index khi INSERT/UPDATE thấp hơn, vì chỉ các dòng thỏa điều kiện WHERE mới nằm trong index.

4. Index có làm chậm INSERT, UPDATE, DELETE không?

Có. Index giúp đọc nhanh hơn nhưng làm ghi chậm hơn, vì mỗi lần dữ liệu thay đổi PostgreSQL phải cập nhật cả bảng lẫn các index liên quan. Bảng càng nhiều index thì thao tác ghi càng chậm và càng tốn dung lượng ổ đĩa.

Cụ thể với bảng orders:

• INSERT: mỗi đơn hàng mới phải được ghi vào bảng và vào từng index. Bảng có 6 index nghĩa là 1 lần ghi bảng cộng thêm 6 lần ghi index.
• UPDATE: PostgreSQL không sửa dòng tại chỗ mà tạo một phiên bản dòng mới, và thường phải thêm entry mới vào mọi index. Có một ngoại lệ là HOT update: nếu không cột nào nằm trong index bị đổi và trang dữ liệu còn chỗ trống, Postgres bỏ qua bước cập nhật index. Index trên cột hay bị sửa (như status, updated_at) sẽ làm mất tối ưu này.
• DELETE: dòng bị xoá chỉ được đánh dấu là "chết". Entry tương ứng trong index vẫn nằm đó cho tới khi VACUUM dọn, nên index nhiều thì VACUUM cũng tốn công hơn.

Ngoài tốc độ ghi, mỗi index còn chiếm dung lượng ổ đĩa và RAM (shared buffers). Index trên bảng vài triệu dòng có thể nặng hàng trăm MB. Xem kích thước bảng so với tổng các index:

SELECT pg_size_pretty(pg_table_size('orders'))   AS table_size,
       pg_size_pretty(pg_indexes_size('orders')) AS indexes_size;

Nguyên tắc: chỉ tạo index cho các câu query thật sự chạy thường xuyên và đang chậm, kiểm tra bằng EXPLAIN ANALYZE chứ không tạo "phòng hờ". Khi tạo index trên bảng đang chạy production, dùng CREATE INDEX CONCURRENTLY để không khoá thao tác ghi trong lúc build index.

5. Tìm và xoá index thừa với pg_stat_user_indexes

Index thừa là index không được query nào dùng tới, nhưng vẫn tốn chi phí ghi và dung lượng như phân tích ở trên. PostgreSQL đếm số lần mỗi index được quét trong view pg_stat_user_indexes. Index có idx_scan = 0 sau một thời gian dài là ứng viên để xoá.

-- Các index chưa từng được dùng, lớn nhất trước
-- (bỏ qua index UNIQUE / PRIMARY KEY vì chúng còn đảm bảo ràng buộc dữ liệu)
SELECT s.relname                                      AS table_name,
       s.indexrelname                                 AS index_name,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

Trước khi xoá, kiểm tra thêm vài điều:

• Thống kê đã chạy đủ lâu chưa: số liệu được đếm từ lần reset thống kê gần nhất. Nên quan sát ít nhất qua một chu kỳ nghiệp vụ đầy đủ (ví dụ cả các báo cáo chạy cuối tháng).
• Read replica: mỗi server tự đếm riêng. Index chỉ được dùng bởi query trên replica sẽ hiện idx_scan = 0 trên primary.
• Index trùng lặp: index (user_id) là thừa khi đã có (user_id, status, created_at), vì theo Prefix Rule ở mục 2, index tổ hợp phục vụ được cả query chỉ lọc theo user_id. Loại này có thể vẫn có idx_scan > 0, nên cần đọc danh sách index (\d orders trong psql) để phát hiện.

Xoá index mà không khoá bảng:

DROP INDEX CONCURRENTLY idx_orders_user_id;

6. Ngoài B-Tree, PostgreSQL còn những loại index nào?

B-Tree là loại mặc định và phù hợp với phần lớn trường hợp. Nhưng với JSON, mảng, tìm kiếm full-text hay bảng log cực lớn, PostgreSQL có các loại index chuyên dụng hiệu quả hơn nhiều:

Hai ví dụ trong hệ thống E-commerce:

-- GIN: lọc sản phẩm theo thuộc tính lưu trong cột JSONB
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
SELECT * FROM products WHERE attributes @> '{"color": "red", "size": "M"}';

-- BRIN: bảng lịch sử trạng thái đơn hàng chỉ thêm mới, dòng nằm theo thứ tự thời gian
CREATE INDEX idx_order_events_created ON order_events USING BRIN (created_at);
SELECT * FROM order_events WHERE created_at >= now() - interval '1 day';

BRIN chỉ lưu giá trị nhỏ nhất và lớn nhất của từng khối trang dữ liệu, nên với bảng hàng trăm triệu dòng nó thường chỉ nặng vài trăm KB, trong khi B-Tree trên cùng cột có thể lên tới vài GB. Đổi lại, BRIN chỉ hiệu quả khi dữ liệu được ghi theo thứ tự (như created_at của bảng chỉ thêm mới).

Muốn tìm kiếm kiểu LIKE '%áo thun%' (ký tự % ở đầu), B-Tree không giúp được. Hãy dùng GIN kết hợp extension pg_trgm:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);