Ngoài B-tree mặc định, PostgreSQL cung cấp một số loại index chuyên biệt — GIN, GiST, BRIN, Hash — mỗi loại phù hợp với các pattern dữ liệu và query khác nhau, cùng với các index partial và expression để linh hoạt. Chọn đúng loại index có thể cải thiện đáng kể hiệu năng cho các query chuyên biệt.
CREATE INDEX idx_users_email ON users(email); -- mặc định = B-tree
-- tuyệt cho: equality (=), range (<, >, BETWEEN), sorting (ORDER BY), hầu hết query
B-tree là mặc định và xử lý phần lớn các trường hợp — equality, range, và sorting trên các cột thông thường.
-- GIN (Generalized Inverted Index) — cho các giá trị CHỨA nhiều phần tử
CREATE INDEX idx_attrs ON products USING GIN (attributes); -- JSONB
CREATE INDEX idx_tags ON posts USING GIN (tags); -- array
CREATE INDEX idx_search ON docs USING GIN (to_tsvector('english', body)); -- full-text
-- tuyệt cho: JSONB containment, array membership, full-text search
Index GIN thiết yếu cho JSONB, array, và full-text search — các query kiểm tra containment hoặc membership.
-- GiST — geometric, range, nearest-neighbor (ví dụ PostGIS, range type)
CREATE INDEX idx_geo ON places USING GiST (location);
-- BRIN (Block Range INdex) — index nhỏ xíu cho bảng LỚN với dữ liệu được sắp xếp tự nhiên
CREATE INDEX idx_time ON logs USING BRIN (created_at); -- tuyệt cho time-series khổng lồ
-- kích thước nhỏ, hiệu quả cho dữ liệu append-only/được sắp xếp lớn
-- Hash — chỉ equality (=); hiếm khi cần (B-tree cũng xử lý equality)
CREATE INDEX idx_hash ON t USING HASH (col);
-- index PARTIAL — chỉ index MỘT SỐ row (nhỏ hơn, có mục tiêu)
CREATE INDEX idx_active ON users(email) WHERE active = true; -- chỉ user active
-- index EXPRESSION — index một giá trị được tính
CREATE INDEX idx_lower_email ON users(LOWER(email)); -- cho tra cứu không phân biệt hoa thường
SELECT * FROM users WHERE LOWER(email) = '[email protected]'; -- dùng index
Index partial (chỉ index các row liên quan — nhỏ hơn, nhanh hơn) và index expression (index một giá trị được tính — cho phép các query dựa trên hàm được index) là các công cụ mạnh mẽ và linh hoạt.
Hiểu sự đa dạng các loại index của PostgreSQL là kiến thức có giá trị cho tối ưu hóa hiệu năng, vì loại index đúng cho một pattern dữ liệu và query nhất định có thể tăng tốc đáng kể các query mà B-tree mặc định xử lý kém hoặc không xử lý được.
Trong khi B-tree (mặc định) bao quát hầu hết các trường hợp (equality, range, sorting), các loại chuyên biệt mở khóa hiệu năng cho dữ liệu chuyên biệt: GIN thiết yếu cho JSONB, array, và full-text search (các query containment/membership — làm cho các tính năng JSONB và array mạnh mẽ của Postgres trở nên hiệu năng); GiST xử lý các query geometric, range, và nearest-neighbor (PostGIS geospatial, range type); và BRIN cung cấp các index nhỏ xíu, hiệu quả cho các bảng lớn được sắp xếp tự nhiên (như dữ liệu time-series/append-only — một lựa chọn có giá trị cho các bảng khổng lồ nơi một B-tree đầy đủ sẽ tốn kém).
Biết loại index nào phù hợp với kịch bản nào (GIN cho JSONB/array/full-text, GiST cho geometric/range, BRIN cho dữ liệu lớn được sắp xếp) là then chốt để tối ưu hóa các tính năng nâng cao của Postgres.
Cũng có giá trị tương đương là index partial (chỉ index các row liên quan — nhỏ hơn và hiệu quả hơn khi bạn query một tập con, như các user active) và index expression (index các giá trị được tính — cho phép tra cứu dựa trên hàm nhanh như tìm kiếm email không phân biệt hoa thường, điều mà một index thường không hỗ trợ được).
Vì lựa chọn index ảnh hưởng đáng kể đến hiệu năng query, và vì các loại index chuyên biệt của Postgres (đặc biệt GIN cho JSONB/array/full-text) là thứ làm cho các kiểu dữ liệu nâng cao của nó trở nên hiệu năng, nên hiểu các loại index và khi nào dùng mỗi loại — bao gồm index partial và expression — là kiến thức có giá trị, liên quan thường xuyên để tối ưu hóa các cơ sở dữ liệu PostgreSQL, phân biệt các developer có thể làm cho Postgres nhanh cho các pattern query đa dạng và là một khía cạnh then chốt của việc tận dụng đầy đủ khả năng hiệu năng của cơ sở dữ liệu.