Ở phần 6, ta thấy REPEATABLE READ giữ một "snapshot ổn định" — cùng một transaction đọc lại dữ liệu vẫn thấy giá trị cũ dù transaction khác đã sửa. Câu hỏi tự nhiên: làm sao PostgreSQL làm được điều đó? Nếu một UPDATE ghi đè giá trị cũ, thì transaction đang giữ snapshot cũ sẽ thấy giá trị mới — mâu thuẫn. Câu trả lời là MVCC (Multi-Version Concurrency Control): PostgreSQL không ghi đè tại chỗ. Mỗi UPDATE tạo một phiên bản mới của dòng và giữ lại phiên bản cũ — để các transaction đang chạy vẫn thấy snapshot của chúng.

MVCC là thứ khiến PostgreSQL mạnh mẽ về đồng thời (reader không chặn writer, writer không chặn reader). Nhưng nó có một cái giá mà nhiều người không lường: mỗi UPDATE/DELETE để lại một dead tuple ("xác chết" — phiên bản cũ không còn ai cần), và nếu không dọn, chúng tích tụ làm bảng phình (bloat) — chậm mọi thao tác quét. Bài này (phần 7 loạt SQL sâu) đo thật hiện tượng bloat và cách VACUUM dọn nó.

Cơ chế: UPDATE tạo bản mới, để lại xác chết

Ảnh chụp đoạn mã nền tối minh hoạ MVCC dead tuple và VACUUM, khối cơ chế MVCC UPDATE tạo phiên bản mới giữ bản cũ UPDATE t SET val bằng 2 WHERE id bằng 1 trên đĩa id 1 val 1 dead tuple bản cũ giữ cho snapshot TX đang chạy id 1 val 2 live tuple bản mới vì sao giữ bản cũ để transaction cũ isolation phần 6 vẫn thấy snapshot của nó reader không chặn writer, khối hệ quả và cách dọn UPDATE DELETE nhiều dead tuple tích tụ bảng phình bloat xem SELECT n_live_tup n_dead_tup FROM pg_stat_user_tables VACUUM t thu hồi dead tuple space dùng lại được không trả OS VACUUM FULL t viết lại bảng trả đĩa về OS nhưng khoá cả đọc autovacuum chạy nền tự làm VACUUM thường đừng tắt

Hình 1: MVCC — UPDATE tạo phiên bản mới (live tuple) và giữ phiên bản cũ (dead tuple) để transaction đang chạy vẫn thấy snapshot của nó (reader không chặn writer, như phần 6). Hệ quả: dead tuple tích tụ → bloat. VACUUM thu hồi dead tuple (space dùng lại được, không trả OS); VACUUM FULL viết lại bảng trả đĩa về OS nhưng khoá cả đọc; autovacuum chạy nền tự làm VACUUM thường.

Đo thật trong pg-lab

Mình tạo trong pg-lab (PostgreSQL 16) bảng demo_bloat 100.000 dòng (tắt autovacuum để kiểm soát demo), UPDATE toàn bộ 8 lần, rồi đo kích thước và dead tuple qua từng bước.

Ảnh chụp bảng kết quả chạy thật trong pg-lab output thật postgresql 16 bảng demo_bloat 100.000 dòng autovacuum off, bảng bước kích thước bảng live tuple dead tuple, ban đầu 100k dòng 3.544 kB live 100.000 dead 0, sau UPDATE toàn bộ 8 lần 31 MB live 100.000 dead 799.810, sau VACUUM thường 31 MB live 100.000 dead 0, cộng 1 UPDATE rồi VACUUM lại 31 MB live 100.000 dead 0, sau VACUUM FULL 3.544 kB live 100.000 dead 0, chú thích 8 lần UPDATE làm bảng phình từ 3.5MB lên 31MB khoảng 9 lần dù chỉ 100k dòng sống 799.810 dead tuple tích tụ VACUUM dọn dead tuple dead về 0 nhưng không trả đĩa vẫn 31MB bằng chứng space dùng lại được update thêm cộng vacuum vẫn 31MB không tăng chỉ VACUUM FULL mới trả đĩa về 3.5MB nhưng nó khoá ACCESS EXCLUSIVE chặn cả đọc

Hình 2: Kết quả thật — ban đầu 3.544 kB/dead 0; sau 8 UPDATE 31 MB/dead 799.810 (live vẫn 100.000); VACUUM: dead→0 nhưng size vẫn 31 MB; +1 UPDATE rồi VACUUM lại vẫn 31 MB (space dùng lại); VACUUM FULL: về 3.544 kB.

Con số kể toàn bộ vòng đời của bloat:

  • 8 UPDATE làm bảng phình 9 lần. Bảng 100.000 dòng ban đầu 3.544 kB. Sau khi UPDATE toàn bộ 8 lần, nó phình lên 31 MB — gấp ~9 lần — với 799.810 dead tuple (8 lần × ~100k dòng cũ bị bỏ lại). Nhưng n_live_tup vẫn đúng 100.000: số dòng thật không đổi, chỉ là mỗi dòng giờ có nhiều phiên bản chết nằm chình ình. Đây là bloat: dung lượng và thời gian quét tăng vọt mà dữ liệu hữu ích không tăng.
  • VACUUM dọn dead tuple nhưng không trả đĩa. Sau VACUUM, n_dead_tup về 0 — các xác chết được đánh dấu là không gian dùng lại được. Nhưng kích thước bảng vẫn 31 MB: VACUUM thường không trả đĩa về hệ điều hành, nó chỉ đánh dấu space bên trong bảng là trống để lần ghi sau dùng lại. Bằng chứng: UPDATE thêm một lần rồi VACUUM lại, bảng vẫn 31 MB (không phình thêm) — vì phiên bản mới lấp vào chỗ trống cũ thay vì xin thêm đĩa.
  • Chỉ VACUUM FULL trả đĩa — nhưng khoá cứng. VACUUM FULL viết lại toàn bộ bảng vào file mới gọn gàng, trả bảng về 3.544 kB — đúng kích thước ban đầu. Cái giá: nó giữ khoá ACCESS EXCLUSIVE suốt quá trình, chặn cả đọc lẫn ghi. Trên bảng lớn ở production, VACUUM FULL có thể khoá hàng phút — không dùng bừa được.

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

MVCC đánh đổi dead tuple lấy đồng thời không chặn — đó là một giao dịch tốt. Đừng coi dead tuple là "lỗi" của PostgreSQL. Chính cơ chế giữ nhiều phiên bản này cho phép reader và writer không chặn nhau (khác các CSDL dùng khoá đọc), và làm cho các isolation level (phần 6) hoạt động. Dead tuple là cái giá phải trả cho sự đồng thời đó. Vấn đề không phải là có dead tuple, mà là không dọn chúng.

Autovacuum chạy nền tự dọn — đừng tắt nó. Mình tắt autovacuum trong demo chỉ để kiểm soát thí nghiệm. Trên production, autovacuum (bật mặc định) chạy nền tự động VACUUM các bảng khi dead tuple vượt ngưỡng — đây là thứ giữ bloat trong tầm kiểm soát mà bạn không phải làm gì. Tắt autovacuum là công thức chắc chắn cho thảm hoạ bloat: bảng phình vô hạn, query chậm dần, tới lúc phải VACUUM FULL khẩn cấp (khoá cứng). Nếu bảng ghi nhiều mà vẫn bloat, hãy chỉnh autovacuum aggressive hơn cho bảng đó, đừng tắt.

Long-running transaction giữ dead tuple không cho dọn. Một cái bẫy tinh vi: VACUUM chỉ dọn được dead tuple không còn transaction nào cần. Nếu có một transaction chạy rất lâu (một báo cáo nặng, một kết nối idle-in-transaction bị quên), nó giữ một snapshot cũ, và mọi dead tuple sau thời điểm đó không thể dọn được — dù chúng chết với phần còn lại của hệ thống. Kết quả: bảng bloat dù autovacuum chạy đều. Đây là lý do các transaction dài (và nhất là kết nối "idle in transaction") rất nguy hiểm — chúng chặn cả cơ chế dọn rác. Giám sát pg_stat_activity để bắt các transaction sống lâu.

Ba ý mang về

  1. MVCC: UPDATE tạo bản mới, để lại dead tuple: đo thật, bảng 100.000 dòng sau 8 UPDATE toàn bộ phình từ 3.5MB lên 31MB (~9 lần) với 799.810 dead tuple, dù n_live_tup vẫn đúng 100.000 — bloat làm chậm scan dù dữ liệu hữu ích không tăng; đây là cái giá của đồng thời không chặn.
  2. VACUUM dọn dead tuple, VACUUM FULL trả đĩa: đo thật, VACUUM đưa dead→0 và cho space dùng lại được (update thêm không làm bảng phình tiếp) nhưng không trả đĩa (vẫn 31MB); chỉ VACUUM FULL viết lại bảng về 3.5MB — nhưng khoá ACCESS EXCLUSIVE, chặn cả đọc.
  3. Autovacuum là bạn, transaction dài là kẻ thù của nó: autovacuum (bật mặc định) tự dọn nền — đừng tắt, chỉnh aggressive hơn nếu cần; và cẩn thận transaction chạy lâu / idle-in-transaction vì chúng giữ snapshot cũ khiến VACUUM không dọn được dead tuple, gây bloat dù autovacuum chạy đều.

Nguồn

Phần sau ta chuyển sang một công cụ SQL mạnh mà nhiều người né vì lạ: window function — ROW_NUMBER, RANK, running total — làm được những phép tính "theo nhóm mà vẫn giữ từng dòng" mà GROUP BY không làm nổi, và đo thật so với self-join.