Chỉ mục B-tree, GIN và GiST: Hướng dẫn thực tiễn cho PostgreSQL

Bạn thực sự đang chọn gì khi chọn chỉ mục

Đa số vấn đề về chỉ mục trong PostgreSQL bắt đầu giống nhau: một màn hình danh sách cảm giác nhanh với 1.000 hàng, rồi chậm với 1.000.000. Hoặc một ô tìm kiếm chạy ổn trong thử nghiệm nhưng thành pause vài giây ở production. Khi đó, dễ bị cám dỗ hỏi “Chỉ mục nào tốt nhất?” Câu hỏi hay hơn là: “Màn hình này yêu cầu cơ sở dữ liệu làm gì?”

Cùng một bảng có thể cần các loại chỉ mục khác nhau vì các màn hình đọc dữ liệu khác nhau. Một view lọc theo một status và sắp xếp theo created_at. Một view khác làm tìm kiếm toàn văn. Một view khác kiểm tra xem một trường JSON có chứa key không. Một view khác tìm các mục gần một điểm trên bản đồ. Những mẫu truy cập khác nhau như vậy khiến không có một loại chỉ mục thắng tuyệt đối ở mọi nơi.

Đó là điều bạn đang chọn khi chọn chỉ mục: cách app truy cập dữ liệu. Bạn chủ yếu làm các phép so sánh chính xác, các khoảng và sắp xếp? Bạn đang tìm kiếm bên trong tài liệu hoặc mảng? Bạn hỏi “cái này gần vị trí này không” hay “cái này chồng lấn khoảng này”? Câu trả lời quyết định B-tree, GIN hay GiST là phù hợp.

B-tree, GIN và GiST nói đơn giản

Chọn chỉ mục ít liên quan đến kiểu cột mà nhiều hơn liên quan đến truy vấn làm gì với cột đó. PostgreSQL chọn chỉ mục dựa vào các toán tử như =, <, @> hoặc @@, chứ không phải vì cột là “text” hay “json”. Đó là lý do cùng một trường có thể cần các chỉ mục khác nhau ở các màn hình khác nhau.

B-tree: nhanh cho lookup theo thứ tự

B-tree là mặc định và lựa chọn phổ biến nhất. Nó tỏa sáng khi bạn lọc theo giá trị chính xác, lọc theo khoảng, hoặc cần kết quả theo một thứ tự cụ thể.

Ví dụ thường thấy là một danh sách admin lọc theo status và sắp xếp theo created_at. Một chỉ mục B-tree trên (status, created_at) có thể hỗ trợ cả lọc lẫn sắp xếp. B-tree cũng là công cụ thông thường cho ràng buộc unique.

GIN: nhanh khi mỗi hàng chứa nhiều khóa có thể tìm kiếm

GIN được thiết kế cho các câu hỏi kiểu “hàng này có chứa term/giá trị này không?”, nơi một hàng có thể khớp nhiều khóa. Ví dụ phổ biến là tìm kiếm toàn văn (một tài liệu chứa nhiều từ) và membership của JSONB/mảng (JSON chứa key/giá trị).

Hãy tưởng tượng hồ sơ khách hàng với preferences kiểu JSONB, và một màn hình lọc người dùng nơi preferences chứa {\"newsletter\": true}. Đó là kiểu lookup phù hợp với GIN.

GiST: linh hoạt cho range, geo và tương đồng

GiST là khung tổng quát dùng cho các kiểu dữ liệu không phù hợp với thứ tự đơn giản. Nó hợp tự nhiên cho ranges (chồng lấn, chứa), truy vấn hình học và địa lý (gần, trong), và một số tìm kiếm tương đồng.

Khi quyết giữa B-tree vs GIN vs GiST, hãy bắt đầu bằng cách liệt kê các toán tử màn hình bận rộn của bạn dùng. Chỉ mục đúng thường rõ ràng sau khi làm việc đó.

Bảng quyết định cho các màn hình phổ biến (lọc, tìm kiếm, JSON, geo)

Hầu hết app chỉ cần vài mẫu chỉ mục. Mẹo là khớp hành vi màn hình với các toán tử truy vấn.

Hai chỉ mục có thể cùng tồn tại khi các endpoint khác nhau. Ví dụ, một danh sách admin có thể cần B-tree trên (tenant_id, created_at) cho sắp xếp nhanh, trong khi trang tìm kiếm cần GIN cho @@. Giữ cả hai chỉ khi cả hai kiểu truy vấn đều phổ biến.

Nếu không chắc, hãy nhìn vào toán tử trước. Chỉ mục giúp khi database có thể bỏ qua phần lớn bảng.

Lọc và sắp xếp: nơi B-tree thường thắng

Với hầu hết màn hình hàng ngày, B-tree là lựa chọn nhàm nhưng hiệu quả. Nếu truy vấn của bạn trông như “chọn các hàng nơi một cột bằng một giá trị, có thể sắp xếp, rồi hiển thị trang 1,” B-tree thường là điều cần thử trước.

Lọc bằng equality là trường hợp kinh điển. Các cột như status, user_id, account_id, type, hoặc tenant_id xuất hiện liên tục trong dashboard và bảng admin. Một chỉ mục B-tree có thể nhảy thẳng tới các giá trị khớp.

Lọc theo khoảng cũng phù hợp B-tree. Khi bạn lọc theo thời gian hoặc khoảng số, cấu trúc có thứ tự giúp: created_at >= ..., price BETWEEN ..., id > .... Nếu UI của bạn có “7 ngày qua” hoặc “$50 đến $100”, B-tree đang làm chính xác việc bạn muốn.

Sắp xếp và phân trang là nơi B-tree có thể tiết kiệm nhiều công sức. Nếu thứ tự trong chỉ mục khớp ORDER BY, PostgreSQL thường trả hàng đã sắp xếp thay vì sắp xếp một tập lớn trong bộ nhớ.

-- A common screen: "My open tickets, newest first"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Chỉ mục composite tuân theo quy tắc đơn giản: PostgreSQL chỉ có thể dùng phần dẫn đầu của chỉ mục một cách hiệu quả. Nghĩ “từ trái sang phải.” Với (user_id, status, created_at), các truy vấn lọc theo user_id (và có thể status) được hưởng lợi. Một truy vấn chỉ lọc theo status thường sẽ không.

Partial index là nâng cấp mạnh khi màn hình của bạn chỉ quan tâm một lát cắt dữ liệu. Các lát cắt phổ biến là “chỉ hàng active,” “không soft-deleted,” hoặc “hoạt động gần đây.” Chúng giữ chỉ mục nhỏ hơn và nhanh hơn.

Cột high-cardinality và chi phí của chỉ mục thêm

Cột high-cardinality có nhiều giá trị duy nhất, như user_id, order_id, email, hay created_at đến giây. Chỉ mục thường tỏa sáng ở đây vì một bộ lọc có thể thu hẹp kết quả xuống rất ít hàng.

Cột low-cardinality thì ngược lại: boolean và enum nhỏ như is_active, status IN ('open','closed'), hoặc plan IN ('free','pro'). Một chỉ mục trên những cột này thường gây thất vọng vì mỗi giá trị khớp nhiều hàng; PostgreSQL có thể chọn quét tuần tự vì nhảy qua chỉ mục vẫn phải đọc nhiều page bảng.

Một chi phí tinh tế khác là việc fetch hàng. Ngay cả khi chỉ mục tìm ID nhanh, database vẫn có thể phải truy cập bảng để lấy các cột còn lại. Nếu truy vấn chỉ cần vài trường, một covering index có thể giúp, nhưng nó cũng làm chỉ mục lớn hơn và đắt để duy trì.

Mỗi chỉ mục thêm đều có giá trị ghi. Insert phải ghi vào mỗi chỉ mục. Update thay đổi cột có chỉ mục phải cập nhật mục tương ứng. Thêm chỉ mục “phòng khi cần” có thể làm chậm toàn bộ app, không chỉ một màn hình.

Hướng dẫn thực tế:

• Bắt đầu với 1–2 chỉ mục chính cho mỗi bảng bận rộn, dựa trên các bộ lọc và sắp xếp thực tế.
• Ưu tiên cột high-cardinality dùng trong WHERE và ORDER BY.
• Thận trọng khi lập chỉ mục boolean và enum nhỏ trừ khi kết hợp với cột chọn lọc khác.
• Thêm chỉ mục mới chỉ sau khi bạn có thể nêu chính xác truy vấn nó sẽ tăng tốc.

Ví dụ: danh sách ticket hỗ trợ lọc theo assignee_id (high-cardinality) hưởng lợi từ chỉ mục, trong khi is_archived = false đứng một mình thường không.

Màn hình tìm kiếm: toàn văn, tiền tố và “chứa”

Ô tìm kiếm trông đơn giản, nhưng người dùng mong đợi nhiều: nhiều từ, các biến thể từ, và xếp hạng hợp lý. Trong PostgreSQL, đó thường là tìm kiếm toàn văn: bạn lưu tsvector (văn bản đã chuẩn bị) và truy vấn bằng tsquery (những gì người dùng gõ, được parse thành các term).

Cho tìm kiếm toàn văn, GIN là mặc định phổ biến vì nhanh khi trả lời “tài liệu này có chứa các term này không?” trên nhiều hàng. Đổi lại là ghi nặng hơn: insert và update hàng thường tốn hơn.

GiST cũng có thể dùng cho tìm kiếm toàn văn. Nó thường nhỏ hơn và rẻ để cập nhật, nhưng đọc thường chậm hơn GIN. Nếu dữ liệu thay đổi liên tục (ví dụ bảng kiểu event), cân bằng đọc-ghi này có thể quan trọng.

Tìm kiếm tiền tố không phải là toàn văn

Tìm kiếm tiền tố nghĩa là “bắt đầu bằng”, như tìm khách hàng theo tiền tố email. Đó không phải là mục tiêu của tìm kiếm toàn văn. Với mẫu tiền tố, một chỉ mục B-tree có thể giúp (thường với operator class phù hợp) vì khớp với cách chuỗi được sắp xếp.

Với tìm “chứa” như ILIKE '%error%', B-tree thường không giúp. Đây là lúc cần chỉ mục trigram hoặc phương án tìm kiếm khác.

Khi người dùng muốn vừa lọc vừa tìm văn bản

Hầu hết màn hình thực tế kết hợp tìm kiếm với bộ lọc: status, assignee, khoảng ngày, tenant, v.v. Thiết lập thực tế là:

• Một chỉ mục GIN (hoặc đôi khi GiST) cho cột tsvector.
• Các chỉ mục B-tree cho các bộ lọc chọn lọc nhất (ví dụ account_id, status, created_at).
• Quy tắc “giữ tối thiểu” nghiêm ngặt, vì quá nhiều chỉ mục làm ghi chậm.

Ví dụ: màn hình ticket hỗ trợ tìm “refund delayed” và lọc status = 'open' và một account_id cụ thể. Tìm kiếm toàn văn đưa bạn tới các hàng phù hợp, trong khi B-tree giúp PostgreSQL thu hẹp tới account và status nhanh.

Trường JSONB: chọn giữa GIN và chỉ mục B-tree có mục tiêu

JSONB tuyệt vời cho dữ liệu linh hoạt, nhưng có thể làm truy vấn chậm nếu bạn xử lý nó như một cột bình thường. Quyết định cốt lõi đơn giản: bạn tìm “bất cứ đâu trong JSON này” hay bạn lọc theo vài đường dẫn cụ thể lặp đi lặp lại?

Với truy vấn containment như metadata @> '{"plan":"pro"}', GIN thường là lựa chọn đầu tiên. Nó được xây dựng cho “tài liệu này có chứa hình dạng này không?” và cũng hỗ trợ kiểm tra tồn tại key như ?, ?|, và ?&.

Nếu app của bạn chủ yếu lọc theo một hoặc hai trường JSON, một chỉ mục biểu thức B-tree có mục tiêu thường nhanh hơn và nhỏ hơn. Nó cũng hữu ích khi bạn cần sắp xếp hoặc so sánh số trên các giá trị trích xuất.

-- Broad support for containment and key checks
CREATE INDEX ON customers USING GIN (metadata);

-- Targeted filters and sorting on one JSON path
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

Quy tắc ngón tay cái:

• Dùng GIN khi người dùng tìm nhiều khóa, tag hoặc cấu trúc lồng nhau.
• Dùng chỉ mục biểu thức B-tree khi người dùng lọc trên các đường dẫn cụ thể thường xuyên.
• Lập chỉ mục những gì xuất hiện trên màn hình thực tế, không phải mọi thứ.
• Nếu hiệu năng phụ thuộc vài key JSON bạn luôn dùng, hãy cân nhắc nâng chúng thành cột thực.

Ví dụ: màn hình hỗ trợ có thể lọc ticket theo metadata->>'priority' và sắp xếp theo created_at. Hãy tạo chỉ mục cho đường dẫn JSON priority và cột created_at bình thường. Bỏ qua GIN rộng trừ khi người dùng cũng tìm tag hoặc thuộc tính lồng nhau.

Geo và truy vấn range: nơi GiST phù hợp nhất

Các màn hình geo và range là nơi GiST thường trở nên rõ ràng. GiST được xây dựng cho “cái này có chồng lấn, chứa, hay nằm gần cái kia không?” hơn là “giá trị này có bằng giá trị kia không?”

Dữ liệu địa lý thường là điểm (vị trí cửa hàng), đường (route) hoặc polygon (vùng giao hàng). Màn hình phổ biến gồm “cửa hàng gần tôi”, “việc trong bán kính 10 km”, “hiển thị các mục trong hộp bản đồ này”, hoặc “địa chỉ này có nằm trong khu vực phục vụ không?”. Chỉ mục GiST (thường qua PostGIS geometry/geography) tăng tốc các toán tử không gian để database có thể bỏ qua hầu hết hàng thay vì kiểm tra từng hình.

Ranges cũng tương tự. PostgreSQL có các kiểu range như daterange và int4range, và câu hỏi điển hình là chồng lấn: “đặt chỗ này có đụng với đặt chỗ khác không?” hoặc “hiển thị subscription hoạt động trong tuần này.” GiST hỗ trợ toán tử chồng lấn và containment hiệu quả, đó là lý do nó phổ biến trong lịch, lập lịch và kiểm tra khả dụng.

B-tree vẫn có vai trò trên các màn hình dạng geo. Nhiều trang trước tiên lọc theo tenant, status hoặc thời gian, rồi áp điều kiện không gian, rồi sắp xếp. Ví dụ: “chỉ giao hàng của công ty tôi, 7 ngày gần nhất, gần nhất trước.” GiST xử lý phần không gian, còn B-tree giúp các bộ lọc chọn lọc và sắp xếp.

Cách chọn chỉ mục từng bước

Việc chọn chỉ mục chủ yếu về toán tử, không phải tên cột. Cùng một cột có thể cần các chỉ mục khác nhau tùy bạn dùng =, >, LIKE 'prefix%', tìm toàn văn, containment JSON hay khoảng cách geo.

Đọc truy vấn như một checklist: WHERE quyết định hàng nào đủ điều kiện, JOIN quyết định cách bảng nối, ORDER BY quyết định thứ tự xuất, và LIMIT quyết định bạn chỉ cần bao nhiêu hàng. Chỉ mục tốt nhất thường là cái giúp bạn tìm 20 hàng đầu nhanh.

Quy trình đơn giản phù hợp hầu hết màn hình:

1. Ghi ra chính xác các toán tử màn hình dùng (ví dụ: status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. Bắt đầu với chỉ mục khớp bộ lọc chọn lọc nhất hoặc sort mặc định. Nếu màn hình sắp xếp theo created_at DESC, bắt đầu với điều đó.
3. Thêm chỉ mục composite chỉ khi bạn thấy cùng các bộ lọc xuất hiện cùng nhau thường xuyên. Đặt các cột equality trước, sau đó cột range, rồi key sắp xếp.
4. Dùng partial index khi bạn luôn lọc cho một tập con (ví dụ: chỉ status = 'open'). Dùng expression index khi bạn truy vấn giá trị tính toán (ví dụ: lower(email) cho lookup không phân biệt hoa thường).
5. Xác thực bằng EXPLAIN ANALYZE. Giữ chỉ mục nếu nó giảm đáng kể thời gian thực thi và số hàng đọc.

Ví dụ cụ thể: dashboard hỗ trợ lọc ticket theo status và sắp xếp theo mới nhất. Một B-tree trên (status, created_at DESC) là thử đầu mạnh. Nếu cùng màn hình cũng lọc theo flag JSONB như meta @> '{"vip": true}', đó là toán tử khác và thường cần chỉ mục tập trung vào JSON.

Lỗi phổ biến lãng phí thời gian (và làm chậm ghi)

Cách phổ biến để thất vọng là chọn “loại chỉ mục đúng” cho toán tử sai. PostgreSQL chỉ dùng chỉ mục khi truy vấn khớp với thứ mà chỉ mục được xây để trả lời. Nếu app dùng ILIKE '%term%', một B-tree bình thường trên cột text đó sẽ không dùng, và bạn vẫn sẽ quét bảng.

Bẫy khác là xây chỉ mục đa cột khổng lồ “phòng khi” thật nhiều. Chúng trông an toàn nhưng đắt để duy trì và thường không khớp pattern truy vấn thực tế. Nếu các cột dẫn đầu không được dùng trong filter, phần còn lại của chỉ mục có thể không giúp.

Index trên cột low-selectivity cũng dễ bị over-index. B-tree trên boolean như is_active hoặc status có vài giá trị có thể gần như vô dụng trừ khi bạn làm partial index khớp đúng điều bạn query.

JSONB có những cạm bẫy riêng. Một GIN rộng có thể tuyệt cho lọc linh hoạt, nhưng nhiều kiểm tra đường dẫn JSON nhanh hơn với expression index trên giá trị trích xuất. Nếu màn hình luôn lọc payload->>'customer_id', index biểu thức cho đường dẫn đó thường nhỏ hơn và nhanh hơn indexing toàn bộ document.

Cuối cùng, mỗi chỉ mục thêm tăng chi phí ghi. Trên bảng update/insert nhiều (như tickets hoặc orders), mỗi thao tác phải cập nhật mọi chỉ mục.

Trước khi thêm chỉ mục, dừng lại và kiểm tra:

• Chỉ mục có khớp đúng toán tử truy vấn không?
• Bạn có thể thay một chỉ mục đa cột rộng bằng một hoặc hai chỉ mục tập trung không?
• Có nên làm partial index để tránh nhiễu từ low-selectivity không?
• Với JSONB, chỉ mục biểu thức có phù hợp màn hình hơn không?
• Bảng có đủ nhiều ghi để chi phí chỉ mục vượt lợi ích đọc không?

Kiểm tra nhanh trước khi thêm (hoặc giữ) một chỉ mục

Trước khi tạo chỉ mục mới, hãy cụ thể về những gì app thực sự làm. Một chỉ mục “hay có” thường biến thành ghi chậm hơn và tốn storage với lợi ích nhỏ.

Bắt đầu với ba màn hình (hoặc endpoint) hàng đầu và ghi chính xác hình dạng truy vấn: bộ lọc, thứ tự sắp xếp, và những gì người dùng gõ. Nhiều “vấn đề chỉ mục” thực ra là “vấn đề truy vấn không rõ ràng”, đặc biệt khi mọi người tranh luận B-tree vs GIN vs GiST mà không nêu toán tử.

Checklist đơn giản:

• Chọn 3 màn hình thực và liệt kê WHERE và ORDER BY chính xác (bao gồm hướng và xử lý NULL).
• Xác nhận loại toán tử: equality (=), range (>, BETWEEN), prefix, contains, overlap, hay distance.
• Chọn một chỉ mục cho mỗi mẫu màn hình phổ biến, test, và chỉ giữ những cái giảm rõ rệt thời gian hoặc số hàng đọc.
• Nếu bảng nhiều ghi, khắt khe: chỉ mục thêm nhân chi phí ghi và có thể tăng áp lực vacuum.
• Kiểm tra lại sau khi thay đổi tính năng. Một bộ lọc mới, sort mặc định mới, hoặc chuyển từ “starts with” sang “contains” có thể làm chỉ mục cũ không còn phù hợp.

Ví dụ: dashboard thêm sort mặc định last_activity DESC. Nếu bạn chỉ lập chỉ mục status, bộ lọc có thể vẫn nhanh, nhưng sort giờ buộc phải làm thêm công.

Ví dụ: ánh xạ màn hình thực tế sang chỉ mục phù hợp

Bảng quyết định chỉ có ích khi bạn gán nó cho màn hình thực bạn ship. Dưới đây là ba màn hình phổ biến và cách chúng khớp với lựa chọn chỉ mục.

Cách thực tế áp dụng là bắt đầu từ UI:

• Liệt kê mọi control thu hẹp kết quả (bộ lọc).
• Ghi lại sort mặc định.
• Cụ thể hóa hành vi tìm kiếm (toàn văn vs starts-with vs contains).
• Ghi các trường “đặc biệt” (JSONB, geo, ranges).

Bước tiếp theo: biến việc lập chỉ mục thành một phần trong quy trình phát triển

Chỉ mục tốt theo sát màn hình: các bộ lọc người dùng click, thứ tự sắp xếp họ mong đợi, và ô tìm kiếm họ thực sự dùng. Hãy coi lập chỉ mục là một thói quen khi phát triển và bạn sẽ tránh được hầu hết bất ngờ về hiệu năng sau này.

Giữ nó lặp được: xác định 1–3 truy vấn một màn hình chạy, thêm chỉ mục nhỏ nhất khớp chúng, test với dữ liệu thực tế, rồi bỏ những thứ không mang lại lợi ích.

Nếu bạn xây công cụ nội bộ hoặc portal khách hàng, lên kế hoạch sớm cho chỉ mục vì những app này thường lớn dần bằng cách thêm bộ lọc và màn hình danh sách. Nếu bạn xây với AppMaster (appmaster.io), việc coi cấu hình bộ lọc và sắp xếp mỗi màn hình như một pattern truy vấn cụ thể rồi chỉ thêm các chỉ mục khớp những click thực tế đó sẽ rất hữu ích.