Giao dịch dài giữ bloat: vì sao một tab quên đóng làm phình cả bảng PostgreSQL

Bài isolation level cho thấy một giao dịch giữ ảnh chụp (snapshot) dữ liệu. Bài này phơi bày một tác dụng phụ nguy hiểm ít ai ngờ của snapshot đó: một giao dịch mở lâu — kể cả một tab psql quên đóng hay một truy vấn analytics chạy hàng giờ — có thể khiến cả database phình bloat, vì nó ngăn VACUUM dọn dead tuple. Bài này đo thật hiện tượng và chỉ cách tìm thủ phạm.

Cơ chế: xmin horizon

Mỗi giao dịch đang mở giữ một backend_xmin — mốc giao dịch cũ nhất mà nó có thể cần "thấy". VACUUM chỉ được phép xóa một dead tuple nếu nó cũ hơn xmin cũ nhất trong toàn hệ thống. Lý do: giao dịch dài kia, theo snapshot của nó, có thể vẫn cần đọc phiên bản cũ của hàng đó (như bài MVCC đã bàn — mỗi UPDATE tạo phiên bản mới, giữ phiên bản cũ cho ai còn cần).

Hệ quả then chốt và phản trực giác: một giao dịch mở lâu chặn việc dọn dead tuple của MỌI bảng, kể cả dead tuple do các giao dịch khác tạo ra sau đó. Nó không cần ghi gì — chỉ cần mở là đủ giữ mốc dọn dẹp đứng yên.

-- A: giao dịch DÀI (analytics lâu, hoặc 'idle in transaction' quên đóng)
BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM blo;   -- giữ snapshot
-- B: sinh nhiều dead tuple bằng cách UPDATE toàn bảng nhiều lần
UPDATE blo SET v=v+1;   -- lặp lại; mỗi UPDATE tạo dead tuple mới
VACUUM (VERBOSE) blo;   -- có dọn được không?

Hình 1: Mỗi giao dịch mở giữ backend_xmin; VACUUM không xóa được dead tuple mới hơn xmin cũ nhất toàn hệ thống. Một giao dịch dài chặn dọn dẹp của mọi bảng. Tìm thủ phạm qua pg_stat_activity, phòng bằng idle_in_transaction_session_timeout.

Đo thật: bảng phình 4 lần, 300k dead tuple mắc kẹt

Tôi tạo bảng blo 100k dòng (3.544 kB), mở một giao dịch A (REPEATABLE READ) giữ snapshot, rồi từ session B cập nhật toàn bảng 3 lần để sinh dead tuple:

Hình 2: Trong khi A còn mở, bảng phình 3.544 kB → 14 MB với 300.000 dead tuple; VACUUM báo "300000 are dead but not yet removable". Thủ phạm là pid 44074 giữ backend_xmin=284330 (chính là removable cutoff). Đóng A rồi VACUUM: 300.000 dead tuple được xóa, n_dead_tup=0.

• Trong khi A còn mở: bảng phình từ 3.544 kB lên 14 MB (~4 lần) với 300.000 dead tuple. VACUUM VERBOSE báo thẳng: 0 removed, 400000 remain, 300000 are dead but not yet removable. VACUUM chạy nhưng không xóa được gì — vì A giữ snapshot cũ hơn.
• Thủ phạm: pg_stat_activity cho thấy pid 44074, backend_xmin=284330, giao dịch đã mở 20 giây. Con số 284330 này chính là removable cutoff mà VACUUM báo — A đang neo mốc dọn dẹp lại.
• Đóng A rồi VACUUM lại: 300000 removed, 0 are dead but not yet removable, n_dead_tup=0. Vừa đóng giao dịch dài, xmin horizon tiến lên và toàn bộ dead tuple xóa được ngay.

Tìm thủ phạm và phòng tránh

Truy vấn tìm giao dịch giữ mốc dọn dẹp (cũ nhất ở đầu):

SELECT pid, state, backend_xmin,
       now()-xact_start AS tuoi_giao_dich, query
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY xact_start;

Phòng và chữa:

-- Chặn 'idle in transaction' treo lâu:
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
-- Gỡ khẩn: đóng giao dịch dài (nó tự commit, hoặc):
SELECT pg_terminate_backend(<pid>);

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

idle in transaction là thủ phạm phổ biến nhất — không phải truy vấn chạy lâu. Nhiều hệ thống bloat không phải vì query nặng, mà vì ứng dụng mở BEGIN rồi đi làm việc khác (gọi API, chờ input người dùng) mà chưa commit. Kết nối ngồi im ở trạng thái idle in transaction vẫn giữ snapshot. Đặt idle_in_transaction_session_timeout là lá chắn quan trọng nhất; và về mã, đừng bao giờ mở giao dịch rồi thực hiện thao tác chậm/chờ mạng bên trong.

Analytics chạy lâu nên tách khỏi primary. Một báo cáo quét hàng giờ trên primary giữ snapshot suốt thời gian đó, làm bloat mọi bảng đang bị ghi. Chạy analytics trên một replica đọc riêng (có hot_standby_feedback cân nhắc riêng) để không neo mốc dọn dẹp của primary.

Bloat không tự biến mất khi VACUUM chạy — chỉ khi giao dịch dài đóng. Đừng tưởng tăng tần suất autovacuum sẽ cứu: autovacuum vẫn không xóa được dead tuple bị neo. Giải pháp là loại bỏ giao dịch dài, sau đó (nếu bảng đã phình to) dùng VACUUM FULL/pg_repack để đòi lại đĩa (như bài bloat đã đo). Giám sát pg_stat_activity cho giao dịch/idle-in-transaction cũ là việc vận hành cốt lõi.

Ba ý mang về

1. Một giao dịch mở lâu giữ xmin horizon, chặn VACUUM dọn dead tuple của MỌI bảng: đo thật, trong khi một giao dịch REPEATABLE READ còn mở, VACUUM báo "300000 are dead but not yet removable" và bảng phình 3,5 MB → 14 MB dù chính giao dịch đó không ghi gì.
2. Đóng giao dịch dài là cách gỡ: đo thật, sau khi kết thúc giao dịch A, VACUUM xóa ngay 300.000 dead tuple (n_dead_tup=0) — vì xmin horizon tiến lên.
3. Tìm thủ phạm qua pg_stat_activity.backend_xmin + tuổi giao dịch, phòng bằng idle_in_transaction_session_timeout: thủ phạm phổ biến là idle in transaction quên đóng, không phải query nặng; và tách analytics chạy lâu sang replica riêng.

Phần sau ta chuyển sang chủ đề mở rộng quy mô bảng lớn: Phần sau mổ xẻ phân vùng bảng (partitioning) theo range, list và hash — khi nào nên chia bảng, và partition pruning giúp truy vấn nhanh thế nào.