Chiến lược indexing PostgreSQL cho ứng dụng web quy mô vừa

Index là công cụ mạnh nhất để tăng tốc query PostgreSQL — nhưng cũng là nguồn gốc của nhiều vấn đề hiệu năng nếu dùng sai. Mỗi index cần không gian lưu trữ và làm chậm write operations. Hiểu khi nào nên và không nên tạo index là kỹ năng quan trọng.

EXPLAIN ANALYZE: công cụ không thể thiếu

Trước khi tạo bất kỳ index nào, chạy EXPLAIN (ANALYZE, BUFFERS) trước query. Output cho bạn biết query planner chọn plan gì, bao nhiêu rows được scan, và thời gian thực tế. Seq Scan trên table nhỏ là bình thường — đừng vội tạo index.

Chú ý đến 'rows=X' estimated vs actual: nếu chênh lệch lớn, table statistics đã cũ. Chạy ANALYZE để refresh. Planner đưa ra quyết định dựa trên statistics — statistics không chính xác dẫn đến bad plans.

B-tree Index: mặc định và versatile nhất

B-tree index (mặc định khi CREATE INDEX) phù hợp với equality và range queries: =, <, >, BETWEEN, LIKE 'prefix%'. Không phù hợp với LIKE '%suffix' hoặc full-text search.

Multi-column index: thứ tự columns rất quan trọng. Index (a, b, c) hỗ trợ queries trên a, a+b, và a+b+c — nhưng không hỗ trợ b hoặc c riêng lẻ. Đặt selective column đầu tiên.

GIN Index cho array và JSONB

GIN (Generalized Inverted Index) được thiết kế cho dữ liệu composite như arrays, JSONB, và full-text tsvector. Nếu bạn query vào JSONB fields thường xuyên, GIN index với jsonb_path_ops là lựa chọn tốt.

GIN index tốn nhiều thời gian build và space hơn B-tree, nhưng query performance cho containment operators (@>, <@) vượt trội so với sequential scan.

Partial Index: index thông minh hơn

Partial index chỉ index rows thỏa mãn một điều kiện: CREATE INDEX idx_active_users ON users(email) WHERE active = true. Nhỏ hơn full index, hiệu quả hơn với queries luôn có cùng filter.

Index Bloat và Maintenance

PostgreSQL dùng MVCC — rows cũ không bị xóa ngay. Sau nhiều UPDATEs và DELETEs, index bị bloat. Dùng REINDEX CONCURRENTLY để rebuild index mà không lock table. Cài pg_stat_user_indexes để monitor index usage và phát hiện unused indexes.