Một index bạn tạo hôm nay chiếm 21 MB. Vài tháng sau, cùng index đó — cùng số dòng — chiếm 172 MB. Không ai thêm dữ liệu, vậy 150 MB kia ở đâu ra? Đó là index bloat: không gian rỗng tích tụ bên trong file index qua thời gian, làm index to hơn cần thiết, ngốn bộ nhớ đệm và làm chậm cả đọc lẫn ghi. Bài này tái tạo bloat thật trên bảng 1 triệu dòng và đo nó bằng pgstattuple.

Vì sao index phình

Nguồn gốc nằm ở MVCC — cơ chế đa phiên bản của PostgreSQL. Mỗi khi bạn UPDATE một dòng ở cột có index, PostgreSQL tạo một phiên bản dòng mới và một mục index mới trỏ tới nó; mục index cũ trở thành "chết" nhưng vẫn nằm đó. DELETE cũng để lại mục chết tương tự.

VACUUM sẽ dọn các mục chết này — nhưng đây là điểm mấu chốt nhiều người hiểu sai: VACUUM chỉ đánh dấu không gian là dùng lại được, chứ không trả nó về hệ điều hành. File index vẫn giữ nguyên kích thước, chỉ là bên trong có nhiều trang nửa rỗng. Qua nhiều vòng update, index tích tụ đầy những trang lá thưa thớt — đó chính là bloat.

-- Cách 1: đo chính xác bằng pgstattuple (quét cả index)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT index_size, avg_leaf_density, leaf_fragmentation
FROM pgstatindex('idx_kho_sku');

Ảnh chụp đoạn mã SQL nền tối minh hoạ index bloat vì sao index phình và cách đo, giải thích MVCC mỗi UPDATE cột có index tạo một mục index mới mục cũ thành chết VACUUM dọn mục chết nhưng để lại trang rỗng trong file index không trả về hệ điều hành nên qua thời gian index chứa đầy trang nửa rỗng gọi là bloat, cách một chính xác dùng pgstattuple đo từng trang qua pgstatindex xem avg_leaf_density thấp là nhiều chỗ rỗng bloat nặng và leaf_fragmentation cao là trang lá lộn xộn, cách hai nhanh không quét theo dõi kích thước qua pg_stat_user_indexes so với kích thước lý thuyết, và khắc phục bằng REINDEX dựng lại compact còn VACUUM không co index

Hình 1: Hai cách đo bloat. pgstatindex cho số chính xác (avg_leaf_density, leaf_fragmentation) nhưng phải quét cả index; theo dõi kích thước qua pg_stat_user_indexes thì nhanh và không tốn gì.

Đo thật: 21 MB phình thành 172 MB

Tạo bảng 1 triệu dòng với index trên cột sku, rồi chạy 10 lần UPDATE toàn bảng lên chính cột đó (mô phỏng một cột trạng thái/tồn kho bị sửa liên tục). Đo index qua từng giai đoạn:

Ảnh chụp bảng kết quả đo thật nền tối index trên bảng một triệu dòng sau 10 lần UPDATE cột sku PostgreSQL 16, ban đầu sạch index 21 MB avg_leaf_density 90,06 phần trăm leaf_fragmentation 0, sau 10 UPDATE bloat index 172 MB density 77,39 phần trăm fragmentation 50, sau VACUUM vẫn 172 MB density tụt còn 22,74 phần trăm fragmentation 49,99, sau REINDEX trở về 21 MB density 90,06 phần trăm fragmentation 0, hai điều mấu chốt là index phình 8 lần chỉ sau 10 lần update toàn bảng vì mỗi update cột có index để lại mục chết, và VACUUM không co index vẫn 172 MB chỉ đánh dấu mục chết dùng lại được density tụt còn 22,74 phần trăm là 77 phần trăm dung lượng bỏ trống chỉ REINDEX mới dựng lại compact trả về 21 MB, dấu hiệu bloat là avg_leaf_density tụt sâu dưới 90 phần trăm leaf_fragmentation cao kích thước index lớn bất thường so với số dòng thực

Hình 2: Index phình từ 21 MB (density 90%) lên 172 MB sau 10 lần update. VACUUM giữ nguyên 172 MB — chỉ đánh dấu chỗ trống dùng lại được (density tụt còn 22,74%). REINDEX mới đưa về 21 MB.

Đọc bảng đo:

  • Ban đầu: 21 MB, avg_leaf_density 90,06%, leaf_fragmentation 0 — index gọn, gần như đặc.
  • Sau 10 lần UPDATE: 172 MB, density 77,39%, fragmentation 50. Index phình 8 lần dù số dòng không đổi.
  • Sau VACUUM: vẫn 172 MB. Density tụt xuống 22,74% — nghĩa là VACUUM đã dọn sạch mục chết, nhưng 77% dung lượng file giờ là chỗ trống dùng lại được, không co lại. Đây là bằng chứng rõ nhất cho việc VACUUM không giải quyết bloat kích thước.
  • Sau REINDEX: về lại 21 MB, density 90,06%, fragmentation 0 — index được dựng lại đặc và compact.

Ba dấu hiệu nhận biết

Khi soi một index nghi bloat, nhìn ba thứ:

avg_leaf_density tụt sâu dưới ~90%. Index vừa dựng có density khoảng 90% (mặc định fillfactor của B-tree). Thấy 40%, 20% nghĩa là quá nửa index là chỗ trống — bloat nặng.

leaf_fragmentation cao. Các trang lá không còn nằm liền mạch theo thứ tự, khiến việc quét khoảng (range scan) phải nhảy nhiều — chậm hơn dù cùng dữ liệu.

Kích thước lớn bất thường so với số dòng. So pg_relation_size(indexrelid) với ước lượng thô "số dòng × cỡ khoá". Chênh lệch lớn là dấu hiệu, và cách này không cần quét index nên rẻ để chạy định kỳ:

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan
FROM pg_stat_user_indexes WHERE relname = 'kho';

Đánh đổi khi đo và xử lý

pgstatindex chính xác nhưng đắt. Nó quét toàn bộ index để đếm density thật, nên trên index nhiều GB đừng chạy vô tội vạ trên production giờ cao điểm. Dùng cách ước lượng qua kích thước cho việc theo dõi thường xuyên, và pgstatindex khi cần xác nhận một nghi vấn cụ thể.

Một chút bloat là bình thường và có ích. Density 90% (tức 10% trống) là thiết kế, không phải lỗi — chỗ trống đó cho phép chèn mục mới mà không phải tách trang liên tục. Đừng REINDEX chỉ vì density 85%; chỉ hành động khi bloat thật sự lớn (density xuống 40-50% hoặc thấp hơn) và index đủ quan trọng.

Bloat là hệ quả tự nhiên của bảng update-nặng. Không tránh hẳn được; điều chỉnh được là tần suất VACUUM (autovacuum) để giữ mục chết không dồn quá nhiều, và fillfactor để cân bằng giữa chỗ trống cho update và độ đặc.

Ba ý mang về

  1. Index bloat là không gian rỗng tích tụ do MVCC: mỗi UPDATE/DELETE trên cột có index để lại mục chết — đo thật, index phình từ 21 MB lên 172 MB (8 lần) sau 10 lần update toàn bảng dù số dòng không đổi.
  2. VACUUM không co index: nó chỉ đánh dấu chỗ trống dùng lại được (density tụt còn 22,74% nhưng vẫn 172 MB) — cần phân biệt rõ với việc thực sự thu hồi dung lượng.
  3. Đo bloat bằng pgstatindex (avg_leaf_density thấp, leaf_fragmentation cao) khi cần chính xác, hoặc theo dõi kích thước qua pg_stat_user_indexes khi cần nhanh — và chỉ xử lý khi bloat thật sự lớn.

Phần sau ta đi vào chính công cụ đã đưa index về 21 MB: Phần sau nói về REINDEX và REINDEX CONCURRENTLY — cách dựng lại index mà không khóa bảng, và cái giá của nó.