GIN, GiST và BRIN trong PostgreSQL: chọn index nào? Trung cấp

🎮 Thử tay

• Chọn loại index: GIN cho jsonb và LIKE, BRIN cho bảng append-only.

Tóm tắt nhanh: toán tử nào cần index nào

B-tree chỉ trả lời câu hỏi so sánh thứ tự (<, <=, =, >=, >, xem B-tree index). Phần còn lại cần access method khác. Chọn theo toán tử trong WHERE:

Bạn cần	Toán tử / hàm	Index	Ghi chú
=, <, BETWEEN, ORDER BY	= < > BETWEEN	B-tree	Mặc định cho phần lớn trường hợp
Mảng, JSONB chứa phần tử	@>, <@, &&	GIN	JSONB: jsonb_ops hoặc jsonb_path_ops
Full-text search	@@ trên tsvector	GIN	Index cột tsvector
LIKE '%x%', tương tự	LIKE, ILIKE, %	GIN + pg_trgm	gin_trgm_ops
Range/hình học giao nhau	&&, @>, <@	GiST	Cũng dùng cho exclusion constraint
Gần nhất (kNN)	ORDER BY col <-> x LIMIT n	GiST (hoặc SP-GiST)	Tuỳ operator class
Bảng khổng lồ, cột tương quan với thứ tự ghi	<, <=, =, >=, >	BRIN	Chỉ khi correlation gần ±1
Chỉ equality	=	Hash	Hiếm khi đáng thay B-tree

Đang tải diagram…

Ba loại index lưu gì

Index	Lưu gì	Recheck?
GIN	Một entry cho từng thành phần bên trong giá trị (mỗi tag, mỗi key JSON, mỗi từ)	Tuỳ operator class
GiST	Cây tổng quát, mỗi node có “vùng bao” theo operator class	Tuỳ operator class
BRIN	min/max tóm tắt cho mỗi nhóm page vật lý liền nhau	Luôn (lossy)

Tài liệu PostgreSQL mô tả BRIN là lossy: executor luôn kiểm tra lại dòng. GIN và GiST chỉ cần recheck với một số operator class. Ngay B-tree cũng có thể thành lossy trong bitmap scan khi thiếu work_mem.

Cách hoạt động

1. GIN: tách giá trị composite thành từng thành phần

Phép tìm tag rare (100/100.000 dòng) không index phải quét toàn bảng:

Seq Scan on search_demo  (cost=0.00..2439.12 rows=482 width=4) (actual time=0.139..6.438 rows=100 loops=1)
  Filter: (tags @> '{rare}'::text[])
  Rows Removed by Filter: 99900
Execution Time: 6.453 ms

Sau CREATE INDEX ... USING GIN (tags):

Bitmap Heap Scan on search_demo  (cost=46.06..300.25 rows=80 width=4) (actual time=0.025..0.159 rows=100 loops=1)
  Recheck Cond: (tags @> '{rare}'::text[])
  Heap Blocks: exact=100
  ->  Bitmap Index Scan on search_demo_tags_gin
        Index Cond: (tags @> '{rare}'::text[])
Execution Time: 0.181 ms

6.453 ms thành 0.181 ms, nhanh hơn 36 lần. GIN là inverted index: mỗi phần tử có danh sách dòng chứa nó, tìm rare là tra thẳng danh sách đó.

backend -> [1, 2, 3, ..., 100000]     (mọi dòng)
general -> [1, 2, 3, ...]             (phần lớn dòng)
rare    -> [1000, 2000, 3000, ...]    (100 dòng)

Hai ví dụ GIN khác, đo thật trên 200.000 dòng (một lần chạy, bảng thật):

Ứng dụng	Không index	Có GIN
Full-text search: tsv @@ to_tsquery('simple','postgresql') (40 dòng khớp)	9.488 ms (Seq Scan)	0.082 ms (Bitmap)
LIKE '%số 12345 %' với pg_trgm (gin_trgm_ops, 1 dòng khớp)	15.433 ms (Seq Scan)	0.980 ms (Bitmap)

LIKE '%x%' là chỗ B-tree bó tay (xem B-tree index); pg_trgm giải quyết bằng cách index từng bộ ba ký tự.

JSONB có hai operator class:

Operator class	Hỗ trợ	Kích thước (200.000 doc)
jsonb_ops (mặc định)	@>, ?, ?|, ?& (và jsonpath @?, @@)	3.144 kB
jsonb_path_ops	chỉ @> (và jsonpath @?, @@)	2.488 kB

Chỉ cần @> thì jsonb_path_ops nhỏ và nhanh hơn.

Chi phí ghi của GIN (theo tài liệu): mặc định có fastupdate, các entry mới vào pending list rồi gộp hàng loạt khi VACUUM/autoanalyze chạy hoặc khi vượt gin_pending_list_limit. Hệ quả:

• Tìm kiếm phải quét thêm pending list.
• Một lần INSERT bất kỳ có thể bị chậm đột ngột vì phải dọn danh sách. Nếu cần latency ổn định: WITH (fastupdate = off), hoặc để autovacuum dọn nền.
• Tạo index GIN rất nhạy với maintenance_work_mem; nạp bulk thì nên tạo index sau khi nạp.

GIN không dùng được cho ORDER BY: kết quả không trả theo thứ tự index.

2. GiST: cây có vùng bao theo operator class

Tìm các khoảng int4range giao với [42000, 42010), không index:

Seq Scan on search_demo  (cost=0.00..2485.00 rows=21 width=4) (actual time=2.348..5.085 rows=19 loops=1)
  Filter: (during && '[42000,42010)'::int4range)
Execution Time: 5.088 ms

Với CREATE INDEX ... USING GiST (during):

Bitmap Heap Scan on search_demo  (cost=4.44..77.05 rows=20 width=4) (actual time=0.034..0.036 rows=19 loops=1)
  Recheck Cond: (during && '[42000,42010)'::int4range)
  ->  Bitmap Index Scan on search_demo_during_gist
        Index Cond: (during && '[42000,42010)'::int4range)
Execution Time: 0.053 ms

5.088 ms thành 0.053 ms, nhanh hơn khoảng 96 lần. Operator class của range dạy GiST cách tính vùng bao của một nhóm khoảng và kiểm tra hai vùng có giao nhau không, nên loại cả nhánh không thể chứa kết quả.

GiST còn hai ứng dụng senior hay dùng:

• kNN: ORDER BY location <-> point '(x,y)' LIMIT 10 lấy 10 điểm gần nhất thẳng từ index (tuỳ operator class).
• Exclusion constraint: cấm dữ liệu chồng lấn ở tầng database. Ví dụ đặt phòng không được trùng lịch (cần btree_gist để dùng = cho cột room):

CREATE TABLE booking (
  room int NOT NULL,
  during tstzrange NOT NULL,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);

Chèn [09:30, 10:30) vào phòng 1 đã có [09:00, 10:00) thì thất bại, còn phòng 2 vẫn chèn được:

ERROR:  conflicting key value violates exclusion constraint "booking_room_during_excl"
DETAIL:  Key (room, during)=(1, ["2026-10-20 09:30:00+00","2026-10-20 10:30:00+00")) conflicts with existing key (room, during)=(1, ["2026-10-20 09:00:00+00","2026-10-20 10:00:00+00")).

Đây là cách chống double booking đúng chuẩn, race-free, mà UNIQUE không làm được.

3. BRIN: chỉ tốt khi dữ liệu tương quan với thứ tự lưu vật lý

BRIN là loại index duy nhất mà hiệu quả phụ thuộc vào cách dữ liệu được ghi, không chỉ cấu trúc index.

BRIN: summary min/max loại được range khi dữ liệu tuần tự, không loại được khi xáo trộn

Phóng to sơ đồ

Range 1 · Range 2 · Range 3 · Range 1 · Range 2 · Range 3

◀ Trước▶ PhátTiếp ▶↺ Từ đầuBước 1/3

BRIN chỉ nhớ min/max của mỗi nhóm page. Hàng trên: dữ liệu ghi tuần tự. Hàng dưới: cùng dữ liệu nhưng bị xáo trộn vật lý.

Tham số quan trọng: pages_per_range. Demo gốc ở dưới dùng pages_per_range = 16, không phải mặc định. Mặc định là 128 page (1 MB) mỗi range. Số nhỏ hơn cho summary mịn hơn và index lớn hơn.

Đo trên bảng thật 1.000.000 dòng (143 MB), created_at tăng dần, query 10 phút (600 dòng):

Cấu hình	Kích thước index	Block đọc từ heap	Dòng recheck bị loại	Thời gian
BRIN mặc định (pages_per_range = 128)	24 kB	128 (lossy)	6.440	0.28 ms
BRIN pages_per_range = 16	48 kB	32 (lossy)	1.160	0.14 ms

Số Heap Blocks: lossy=128 khớp đúng pages_per_range mặc định. Các số 16 và 24 kB trong demo dưới đây phụ thuộc cấu hình đó, nếu bạn copy với mặc định sẽ ra số khác.

Demo gốc (100.000 dòng, pages_per_range = 16). Bảng có created_at tăng dần đúng thứ tự insert:

Bitmap Heap Scan on search_demo  (cost=12.16..1314.23 rows=611 width=4) (actual time=0.033..0.071 rows=600 loops=1)
  Recheck Cond: ((created_at >= '2026-01-02 02:00:00+00') AND (created_at < '2026-01-02 02:10:00+00'))
  Heap Blocks: lossy=16
  ->  Bitmap Index Scan on search_demo_created_brin
Execution Time: 0.092 ms

Cùng dữ liệu nhưng thứ tự vật lý bị xáo trộn (ORDER BY random() khi copy), cùng BRIN:

Seq Scan on search_demo_shuffled  (cost=0.00..2780.00 rows=599 width=4) (actual time=0.006..2.891 rows=600 loops=1)
  Filter: ((created_at >= '2026-01-02 02:00:00+00') AND (created_at < '2026-01-02 02:10:00+00'))
  Rows Removed by Filter: 99400
Execution Time: 2.909 ms

Cả hai index đều 24 kB và hoàn toàn bình thường, nhưng 0.092 ms so với 2.909 ms, chậm hơn 31 lần, chỉ vì thứ tự vật lý. Trên bảng 1.000.000 dòng xáo trộn, planner vẫn dùng BRIN nhưng phải đọc 18.192 block, tức cả bảng, loại 999.400 dòng ở bước recheck, tổng 89 ms.

Chẩn đoán: pg_stats.correlation

Trước khi tạo BRIN, và định kỳ sau đó, kiểm tra:

SELECT correlation FROM pg_stats WHERE tablename = 'log_demo' AND attname = 'created_at';

correlation gần +1 hoặc -1 là tốt cho BRIN; gần 0 thì BRIN vô dụng. Đo thật:

Tình huống	correlation
Append-only theo thời gian	1
Xáo trộn hoàn toàn	0.008
Xoá 1/3 dòng rải đều, VACUUM, rồi chèn dòng mới (backfill vào chỗ trống)	0.547

Kẻ phá correlation chính không phải UPDATE mà là DELETE rồi VACUUM: dòng mới lấp vào chỗ trống ở giữa bảng. Backfill dữ liệu cũ và insert song song theo lô cũng tương tự. Với bảng retention bằng DELETE rải rác, partitioning theo thời gian thường là giải pháp sạch hơn.

Vận hành BRIN: range mới chưa có summary cho tới khi VACUUM chạy; có thể bật autosummarize (mặc định tắt) hoặc gọi brin_summarize_new_values(). Ngoài minmax, còn operator class minmax-multi, inclusion, bloom cho dữ liệu kém tương quan hơn.

Khi nào dùng / không dùng

Index	Dùng khi	Không dùng khi
GIN	Tìm “chứa phần tử” trên mảng/JSONB/tsvector, LIKE '%x%' với trigram	Ghi cực nhiều và cần latency ổn định (cân nhắc fastupdate = off); cần ORDER BY
GiST	Range/hình học giao nhau, kNN, exclusion constraint	Chỉ so sánh =/<: B-tree nhanh hơn và gọn hơn
BRIN	Bảng log khổng lồ, append-only, correlation gần ±1	Dữ liệu bị xáo trộn, update/delete/backfill nhiều

Ví dụ thực tế

access_logs 50 triệu dòng/tháng, chỉ insert

BRIN trên created_at tốn vài chục kB thay vì hàng trăm MB của B-tree và vẫn lọc tốt theo khoảng thời gian, vì thứ tự insert tự nhiên tương quan với thời gian.

Nếu sau này có job “sửa lại log cũ” hoặc xoá rải rác, tương quan mất dần mà không có cảnh báo. Lên lịch kiểm tra pg_stats.correlation hằng tuần; nếu tụt dưới khoảng 0.9, cân nhắc partition theo ngày và DROP partition cũ thay cho DELETE.

Trade-offs

Ưu điểm

• GIN nhanh hơn 36 lần trong demo cho containment, hơn 100 lần cho full-text, và là giải pháp cho LIKE '%x%'
• GiST nhanh hơn khoảng 96 lần cho range overlap, hỗ trợ kNN và exclusion constraint
• BRIN rất nhỏ (24 kB cho 1 triệu dòng mặc định), rẻ hơn B-tree hàng trăm lần khi tương quan tốt

Nhược điểm

• GIN tốn chi phí ghi (pending list, cleanup đột ngột) và tạo chậm nếu thiếu maintenance_work_mem
• GiST chỉ hỗ trợ toán tử mà operator class định nghĩa
• BRIN mất tác dụng khi correlation giảm, không có cảnh báo, chỉ thấy query chậm dần

Sai lầm thường gặp

Sai lầm thường gặp

Tạo BRIN cho cột timestamp chỉ vì bảng lớn. Kiểm tra pg_stats.correlation trước, và tiếp tục theo dõi sau khi có DELETE hoặc backfill. Đừng copy số “24 kB” hay “lossy=16” từ demo mà quên rằng chúng phụ thuộc pages_per_range.

Checklist trước khi tạo GIN, GiST hoặc BRIN

1. Xác định toán tử trong WHERE rồi tra bảng tóm tắt ở đầu bài; so sánh thứ tự thì B-tree vẫn là lựa chọn đầu.
2. GIN trên JSONB chỉ dùng @>: chọn jsonb_path_ops (nhỏ hơn) thay cho jsonb_ops mặc định.
3. GIN trên bảng ghi nhiều cần latency ổn định: cân nhắc fastupdate = off; nạp bulk thì tạo index sau khi nạp.
4. Cần chống chồng lấn (đặt phòng, lịch): dùng EXCLUDE USING gist (kèm btree_gist cho cột =), không kiểm tra trong ứng dụng.
5. BRIN: kiểm tra pg_stats.correlation gần ±1 trước khi tạo, và theo dõi định kỳ sau DELETE hoặc backfill.
6. BRIN: chọn pages_per_range có chủ đích (mặc định 128) và xử lý range chưa summary bằng autosummarize hoặc brin_summarize_new_values().

Tóm tắt

Tóm tắt

• Chọn index theo toán tử trong WHERE: so sánh thứ tự là B-tree, chứa phần tử là GIN, giao nhau và kNN là GiST, bảng log khổng lồ tương quan là BRIN
• GIN: inverted index, nhanh 36 lần (array), hơn 100 lần (full-text) trong demo; nhớ pending list và fastupdate
• GiST: nhanh khoảng 96 lần cho range overlap, và là nền của EXCLUDE USING gist chống chồng lịch
• BRIN: demo dùng pages_per_range = 16, mặc định là 128; kiểm tra bằng pg_stats.correlation, gần ±1 mới đáng dùng
• DELETE rồi VACUUM và backfill phá correlation: trong demo từ 1 xuống 0.547

Tự kiểm tra

1. Với WHERE tags @> ARRAY['rare'] và WHERE created_at BETWEEN ... trên bảng 1 tỷ dòng log, chọn index nào cho từng query?
2. Vì sao Heap Blocks: lossy=128 trùng với pages_per_range mặc định?
3. Làm sao biết một BRIN đang dần mất tác dụng?
4. Vì sao exclusion constraint với GiST chống double booking tốt hơn kiểm tra trong ứng dụng?

Tham khảo

• PostgreSQL 17 — Index Types
• PostgreSQL 17 — GIN Indexes
• PostgreSQL 17 — GiST Indexes
• PostgreSQL 17 — BRIN Indexes
• PostgreSQL 17 — pg_trgm