Knowing When Your Composite Index Earns Its Write Cost

Tiger Data · 2026 · 2. Tăng trưởng · Độ khó: Trung bình — bài gốc

Bài của nhà cung cấp, cuối bài quảng bá hypertable (index theo từng chunk thời gian). Phần đo đạc và quy tắc ra quyết định áp dụng cho Postgres thường; số liệu là theo Tiger Data. Đi cặp với Tiger Data 2026.

Bối cảnh

• Index thường được thêm trong lúc xử lý sự cố và không ai quay lại kiểm tra. EXPLAIN cho biết index tiết kiệm bao nhiêu (đến từng buffer) nhưng không cho biết mỗi insert tốn thêm bao nhiêu: lợi ích có con số, chi phí chỉ là cảm giác.
• Ví dụ: bảng readings 40 triệu dòng, 90 ngày, 10.000 tag; index (tag_id, ts DESC) cho query dashboard.

Thay đổi chính (quy trình bốn bước)

1. Index phục vụ được query nào: B-tree sắp theo thứ tự từ điển, nên (tag_id, ts DESC) phục vụ query bắt đầu bằng tag_id (lọc một tag theo khoảng thời gian, lấy 500 dòng mới nhất của một tag không cần sort). Query chỉ theo ts trên mọi tag thì tệ hơn bị bỏ qua: planner vẫn chọn index, đọc 161.347 buffer để lấy 18.500 dòng, trong khi quét cả bảng là khoảng 254.000. Mở cửa sổ lên một ngày thì planner bỏ index.
2. Đo phần tiết kiệm bằng EXPLAIN (ANALYZE, BUFFERS): đọc số buffer, không đọc thời gian (thời gian dao động theo cache). Query dashboard đi từ 254.781 buffer xuống 506, mất luôn bước sort.
3. Đo chi phí mỗi insert, phụ thuộc cột đầu của index: index theo ts chỉ có một điểm chèn (lá ngoài cùng phải), split để lại page đầy 90%. (tag_id, ts) có 10.000 điểm chèn, mỗi split để lại hai nửa trống một nửa. Nạp 5 triệu dòng: index ts 107 MB, mật độ lá 90,1%; index (tag_id, ts DESC) dựng bằng insert 236 MB, mật độ 57,6%; cùng index đó dựng bằng một lệnh CREATE INDEX chỉ 150 MB, 90%. Phần chênh là page split, chỉ REINDEX mới lấy lại. WAL cũng vậy: index rải chèn ghi 834 MB WAL so với 699 MB, mất 22,1 giây so với 15,7 (nhiều full page image hơn vì chạm nhiều page khác nhau sau checkpoint).
4. Benchmark trần ingest bằng pgbench trên bản sao production, không trên bảng rỗng: lần chạy đầu trên bảng 10 triệu dòng chỉ thấy phạt 4,7% vì index còn vừa shared_buffers. Trên bảng 40 triệu dòng: không index 12.623 insert/giây, có index 11.639 (giảm 7,8%), và khoảng cách sẽ rộng ra khi index vượt bộ nhớ.

Quy tắc ra quyết định (theo Tiger Data)

• Đổi chi phí sang đơn vị đang ràng buộc mình: trần ingest. So với đỉnh ingest, không phải trung bình (pipeline trung bình 4.000 dòng/giây nhưng vọt lên 12.000 đầu mỗi giờ sẽ gãy ở đỉnh).
  • Đỉnh thấp hơn nhiều so với trần khi có index: index coi như miễn phí.
  • Đỉnh gần trần: query phải đáng giá bằng năng lực, không chỉ bằng mili giây.
  • Đỉnh cao hơn trần: đừng thêm; mở rộng index có sẵn hoặc chuyển query sang bảng tổng hợp trước (continuous aggregate).
• Áp dụng cho index đang có: tìm index lớn có idx_scan gần 0. Trước khi xoá: kiểm tra stats_reset (cần đủ một chu kỳ kinh doanh, kể cả báo cáo tháng); idx_scan tính theo từng node (index không dùng ở primary có thể đang gánh query analytics trên replica); không xoá index unique hay index giữ constraint. Index (tag_id) đứng cạnh (tag_id, ts DESC) gần như thừa. Xoá bằng DROP INDEX CONCURRENTLY.

Bài học cho SWE

• Câu hỏi không phải "index có làm chậm ghi không" mà là "index có rải ghi ra không": cột đầu có cardinality cao làm cây phình, WAL nhiều và cache kém (Query planner và hiệu năng truy vấn).
• Có index không có nghĩa planner dùng nó tốt: index không khớp cột đầu vẫn có thể được chọn và gần như không giúp gì.
• Benchmark ghi trên bảng nhỏ nói dối, vì cả index nằm trong cache. Kích thước test phải theo tỉ lệ bộ nhớ thật.
• Sau khi thêm hay xoá index, reset counter và đo lại sau một chu kỳ; nếu trần ingest không đổi như dự đoán thì nút thắt nằm ở chỗ khác (flush WAL, autovacuum).

Liên quan

• Tiger Data 2026 · Render 2024 · Zerodha 2021
• Query planner và hiệu năng truy vấn