HOT update là gì?

Ở bài trước, chiếc xe 13E-832.74 được giảm giá từ 500 xuống 480 triệu. PostgreSQL không sửa dòng cũ mà ghi một phiên bản mới của dòng. Phiên bản mới nằm ở chỗ khác, nên index biển số phải thêm một mục trỏ tới nó, dù biển số không đổi.

Giá và ghi chú là những thứ người bán sửa nhiều nhất, còn biển số gần như không bao giờ đổi. Lần sửa nào cũng thêm mục vào index thì index cứ to dần, trong khi số xe vẫn vậy.

Nếu biển số không đổi, có cách nào giữ nguyên index không?

HOT update là gì?

HOT update là một cách PostgreSQL tối ưu UPDATE. Nếu đủ điều kiện, PostgreSQL đặt phiên bản mới của dòng ngay trong page chứa phiên bản cũ, và không thêm mục mới vào index. Page là một đơn vị lưu trữ của bảng: mỗi page 8 KB, chứa nhiều dòng.

Index biển số có một mục 13E-832.74, dẫn tới vị trí ban đầu của dòng. Khi đổi giá, PostgreSQL ghi phiên bản mới vào cùng page và nối phiên bản cũ tới nó. Tìm xe qua index, PostgreSQL vẫn tới vị trí ban đầu, rồi đi theo các phiên bản nối nhau trong page tới phiên bản hợp với câu đọc. Index không cần biết phiên bản mới nằm ở đâu.

HOT là viết tắt của Heap-Only Tuple: phiên bản dòng chỉ có trong bảng, không có mục index riêng. HOT không ghi đè: chiếc xe vẫn có hai phiên bản, chỉ là cả hai nằm chung một page.

Khi nào UPDATE có thể là HOT?

UPDATE có thể là HOT khi thoả cả hai điều kiện: không đổi giá trị cột nào mà index dùng tới, và page chứa phiên bản cũ còn đủ chỗ cho phiên bản mới.

Bảng ví dụ có hai index B-tree: khoá chính trên id và index trên biển số. Giá không có index, nên đổi giá chỉ còn phụ thuộc vào chỗ trống trong page:

Lần UPDATE	Page cũ	HOT?
Đổi giá	Còn chỗ	Có thể
Đổi giá	Không đủ chỗ, như ở bài trước	Không
Đổi biển số	Còn chỗ	Không: biển số có index B-tree

Dòng cuối cho thấy nằm cùng page chưa đủ để là HOT. Đổi biển số thì phiên bản mới có thể nằm ngay cạnh phiên bản cũ, nhưng index biển số vẫn cần một mục cho biển số mới.

Cột nào tính là cột index dùng tới?

Không chỉ cột được đánh index trực tiếp. Cột nằm trong biểu thức của một expression index, hay trong điều kiện WHERE của một partial index, cũng tính.

Ngoại lệ là BRIN, loại index duy nhất trong PostgreSQL gốc chỉ lưu bản tóm tắt cho từng khối page. Đổi cột của một BRIN index thì UPDATE vẫn có thể là HOT, nhưng BRIN có thể vẫn phải cập nhật.

Làm sao để page còn chỗ?

Khi thêm dữ liệu, PostgreSQL mặc định xếp mỗi page của bảng đầy tới mức có thể. Tham số mức lấp đầy (fillfactor) của bảng quyết định điều đó. Giá trị tính theo phần trăm, mặc định là 100.

Với fillfactor = 90, INSERT chỉ xếp mỗi page tới khoảng 90%. Phần còn lại để dành cho phiên bản mới khi UPDATE các dòng trong page đó.

Đó là chỗ để dành, không phải lời hứa. Vài lần UPDATE có thể dùng hết chỗ trống, và không phải UPDATE nào cũng thành HOT. Ngược lại, bảng để fillfactor 100 vẫn có HOT khi page tình cờ còn đủ chỗ. Cái giá là cùng số dòng mà cần nhiều page hơn, nên bảng to hơn.

Index cũng có fillfactor riêng. Bài này chỉ nói fillfactor của bảng.

Thử với 200.000 xe

File SQL của bài tạo hai bảng cùng 200.000 xe, cùng index trên khoá chính và biển số. u100 để fillfactor 100, u90 đặt 90:

CREATE TABLE u90 WITH (fillfactor = 90) AS
SELECT * FROM vehicles WHERE id <= 200000 ORDER BY id;

Sau đó sửa ghi chú, một cột không có index, của 20.000 xe trên mỗi bảng, rồi đếm số UPDATE là HOT trong pg_stat_user_tables. Cột n_tup_upd là tổng số dòng đã UPDATE, n_tup_hot_upd là số UPDATE là HOT trong số đó:

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS phan_tram_hot
FROM pg_stat_user_tables
WHERE relname IN ('u100', 'u90');

Kết quả trong môi trường thực hành:

Bảng	fillfactor	Dung lượng lúc tạo	UPDATE là HOT
u100	100	61 MB	0 / 20.000 (0%)
u90	90	66 MB	17.312 / 20.000 (86,6%)

Trong lần thử này, chừa thêm chỗ giúp phần lớn UPDATE ở u90 trở thành HOT, đổi lại bảng lúc tạo lớn hơn 5 MB. Con số 86,6% là của bộ dữ liệu và lần thử này, PostgreSQL không bảo đảm tỷ lệ nào.

Câu truy vấn trên dùng được cho bảng của bạn, chỉ cần đổi tên bảng. Các con số trong pg_stat_user_tables cộng dồn từ lần thống kê được reset gần nhất, có thể cập nhật trễ một chút, và gồm mọi UPDATE khác trên bảng. Muốn biết một thay đổi có giúp không, hãy ghi số trước và sau trong cùng một khoảng thời gian, hoặc thử trên một bảng riêng như ở đây.

Với bảng đã có dữ liệu

ALTER TABLE ten_bang SET (fillfactor = 90);

Lệnh này chỉ đổi thiết lập. Nó không xếp lại dữ liệu đang có, nên các page cũ vẫn kín như trước; chỉ dữ liệu ghi về sau mới theo mức 90%. Muốn page cũ cũng có chỗ trống thì phải viết lại bảng, chẳng hạn bằng VACUUM FULL. Đó là thao tác nặng: nó khoá độc quyền bảng trong suốt lúc chạy, nên phải cân nhắc kỹ trước khi dùng trên production.

Có nên hạ fillfactor không, và hạ bao nhiêu, phụ thuộc vào cách bảng bị sửa và dung lượng bạn chấp nhận được. Cách chắc nhất là đo tỷ lệ HOT trước và sau khi đổi.

Phiên bản cũ thì sao?

HOT vẫn tạo phiên bản mới của dòng, nó chỉ giúp index không phải ghi thêm. Phiên bản cũ chỉ dọn được khi không còn transaction nào cần nhìn thấy nó. Một số phiên bản trong chuỗi HOT có thể được dọn ngay trong lúc PostgreSQL làm việc bình thường với page đó, nhưng việc này không thay thế VACUUM.

PostgreSQL dọn những phiên bản cũ như thế nào?