VACUUM & dead tuple — vì sao TaskFlow phình to dù count(*) không tăng

TaskFlow chạy 6 tháng. Bảng tasks báo chiếm 5 GB trên disk, nhưng SELECT count(*) chỉ trả về 50 nghìn row. Mỗi row khoảng 2 KB → expected ~100 MB. 4,9 GB còn lại là gì?

Không phải corruption. Không phải log. Đó là dead tuple — tàn tích tích luỹ của hàng triệu UPDATE và DELETE mà cơ chế MVCC (bài 4 của module này) chủ động giữ lại để phục vụ concurrent snapshot, và sau đó chưa ai dọn.

Bài này mở từng lớp của vấn đề: tại sao dead tuple sinh ra, VACUUM làm gì để reclaim chúng, autovacuum daemon vận hành ra sao, và hai pitfall production khiến bloat tích luỹ theo cấp số nhân cho dù autovacuum đang chạy.

1. Analogy — Hộp thư điện tử

Hãy nghĩ về inbox email của bạn — cụ thể là cách email "bị xoá" diễn ra theo nhiều tầng.

2. Dead tuple — kết quả tất yếu của MVCC

Bài 4 của module này đã giải thích: trong PostgreSQL, UPDATE không sửa tuple tại chỗ. Thay vào đó:

1. UPDATE = set xmax của tuple cũ (txid hiện tại) + INSERT một tuple mới với xmin mới.
2. DELETE = chỉ set xmax của tuple đó. Không có gì bị xoá vật lý.

Tuple với xmax đã committed và không còn snapshot nào cần nhìn thấy nó nữa gọi là dead tuple. Nó vẫn chiếm chỗ vật lý trên heap page, không visible với bất kỳ query nào, nhưng postgres chưa tái sử dụng slot đó.

-- Sau khi 1 trieu UPDATE tasks SET status = 'done':
-- Moi UPDATE tao 1 dead tuple (tuple cu) + 1 live tuple (tuple moi)
-- Neu autovacuum chua kip chay:
-- pg_relation_size('tasks') = (live_tuples + dead_tuples) * avg_tuple_size

-- Xem ngay:
SELECT
  n_live_tup,
  n_dead_tup,
  n_dead_tup::float / nullif(n_live_tup, 0) AS dead_ratio
FROM pg_stat_user_tables
WHERE relname = 'tasks';

-- n_live_tup | n_dead_tup | dead_ratio
--      50000 |    2450000 |       49.0   <- 49 dead tren moi 1 live

Đây chính xác là tình huống TaskFlow: 50 nghìn task active, nhưng 6 tháng UPDATE assignee_id, status, updated_at đã sinh ra gần 2,5 triệu dead tuple chưa được reclaim.

3. Đo bloat — trước khi hành động

Không đoán mò — đo trước, tune sau.

-- Giam sat dead tuple % theo tung bang
SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 2) AS dead_pct,
  last_vacuum,
  last_autovacuum,
  pg_size_pretty(pg_relation_size(relid))  AS table_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- relname | n_live_tup | n_dead_tup | dead_pct | last_autovacuum        | table_size
-- tasks   |      50000 |    2450000 |  4900.00 | 2025-11-01 03:14:22+07 | 5012 MB
-- projects|       1200 |        180 |    15.00 | 2025-11-03 02:00:11+07 | 3 MB

Để có số chính xác hơn (byte-level, không phụ thuộc stats counter), dùng extension pgstattuple:

-- Extension chinh xac hon: do truc tiep tren heap
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  table_len,
  tuple_count,
  dead_tuple_count,
  dead_tuple_len,
  dead_tuple_percent,
  free_space,
  free_percent
FROM pgstattuple('tasks');

-- table_len | tuple_count | dead_tuple_count | dead_tuple_len | dead_tuple_percent
-- 5252014080|       50000 |          2450000 |     4998246400 |              95.16
-- -> 95% khong gian la dead tuple, chi 5% la live data

pgstattuple thực sự scan full table — chậm trên bảng lớn. Chỉ chạy khi cần diagnose, không chạy định kỳ.

4. VACUUM vs VACUUM FULL — chọn cái nào?

-- VACUUM thuong: chay bất cu luc nao, khong can window
VACUUM tasks;

-- VACUUM + cap nhat statistics cho query planner (Module 7 cua khoa nay)
VACUUM ANALYZE tasks;

-- VACUUM FULL: chi trong maintenance window, co the ngat production
-- VACUUM FULL tasks;  -- KHONG chay production gio cao diem

-- Xem tien trinh VACUUM dang chay:
SELECT
  pid,
  phase,
  heap_blks_scanned,
  heap_blks_total,
  round(100.0 * heap_blks_scanned / nullif(heap_blks_total, 0), 1) AS pct
FROM pg_stat_progress_vacuum
WHERE relid = 'tasks'::regclass;

Tại sao VACUUM không trả disk về OS? Để tái sử dụng slot ngay cho INSERT/UPDATE tiếp theo mà không cần resize file (tốn I/O). VACUUM chỉ cập nhật FSM (Free Space Map) — một bitmap nhỏ ghi lại page nào có slot trống. INSERT tiếp theo tra FSM trước khi tạo page mới.

5. Autovacuum — daemon background tự động

Autovacuum là background daemon của PostgreSQL, chạy theo chu kỳ và tự quyết định khi nào vacuum bảng nào.

Công thức trigger VACUUM:

n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup

Với default PostgreSQL:

• autovacuum_vacuum_threshold = 50
• autovacuum_vacuum_scale_factor = 0.2 (20%)

→ Bảng 1 triệu row: trigger khi 1,000,000 × 0.2 + 50 = 200,050 dead tuple.

Công thức trigger ANALYZE (cập nhật statistics cho query planner):

n_mod_since_analyze > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * n_live_tup

Default: 50 + 0.1 × n_live_tup.

Monitor autovacuum workers:

-- Xem autovacuum workers dang chay
SELECT
  pid,
  datname,
  relname,
  phase,
  heap_blks_scanned,
  heap_blks_total
FROM pg_stat_progress_vacuum pv
JOIN pg_stat_activity pa USING (pid)
WHERE pa.backend_type = 'autovacuum worker';

-- Lich su autovacuum tren tung bang
SELECT
  relname,
  last_vacuum,
  last_autovacuum,
  vacuum_count,
  autovacuum_count,
  n_dead_tup,
  n_live_tup
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY last_autovacuum DESC NULLS LAST;

autovacuum_max_workers (default 3) giới hạn số worker song song. Với nhiều bảng heavy-write, 3 worker có thể không đủ — tăng lên 5–6 trên server có nhiều CPU.

6. Per-table autovacuum tuning — khi default không đủ

Default 20% (0.2) scale factor hợp lý cho bảng nhỏ. Với bảng lớn hoặc heavy-update, threshold quá cao — autovacuum trigger muộn, bloat tích luỹ.

Nguyên tắc: Bảng càng lớn và càng nhiều UPDATE → giảm scale_factor càng mạnh.

-- tasks: heavy UPDATE (assignee_id, status, updated_at) -> aggressive tune
ALTER TABLE tasks SET (
  autovacuum_vacuum_scale_factor = 0.05,   -- trigger khi 5% dead (default 20%)
  autovacuum_vacuum_threshold = 100,
  autovacuum_analyze_scale_factor = 0.02,  -- 2% modified -> ANALYZE
  fillfactor = 80                          -- HOT update friendly (bai 2 module 05)
);

-- task_history: insert-only audit log -> gan default la ok
-- khong can tune aggressive (dead tuple gan = 0)
ALTER TABLE task_history SET (
  autovacuum_vacuum_scale_factor = 0.1,
  autovacuum_analyze_scale_factor = 0.05
);

-- Disable autovacuum cho 1 bang (KHONG khuyen nghi, chi cho special case
-- nhu bulk load temporary table)
-- ALTER TABLE temp_import SET (autovacuum_enabled = false);

-- Xem storage params hien tai cua bang:
SELECT reloptions FROM pg_class WHERE relname = 'tasks';
-- {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100,...}

Sau khi tune, xác nhận autovacuum chạy sớm hơn bằng cách watch last_autovacuum trong pg_stat_user_tables.

7. Visibility Map — và Index Only Scan

Visibility Map (VM) là một bitmap nhỏ PostgreSQL duy trì song song với mỗi heap file. Mỗi heap page có 2 bit trong VM:

Khi VACUUM quét một heap page và xác nhận không còn dead tuple, nó set bit all-visible trong VM cho page đó.

Tại sao VM quan trọng — Index Only Scan:

Thông thường khi query chỉ cần column nằm trong index (covering index), planner vẫn phải "heap fetch" — đọc heap page để kiểm tra tuple visibility. Nếu VM bit = all-visible, planner biết mọi tuple trên page đó đều safe — skip heap fetch hoàn toàn.

-- Sau khi VACUUM chay va set VM bits:
EXPLAIN ANALYZE
SELECT id, status FROM tasks WHERE project_id = 42;

-- Voi index (project_id, status) va VM all-visible:
-- Index Only Scan using idx_tasks_project_status on tasks
--   Index Cond: (project_id = 42)
--   Heap Fetches: 0    <- VM all-visible, khong can doc heap
--   Rows: 312  Actual: 0.3 ms

-- Khi chua VACUUM (VM bit chua set hoac dead tuple con):
-- Index Scan (khong phai Index Only Scan)
--   Heap Fetches: 312  <- phai doc heap moi row
--   Actual: 8.2 ms     <- cham 27x

Module 7 của khoá này (EXPLAIN ANALYZE) sẽ đi sâu vào đọc execution plan — bạn sẽ thấy Heap Fetches: 0 là tín hiệu Index Only Scan đang hoạt động đúng. Điều kiện: index phải cover đủ column query cần (bài 2 module 05 — composite index) VÀ VM phải có bit all-visible — tức VACUUM phải đã chạy gần đây.

8. Pitfall — Long transaction và VACUUM FULL downtime

-- Kich ban: session A BEGIN luc 9h, khong commit den 11h
-- session A: xmin = txid_9h = 5000

-- Trong 2h do, co 500.000 UPDATE tren tasks
-- -> 500.000 dead tuple voi xmax = 5001, 5002, ..., 505000

-- Autovacuum chay luc 9h30:
-- Oldest active snapshot xmin = 5000
-- Khong the reclaim dead tuple nao co xmax > 5000
-- -> tat ca 500.000 dead tuple van giu nguyen, bloat tang

-- Detect long transaction giu snapshot cu (co the block VACUUM)
SELECT
  pid,
  now() - query_start                AS duration,
  age(backend_xmin)                  AS xmin_age,
  state,
  wait_event_type,
  left(query, 80)                    AS query_preview
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

-- xmin_age > 100000 = dang giu snapshot cu ~ block VACUUM reclaim
-- Fix: terminate neu la idle-in-transaction
-- SELECT pg_terminate_backend(pid) FROM pg_stat_activity
--   WHERE state = 'idle in transaction'
--     AND now() - query_start > interval '10 minutes';

-- Phong ngua: dat timeout cho idle in transaction
-- idle_in_transaction_session_timeout = '10min'  (trong postgresql.conf)

-- KHONG lam production:
-- VACUUM FULL tasks;  -- block tat ca, co the chay > 1 gio

-- Thay the: pg_repack (Module 11 cua khoa nay)
-- pg_repack xay dung ban copy moi trong khi table van serve traffic,
-- sau do atomic swap. Khong lock, khong downtime.
-- cai dat: CREATE EXTENSION pg_repack;
-- chay:    pg_repack -t tasks -d taskflow_db

9. Applied — TaskFlow per-table tuning

Phân tích workload:

• task_history: insert-only audit log → dead tuple gần bằng 0 → autovacuum default OK.
• tasks: heavy UPDATE (assignee_id, status, updated_at thay đổi thường xuyên) → cần aggressive autovacuum tuning.

-- Aggressive autovacuum cho tasks (heavy update)
ALTER TABLE tasks SET (
  autovacuum_vacuum_scale_factor = 0.05,   -- trigger khi 5% dead (thay vi 20%)
  autovacuum_vacuum_threshold = 100,
  autovacuum_analyze_scale_factor = 0.02,  -- 2% modified -> update planner stats
  fillfactor = 80                          -- giu 20% free space cho HOT update
);

-- Weekly bloat audit query (chay tren cron hoac monitoring)
SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup, 0), 2)  AS dead_pct,
  pg_size_pretty(pg_relation_size(relid))                AS size,
  age(now(), last_autovacuum)                            AS since_last_av
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC NULLS LAST;

-- Alert rule: dead_pct > 20% -> xem lai scale_factor
-- dead_pct > 50% va since_last_av > '1 hour' -> autovacuum bi block?
--   -> check pg_stat_activity long transactions

Fix immediate bloat (trong maintenance window):

-- Manual vacuum khi can reset nhanh (khong block, nhung giu disk):
VACUUM ANALYZE tasks;

-- Sau khi fix long transaction pitfall, autovacuum se chay lai
-- va reclaim dead tuple. Kiem tra progress:
SELECT phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum
WHERE relid = 'tasks'::regclass;

Kết quả kỳ vọng sau tuning: autovacuum trigger sớm hơn (ở 5% thay vì 20%), bloat ổn định dưới 10%, Heap Fetches: 0 trong EXPLAIN khi dùng covering index.

10. Deep Dive

11. Tóm tắt

• Dead tuple = tàn tích MVCC: UPDATE/DELETE chỉ set xmax — tuple cũ vẫn nằm trên heap, chiếm chỗ vật lý. Sau khi mọi snapshot không còn cần nó, nó trở thành dead tuple chờ VACUUM.
• VACUUM mark FSM reusable, không trả OS: VACUUM scan heap + index, đánh dấu slot dead tuple có thể tái sử dụng trong FSM. Disk space thực sự chưa giảm — INSERT/UPDATE tiếp theo sẽ tái dùng slot đó.
• VACUUM FULL trả OS nhưng cần downtime: Rewrite toàn bộ table → drop file cũ. Cần ACCESS EXCLUSIVE lock, block mọi query. Production dùng pg_repack (Module 11 của khoá này) thay thế.
• Autovacuum daemon: Trigger theo công thức n_dead_tup > threshold + scale_factor × n_live_tup. Default: 50 + 0.2 × size. Bảng heavy-update cần giảm scale_factor xuống 0.05 hoặc thấp hơn qua ALTER TABLE ... SET (...).
• Long transaction block autovacuum: Snapshot xmin thấp → VACUUM không reclaim dead tuple có xmax lớn hơn → bloat tích luỹ vô hạn. Monitor bằng age(backend_xmin) trong pg_stat_activity. Phòng ngừa bằng idle_in_transaction_session_timeout.
• Visibility Map (VM): Bitmap 2-bit per heap page (all-visible, all-frozen). VACUUM set bit khi page sạch. All-visible → planner có thể dùng Index Only Scan, Heap Fetches: 0 — đọc index mà không cần touch heap.
• VACUUM ANALYZE: Kết hợp reclaim dead tuple + cập nhật statistics cho query planner (Module 7 của khoá này) — nên chạy sau bulk load hoặc data migration lớn.
• Monitoring thiết yếu: pg_stat_user_tables cho n_dead_tup, last_autovacuum; pg_stat_progress_vacuum khi VACUUM đang chạy; pgstattuple extension cho byte-accurate bloat measurement.

12. Tự kiểm tra

Bài tiếp theo: Locks & deadlock — 8 lock mode, FOR UPDATE SKIP LOCKED queue pattern