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

PostgreSQL có nhiều loại index, mỗi loại hợp một kiểu dữ liệu/truy vấn:

• B-tree (mặc định): so sánh bằng (=), khoảng (<, >, BETWEEN), ORDER BY, prefix LIKE 'abc%'. Đúng cho ~95% trường hợp.
• Hash: chỉ so sánh bằng — lý thuyết nhanh hơn B-tree cho = nhưng không làm được khoảng.
• GIN: dữ liệu "nhiều giá trị trong một ô" — array, JSONB (@>, ?), full-text (tsvector). Build chậm nhưng query trên cột đa giá trị cực nhanh.
• GiST: dữ liệu hình học (PostGIS), range type — linh hoạt hơn GIN nhưng chậm hơn cho tìm kiếm đơn giản.
• BRIN: bảng cực lớn dạng append-only mà dữ liệu sắp tự nhiên theo thứ tự ghi (vd cột timestamp) — index chỉ vài KB thay vì GB.

Hai loại hay bị quên nhưng cực lợi hại:
- Expression index: CREATE INDEX ON orders (LOWER(email)) — hợp khi query luôn dùng LOWER().
- Partial index: CREATE INDEX ON orders (user_id) WHERE status = 'pending' — nhỏ, cập nhật nhanh, rất hiệu quả khi query luôn lọc theo một điều kiện cố định.

Hai loại này có thể tăng tốc 10-100x cho đúng pattern query — đáng nhớ khi phỏng vấn.

PostgreSQL has several index types, each suited to a kind of data/query:

• B-tree (default): equality (=), ranges (<, >, BETWEEN), ORDER BY, prefix LIKE 'abc%'. Right for ~95% of cases.
• Hash: equality only — theoretically faster than B-tree for = but can't do ranges.
• GIN: "many values in one cell" data — arrays, JSONB (@>, ?), full-text (tsvector). Slow to build but extremely fast for multi-value-column queries.
• GiST: geometric data (PostGIS), range types — more flexible than GIN but slower for simple searches.
• BRIN: huge append-only tables whose data is naturally ordered by insertion (e.g. a timestamp column) — only a few KB instead of GB.

Two often-forgotten but powerful types:
- Expression index: CREATE INDEX ON orders (LOWER(email)) — for when queries always use LOWER().
- Partial index: CREATE INDEX ON orders (user_id) WHERE status = 'pending' — small, fast to update, very effective when queries always filter on a fixed condition.

These two can speed things up 10-100x for the right query pattern — worth mentioning in an interview.