PostgreSQL BRIN Index thực chiến: Index nhỏ cho bảng dữ liệu cực lớn

BRIN (Block Range Index) không lưu một entry cho từng row như B-tree. Nó lưu thông tin tóm tắt cho từng dải block vật lý của bảng, nhờ đó index có thể rất nhỏ và giúp PostgreSQL bỏ qua phần lớn một bảng khổng lồ khi giá trị có tương quan với vị trí lưu trữ. Đổi lại, BRIN là index lossy: các block ứng viên vẫn phải được đọc và row phải được kiểm tra lại.

Bài viết dựa trên tài liệu PostgreSQL BRIN Indexes, tập trung vào time-series/event table, cách chọn pages_per_range, maintenance summary, đọc execution plan và rollout an toàn trên production.

1. BRIN giải bài toán nào?

Giả sử bảng events có hàng trăm triệu row được append gần theo thời gian. Row cũ nằm ở block đầu, row mới nằm ở block cuối. Một BRIN minmax trên occurred_at ghi khoảng thời gian nhỏ nhất/lớn nhất cho mỗi nhóm block. Query một ngày gần đây có thể bỏ qua hầu hết range chắc chắn không chứa thời điểm đó.

BRIN phù hợp khi:

• bảng rất lớn, scan toàn bảng đắt;
• cột query có tương quan vật lý tự nhiên như timestamp tăng dần, sequence ID hoặc vùng dữ liệu được nạp theo batch;
• query chọn một lát đủ lớn để B-tree phải fetch nhiều heap page nhưng đủ nhỏ để BRIN loại được phần lớn bảng;
• ưu tiên index nhỏ, chi phí ghi thấp hơn và cache footprint nhỏ.

2. BRIN khác B-tree ở contract nào?

BRIN không enforce uniqueness. Nó cũng không phải “B-tree nhỏ hơn” để thay thế máy móc. Một hệ thống có thể giữ B-tree cho truy vấn theo primary key và BRIN cho lọc thời gian dài.

3. Đo correlation trước khi tạo index

ANALYZE events;

SELECT attname, correlation, n_distinct
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'events'
  AND attname IN ('occurred_at', 'tenant_id');

correlation gần 1 hoặc -1 cho thấy thứ tự giá trị bám sát thứ tự vật lý theo thống kê mẫu; gần 0 thường kém hứa hẹn cho minmax BRIN. Đây là tín hiệu khởi đầu, không phải acceptance test. Phân bố theo range mới quan trọng: vài outlier trong mỗi range có thể mở rộng min/max và tạo nhiều false positive.

Đo trên dữ liệu production-like sau khi ANALYZE. Một bảng vừa bulk-load theo thứ tự có thể đẹp, nhưng update ngẫu nhiên trong nhiều tháng sẽ làm correlation suy giảm.

4. Tạo BRIN cơ bản và so kích thước

CREATE INDEX CONCURRENTLY events_occurred_at_brin
ON events USING brin (occurred_at)
WITH (pages_per_range = 128, autosummarize = on);

SELECT pg_size_pretty(pg_relation_size('events_occurred_at_brin')) AS brin_size,
       pg_size_pretty(pg_relation_size('events_occurred_at_btree')) AS btree_size;

CONCURRENTLY giảm việc chặn write nhưng chạy lâu hơn, thực hiện nhiều phase và vẫn cần capacity/lock monitoring. Nếu thất bại, index invalid có thể còn lại và phải được xử lý theo runbook; không chạy lại mù quáng với cùng tên.

So kích thước chỉ là một chiều. Index 20 MB thay cho 20 GB nghe hấp dẫn, nhưng phải chứng minh query latency, heap blocks đọc và write overhead trên workload thật.

5. pages_per_range là núm điều chỉnh quan trọng nhất

pages_per_range quyết định số heap page được gộp trong một summary. Giá trị nhỏ tạo nhiều summary hơn: index lớn hơn và maintenance nhiều hơn, nhưng min/max chính xác hơn. Giá trị lớn làm index cực nhỏ nhưng mỗi match kéo theo nhiều block recheck.

Mặc định phổ biến là 128 page; với block 8 KiB, đó xấp xỉ 1 MiB heap mỗi range, nhưng block size có thể khác. Đừng chọn chỉ từ công thức. Tạo candidate index trên bản sao representative với 32/64/128/256, rồi so plan và latency ở nhiều cửa sổ thời gian.

6. Đọc plan: Bitmap Index Scan và lossy recheck

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)
SELECT count(*)
FROM events
WHERE occurred_at >= timestamptz '2026-10-01 00:00:00+00'
  AND occurred_at <  timestamptz '2026-10-02 00:00:00+00';

BRIN thường xuất hiện dưới Bitmap Index Scan rồi Bitmap Heap Scan. Quan sát:

• Heap Blocks đọc chính xác/lossy;
• Rows Removed by Index Recheck — false positive phải loại;
• shared hit/read blocks và I/O timing nếu bật;
• planning/execution time trên cold và warm cache;
• plan có ổn định với parameter/selectivity khác nhau không.

Recheck không phải bug; nó là contract của BRIN. Vấn đề là tỷ lệ recheck/row trả về quá lớn khiến sequential scan hoặc B-tree tốt hơn.

7. Selectivity quyết định planner có dùng BRIN hay không

Query lấy phần lớn bảng thường hợp với sequential scan. Query chỉ lấy vài row ngẫu nhiên thường hợp B-tree. BRIN sống ở vùng giữa: nó có thể loại rất nhiều block nhờ correlation nhưng chấp nhận đọc trọn các range ứng viên.

Không đặt enable_seqscan=off trên production để “ép index”. Có thể dùng nó trong session chẩn đoán để xem alternative plan, nhưng quyết định rollout phải dựa trên plan tự nhiên với statistics và cost phù hợp. Tăng statistics target cho cột skewed khi cần rồi chạy ANALYZE.

8. Summary được tạo và cập nhật ra sao?

Khi build index, PostgreSQL scan các heap page hiện có và tạo summary. Sau đó, insert vào range đã summarize sẽ mở rộng summary khi cần. Range mới có thể chưa có summary cho tới khi:

• VACUUM/autovacuum xử lý;
• autosummarize=on gửi yêu cầu cho autovacuum;
• operator gọi hàm summarize thủ công.

SELECT brin_summarize_new_values('events_occurred_at_brin');

ALTER INDEX events_occurred_at_brin
SET (autosummarize = on);

autosummarize mặc định tắt và là best-effort: queue request của autovacuum có thể đầy, khi đó log báo request không được ghi nhận. Một range chưa summarize không thể được loại an toàn nên làm scan kém hiệu quả. Theo dõi log và chạy summarize có kiểm soát sau bulk load.

9. Update/delete có thể làm summary ngày càng kém chính xác

Summary minmax dễ mở rộng khi một row trong range nhận giá trị outlier. Xóa hoặc sửa lại outlier không nhất thiết làm summary tự co hẹp ngay, vì loại một cực trị đòi hỏi scan lại range. Sau nhiều update ngẫu nhiên, BRIN vẫn đúng nhưng tạo nhiều false positive hơn.

-- Bảo trì đặc biệt sau khi xác định đúng block range
SELECT brin_desummarize_range('events_occurred_at_brin', 1048576);
SELECT brin_summarize_range('events_occurred_at_brin', 1048576);

Hai hàm trên nhận block number thuộc range, không phải range ordinal. Đừng desummarize hàng loạt trên production nếu chưa đo tác động; khoảng giữa hai bước sẽ bị xem như unsummarized. Nếu toàn bảng mất correlation, rebuild hoặc sắp xếp lại dữ liệu có thể cần thiết, nhưng CLUSTER/VACUUM FULL/table rewrite là thay đổi nặng cần kế hoạch riêng.

10. Bulk load và dữ liệu đến trễ

Load theo occurred_at giữ correlation tốt. Backfill dữ liệu cũ vào cuối heap khiến giá trị lịch sử nằm cạnh giá trị mới, mở rộng min/max của range cuối. Nếu late-arriving data thường xuyên, BRIN vẫn có thể hữu ích nhưng cần benchmark với đúng tỷ lệ đến trễ.

Các lựa chọn gồm load vào partition đúng thời gian, stage và sort trước khi merge, tách hot/cold table hoặc chấp nhận BRIN cho query lớn và giữ B-tree cho lookup chính xác. Không hy sinh correctness của ingest chỉ để có correlation đẹp.

11. BRIN và partitioning bổ sung cho nhau

Partition pruning loại cả partition trước khi scan; BRIN loại block range bên trong partition còn lại. Với bảng event theo tháng, mỗi partition có BRIN timestamp nhỏ có thể là tổ hợp hiệu quả. Nhưng quá nhiều partition/index làm tăng planning, catalog và maintenance overhead.

Tạo index trên parent/partition theo đúng version và quy trình deploy. Kiểm tra tất cả leaf partition có index hợp lệ, summary mới được cập nhật và partition mặc định không trở thành túi dữ liệu mất correlation.

12. Operator class không chỉ có minmax

BRIN lưu loại summary tùy operator class và data type. Core PostgreSQL có các nhóm như minmax, minmax-multi, inclusion và bloom cho những kiểu phù hợp. minmax_multi có thể mô tả nhiều khoảng giá trị trong một range tốt hơn khi phân bố có vài cụm, đổi lại summary lớn và chi phí khác.

CREATE INDEX CONCURRENTLY events_device_brin
ON events USING brin (device_id int8_minmax_multi_ops)
WITH (pages_per_range = 64);

Tên operator class và hỗ trợ phụ thuộc type/version. Tra catalog/tài liệu của major đang chạy và benchmark; không sao chép ví dụ nếu column type không khớp.

13. Multicolumn BRIN: nhỏ không có nghĩa miễn phí

Có thể đặt nhiều cột vào một BRIN, nhưng mỗi cột cần có khả năng loại range cho workload. Nếu tenant_id phân tán ngẫu nhiên trong mọi block, thêm nó có thể không giúp nhiều dù index vẫn nhỏ. Hai BRIN riêng cũng có thể được planner kết hợp bằng bitmap khi có lợi.

So sánh multicolumn với index đơn, nhiều index và partial BRIN trên partition/cohort. Đừng dùng số lượng index scan làm metric duy nhất; một index được dùng nhiều nhưng đọc quá nhiều heap block vẫn có thể tệ.

14. Monitoring và acceptance test

SELECT schemaname, relname, indexrelname,
       idx_scan, last_idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'events_occurred_at_brin';

SELECT pg_size_pretty(pg_relation_size('events_occurred_at_brin'));

Statistics tích lũy cần được đọc cùng thời gian reset và query volume. Acceptance test nên dùng dataset có size, correlation, late arrivals và update pattern giống thật; chạy nhiều query window (giờ/ngày/tháng), cold/warm cache và tải concurrent.

Ghi baseline: latency p50/p95/p99, heap blocks, rows rechecked, CPU, I/O bytes, index size, insert/update throughput và autovacuum. Rollout canary một bảng/partition, quan sát đủ chu kỳ ingest và vacuum rồi mới mở rộng.

Checklist production

• Bảng đủ lớn và cột có correlation vật lý đã đo.
• Query mục tiêu là range có thể loại phần lớn block.
• Đã benchmark nhiều pages_per_range trên dữ liệu representative.
• EXPLAIN ANALYZE BUFFERS cho thấy heap blocks/recheck chấp nhận được.
• Không kỳ vọng BRIN enforce unique hoặc thay B-tree cho point lookup.
• autosummarize/VACUUM/manual summarize có owner và alert log.
• Bulk load/late data/update pattern được đưa vào test.
• Maintenance desummarize/rebuild có runbook và rollback.
• Partitioning và index coverage được kiểm tra trên mọi leaf.
• Rollout đo cả read latency, write cost, CPU, I/O và index size.

Kết luận

BRIN hiệu quả khi cấu trúc vật lý của heap mang thông tin: các row gần nhau trên disk có giá trị đủ gần để một summary nhỏ loại được nhiều block. Thành công không nằm ở việc index nhỏ bao nhiêu, mà ở tỷ lệ block bỏ qua so với false positive phải recheck. Đo correlation, thử nhiều range size, giữ summary cập nhật và benchmark dưới workload thật sẽ biến BRIN từ một tối ưu “nghe có vẻ hay” thành công cụ production có bằng chứng.