maintenance_work_mem trong PostgreSQL: RAM cho VACUUM và CREATE INDEX

Bài trước cảnh báo work_mem phải đặt khiêm tốn vì nó nhân lên theo số kết nối. maintenance_work_mem là người anh em của nó — cũng là RAM cho các thao tác cần bộ nhớ tạm — nhưng dành cho bảo trì: VACUUM, CREATE INDEX, REINDEX, ALTER TABLE ADD FOREIGN KEY. Điểm khác biệt then chốt là bạn có thể (và nên) đặt nó cao hơn work_mem nhiều. Bài này đo thật tác động của nó — với một kết quả bất ngờ: nó ảnh hưởng VACUUM rất lớn nhưng CREATE INDEX lại khiêm tốn.

maintenance_work_mem tách khỏi work_mem

maintenance_work_mem (mặc định 64MB, so với work_mem chỉ 4MB) là RAM cho các thao tác bảo trì. Lý do nó tách riêng và đặt cao hơn được rất quan trọng: chỉ vài thao tác bảo trì chạy đồng thời, khác với hàng trăm sort/hash của các truy vấn thường.

SHOW maintenance_work_mem;  -- mặc định 64MB
-- dùng cho: VACUUM, CREATE INDEX, REINDEX, ALTER TABLE ADD FK

Nhớ lại phép tính đáng sợ của work_mem: 100 kết nối × 256MB × 3 thao tác = 75 GB. Với maintenance_work_mem, không có phép nhân đó — thường chỉ một hoặc vài lệnh VACUUM/CREATE INDEX chạy cùng lúc. Nên đặt nó 256MB, 512MB, hay 1GB an toàn hơn nhiều.

Hình 1: maintenance_work_mem cho VACUUM/CREATE INDEX/REINDEX, tách khỏi work_mem và đặt cao hơn được vì ít thao tác bảo trì đồng thời. Tác dụng rõ nhất ở VACUUM.

Đo thật: VACUUM quét index 1 lần thay vì 58 lần

Đây là nơi maintenance_work_mem tạo khác biệt lớn nhất, và ít người biết. Khi VACUUM dọn dead tuple, nó thu thập danh sách các dead tuple vào maintenance_work_mem, rồi quét index để gỡ con trỏ tới chúng. Nếu danh sách không vừa, VACUUM phải xử lý theo nhiều lô — và mỗi lô cần một lần quét toàn bộ index.

Đo thật trên bảng 10 triệu dòng xóa hết (10 triệu dead tuple):

Hình 2: VACUUM 10 triệu dead tuple với maintenance_work_mem 1MB cần 58 lần quét index; với 1GB chỉ 1 lần. CREATE INDEX 8 triệu dòng: 16MB ~10,2s, 1GB ~11,8s — khác biệt nhỏ.

• maintenance_work_mem 1MB: VACUUM VERBOSE báo index scans: 58 — danh sách 10 triệu dead tuple không vừa 1MB, nên VACUUM xử lý theo lô và quét lại toàn bộ index 58 lần.
• maintenance_work_mem 1GB: index scans: 1 — cả danh sách vừa RAM, chỉ một lần quét.

58 lần quét index so với 1 lần! Trên bảng lớn có index lớn, mỗi lần quét thừa là đọc lại cả index — chênh lệch này quyết định VACUUM chạy vài phút hay vài giờ. Đây là lý do quan trọng nhất để đặt maintenance_work_mem đủ cao.

Bất ngờ: CREATE INDEX tác dụng khiêm tốn

Nhiều người tin tăng maintenance_work_mem làm CREATE INDEX nhanh gấp bội. Đo thật cho thấy điều đó không đúng lắm: tạo index trên 8 triệu dòng (cột md5), maintenance_work_mem 16MB mất ~10,2 giây, 1GB mất ~11,8 giây — khác biệt nhỏ, thậm chí là nhiễu. Vì sao? External sort của PostgreSQL đã rất hiệu quả; với dữ liệu vừa cache, việc sort dựa đĩa không chậm hơn nhiều so với trong RAM. maintenance_work_mem cao có giúp trên bảng cực lớn (giảm số merge pass), nhưng đừng kỳ vọng phép màu — tác dụng rõ nhất của nó là ở VACUUM, không phải CREATE INDEX.

Cách đặt

ALTER SYSTEM SET maintenance_work_mem = '512MB';  -- toàn cục
SET maintenance_work_mem = '1GB';                 -- per-session cho một lần bảo trì lớn

Với máy chủ có RAM khá (16GB+), đặt maintenance_work_mem 512MB-1GB toàn cục là hợp lý — nó giúp cả VACUUM thủ công lẫn CREATE INDEX. Và có thể nâng cao hơn nữa per-session trước một lần REINDEX/VACUUM lớn.

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

Autovacuum dùng autovacuum_work_mem, không phải maintenance_work_mem trực tiếp. Autovacuum có tham số riêng autovacuum_work_mem — nếu để -1 (mặc định) nó rơi về dùng maintenance_work_mem. Nhưng nhớ: nhiều autovacuum worker chạy song song (mặc định 3), nên tổng RAM autovacuum = số worker × giá trị này. Nếu đặt maintenance_work_mem rất cao và để autovacuum dùng nó, ba worker có thể ngốn gấp ba — cân nhắc đặt autovacuum_work_mem riêng, thấp hơn.

Cao hơn không phải luôn tốt cho CREATE INDEX song song. Khi max_parallel_maintenance_workers > 0, maintenance_work_mem được chia cho các worker. Đặt quá cao mà ít RAM thực có thể phản tác dụng. Đo với cấu hình song song thật của bạn.

PostgreSQL 17 thay đổi cách VACUUM lưu dead tuple. PG17 dùng cấu trúc TID store mới hiệu quả hơn nhiều, làm giảm áp lực lên maintenance_work_mem cho VACUUM (ít cần đặt cực cao). Trên PG16 (bài này), giới hạn danh sách dead tuple vẫn là ~1GB cứng và số lần quét index vẫn phụ thuộc maintenance_work_mem như đo ở trên.

Ba ý mang về

1. maintenance_work_mem là RAM cho VACUUM/CREATE INDEX/REINDEX, tách khỏi work_mem và đặt cao hơn được: vì chỉ vài thao tác bảo trì chạy đồng thời (không nhân theo trăm kết nối như work_mem), 256MB-1GB là an toàn.
2. Tác dụng lớn nhất ở VACUUM — số lần quét index: đo thật, VACUUM 10 triệu dead tuple với maintenance_work_mem 1MB cần 58 lần quét index, với 1GB chỉ 1 lần — vì danh sách dead tuple phải vừa RAM để tránh xử lý theo lô.
3. Tác dụng lên CREATE INDEX khiêm tốn: đo thật, tạo index 8 triệu dòng chỉ nhanh hơn chút giữa 16MB và 1GB (10,2s vs 11,8s) — external sort đã hiệu quả; đừng kỳ vọng phép màu, và nhớ autovacuum dùng autovacuum_work_mem (× số worker).

Phần sau ta quay lại tham số đã nhắc ở bài shared_buffers: Phần sau mổ xẻ effective_cache_size — vì sao nó chỉ là gợi ý cho planner (không cấp phát bộ nhớ), ảnh hưởng lựa chọn index scan vs seq scan, và đặt bao nhiêu.