Database indexing strategies: B-tree index, Hash index, GIN index khác nhau như thế nào?

B-tree cho 95% trường hợp; GIN cho JSONB/array/full-text search; BRIN cho time-series append-only tables; partial index và expression index thường bị bỏ qua nhưng có thể improve performance 10-100x.

• B-tree (default): balanced tree structure, phù hợp equality (=), range queries (<, >, BETWEEN), ORDER BY, prefix LIKE 'abc%'.
• Là lựa chọn đúng cho 95% trường hợp.
• Hash: chỉ equality checks — lý thuyết nhanh hơn B-tree cho equality nhưng không thể dùng cho range, không được replicated trong PostgreSQL cũ (fixed 10+).
• GIN (Generalized Inverted Index): array containment, full-text search (tsvector), JSONB operators (@>, ?) — mỗi phần tử trong array/document được index riêng, build chậm hơn nhưng queries trên multi-value columns cực nhanh.
• GiST: geometric data (PostGIS), range types, full-text search — linh hoạt hơn GIN nhưng chậm hơn cho tìm kiếm đơn giản.
• BRIN (Block Range Index): rất nhỏ, cho time-series data có natural correlation với physical storage (ví dụ timestamp columns trong append-only tables) — index size chỉ vài KB thay vì GB.
• Expression indexes: CREATE INDEX ON orders (LOWER(email)) — index tính toán expression, hữu ích khi query luôn dùng LOWER().
• Partial indexes với WHERE clause: CREATE INDEX ON orders (user_id) WHERE status = 'pending' — index nhỏ hơn, update nhanh hơn, rất hiệu quả khi query luôn filter theo condition cố định.
• Index on expressions và partial indexes thường bị bỏ qua nhưng có thể improve performance 10-100x cho specific query patterns.