PostgreSQL Indexing nâng cao: B-tree, Hash, GIN, GiST, BRIN cho hiệu năng tối đa
PostgreSQL cung cấp 6 loại index chính: B-tree (mặc định), Hash, GIN, GiST, SP-GiST và BRIN. Mỗi loại tối ưu cho một kiểu truy vấn và dữ liệu cụ thể. Hiểu rõ đặc điểm từng loại giúp bạn tránh over-indexing (tốn disk, chậm write) và under-indexing (truy vấn chậm, sequential scan).
Quy tắc vàng: chỉ index cột xuất hiện trong WHERE, JOIN, ORDER BY, GROUP BY. Index không phải miễn phí — mỗi INSERT/UPDATE/DELETE phải cập nhật thêm index, làm chậm write throughput. Chỉ index khi select ratio đủ lớn (thường >5-10% rows).
B-tree: Index đa năng cho so sánh và sắp xếp
B-tree là mặc định, hỗ trợ mọi phép so sánh: =, , =, BETWEEN, IN, IS NULL, LIKE ‘prefix%’. Cũng hỗ trợ ORDER BY và GROUP BY nếu sort order khớp index. Phù hợp cho khóa chính, khóa ngoại, timestamp, ID dạng UUID có sort.
-- Index B-tree mặc định
CREATE INDEX idx_users_email ON users(email);
-- B-tree descending cho ORDER BY timestamp DESC
CREATE INDEX idx_logs_created_at ON logs(created_at DESC);
-- Partial index: chỉ index rows active
CREATE INDEX idx_orders_pending ON orders(user_id) WHERE status = 'pending';Partial index giảm kích thước đáng kể khi lọc tập nhỏ. Covering index (INCLUDE) giúp index-only scan mà không cần lookup heap tuple.
Hash: Tốc độ tối đa cho equality check
Hash index chỉ hỗ trợ =, không hỗ trợ range hay ordering. Kích thước nhỏ hơn B-tree, tốc độ O(1) cho exact match. PostgreSQL 10+ có WAL logging cho Hash nên crash-safe. Dùng cho lookup key-value, session ID, cache invalidation.
-- Hash index cho exact match
CREATE INDEX idx_sessions_token ON sessions USING hash(session_token);
-- Not used: WHERE token LIKE 'abc%' -- sequential scanGIN: Inverted index cho array, JSONB, full-text search
Generalized Inverted Index lưu mapping từ element đến list row chứa nó. Hoàn hảo cho array, tsvector, jsonb với @>, ?, ?|, ?& operators. GIN hỗ trợ fastupdate (pending list) để giảm overhead write.
-- GIN cho full-text search tiếng Việt
CREATE INDEX idx_articles_content ON articles USING gin(to_tsvector('vietnamese', content));
-- GIN cho JSONB query
CREATE INDEX idx_products_attrs ON products USING gin(attributes);
-- GIN cho array tags
CREATE INDEX idx_posts_tags ON posts USING gin(tags);GiST: Index không gian, geo, và range type
Generalized Search Tree cho phép định nghĩa operator class tùy biến. Hỗ trợ PostGIS geometry (&&, @>, <@, ), range types (int4range, tsrange), và cube. Dùng cho truy vấn khoảng cách, giao nhau, bao quanh.
-- GiST cho PostGIS point
CREATE INDEX idx_places_location ON places USING gist(geom);
-- GiST cho range type (booking, scheduling)
CREATE INDEX idx_room_booking ON rooms USING gist(booking_period);BRIN: Block Range Index cho bảng khổng lồ, dữ liệu có order tự nhiên
Block Range Index lưu min/max của từng block range (mặc định 128 pages). Chỉ hoạt động tốt khi dữ liệu vật lý tương quan với logic (timestamp tự tăng, ID tự tăng). Kích thước cực nhỏ (KB cho TB data), write gần như zero overhead.
-- BRIN cho log table theo thời gian
CREATE INDEX idx_logs_ts_brin ON logs USING brin(created_at) WITH (pages_per_range = 128);
-- BRIN cho big serial ID
CREATE INDEX idx_events_id_brin ON events USING brin(id);So sánh nhanh: chọn loại nào?
| Loại | Phù hợp | Không phù hợp | Kích thước |
|---|---|---|---|
| B-tree | Equality, range, sort, PK/FK | Array, JSON, full-text | Trung bình |
| Hash | Equality only, high cardinality | Range, sort, LIKE prefix | Nhỏ |
| GIN | Array, JSONB, full-text, @> | Range, sort | Lớn (fastupdate giúp) |
| GiST | Geo, range, custom operators | Equality only | Trung bình |
| BRIN | Big table, ordered data, timestamp | Random data, small table | Rất nhỏ |
Best practices
- Dùng
EXPLAIN ANALYZEđể verify index được dùng (Index Scan vs Seq Scan) - Multi-column index: thứ tự cột quan trọng (equality trước, range sau)
REINDEXđịnh kỳ cho index bị bloat (bloat > 30% thì reindex)- Monitor
pg_stat_user_indexes.idx_scan = 0để tìm index không dùng - Kết hợp partial index + covering index cho workload cụ thể
Không có “index tốt nhất”, chỉ có index đúng cho workload của bạn. Hãy đo lường, test, và điều chỉnh — PostgreSQL EXPLAIN (ANALYZE, BUFFERS) là công cụ tin cậy nhất để dẫn đường.
Tham khảo thêm: PostgreSQL Documentation – Indexes và Use The Index, Luke! cho design pattern indexing chuyên sâu.
Multi-column index: thứ tự cột quan trọng (equality trước, range sau).
Monitor pg_stat_user_indexes.idx_scan = 0 để tìm index không dùng.
