Indexing trong PostgreSQL: Khi B-Tree là chưa đủ

Cái bẫy “B-Tree cho mọi thứ”

Tất cả chúng ta đều đã từng gặp tình trạng này: một câu truy vấn chạy mất 5ms trên máy tính cá nhân nhưng đột ngột tốn tới 5 giây khi đưa lên production. Tôi từng làm việc trên một nền tảng phân tích, nơi mà một đợt migration đơn giản từ schema phẳng sang mô hình nặng về JSONB đã khiến dashboard chạy chậm như rùa. Thủ phạm là gì? Chúng tôi đã lạm dụng index B-Tree mặc định cho mọi thứ.

PostgreSQL cực kỳ linh hoạt, nhưng chính sự linh hoạt đó thường khiến các lập trình viên rơi vào thói quen xấu. Một index tiêu chuẩn hoạt động rất tốt cho các tra cứu đơn giản. Tuy nhiên, khi các bảng dữ liệu tăng từ 10.000 lên 10 triệu dòng, hoặc khi bạn bắt đầu lưu trữ dữ liệu phức tạp như tọa độ địa lý, B-Tree trở thành một gánh nặng. Nó tiêu tốn không gian đĩa và thường bị bộ lập kế hoạch truy vấn (query planner) lờ đi hoàn toàn.

Hiệu năng chậm thường không phải do thiếu index, mà do dùng sai công cụ. PostgreSQL cung cấp một bộ công cụ chuyên dụng—B-Tree, GIN, GiST và BRIN. Chọn đúng loại index là sự khác biệt giữa một phản hồi 15ms mượt mà và một lỗi timeout 30 giây khiến người dùng khó chịu.

Thiết lập: Chuẩn bị công cụ phù hợp

Hầu hết các loại index đều có sẵn. Tuy nhiên, nếu bạn muốn thực hiện tìm kiếm văn bản fuzzy (gần đúng) hoặc kết hợp các logic indexing khác nhau, bạn sẽ cần kích hoạt một vài extension tiêu chuẩn. Hãy đảm bảo bạn đang chạy ít nhất PostgreSQL 12 để tận dụng những cải tiến hiệu năng mới nhất của GIN và BRIN.

# Kiểm tra phiên bản của bạn
psql -c "SELECT version();"

# Kết nối và kích hoạt các extension cần thiết
psql -d my_project_db

-- Cho tìm kiếm văn bản fuzzy (gần đúng)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Để kết hợp logic B-Tree với GiST
CREATE EXTENSION IF NOT EXISTS btree_gist;

Chọn vũ khí: Giải thích các loại Index

Mỗi loại index phục vụ một mục đích kiến trúc cụ thể. Đừng đoán mò; hãy khớp index với kiểu truy vấn của bạn.

1. B-Tree: Lựa chọn mặc định đáng tin cậy

B-Tree là lựa chọn cơ bản nhất. Nó giữ cho dữ liệu được sắp xếp, giúp nó trở thành lựa chọn hoàn hảo cho các truy vấn bằng (=) và truy vấn khoảng (<, >, BETWEEN). Hãy sử dụng nó cho primary key, foreign key và bất kỳ cột nào bạn cần tìm một giá trị cụ thể hoặc một khoảng ngày tháng.

-- Hoàn hảo cho các tra cứu có độ chọn lọc (cardinality) cao
CREATE INDEX idx_users_email ON users USING btree (email);

-- Truy vấn một khoảng ngày cụ thể
SELECT * FROM orders WHERE created_at > '2024-01-01';

2. GIN: Sức mạnh cho JSONB và Array

Tìm kiếm bên trong một cột JSONB bằng B-Tree giống như mò kim đáy bể khi bị trói tay. Generalized Inverted Indexes (GIN) được thiết kế cho dữ liệu chứa nhiều giá trị. Nếu bạn đang lọc theo tag hoặc tìm kiếm sâu bên trong các tài liệu JSON, GIN là bắt buộc. Trong một bảng có 1 triệu dòng, một index GIN có thể biến một lần quét tuần tự (sequential scan) mất 1,2 giây thành một lần tra cứu chỉ mất 5ms.

-- Tối ưu hóa tìm kiếm JSONB với path_ops để đạt hiệu năng tốt hơn
CREATE INDEX idx_user_prefs ON users USING GIN (preferences jsonb_path_ops);

-- Tìm ngay lập tức tất cả người dùng đang bật chế độ dark mode
SELECT * FROM users WHERE preferences @> '{"theme": "dark"}';

3. GiST: Làm chủ dữ liệu không gian và dữ liệu chồng lấp

GiST (Generalized Search Tree) xử lý các hình khối hình học phức tạp và các khoảng dữ liệu. Nếu bạn sử dụng PostGIS hoặc cần tìm các khoảng thời gian chồng lấn, GiST là lựa chọn thực tế duy nhất. Nó tổ chức dữ liệu thành các “bounding box”, cho phép database nhanh chóng loại bỏ các khối dữ liệu không liên quan.

-- Index các điểm địa lý cho tính năng định vị cửa hàng
CREATE INDEX idx_stores_location ON stores USING GIST (location);

-- Tìm các cửa hàng trong bán kính 5km
SELECT name FROM stores 
WHERE ST_DWithin(location, ST_MakePoint(10.7, 106.6)::geography, 5000);

4. BRIN: Hiệu quả cho các bảng dữ liệu khổng lồ

Tôi từng quản lý một bảng log dung lượng 500GB, nơi một index B-Tree tiêu chuẩn trên cột timestamp đã ngốn tới 45GB RAM. Đó là một sự lãng phí khủng khiếp. Block Range Indexes (BRIN) thông minh hơn nhiều đối với dữ liệu được sắp xếp tự nhiên. Thay vì index từng dòng, BRIN lưu trữ các giá trị min/max cho một khối trang (block of pages). Kết quả là? Index 45GB của chúng tôi đã giảm xuống chỉ còn 60MB trong khi vẫn duy trì tốc độ truy vấn gần như tương đương.

-- Sử dụng BRIN cho dữ liệu time-series hoặc log
CREATE INDEX idx_logs_created_at ON system_logs USING BRIN (created_at);

-- Index này rất nhỏ và hoàn hảo cho các bảng có kích thước hàng Terabyte.

Xác minh: Đừng tin tưởng, hãy kiểm tra

Tạo một index không có nghĩa là database sẽ sử dụng nó. Bạn cần kiểm tra kế hoạch thực thi để đảm bảo bạn không lãng phí tài nguyên.

Kiểm tra với EXPLAIN ANALYZE

Luôn chạy các truy vấn của bạn với EXPLAIN ANALYZE. Bạn cần thấy dòng “Index Scan” hoặc “Bitmap Index Scan”. Nếu bạn thấy “Seq Scan”, index của bạn đang bị lờ đi.

EXPLAIN ANALYZE 
SELECT * FROM users WHERE preferences @> '{"theme": "dark"}';

Nếu bộ lập kế hoạch bỏ qua index của bạn, có thể bảng đó quá nhỏ hoặc câu truy vấn không khớp với định nghĩa index. PostgreSQL thường quyết định rằng quét tuần tự sẽ nhanh hơn nếu bảng có ít hơn vài nghìn dòng.

Theo dõi tình trạng phình Index (Index Bloat)

Index không hề miễn phí. Chúng làm chậm các thao tác INSERT và UPDATE. Hãy sử dụng câu truy vấn này để so sánh kích thước index với kích thước bảng và xem cái nào thực sự đang được sử dụng:

SELECT
    t.relname AS table_name,
    i.relname AS index_name,
    pg_size_pretty(pg_relation_size(t.oid)) AS table_size,
    pg_size_pretty(pg_relation_size(i.oid)) AS index_size,
    idx_scan AS times_used
FROM pg_class t
JOIN pg_index x ON t.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_stat_all_indexes s ON s.indexrelid = i.oid
WHERE t.relkind = 'r'
ORDER BY pg_relation_size(i.oid) DESC;

Nếu bạn thấy một index 10GB với 0 lần quét, hãy xóa nó đi. Nó chỉ là gánh nặng. Đối với các index bị phình to do cập nhật thường xuyên, việc reindex đồng thời (concurrent reindex) có thể giải phóng không gian mà không ngăn cản người dùng truy cập hệ thống.

-- Rebuild mà không gây gián đoạn (downtime)
REINDEX INDEX CONCURRENTLY idx_users_email;

Indexing không phải là công việc “thiết lập một lần rồi thôi”. Hãy bắt đầu với B-Tree cho các ID, dùng GIN cho JSON và dành BRIN cho các bảng log khổng lồ. Bằng cách khớp index với kiểu dữ liệu, bạn sẽ giữ cho database tinh gọn và ứng dụng luôn nhanh nhạy khi mở rộng quy mô.