Bài trước ta thấy index phình từ 21 MB lên 172 MB, và VACUUM không co lại được — chỉ REINDEX mới đưa về 21 MB. Nhưng REINDEX có một cái bẫy y hệt CREATE INDEX: nó khóa ghi cả bảng trong lúc chạy. Trên production, dựng lại một index bloat vài GB có thể ngừng ghi hàng phút. Và cũng y như vậy, có một biến thể CONCURRENTLY né được. Bài này đo cả hai.

REINDEX làm gì, và khóa gì

REINDEX dựng lại index từ đầu — quét dữ liệu bảng, xây một cấu trúc index mới compact, rồi thay thế cái cũ. Vì compact, nó xóa sạch bloat: các trang nửa rỗng biến mất, density trở về ~90%.

REINDEX INDEX idx_kho2_sku;   -- dựng lại một index cụ thể
REINDEX TABLE kho2;           -- dựng lại MỌI index của bảng

Nhưng để làm điều đó an toàn, REINDEX thường lấy khóa nặng. Đo thật trên bảng 5 triệu dòng, bắt lock từ pg_locks trong lúc nó chạy:

Ảnh chụp bảng kết quả đo thật nền tối REINDEX trên bảng 5 triệu dòng PostgreSQL 16, khóa và ảnh hưởng tới ghi bắt từ pg_locks, REINDEX thường lấy ShareLock trên bảng cộng AccessExclusiveLock trên index nên INSERT song song bị chặn, REINDEX CONCURRENTLY lấy ShareUpdateExclusiveLock nên INSERT song song chạy được trả về INSERT 0 1, thời gian dựng lại cùng index với maintenance_work_mem 16MB là REINDEX thường 703 mili giây REINDEX CONCURRENTLY 899 mili giây chậm hơn khoảng 1,3 lần cái giá để không chặn ghi, hiệu quả co index từ bài trước index bloat 172 MB trước REINDEX 172 MB density 22 phần trăm sau REINDEX 21 MB density 90 phần trăm trả lại khoảng 150 MB dung lượng, nếu CONCURRENTLY thất bại để lại index INVALID hậu tố ccnew phải tìm qua pg_index where not indisvalid rồi DROP INDEX CONCURRENTLY

Hình 1: REINDEX thường lấy ShareLock trên bảng + AccessExclusiveLock trên index — chặn ghi. REINDEX CONCURRENTLY chỉ lấy ShareUpdateExclusiveLock, INSERT song song trả về INSERT 0 1. Đổi lại CONCURRENTLY chậm hơn (703 so với 899 ms).

ShareLock trên bảng không chặn đọc — SELECT vẫn chạy — nhưng xung khắc với khóa ghi, nên INSERT/UPDATE/DELETE phải xếp hàng chờ. Cộng thêm AccessExclusiveLock trên chính index. Kết quả: mọi ghi vào bảng đứng lại tới khi REINDEX xong. Đúng cái ta muốn tránh trên hệ thống đang chạy.

REINDEX CONCURRENTLY: dựng lại mà vẫn cho ghi

Thêm CONCURRENTLY, PostgreSQL đổi chiến lược: dựng một index mới song song với index cũ (bắt kịp các thay đổi trong lúc dựng), rồi tráo. Nó chỉ cần ShareUpdateExclusiveLock — cho phép INSERT/UPDATE/DELETE chạy đồng thời.

REINDEX INDEX CONCURRENTLY idx_kho2_sku;   -- không chặn ghi

Đo thật xác nhận: pg_locks cho thấy ShareUpdateExclusiveLock, và INSERT song song trả về INSERT 0 1 ngay lập tức. Một điểm tiện: REINDEX INDEX CONCURRENTLY giữ nguyên tên index sau khi tráo, nên không phải sửa gì trong ứng dụng hay migration.

Ảnh chụp đoạn mã SQL nền tối minh hoạ REINDEX dựng lại index để xóa bloat hai kiểu hai mức khóa, nhắc bài trước index phình 172 MB VACUUM không co REINDEX dựng lại index từ đầu compact trả 172 MB về còn 21 MB nhưng nó khóa gì, REINDEX thường lấy ShareLock trên bảng cộng AccessExclusiveLock trên index chặn mọi ghi tới khi xong đọc vẫn chạy, REINDEX INDEX và REINDEX TABLE dựng lại mọi index của bảng, REINDEX CONCURRENTLY chỉ ShareUpdateExclusiveLock ghi vẫn chạy, cái giá là chậm hơn và nếu thất bại để lại index INVALID hậu tố ccnew phải DROP tay tìm qua pg_index where not indisvalid, mẹo REINDEX INDEX CONCURRENTLY giữ nguyên tên index không cần đổi code

Hình 2: Ba dạng REINDEX và cách dọn index INVALID. CONCURRENTLY giữ nguyên tên index nên không cần đổi code; đổi lại có ba ràng buộc vận hành.

Hiệu quả co index

Dù thường hay concurrently, kết quả co bloat như nhau. Trên index đã phình ở bài trước:

  • Trước REINDEX: 172 MB, avg_leaf_density 22%.
  • Sau REINDEX: 21 MB, density 90%.

Trả lại khoảng 150 MB dung lượng — đó là toàn bộ chỗ trống mà bloat để lại, giờ được thu hồi thật sự về hệ điều hành (khác VACUUM chỉ đánh dấu dùng lại được).

Đánh đổi phải nhớ

CONCURRENTLY chậm hơn. Đo thật: 703 ms (thường) so với 899 ms (concurrently) — chênh ~1,3 lần vì phải dựng index mới song song rồi tráo. Trên index lớn khoảng cách còn rộng hơn. Bạn đổi thời gian lấy việc không chặn ghi — gần như luôn đáng trên production.

Thất bại để lại index INVALID hậu tố _ccnew. Y như CREATE INDEX CONCURRENTLY, nếu REINDEX CONCURRENTLY hỏng giữa chừng, nó để lại một index dở dang tên idx..._ccnew với indisvalid = f. Index này vô dụng cho truy vấn nhưng vẫn ngốn chi phí ghi. Phải tự tìm và xóa:

SELECT c.relname FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

DROP INDEX CONCURRENTLY idx_kho2_sku_ccnew;

REINDEX cần thêm dung lượng đĩa tạm. Vì dựng index mới trước khi bỏ cái cũ, trong lúc chạy cần đủ chỗ cho cả hai. Trên index bloat lớn, đảm bảo còn dư đĩa trước khi chạy.

Khi nào dùng cái nào. Trong bảo trì có cửa sổ ngừng dịch vụ (đêm khuya, không ai ghi), REINDEX thường nhanh hơn và đơn giản. Trên hệ thống 24/7 không được ngừng ghi, luôn dùng CONCURRENTLY.

Ba ý mang về

  1. REINDEX dựng lại index compact, xóa bloat (172 MB → 21 MB, density 22% → 90%) và thu hồi dung lượng thật về hệ điều hành — thứ VACUUM không làm được.
  2. REINDEX thường khóa ghi cả bảng (ShareLock + AccessExclusiveLock trên index) — đo thật, INSERT song song bị chặn; dùng nó chỉ khi có cửa sổ bảo trì.
  3. REINDEX CONCURRENTLY cho ghi chạy song song (ShareUpdateExclusiveLock, INSERT 0 1 thành công) với cái giá chậm hơn ~1,3 lần và có thể để lại index INVALID _ccnew phải dọn — lựa chọn mặc định cho hệ thống đang chạy.

Phần sau ta chuyển từ index bị phình sang index chưa bao giờ được dùng: Phần sau đo cách tìm những index không truy vấn nào chạm tới — chúng chỉ tốn chi phí ghi mà không đổi lại gì — và xóa an toàn.