Bảng PostgreSQL ngày càng chậm: phân biệt bloat với thiếu index, chữa đúng bệnh

"Bảng này hồi trước nhanh, giờ ngày càng chậm" — một than phiền kinh điển. Nhưng đằng sau nó là hai nguyên nhân rất khác nhau, và chữa nhầm thì vô ích: bloat (bảng phình to do dead tuple tích tụ) hoặc thiếu index (truy vấn buộc quét tuần tự khi dữ liệu lớn dần). Chúng có triệu chứng giống nhau — chậm dần — nhưng cách chẩn đoán và cách chữa hoàn toàn khác. Bài này đo thật một bảng bị bloat, chỉ cách phân biệt với thiếu index, và chữa đúng.

Hai nguyên nhân, hai cách chữa

Trước khi chữa, phải chẩn đoán. Truy vấn phân biệt nhìn vào ba thứ: tỷ lệ dead tuple, kích thước bảng so với số dòng sống, và bảng có bị seq scan không.

SELECT relname, n_live_tup, n_dead_tup,
  round(100.0*n_dead_tup/nullif(n_live_tup+n_dead_tup,0),1) AS pct_chet,
  seq_scan, idx_scan,
  pg_size_pretty(pg_relation_size(relid)) AS kich_thuoc
FROM pg_stat_user_tables WHERE relname='phien';

Hình 1: Truy vấn phân biệt bloat với thiếu index. Bloat: dead tuple cao, kích thước phình, query vẫn dùng index. Thiếu index: seq_scan cao, EXPLAIN cho Seq Scan. Chữa bloat bằng VACUUM/VACUUM FULL, chữa thiếu index bằng thêm index.

Đo thật: một bảng bị bloat

Bảng phien 500.000 dòng, ban đầu sạch: 21 MB, index 3.512 kB, một truy vấn index scan đọc 50 buffer. Tôi mô phỏng ứng dụng cập nhật cột cap_nhat liên tục — UPDATE cả bảng 8 lần:

Hình 2: Sau 8 lần UPDATE, bảng phình 21→176 MB (8,4×), index 3.512 kB→14 MB, 1,5 triệu dead tuple (75%), buffer query 50→147. VACUUM dọn dead nhưng giữ 176 MB; VACUUM FULL trả về 21 MB, buffer query về 50.

Diễn biến thật:

• Ban đầu: 21 MB, buffer query 50.
• Sau 8 lần UPDATE: bảng phình lên 176 MB (8,4 lần!), index 14 MB, 1.499.990 dead tuple, và truy vấn giờ đọc 147 buffer thay vì 50 — chậm hơn.

Chẩn đoán: đây là bloat, không phải thiếu index

Truy vấn phân biệt cho câu trả lời rõ ràng: n_live = 500.000, n_dead = 1.499.990, pct_chet = 75%, kích thước 176 MB cho một bảng lẽ ra chỉ 21 MB. Dead tuple chiếm ba phần tư — đây là bloat kinh điển do UPDATE nhiều mà autovacuum không theo kịp.

Điểm mấu chốt để phân biệt với thiếu index: truy vấn VẪN dùng index (Index Scan), chỉ là đọc nhiều buffer hơn vì phải lội qua các trang đầy dead tuple. Nếu là thiếu index, EXPLAIN sẽ cho thấy Seq Scan và seq_scan/seq_tup_read cao (như bài pg_stat_user_tables đã đo) — một triệu chứng hoàn toàn khác.

Chữa: VACUUM hay VACUUM FULL

Hai cách, tuỳ mức độ:

• VACUUM phien (136 ms): dọn dead tuple, đưa n_dead_tup về 0, đánh dấu chỗ trống để tái dùng. Nhưng kích thước vẫn 176 MB — không trả đĩa cho hệ điều hành. Không khoá ghi, chạy thường xuyên được. Với bảng còn tiếp tục UPDATE, đây thường là đủ (chỗ trống sẽ được ghi đè lại).
• VACUUM FULL phien (240 ms): viết lại toàn bộ bảng, trả đĩa (176→21 MB) và nén luôn index (14 MB→3.512 kB). Nhưng nó lấy khoá ACCESS EXCLUSIVE (chặn cả đọc), nên chỉ chạy trong cửa sổ bảo trì.

Sau VACUUM FULL, truy vấn đọc lại 50 buffer — về đúng như ban đầu. Nếu chỉ index phình (không phải bảng), REINDEX là đủ và nhẹ hơn VACUUM FULL.

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

Phòng ngừa tốt hơn chữa. Bloat xảy ra khi autovacuum không theo kịp tốc độ tạo dead tuple. Thay vì VACUUM FULL định kỳ (nặng, khoá), hãy điều chỉnh autovacuum cho bảng UPDATE nhiều: giảm autovacuum_vacuum_scale_factor để nó chạy thường xuyên hơn, và cân nhắc fillfactor thấp hơn (chừa chỗ trong trang cho HOT update, giảm bloat ngay từ đầu — bài về HOT đã đo). Bảng khoẻ mạnh không bao giờ cần VACUUM FULL.

Đừng nhầm hai bệnh. Chữa bloat (VACUUM) trên một bảng thật ra thiếu index sẽ vô ích — query vẫn seq scan chậm. Ngược lại, thêm index cho một bảng bloat không giải quyết được phình. Bước chẩn đoán (dead tuple vs kế hoạch query) là để tránh chính sai lầm này. Luôn xem EXPLAIN để biết query dùng index hay seq scan.

VACUUM FULL không phải lúc nào cũng cần. Với một bảng UPDATE liên tục, VACUUM thường xuyên (giữ dead tuple ở mức thấp, tái dùng chỗ) đủ giữ hiệu năng ổn định — không cần trả đĩa. Chỉ dùng VACUUM FULL khi bảng đã phình lớn sau một đợt xoá/cập nhật hàng loạt và bạn cần thu hồi đĩa. Nó khoá bảng, nên cân nhắc pg_repack (không khoá) cho production nếu cần.

Ba ý mang về

1. Một bảng chậm dần có hai nguyên nhân khác nhau: bloat (phình do dead tuple, query vẫn dùng index nhưng đọc nhiều buffer hơn) hoặc thiếu index (query chuyển sang seq scan) — phân biệt bằng n_dead_tup/pct_chet và kế hoạch EXPLAIN trước khi chữa.
2. Đo thật một bảng phình 21→176 MB (8,4×) do 8 lần UPDATE: 75% dead tuple, buffer query tăng 50→147; VACUUM (136 ms) dọn dead về 0 nhưng giữ 176 MB, VACUUM FULL (240 ms) trả về 21 MB và buffer query về 50.
3. Chữa đúng loại và phòng ngừa gốc: VACUUM thường xuyên cho bảng UPDATE liên tục (tái dùng chỗ, không khoá), VACUUM FULL/REINDEX khi cần thu đĩa (khoá, dùng trong bảo trì) — nhưng tốt nhất là điều chỉnh autovacuum và fillfactor để bloat không tích tụ ngay từ đầu.

Phần sau ta xử một tình huống vận hành cụ thể rất hay gặp: Phần sau một dashboard chạy nhanh lúc vắng nhưng chậm hẳn vào giờ cao điểm — cách tìm ra nút thắt xuất hiện dưới tải đồng thời và xử lý nó.