VACUUM, autovacuum và bloat: vì sao bảng vẫn lớn sau DELETE? Trung cấp

Vấn đề

Bảng job được cập nhật liên tục: pending, running, done. Vài tuần sau count(*) vẫn quanh một triệu row nhưng file bảng lớn lên nhiều lần. Đội vận hành DELETE job cũ: row giảm, file trên đĩa gần như không đổi.

Đây là hành vi bình thường của MVCC: UPDATE/DELETE để lại version cũ cho các snapshot đang đọc, VACUUM dọn chúng và đánh dấu chỗ trống để tái sử dụng, nhưng thường không trả file về hệ điều hành.

Ba thứ hay bị trộn:

• Dead tuple: version không còn ai nhìn thấy.
• Free space: chỗ trống trong file, dùng lại được.
• Bloat: phần dung lượng dư đáng kể so với dữ liệu sống và nhu cầu ghi tiếp. File to mà sắp ghi nhiều thì chưa chắc là vấn đề; file và index phình đều kèm latency tăng thì là.

Bốn lệnh, bốn việc khác nhau

	Việc làm	Lock	Trả disk về OS?
VACUUM	Dọn dead tuple và index entry tương ứng, cập nhật visibility map, freeze XID cũ	Không chặn đọc/ghi thông thường	Thường không. Ngoại lệ: các page trống ở cuối bảng có thể bị cắt nếu lấy được exclusive lock dễ dàng
ANALYZE	Lấy mẫu cập nhật statistics cho planner	Nhẹ	Không
VACUUM FULL	Viết lại cả bảng thành file mới, không còn chỗ trống	ACCESS EXCLUSIVE: chặn mọi truy cập; cần thêm disk bằng cỡ bảng	Có
autovacuum	Tiến trình nền tự chạy VACUUM và ANALYZE theo ngưỡng; còn chạy chống wraparound	Như VACUUM	Như VACUUM

Đừng tắt autovacuum để đỡ I/O: nó đang trả chi phí bắt buộc của MVCC và bảo vệ an toàn XID (xem wraparound).

Heap page trước và sau (minh hoạ)

Trước VACUUM  (một page, 6 chỗ)
  [ live ][ DEAD ][ live ][ DEAD ][ DEAD ][ live ]

Sau VACUUM
  [ live ][ free ][ live ][ free ][ free ][ live ]   file KHÔNG co; free dùng cho INSERT/UPDATE sau

Sau VACUUM FULL
  [ live ][ live ][ live ]                           file mới, nhỏ hơn; bảng bị khoá suốt quá trình

Ngưỡng autovacuum: số mặc định và phép tính

Autovacuum chạy VACUUM trên một bảng khi số dead tuple vượt:

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor x reltuples

Tham số (PostgreSQL 17)	Mặc định
autovacuum_naptime	1min
autovacuum_max_workers	3
autovacuum_vacuum_threshold / scale_factor	50 / 0.2
autovacuum_vacuum_insert_threshold / insert_scale_factor	1000 / 0.2
autovacuum_analyze_threshold / scale_factor	50 / 0.1
autovacuum_vacuum_cost_delay	2ms

Ví dụ tính: bảng 10 triệu row, mặc định. Ngưỡng là 50 + 0,2 x 10.000.000 = 2.000.050 dead tuple mới kích hoạt. Với bảng job bị update dày, autovacuum đến quá muộn, và mỗi lần chạy phải dọn một khối lớn.

Chỉnh riêng bảng nóng, không đổi mặc định toàn cụm:

ALTER TABLE jobs SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- 10M row: ngưỡng 1.000 + 100.000 = 101.000 thay vì 2.000.050
  autovacuum_vacuum_threshold    = 1000,
  autovacuum_vacuum_cost_delay   = 0       -- bớt nhường I/O, chỉ làm cho bảng nóng
);

PostgreSQL 18 thêm autovacuum_vacuum_max_threshold, đặt trần số dead tuple cố định để bảng cực lớn không phải đợi theo phần trăm. PostgreSQL 18 cũng thêm autovacuum_worker_slots để đổi autovacuum_max_workers mà không restart.

Thứ chặn VACUUM dọn: “xmin horizon”

VACUUM chỉ xoá version mà không snapshot nào còn cần. Một thứ giữ snapshot cũ sẽ chặn dọn cho mọi bảng, và dead tuple tích tụ dù autovacuum vẫn chạy:

Nguồn	Cách phát hiện	Cách chặn
Transaction dài, hoặc session idle in transaction	pg_stat_activity theo xact_start	idle_in_transaction_session_timeout (mặc định 0, tức tắt) và statement_timeout
Prepared transaction bị bỏ quên	pg_prepared_xacts	Rollback hoặc commit
Replication slot cũ	pg_replication_slots	Xoá slot chết (cẩn thận, replica có thể cần dựng lại)
Replica bật hot_standby_feedback chạy query dài	Kiểm tra trên replica	Rút ngắn query trên replica

UPDATE tạo phiên bản cũ; VACUUM thu hồi khi an toàn

Phóng to sơ đồ

UPDATE → Heap versionsv1 → v2

◀ Trước▶ PhátTiếp ▶↺ Từ đầuBước 1/5

UPDATE tạo version mới; version cũ vẫn nằm trong heap vì snapshot đang mở có thể cần nó.

Quan sát

-- Bảng nào nhiều dead tuple nhất; n_dead_tup là ước lượng, dùng để xem xu hướ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 dead_pct,
       last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- VACUUM đang chạy (view này chỉ có dữ liệu trong lúc đang chạy)
SELECT relid::regclass, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

-- Ai đang giữ transaction cũ nhất
SELECT pid, state, now() - xact_start AS transaction_age, query
FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;

Cách đọc nhanh:

• last_autovacuum cũ chưa chắc là autovacuum tắt: có thể bảng chưa đạt ngưỡng.
• pg_relation_size() không giảm sau VACUUM là bình thường. Dùng pg_total_relation_size() nếu cần tính cả index và TOAST.
• Index phình là chuyện riêng: chữa bằng REINDEX CONCURRENTLY, không phải VACUUM FULL.
• Đo bloat chính xác theo byte cần extension pgstattuple; n_dead_tup không thay được nó.

Ví dụ thực tế

Bảng jobs 10 triệu row phình từ 4 GB lên 30 GB

Worker đổi trạng thái mỗi job 3 lần, mỗi lần là một version mới. Với mặc định, autovacuum chỉ chạy khi có hơn 2 triệu dead tuple; trong lúc đó một báo cáo mở transaction sáu tiếng giữ horizon, nên lần dọn sau còn dọn được ít hơn.

Cách xử lý:

1. Tìm và chặn transaction dài.
2. ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0.01).
3. Xoá job done cũ theo lô, hoặc tốt hơn là partition theo ngày rồi DETACH PARTITION/DROP TABLE partition cũ (PostgreSQL không có lệnh DROP PARTITION; xem partitioning).
4. Không chạy VACUUM FULL giờ cao điểm.

Con số 4 GB và 30 GB là kịch bản minh hoạ. Bảng queue đặc biệt nhạy với bloat, xem SKIP LOCKED.

Demo (tạo và xoá bảng vacuum_demo, không chạy trên database có bảng cùng tên):

docker compose -f examples/docker-compose.yml up -d postgres
docker compose -f examples/docker-compose.yml exec -T postgres psql -U hub -d hub < examples/src/postgres-vacuum/vacuum-demo.sql

Script tạo 10.000 row, UPDATE tất cả một lần, in kích thước heap trước và sau, chạy VACUUM (VERBOSE, ANALYZE) rồi in lại. Điều cần rút ra: count(*) vẫn 10.000 và file không bắt buộc co lại sau VACUUM. Dùng bảng thường, không dùng TEMP, vì bảng TEMP không được autovacuum xử lý.

Khi nào VACUUM FULL là chấp nhận được

• Đã xoá vĩnh viễn phần lớn dữ liệu và bảng sẽ không lớn lại.
• Có maintenance window, đủ disk cho bản sao mới, và chấp nhận chặn mọi truy cập vào bảng.
• Không dùng làm cứu hộ wraparound: nó rewrite bảng, lấy lock mạnh và bản thân có thể cần XID.

Muốn thu nhỏ bảng mà ít gián đoạn hơn, dùng công cụ ngoài như pg_repack hoặc pg_squeeze (cần kiểm tra khả dụng trên nền tảng của bạn).

Checklist production

1. Theo dõi theo bảng: n_dead_tup, dead_pct, last_autovacuum, kích thước heap và index theo thời gian.
2. Bảng update dày (queue, session, counter): đặt autovacuum_vacuum_scale_factor thấp (cỡ 0.01-0.05) cho riêng bảng đó.
3. Đặt idle_in_transaction_session_timeout và statement_timeout cho app; cảnh báo transaction dài hơn vài phút.
4. Kiểm tra định kỳ prepared transaction, replication slot không hoạt động và hot_standby_feedback.
5. Xoá dữ liệu cũ theo partition thay vì DELETE hàng loạt khi có thể.
6. Index phình: REINDEX CONCURRENTLY. VACUUM FULL chỉ theo kế hoạch có window.
7. Không tắt autovacuum, kể cả cho bảng chỉ insert (vẫn cần freeze).

Trade-offs

VACUUM thường

• Chạy song song với app, thu hồi chỗ cho lần ghi sau.
• Autovacuum tự theo dõi workload và bảo vệ khỏi wraparound.
• VACUUM (ANALYZE) làm mới statistics cho planner sau thay đổi lớn.

Khi muốn thu nhỏ file

• Tốn I/O; chạy muộn hoặc bị horizon chặn thì bloat tích tụ.
• Thường không trả dung lượng về OS.
• VACUUM FULL trả disk nhưng rewrite bảng, cần thêm disk và chặn truy cập.

Sai lầm thường gặp

Sai lầm thường gặp

“DELETE một triệu row rồi VACUUM mà disk không giảm, chắc VACUUM hỏng.” VACUUM chuẩn bị chỗ trống để tái sử dụng. Xem xu hướng kích thước và tốc độ ghi tiếp trước khi quyết định cần trả disk về OS.

Sai lầm thường gặp

“Tắt autovacuum để giảm tải I/O.” Dead tuple tích tụ và cơ chế freeze tự động biến mất. Bảng quá nóng thì chỉnh ngưỡng và cost riêng cho bảng đó.

Sai lầm thường gặp

“VACUUM FULL là bảo trì vô hại.” Nó lấy ACCESS EXCLUSIVE, rewrite bảng và cần chỗ trống tạm. Trên production phải tính lock wait, thời gian chạy và dung lượng trước.

Tóm tắt

Tóm tắt

• UPDATE/DELETE để lại version cũ; VACUUM dọn khi không còn snapshot cần và để chỗ trống tái sử dụng.
• Mặc định, autovacuum chỉ chạy khi dead tuple vượt 50 + 20% số row: bảng lớn và nóng cần ngưỡng riêng.
• Transaction dài, prepared transaction, replication slot và hot_standby_feedback chặn dọn cho cả database.
• VACUUM FULL là rewrite có khoá mạnh, không phải routine fix; index bloat dùng REINDEX CONCURRENTLY.

Tự kiểm tra: (1) Vì sao DELETE đã commit mà file chưa nhỏ? (2) Bảng 10 triệu row ở mặc định cần bao nhiêu dead tuple để autovacuum chạy, và chỉnh thế nào cho bảng nóng? (3) Một transaction chỉ đọc mở lâu liên quan gì đến dead tuple?

Tham khảo

• Routine Vacuuming — PostgreSQL 17
• VACUUM — PostgreSQL 17
• Automatic Vacuuming — PostgreSQL 17
• Statistics Views — PostgreSQL 17
• Progress Reporting — PostgreSQL 17
• Release Notes — PostgreSQL 18