PostgreSQL không phải "cài xong rồi quên". Ba việc bảo trì nền — ANALYZE, VACUUM, REINDEX — quyết định database chạy nhanh hay ì ạch theo thời gian. May là autovacuum tự lo phần lớn, nhưng hiểu mỗi việc làm gì giúp bạn chẩn đoán khi có chuyện, và biết khi nào cần ra tay thủ công. Bài này đo thật cả ba: một truy vấn bị đoán sai 4 lần vì thống kê cũ, một triệu dead tuple được dọn, và một index phình gấp bốn lần được dựng lại về như mới.

ANALYZE: giữ cho planner nhìn đúng

Planner của PostgreSQL chọn kế hoạch dựa trên thống kê về phân bố dữ liệu — bảng có bao nhiêu dòng, một giá trị xuất hiện bao nhiêu lần. Nếu thống kê cũ (vừa nạp một khối lớn, hoặc dữ liệu đổi nhiều), planner ước lượng sai số dòng và có thể chọn nhầm kế hoạch — ví dụ Seq Scan khi lẽ ra Index Scan rẻ hơn.

ANALYZE bang;              -- thu thập thống kê toàn bảng
ANALYZE bang (cot1, cot2); -- chỉ vài cột
VACUUM ANALYZE bang;       -- dọn dead tuple + cập nhật thống kê một lượt

Ảnh chụp đoạn mã SQL nền tối minh hoạ ba việc bảo trì định kỳ ANALYZE VACUUM REINDEX PostgreSQL 16 autovacuum lo phần lớn nhưng cần biết mỗi việc làm gì, ANALYZE cập nhật thống kê để planner chọn kế hoạch đúng ANALYZE bang thu thập phân bố dữ liệu của bảng ANALYZE bang cot1 cot2 chỉ vài cột thống kê cũ planner ước lượng sai số dòng chọn nhầm Seq Scan thay Index Scan chạy sau khi nạp đổi nhiều dữ liệu, VACUUM dọn dead tuple trả chỗ cho bảng tái dùng VACUUM bang dọn dead tuple không khoá ghi không trả HĐH VACUUM ANALYZE bang dọn cộng cập nhật thống kê một lượt VACUUM FULL bang viết lại cả bảng trả chỗ cho HĐH nhưng khoá ACCESS EXCLUSIVE chặn cả đọc, REINDEX dựng lại index đã phình REINDEX INDEX idx dựng lại một index khoá ghi REINDEX TABLE bang mọi index của bảng REINDEX INDEX CONCURRENTLY idx không khoá ghi chậm hơn index phình sau nhiều UPDATE DELETE trên cột có index, chẩn đoán xem bảng nào cần bảo trì SELECT relname n_dead_tup last_autovacuum last_analyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC n_dead_tup cao cần VACUUM last_analyze cũ cần ANALYZE

Hình 1: ANALYZE cập nhật thống kê cho planner; VACUUM dọn dead tuple (bản FULL trả chỗ cho HĐH nhưng khoá cả đọc); REINDEX dựng lại index phình. Dùng pg_stat_user_tables để biết bảng nào cần bảo trì.

Đo thật trên bảng hai triệu dòng, cột loai có 50 giá trị (mỗi giá trị ~40.000 dòng). Ngay sau khi nạp, chưa ANALYZE:

Ảnh chụp bảng kết quả đo thật nền tối ANALYZE VACUUM REINDEX PostgreSQL 16, ANALYZE thống kê cũ khiến planner ước lượng sai bảng 2 triệu dòng loại bằng 7 có 40000 dòng thật trạng thái chưa ANALYZE vừa nạp reltuples catalog -1 chưa biết planner ước lượng rows 10000 thực tế 40000 sau ANALYZE reltuples 2000000 planner ước lượng rows 42000 thực tế 40000 chưa ANALYZE đoán bừa 10k sai 4 lần dễ chọn nhầm kế hoạch sau sát thực, VACUUM dọn dead tuple bảng 1 triệu dòng UPDATE cả bảng bước sau UPDATE cả bảng dead tuple 1000000 kích thước bảng 69 MB VACUUM 218 ms dead tuple 0 kích thước 69 MB chỗ trống để tái dùng VACUUM FULL 216 ms dead tuple 0 kích thước 35 MB trả về HĐH VACUUM thường không khoá ghi và không trả HĐH VACUUM FULL trả HĐH nhưng khoá cả đọc, REINDEX dựng lại index đã phình 5 lần UPDATE cột có index trạng thái idx_v ban đầu 21 MB sau 5x UPDATE cộng VACUUM phình 86 MB gấp 4 lần REINDEX 137 ms 21 MB về như mới REINDEX INDEX CONCURRENTLY 176 ms chậm hơn chút nhưng không khoá ghi

Hình 2: Chưa ANALYZE, planner đoán 10.000 dòng (sai 4 lần so với 40.000 thực). Sau ANALYZE: đoán 42.000 — sát thực. VACUUM dọn 1 triệu dead tuple (218 ms) nhưng bảng vẫn 69 MB; VACUUM FULL trả về 35 MB. Index phình 21→86 MB, REINDEX về 21 MB.

  • Chưa ANALYZE: catalog ghi reltuples = -1 (chưa biết), planner đoán bừa 10.000 dòng trong khi thực tế là 40.000 — sai 4 lần. Ước lượng lệch cỡ này dễ khiến planner chọn nhầm kế hoạch cho các truy vấn phức tạp hơn.
  • Sau ANALYZE: reltuples = 2.000.000, planner đoán 42.000 — sát với 40.000 thực. Bây giờ nó chọn kế hoạch trên cơ sở đúng.

VACUUM: dọn dead tuple

Như các bài trước đã thấy, mỗi UPDATE/DELETE để lại dead tuple — phiên bản dòng cũ không còn ai thấy nhưng vẫn chiếm chỗ. VACUUM quét bảng, đánh dấu những chỗ đó là trống để tái dùng cho dòng mới.

Đo thật: bảng một triệu dòng, UPDATE cả bảng tạo một triệu dead tuple (69 MB). Sau VACUUM (218 ms), dead tuple về 0 — nhưng kích thước bảng vẫn 69 MB. Đây là điểm hay nhầm: VACUUM thường không trả chỗ cho hệ điều hành, nó chỉ đánh dấu chỗ trống để PostgreSQL ghi đè lên sau. Muốn thật sự thu nhỏ tệp, cần VACUUM FULL — đo thật viết lại cả bảng còn 35 MB trả cho HĐH. Đổi lại VACUUM FULL lấy khoá ACCESS EXCLUSIVE (chặn cả đọc lẫn ghi), nên chỉ chạy trong cửa sổ bảo trì.

Trong thực tế, autovacuum làm việc này tự động khi dead tuple vượt ngưỡng, nên hiếm khi bạn phải gọi VACUUM tay. Nhưng khi một bảng ghi/xoá dữ dội, cần theo dõi n_dead_tup để chắc autovacuum theo kịp.

REINDEX: dựng lại index phình

Index cũng phình. Khi bạn UPDATE một cột có index nhiều lần, mỗi lần tạo một mục index mới và bỏ mục cũ — cây B-tree đầy những trang thưa thớt, kích thước đội lên dù số dòng không đổi.

Đo thật: index idx_v ban đầu 21 MB. Sau 5 lần UPDATE cột v (có index) rồi VACUUM, nó phình lên 86 MB — gấp bốn lần! (VACUUM dọn dead tuple trong index nhưng không nén lại cây.) REINDEX INDEX idx_v dựng lại từ đầu, đưa về 21 MB trong 137 ms — gọn như mới. Bản REINDEX INDEX CONCURRENTLY mất 176 ms (chậm hơn chút) nhưng không khoá ghi, dùng được trên bảng đang phục vụ.

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

Autovacuum lo phần lớn, đừng vô hiệu hoá nó. ANALYZE và VACUUM chạy tự động theo ngưỡng dead tuple và số dòng thay đổi. Tắt autovacuum là công thức cho thảm hoạ: thống kê thối, dead tuple chất thành núi, bảng phình không kiểm soát. Việc thủ công chỉ để bổ sung khi cần (sau một lần nạp lớn, trước một cửa sổ bảo trì), không phải để thay thế.

Phân biệt VACUUM và VACUUM FULL rất quan trọng. VACUUM nhẹ, không khoá ghi, chạy thường xuyên được. VACUUM FULL nặng, khoá cả đọc, chỉ dùng khi thật sự cần thu hồi đĩa (bảng đã phình lớn sau một đợt xoá hàng loạt). Đừng chạy VACUUM FULL theo lịch định kỳ trên bảng đang phục vụ.

REINDEX cần khi index phình nhiều, nhưng đo trước. Không phải index nào cũng phình. Kiểm kích thước index so với số dòng (hoặc dùng extension như pgstattuple) trước khi REINDEX. Trên bảng đang chạy, luôn ưu tiên CONCURRENTLY để không khoá ghi — trừ khi bạn có cửa sổ bảo trì.

Ba ý mang về

  1. ANALYZE giữ thống kê tươi để planner chọn đúng kế hoạch: đo thật, thống kê cũ khiến planner đoán 10.000 dòng trong khi thực tế 40.000 (sai 4 lần) — chạy sau khi nạp hoặc đổi nhiều dữ liệu, hoặc để autovacuum lo.
  2. VACUUM dọn dead tuple cho tái dùng nhưng không trả chỗ cho HĐH: đo thật dọn 1 triệu dead tuple trong 218 ms mà bảng vẫn 69 MB; chỉ VACUUM FULL mới thu nhỏ (về 35 MB) nhưng khoá cả đọc nên chỉ dùng trong cửa sổ bảo trì.
  3. REINDEX dựng lại index phình về như mới: đo thật một index phình từ 21 lên 86 MB (gấp 4 lần) sau nhiều UPDATE, REINDEX đưa về 21 MB — dùng CONCURRENTLY để không khoá ghi trên bảng đang phục vụ; và nhớ đừng bao giờ tắt autovacuum.

Phần sau ta chuyển sang chủ đề mở rộng và độ sẵn sàng cao: Phần sau tìm hiểu streaming replication — cách PostgreSQL đẩy WAL sang máy chủ dự phòng để có bản sao nóng, và các đánh đổi giữa đồng bộ và bất đồng bộ.