Index trong PostgreSQL: B-tree, GIN và khi nào dùng

1. Tổng quan

PostgreSQL hỗ trợ nhiều loại index, nhưng B-tree và GIN là hai loại bạn gặp thường xuyên nhất trong công việc hàng ngày. Chọn sai loại index khiến query chậm hoặc index tốn dung lượng mà không giúp được gì.

Bài này giải thích cách B-tree và GIN hoạt động ở mức đủ dùng, cách tạo index đúng cách, cách đọc EXPLAIN để biết index có được dùng không, và một checklist để chọn loại index phù hợp. Đọc xong, bạn tự tin thêm index cho bảng Order, Product mà không cần đoán mò.

2. B-tree Index: mặc định và phổ biến nhất

2.1. B-tree hoạt động như thế nào

B-tree (balanced tree) lưu giá trị theo thứ tự sắp xếp, cho phép tìm kiếm, so sánh (<, >, BETWEEN) và sắp xếp (ORDER BY) rất nhanh — độ phức tạp O(log n). Đây là loại index mặc định khi bạn chạy CREATE INDEX mà không chỉ định phương pháp.

CREATE TABLE customer_order (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total_amount NUMERIC(12,2)
);

CREATE INDEX idx_order_customer_id ON customer_order (customer_id);

Dòng CREATE INDEX tạo B-tree trên cột customer_id. Khi query WHERE customer_id = 123, PostgreSQL duyệt cây thay vì quét toàn bảng (sequential scan).

2.2. Kiểm tra index có được dùng không

EXPLAIN ANALYZE
SELECT * FROM customer_order WHERE customer_id = 123;

Index Scan using idx_order_customer_id on customer_order
  Index Cond: (customer_id = 123)
  Planning Time: 0.123 ms
  Execution Time: 0.045 ms

Index Scan nghĩa là planner chọn dùng index. Nếu thấy Seq Scan dù đã có index, thường là do bảng quá nhỏ (planner thấy quét toàn bảng rẻ hơn) hoặc thống kê (statistics) lỗi thời — chạy ANALYZE customer_order; để cập nhật.

2.3. Composite index và thứ tự cột

B-tree hỗ trợ nhiều cột, nhưng thứ tự cột quyết định hiệu quả.

CREATE INDEX idx_order_customer_status
    ON customer_order (customer_id, status);

-- Dùng được index đầy đủ
SELECT * FROM customer_order
WHERE customer_id = 123 AND status = 'PAID';

-- Chỉ dùng được phần customer_id của index
SELECT * FROM customer_order WHERE customer_id = 123;

-- KHÔNG dùng được index này (thiếu customer_id)
SELECT * FROM customer_order WHERE status = 'PAID';

Nguyên tắc: composite index giống quyển sổ địa chỉ sắp theo (customer_id, status) — bạn tra được theo customer_id trước, nhưng không tra trực tiếp theo status một mình.

Lưu ý: Đặt cột có độ chọn lọc cao (selectivity cao, nhiều giá trị khác nhau) lên trước nếu các cột đều được dùng độc lập trong query. Nếu luôn query cả hai cột cùng lúc, đặt cột lọc mạnh nhất trước để cây thu hẹp phạm vi nhanh.

3. GIN Index: cho dữ liệu “nhiều giá trị trong một hàng”

3.1. Khi nào B-tree bất lực

B-tree không hiệu quả với kiểu dữ liệu mà một hàng chứa nhiều “phần tử” cần tìm kiếm độc lập: mảng (array), JSON (jsonb), full-text search (tsvector). GIN (Generalized Inverted Index) sinh ra cho trường hợp này — nó đánh index từng phần tử riêng lẻ, không phải cả giá trị.

3.2. GIN cho JSONB

CREATE TABLE product (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    attributes JSONB NOT NULL
);

CREATE INDEX idx_product_attributes ON product USING GIN (attributes);

INSERT INTO product (name, attributes) VALUES
('Laptop Dell', '{"brand": "Dell", "ram_gb": 16, "tags": ["laptop", "office"]}'),
('Laptop HP',   '{"brand": "HP", "ram_gb": 8, "tags": ["laptop", "gaming"]}');

SELECT * FROM product WHERE attributes @> '{"brand": "Dell"}';
SELECT * FROM product WHERE attributes -> 'tags' @> '["gaming"]';

USING GIN (attributes) tạo index trên toàn bộ cột JSONB. Toán tử @> (“contains”) là cái GIN tối ưu nhất — nó kiểm tra xem JSON bên trái có chứa cấu trúc bên phải không, mà không cần parse lại toàn bộ document mỗi lần.

Bitmap Heap Scan on product
  Recheck Cond: (attributes @> '{"brand": "Dell"}'::jsonb)
  ->  Bitmap Index Scan on idx_product_attributes
        Index Cond: (attributes @> '{"brand": "Dell"}'::jsonb)

3.3. GIN cho full-text search

ALTER TABLE product ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED;

CREATE INDEX idx_product_search ON product USING GIN (search_vector);

SELECT name FROM product
WHERE search_vector @@ to_tsquery('simple', 'laptop & dell');

Cột search_vector là generated column tự sinh tsvector từ name. GIN index trên cột này giúp @@ (match) chạy nhanh với dữ liệu text lớn, thay vì LIKE '%laptop%' phải quét toàn bảng.

3.4. GIN cho array

CREATE TABLE article (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200),
    tag_list TEXT[]
);

CREATE INDEX idx_article_tags ON article USING GIN (tag_list);

SELECT * FROM article WHERE tag_list @> ARRAY['spring-boot'];

Tương tự JSONB, GIN đánh index từng phần tử trong mảng tag_list, nên truy vấn “bài viết có chứa tag X” nhanh hơn B-tree nhiều lần với bảng lớn.

4. So sánh và chi phí

Tiêu chí	B-tree	GIN
Dữ liệu phù hợp	scalar: số, chuỗi, ngày	array, jsonb, tsvector
Toán tử hỗ trợ	=, <, >, BETWEEN, ORDER BY	@>, ?, @@, &&
Tốc độ ghi (INSERT/UPDATE)	nhanh	chậm hơn, đặc biệt UPDATE JSONB lớn
Kích thước index	nhỏ hơn	lớn hơn (lưu từng phần tử)
Tốc độ build index	nhanh	chậm hơn trên bảng lớn

GIN đắt hơn ở chi phí ghi vì mỗi lần INSERT/UPDATE phải cập nhật nhiều entry (một cho mỗi phần tử trong JSON/array), không phải một entry như B-tree.

-- Kiểm tra kích thước index thực tế
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'product';

5. Test xác nhận index được dùng (tích hợp với Spring Boot)

Khi làm việc với Spring Data JPA, bạn thường muốn chắc index hoạt động đúng trước khi deploy. Dùng Testcontainers để test thật trên PostgreSQL.

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.20.1</version>
    <scope>test</scope>
</dependency>

@Testcontainers
@SpringBootTest
class ProductIndexTest {

    @Container
    static PostgreSQLContainer<?> postgres =
            new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void shouldUseGinIndexForJsonbQuery() {
        jdbcTemplate.execute("""
            INSERT INTO product (name, attributes)
            VALUES ('Laptop Dell', '{"brand": "Dell"}')
        """);

        String plan = jdbcTemplate.queryForList(
                "EXPLAIN SELECT * FROM product WHERE attributes @> '{\"brand\": \"Dell\"}'"
        ).stream()
         .map(row -> row.get("QUERY PLAN").toString())
         .reduce("", (a, b) -> a + b);

        assertThat(plan).containsIgnoringCase("idx_product_attributes");
    }
}

Test này thực thi EXPLAIN (không cần ANALYZE), gộp các dòng kết quả thành một chuỗi, rồi kiểm tra plan có nhắc tới tên index mong đợi không. Đây là cách xác nhận index thực sự tham gia vào query plan, không chỉ “tồn tại trong schema”.

Lưu ý: EXPLAIN không chạy query thật, chỉ hỏi planner. Muốn biết thời gian thực thi thật, dùng EXPLAIN ANALYZE — nhưng nhớ nó thực thi câu lệnh, tránh dùng với DELETE/UPDATE trừ khi bọc trong transaction rollback.

6. Checklist chọn loại index

• Query lọc theo cột scalar (số, chuỗi, ngày) với =, <, >, hoặc cần ORDER BY → B-tree.
• Query lọc nhiều cột cùng lúc theo kiểu “AND” → composite B-tree, cột chọn lọc cao hoặc cột dùng độc lập đặt trước.
• Query trên cột JSONB dùng @>, ?, ?&, ?| → GIN.
• Tìm kiếm full-text (@@ với tsvector) → GIN.
• Query trên array dùng @>, &&, <@ → GIN.
• Bảng ghi nhiều, đọc ít, dữ liệu JSONB thay đổi thường xuyên → cân nhắc không đánh GIN toàn bộ cột, chỉ đánh trên biểu thức con (expression index) cần thiết.

-- Expression index: chỉ đánh index một key cụ thể trong JSONB, nhẹ hơn GIN toàn cột
CREATE INDEX idx_product_brand ON product ((attributes ->> 'brand'));

Expression index này thực chất là B-tree trên kết quả attributes ->> 'brand' (giá trị text), phù hợp khi bạn chỉ luôn query theo đúng một key cố định, không cần tính linh hoạt của GIN.

7. Lỗi hay gặp

• Tạo index trên cột có cardinality thấp (ví dụ status chỉ có 3 giá trị trên bảng triệu dòng): planner thường bỏ qua index, chọn Seq Scan vì rẻ hơn. Không phải cứ có index là được dùng.
• Quên ANALYZE sau khi insert dữ liệu lớn: planner dựa vào statistics cũ, ước lượng sai số dòng, chọn plan tệ.
• Dùng GIN cho cột ít thay đổi nhưng ghi rất nhiều lần nhỏ: chi phí update index tích lũy lớn hơn lợi ích truy vấn — đo thử bằng EXPLAIN ANALYZE trước khi quyết định.
• Composite B-tree sai thứ tự cột: đặt cột ít dùng lên trước khiến index gần như vô dụng cho các query thực tế.
• Tạo quá nhiều index trên một bảng: mỗi INSERT/UPDATE phải cập nhật tất cả index liên quan, làm chậm write path mà không ai dùng hết các index đó.

8. Kết luận

B-tree là lựa chọn mặc định, phù hợp cho hầu hết điều kiện lọc và sắp xếp trên dữ liệu scalar. GIN dành riêng cho dữ liệu “nhiều giá trị” như JSONB, array, full-text — nơi B-tree không thể biểu diễn hiệu quả.

Trước khi thêm index, luôn kiểm tra bằng EXPLAIN ANALYZE xem planner có thực sự dùng nó không, và đo chi phí ghi nếu bảng có tần suất INSERT/UPDATE cao. Index không phải “thêm vào cho chắc” — mỗi index là một khoản đánh đổi giữa tốc độ đọc và tốc độ ghi.