UPDATE trong PostgreSQL hoạt động như thế nào?

Chiếc xe biển số 13E-832.74 đang được rao giá 500 triệu. Người bán giảm giá xuống còn 480 triệu:

UPDATE u
SET price_vnd = 480000000
WHERE id = 1046;

Nhìn câu lệnh, ta có thể nghĩ PostgreSQL tìm dòng của chiếc xe rồi sửa giá ngay trên dòng đó. Nhưng PostgreSQL làm việc khác: nó tạo ra một phiên bản mới của dòng.

Vậy phiên bản cũ đi đâu?

PostgreSQL ghi một phiên bản mới

Trước khi cập nhật, bảng có một phiên bản của chiếc xe, với giá 500 triệu. Sau UPDATE, PostgreSQL ghi thêm một phiên bản có giá 480 triệu:

Với người dùng, đây vẫn là cùng chiếc xe có id = 1046. Nhưng trong bảng, PostgreSQL đang giữ hai phiên bản dòng (row version): một phiên bản có giá cũ và một phiên bản có giá mới.

Vì sao PostgreSQL cần giữ lại phiên bản cũ?

Vì sao phải giữ phiên bản cũ?

Giả sử một câu SELECT bắt đầu liệt kê xe khi giá của chiếc xe này vẫn là 500 triệu. Trong lúc câu đọc đang chạy, người bán đổi giá thành 480 triệu và lưu thay đổi.

Câu đọc đã bắt đầu trước đó phải thấy giá nào?

Ở mức cô lập mặc định Read Committed, một câu SELECT thông thường nhìn dữ liệu theo thời điểm câu đó bắt đầu:

• Câu đọc A bắt đầu trước khi giá mới được lưu, nên thấy 500 triệu, dù nó đọc đến chiếc xe sau khi COMMIT.
• Câu đọc B bắt đầu sau COMMIT, nên thấy 480 triệu.

Nếu giá 500 triệu bị ghi đè ngay, câu đọc A sẽ không còn phiên bản phù hợp để đọc. Giữ lại phiên bản cũ giúp câu đọc đang chạy tiếp tục nhìn thấy dữ liệu nhất quán.

Cách PostgreSQL giữ nhiều phiên bản của một dòng được gọi là điều khiển đồng thời đa phiên bản (MVCC, Multiversion Concurrency Control). Phiên bản cũ không ở lại mãi: PostgreSQL có thể dọn nó khi không còn câu đọc nào cần đến.

Một chiếc xe giờ có hai phiên bản ở hai vị trí khác nhau. Vậy điều gì xảy ra với index biển số?

Vì sao index biển số cũng phải ghi thêm?

Index biển số giúp PostgreSQL tìm vị trí của một chiếc xe trong bảng. Trước khi cập nhật, mục của chiếc xe này trỏ tới phiên bản có giá 500 triệu:

Index biển số                      Bảng

13E-832.74  ────────────────────→  13E-832.74 · 500 triệu

Sau UPDATE, phiên bản giá 480 triệu nằm ở một vị trí khác. Trong ví dụ này, page chứa phiên bản cũ không còn đủ chỗ nên PostgreSQL phải ghi phiên bản mới sang page khác.

Vị trí ở đây là địa chỉ dòng của PostgreSQL: (40,6) là vị trí số 6 trên page 40. Nó là địa chỉ của một phiên bản dòng, không phải mã cố định của chiếc xe.

Muốn tìm được phiên bản mới qua index, PostgreSQL cần thêm một mục trỏ tới vị trí mới đó.

Biển số không đổi. Thứ thay đổi là vị trí của phiên bản dòng mà index cần trỏ tới.

Trong thí nghiệm này, bảng còn có index khóa chính trên id. Index đó cũng cần thêm một mục cho phiên bản mới, dù id = 1046 không đổi.

Khi cập nhật nhiều dòng, việc ghi thêm vào các index cũng tăng theo. Trong thí nghiệm với 200.000 xe, sau khi sửa ghi chú của tất cả xe, index biển số tăng từ 6.184 kB lên 12 MB, dù không biển số nào thay đổi.

Trong ví dụ này, cập nhật giá tạo ra một phiên bản ở page khác. Hai index B-tree phải có thêm mục trỏ tới phiên bản mới, dù các cột được index không thay đổi.

Có cách nào UPDATE mà không phải ghi thêm vào index?