Bài 1 cho thấy UPDATE tạo một phiên bản mới ở vị trí (ctid) mới. Điều đó kéo theo một hệ quả tốn kém: mọi index trỏ tới hàng đó phải được cập nhật để trỏ sang ctid mới. Một bảng có 5 index thì một UPDATE = ghi 5 index, cộng thêm việc để lại các entry chết trong index (cũng cần VACUUM dọn). Đây là một trong những chi phí ẩn lớn nhất của ghi trong PostgreSQL.

PostgreSQL có hai tối ưu quan trọng liên quan tới index và MVCC: HOT update (giảm chi phí ghi index) và index-only scan (giảm chi phí đọc heap). Bài này (phần 9 loạt PostgreSQL) đo thật cả hai trên pg-lab — và cho thấy chúng không tự động bật mà phụ thuộc vào cấu hình bạn chọn.

HOT update: cập nhật không ghi index

HOT (Heap-Only Tuple) update xảy ra khi UPDATE không cần đụng tới bất kỳ index nào. Điều kiện: (1) các cột được UPDATE không nằm trong index nào, VÀ (2) còn chỗ trống trong cùng trang để đặt phiên bản mới. Khi đó, phiên bản mới nằm cùng trang với bản cũ, các con trỏ index cũ vẫn đúng → không phải ghi index.

HOT update và index-only scan PostgreSQL: UPDATE thường phải ghi lại tất cả index MVCC UPDATE tạo tuple mới ở vị trí ctid mới nên mọi index phải trỏ tới ctid mới bảng 5 index thì 1 UPDATE ghi 5 index tốn I/O tạo dead entry trong index; HOT update heap-only tuple không ghi index điều kiện HOT một các cột được UPDATE không nằm trong index nào hai còn chỗ trống trong cùng trang để đặt tuple mới nên tuple mới nằm cùng trang index cũ vẫn trỏ đúng không ghi index, CREATE TABLE WITH fillfactor 70 chừa 30 phần trăm chỗ để HOT có chỗ, n_tup_hot_upd trong pg_stat_user_tables là số UPDATE là HOT; index-only scan không cần đọc heap nếu query chỉ cần cột có trong index cộng trang đã all-visible visibility map SELECT count từ t WHERE id Index Only Scan Heap Fetches 0 không chạm heap nhanh ít I/O cần VACUUM cập nhật visibility map trước

Hình 1: UPDATE thường ghi lại mọi index. HOT update (cột không-index + còn chỗ trong trang) đặt tuple mới cùng trang → không ghi index. Index-only scan đọc dữ liệu từ index + visibility map, không chạm heap.

-- fillfactor < 100 chua cho trong trang de HOT co cho dat tuple moi
CREATE TABLE t(id int primary key, key int, payload int) WITH (fillfactor=70);
-- n_tup_hot_upd dem so UPDATE la HOT
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname='t';

Đo thật: HOT phụ thuộc fillfactor và cột được cập nhật

Mình đo số HOT update (n_tup_hot_upd) trong ba tình huống trên pg-lab. Kết quả thật:

Bảng kết quả đo thật HOT update và index-only scan trên pg-lab postgresql 16.15 pg_stat_user_tables EXPLAIN ANALYZE BUFFERS: HOT update cần cột không-index cộng chỗ trống trong trang, fillfactor 100 UPDATE cột KHÔNG index n_tup_upd 10.000 n_tup_hot_upd 0 không HOT trang đầy, fillfactor 70 UPDATE cột KHÔNG index n_tup_upd 10.000 n_tup_hot_upd 4.368 HOT, fillfactor 70 UPDATE cột CÓ index cộng 10.000 cộng 0 cột index không HOT; index-only scan Heap Fetches 0 khi query được index che phủ SELECT count WHERE id chỉ cột id trong index Index Only Scan Heap Fetches 0 Buffers read 137 3,9 ms, SELECT sum v cột v không trong index Bitmap Index Scan cộng đọc heap Buffers shared hit 355 4,0 ms. Badge output thật màu xanh

Hình 2: Kết quả thật. fillfactor=100: cập nhật cột không-index vẫn 0 HOT (trang đầy, không có chỗ). fillfactor=70: 4.368/10.000 là HOT. Cập nhật cột CÓ index: 0 HOT. Index-only scan cho Heap Fetches=0 và ít buffer hơn.

  • fillfactor=100 (mặc định): 0 HOT dù cập nhật cột không-index. Trang đã đầy (packed 100%) nên không còn chỗ cho phiên bản mới trong cùng trang → buộc đặt sang trang khác → không HOT. Đây là điều nhiều người không biết: HOT không tự xảy ra với cấu hình mặc định.
  • fillfactor=70: 4.368/10.000 là HOT khi cập nhật cột không-index. Chừa 30% chỗ trống trong mỗi trang cho phép phần lớn UPDATE đặt phiên bản mới cùng trang → không ghi index. (Không phải 100% HOT vì một số trang vẫn đầy dần.)
  • Cập nhật cột CÓ index (key): 0 HOT thêm. Khi cột được UPDATE nằm trong index, index đó phải được cập nhật → không bao giờ HOT, bất kể fillfactor.

Đo thật: index-only scan bỏ qua heap

Khi query chỉ cần các cột có trong index và các trang liên quan đã được đánh dấu "all-visible" (qua visibility map, cập nhật bởi VACUUM), PostgreSQL có thể trả lời chỉ từ index, không đọc heap. Đo thật qua EXPLAIN:

  • SELECT count(*) WHERE id (chỉ cần cột id, có trong index): Index Only Scan, Heap Fetches: 0, Buffers read=137, 3,9 ms. Không chạm heap.
  • SELECT sum(v) (cần cột v, không trong index): Bitmap/Index Scan + đọc heap để lấy v, Buffers shared hit=355 (nhiều hơn vì phải đọc thêm trang heap).

Query được index "che phủ" (covering) đọc ít buffer hơn hẳn (137 vs 355) vì không phải vào heap. Đây là lý do kỹ thuật covering index (dùng INCLUDE để thêm cột hay truy vấn vào index) tăng tốc đáng kể các query đọc nhiều.

Đánh đổi cần cân nhắc

fillfactor thấp bật HOT và giảm bloat, nhưng tốn đĩa hơn. Chừa chỗ trống (fillfactor < 100) cho phép HOT update (không ghi index) và cho phép không gian chết được tái dùng ngay trong trang (HOT chain pruning, giảm bloat mà không cần VACUUM). Nhưng mỗi trang chứa ít hàng hơn, nên bảng tốn nhiều đĩa hơn và quét tuần tự đọc nhiều trang hơn. Đây là đánh đổi: dùng fillfactor thấp (70-90) cho bảng UPDATE nhiều cột không-index (counter, trạng thái); giữ fillfactor 100 cho bảng append-only hay ít update (để tiết kiệm đĩa). Chọn theo workload thật.

Index-only scan cần VACUUM cập nhật visibility map — và không phải lúc nào cũng được. Index-only scan chỉ bỏ qua heap khi trang được đánh dấu "all-visible" trong visibility map, mà dấu này do VACUUM đặt. Nếu bảng vừa bị ghi nhiều (nhiều trang chưa all-visible), Heap Fetches sẽ > 0 — PostgreSQL phải vào heap kiểm tra tính hiển thị cho các hàng ở trang chưa all-visible, làm mất phần nào lợi ích. Để index-only scan hiệu quả trên bảng đọc nhiều, cần VACUUM đủ thường xuyên. Đây là một liên kết nữa giữa VACUUM (bài 8) và hiệu năng.

Thêm index có cái giá ghi — cân nhắc trước khi thêm. Mỗi index bạn thêm làm mọi UPDATE chạm cột đó trở thành non-HOT (phải ghi index), và làm INSERT/UPDATE chậm hơn (phải cập nhật thêm một cấu trúc). Index tăng tốc đọc nhưng làm chậm ghi và tăng bloat index. Đừng thêm index "cho chắc" — chỉ thêm index thực sự được query dùng, và cân nhắc rằng một index trên cột hay-update có thể giết HOT, làm chậm toàn bộ ghi vào bảng đó.

Ba ý mang về

  1. HOT update tránh ghi index, nhưng cần cột không-index + chỗ trống trong trang. Đo thật: fillfactor=100 cho 0 HOT (trang đầy), fillfactor=70 cho 4.368/10.000 HOT; cập nhật cột CÓ index luôn 0 HOT. HOT không tự bật ở mặc định — phải hạ fillfactor cho bảng update nhiều.
  2. Index-only scan bỏ qua heap khi index che phủ query. Đo thật: SELECT chỉ cột trong index → Index Only Scan, Heap Fetches=0, 137 buffer; SELECT cột ngoài index → vào heap, 355 buffer. Dùng covering index (INCLUDE) cho query đọc nhiều.
  3. Cả hai là đánh đổi, không miễn phí. fillfactor thấp bật HOT + giảm bloat nhưng tốn đĩa; index-only scan cần VACUUM cập nhật visibility map (bảng ghi nhiều → Heap Fetches>0); mỗi index thêm làm UPDATE cột đó non-HOT + chậm ghi. Thêm index có chủ đích, chọn fillfactor theo workload.

Nguồn

Phần sau ta xuống tầng lưu bền: WAL (write-ahead log) — vì sao PostgreSQL ghi log trước khi ghi dữ liệu, cách nó đảm bảo bền vững và phục hồi sau sự cố, và đo thật WAL sinh ra khi ghi.