Chi phí ẩn của index: mỗi index bạn thêm làm chậm mọi lần ghi bao nhiêu?

Cả loạt bài trước ca ngợi index vì tăng tốc đọc gấp hàng chục, hàng trăm lần. Nhưng index không miễn phí, và cái giá nằm ở phía ngược lại: mỗi lần ghi. Khi bạn INSERT một dòng, PostgreSQL phải chèn một mục vào mọi index của bảng; khi UPDATE, thường phải thêm mục mới vào tất cả. Rải index vô tội vạ "cho chắc" là một cách làm chậm cả hệ thống ghi mà không ai để ý. Bài này đo chính xác cái giá đó.

INSERT: mỗi index là một khoản thuế cộng dồn

Thí nghiệm đơn giản: chèn 500.000 dòng vào cùng một bảng, thay đổi số lượng index từ 1 (chỉ khóa chính) tới 7, đo thời gian.

Hình 1: INSERT 500.000 dòng chậm dần theo số index — 838 ms với 1 index, lên 5.143 ms với 7 index (gấp 6,1 lần). Mỗi index thêm vào là một khoản chi cộng dồn lên đường ghi.

Con số rất rõ ràng: từ 838 ms (chỉ khóa chính) tăng lên 1.245 ms (2 index), 2.785 ms (4 index), rồi 5.143 ms (7 index). Mỗi index bắt PostgreSQL làm thêm việc cho từng dòng chèn — tính vị trí trong cây, ghi mục mới, đôi khi tách trang. Bảy index làm INSERT chậm hơn sáu lần so với khi chỉ có khóa chính.

-- Mỗi câu này buộc cập nhật MỌI index của bảng:
INSERT INTO wtest (a, b, c, d) VALUES (...);   -- ghi heap + N index

Đây là lý do các bảng ghi nhiều (log, hàng đợi, bảng giao dịch) nên giữ số index tối thiểu, và vì sao khi nạp dữ liệu hàng loạt, mẹo quen thuộc là bỏ index trước khi nạp rồi tạo lại sau — tạo index một lần trên dữ liệu tĩnh rẻ hơn nhiều so với cập nhật nó theo từng dòng.

UPDATE và phép màu HOT

UPDATE trong PostgreSQL không sửa dòng tại chỗ — nó tạo một phiên bản mới của dòng (do cơ chế MVCC). Bình thường, phiên bản mới nằm ở vị trí khác, nên mọi index phải trỏ lại — tức thêm một mục vào từng index, đắt y như INSERT. Nhưng PostgreSQL có một tối ưu quan trọng: HOT (Heap-Only Tuple).

Nếu cột bị thay đổi không nằm trong bất kỳ index nào, và trang heap còn chỗ trống cho phiên bản mới, PostgreSQL đặt phiên bản mới ngay trên cùng trang và không đụng tới index nào cả. Index cũ vẫn trỏ đúng qua một chuỗi chuyển tiếp trong trang. Kết quả: UPDATE rẻ hơn hẳn.

-- Chừa chỗ trên trang cho HOT bằng fillfactor < 100
CREATE TABLE utest (...) WITH (fillfactor = 80);

UPDATE utest SET khong_index = khong_index + 1;   -- đủ điều kiện HOT
UPDATE utest SET co_index   = co_index   + 1;     -- KHÔNG HOT: phải sửa index

Đo thật trên 500.000 dòng cho thấy khác biệt qua cột n_tup_hot_upd trong pg_stat_user_tables:

• Đổi cột không có index: 129.880 dòng được cập nhật kiểu HOT — chừng đó lần né được việc sửa index.
• Đổi cột có index: 0 HOT update — mọi dòng đều phải chèn mục index mới.

Hình 2: HOT update chỉ kích hoạt khi cột đổi không có index và trang còn chỗ (nhờ fillfactor < 100). Đó là lý do đánh index lên một cột bị UPDATE liên tục có thể đắt gấp bội — nó tắt mất HOT.

Lưu ý HOT cần chỗ trống trên trang để đặt phiên bản mới; vì thế fillfactor = 80 (chừa 20% mỗi trang) mở đường cho HOT, còn fillfactor = 100 mặc định thì trang đầy, HOT khó xảy ra. Đây là một núm chỉnh đáng biết cho bảng bị update nhiều.

Đánh đổi: chọn index cho đúng, không cho nhiều

Bài học rút ra không phải "tránh index" — chúng thiết yếu cho đọc — mà là cân nhắc từng cái:

Mỗi index phải kiếm được chỗ đứng. Một index chỉ đáng giữ nếu có truy vấn thật sự dùng nó. Index tạo "cho chắc" nhưng không truy vấn nào chạm tới là chi phí ghi thuần túy, không đổi lại gì. Ở bài sau về index không dùng, ta sẽ đo cách phát hiện chúng.

Cẩn thận index trên cột bị UPDATE thường xuyên. Một cột như trang_thai hay lan_cap_nhat_cuoi thay đổi liên tục; đánh index lên nó tắt HOT và khiến mỗi lần cập nhật đắt hơn. Chỉ làm nếu thật sự có truy vấn lọc theo cột đó nhiều.

Chỉnh fillfactor cho bảng update-nặng. Chừa chỗ trống trên trang giúp HOT hoạt động, giảm bloat index và tốc độ update. Đổi lại bảng chiếm nhiều dung lượng hơn một chút.

Ba ý mang về

1. Mỗi index đánh thuế lên mọi lần ghi: đo thật, INSERT 500.000 dòng chậm từ 838 ms (1 index) lên 5.143 ms (7 index) — gấp 6,1 lần, tăng cộng dồn theo từng index.
2. HOT update né chi phí index khi cột đổi không có index và trang còn chỗ: 129.880 dòng được HOT khi đổi cột không index, so với 0 khi đổi cột có index — đánh index lên cột hay bị UPDATE sẽ tắt tối ưu này.
3. Chọn index theo truy vấn thật, không tạo "cho chắc", tránh index trên cột update-nặng, và cân nhắc fillfactor < 100 cho bảng bị cập nhật nhiều để mở đường cho HOT.

Phần sau ta đo một hệ quả trực tiếp của việc ghi nhiều lên index: Phần sau nói về index bloat — vì sao index phình to theo thời gian, cách phát hiện và đo mức phình thật.