PostgreSQL Indexing thực chiến — B-tree, GIN và cái bẫy hay gặp | Blog

Index trong PostgreSQL nghe thì quen mà xài lại dễ sai. Hồi mới code backend, em cứ thấy query chậm là add index, y như đổ thêm gia vị vô món ăn. Kết quả: database phình to, insert chậm rì, mà query thiệt sự chậm thì vẫn chậm. Bài này ghi lại mấy thứ em đã học được — cái nào nên dùng, cái nào là bẫy.

1. Hiểu đúng cái giá của index

Index không phải thứ "có càng nhiều càng tốt". Mỗi index là một cấu trúc dữ liệu riêng (thường là B-tree) mà database phải cập nhật mỗi lần INSERT/UPDATE/DELETE. Nghĩa là: index giúp SELECT nhanh hơn, nhưng làm write chậm hơn, và tốn disk. Trên bảng 10 triệu dòng, một index thừa có thể làm insert chậm gấp đôi vì phải ghi thêm cả cây B-tree.

Quy tắc đầu tiên: chỉ index cái gì query thật sự cần, và luôn đo bằng EXPLAIN ANALYZE trước khi quyết định.

2. B-tree — index mặc định và mấy chỗ nó bó tay

Gõ CREATE INDEX mà không chỉ định gì thì PostgreSQL dùng B-tree. Nó cực nhanh cho phép so sánh bằng (=), khoảng (>, <), và sắp xếp. Nhưng có 3 trường hợp B-tree "chết" mà em hay gặp:

a) Hàm bọc cột

-- Index này SẼ KHÔNG được dùng:
CREATE INDEX idx_users_email ON users (email);
SELECT * FROM users WHERE lower(email) = 'lumi@example.com';

PostgreSQL không biết lộn ngược hàm lower() nên nó quét cả bảng. Sửa: index lên chính biểu thức đó:

CREATE INDEX idx_users_email_lower ON users (lower(email));

b) LIKE với wildcard ở đầu

SELECT * FROM users WHERE email LIKE '%@gmail.com';

B-tree chỉ tìm được prefix, còn % đứng đầu thì bó tay. Cần tìm kiếm kiểu này thì nghĩ tới pg_trgm hoặc full-text search (bên dưới).

c) Cột low-cardinality

Index trên cột status chỉ có 3 giá trị ('pending', 'done', 'failed') gần như vô dụng — PostgreSQL thà quét bảng còn nhanh hơn nhảy index. Em từng thấy người ta index cột boolean rồi tự hỏi sao query vẫn chậm.

3. GIN — vũ khí cho full-text search & JSONB

GIN (Generalized Inverted Index) dùng cho dữ liệu dạng "một dòng, nhiều key": full-text search, JSONB, array. Ví dụ search tiếng Việt:

CREATE INDEX idx_posts_search
ON posts USING gin (to_tsvector('vietnamese', content));

SELECT * FROM posts
WHERE to_tsvector('vietnamese', content) @@ to_tsquery('vietnamese', 'index & postgres');

JSONB thì thêm jsonb_path_ops để index nhỏ hơn và nhanh hơn cho phép truy cập trực tiếp:

CREATE INDEX idx_products_meta
ON products USING gin (meta jsonb_path_ops);

4. Partial index & index đa cột — xài khéo là lời to

Partial index chỉ index một phần dòng — nhỏ hơn, nhanh hơn, đúng nhu cầu:

-- 99% query chỉ quan tâm đơn hàng đang pending
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Còn index đa cột thì thứ tự cột cực kỳ quan trọng: cột nào dùng cho điều kiện = trước, rồi mới tới cột sắp xếp. (a, b) KHÔNG thể phục vụ query lọc theo b một mình.

5. Kinh nghiệm thực chiến

Bài học đắt nhất của em: đừng index theo cảm tính, hãy index theo EXPLAIN ANALYZE. Quy trình em hay dùng:

Chạy EXPLAIN (ANALYZE, BUFFERS) xem query chậm ở đâu — scan cả bảng hay đã dùng index.
Kiểm tra selectivity: nếu filter trả về >5-10% số dòng, index thường không thắng nổi seq scan.
Tạo index, chạy lại EXPLAIN, đo thời gian thật — đừng đoán.
Rà soát index thừa định kỳ: hai index có cùng cột đầu là dấu hiệu cần gộp.

PostgreSQL còn có pg_stat_user_indexes để coi index nào được dùng, index nào nằm chơi không — cái nào lâu rồi không ai đụng thì mạnh dạn drop.

Index là công cụ, không phải thần dược. Hiểu cấu trúc dữ liệu đằng sau, đo lường bằng EXPLAIN, rồi mới đặt index — đó mới là cách backend engineer xử lý query chậm bền vững.