postgresql - Vacuum 與維運

為什麼需要 VACUUM

PostgreSQL 的 MVCC 把新舊版本都存在同一張表裡（見 MVCC 原理）。UPDATE 和 DELETE 留下的舊 tuple（dead tuple）不會自動消失，必須靠 VACUUM 回收。MySQL 不需要這個步驟，因為舊版本在 Undo Log 裡，Purge Thread 會自動清理。

VACUUM

上圖左側展示不 VACUUM 的後果：heap page 中大量 dead tuple 佔用空間但不可見，Sequential Scan 必須掃過這些垃圾，效能下降且檔案不斷膨脹。右側是 VACUUM 後的結果：dead tuple 被標記為可重用空間（加入 Free Space Map），新的 INSERT 可以重用這些位置。

下方比較兩種 VACUUM：一般 VACUUM 不阻塞讀寫、不縮小檔案（空間內部可重用），生產環境靠 autovacuum 自動執行；VACUUM FULL 會重建整張表、真正縮小檔案，但需要 AccessExclusiveLock 阻塞所有讀寫，生產環境盡量不用，改用 pg_repack 做 online rebuild。

VACUUM（一般）

VACUUM orders;

掃描表，找出所有 dead tuple（xmax 已 committed 且不再被任何活躍快照需要）
把 dead tuple 標記為可重用空間（加入 Free Space Map）
更新 Visibility Map（標記哪些 page 上全是 live tuple）
不回縮檔案大小——空間只是內部可重用，不歸還 OS

VACUUM 不阻塞讀寫（不加排他鎖），可以和正常 DML 併發執行。

VACUUM FULL

VACUUM FULL orders;

重建整張表——把 live tuple 複製到新檔案，刪掉舊檔案。真正回縮檔案大小，但：

加排他鎖（AccessExclusiveLock），阻塞所有讀寫
需要額外的磁碟空間存放新表
執行時間和表大小成正比

生產環境盡量不用 VACUUM FULL。替代方案：pg_repack（online rebuild，不阻塞）。

Visibility Map

每個表有一個 Visibility Map（VM），每個 heap page 佔 2 bits：

all-visible：頁面上所有 tuple 對所有交易都可見
all-frozen：頁面上所有 tuple 都已 frozen（不需要再 vacuum）

用途：

Index-Only Scan：如果 VM 標記某 page 為 all-visible，可以直接從 index 拿資料不需要回 heap——這是 PostgreSQL 實現覆蓋索引的方式
VACUUM 加速：跳過 all-visible 的 page，只掃 dirty page

Autovacuum

手動 VACUUM 不實際，生產環境靠 autovacuum。

觸發條件

dead tuple 數 > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × 表行數

預設值：threshold = 50、scale_factor = 0.2。意思是 dead tuple 超過「50 + 表總行數的 20%」就觸發。

對大表（1 億行），要死 2000 萬行才觸發——太晚了。大表應該調低 scale_factor：

ALTER TABLE huge_table SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- 1%
    autovacuum_vacuum_threshold = 1000
);

關鍵參數

autovacuum = on                               # 永遠不要關
autovacuum_max_workers = 3                    # 同時跑幾個 worker
autovacuum_naptime = 1min                     # launcher 每分鐘檢查一次
autovacuum_vacuum_cost_limit = 200            # 每輪 vacuum 的 IO quota
autovacuum_vacuum_cost_delay = 2ms            # 用完 quota 後暫停時間

cost_limit 和 cost_delay 控制 vacuum 的 IO 節流。預設值偏保守，寫入量大的系統建議調高 cost_limit（例如 1000-2000）讓 vacuum 跑快一點，否則 dead tuple 累積速度 > vacuum 清理速度，表會一直膨脹。

Transaction ID Wraparound

問題

PostgreSQL 的交易 ID（txid）是 32-bit 無符號整數，最大約 42 億。用完會 wraparound——txid 210 億突然變成「未來」的交易，所有資料對它來說都是「未來建立的」→ 全部不可見 → 整張表看起來像空的。

解法：Freeze

VACUUM 除了清理 dead tuple，還負責 freeze 舊 tuple——把很久以前 committed 的 tuple 的 xmin 標記為一個特殊的 frozen txid（FrozenTransactionId = 2）。Frozen tuple 對任何交易都可見，不再需要 visibility check，也不受 wraparound 影響。

vacuum_freeze_min_age = 50000000         # tuple 存活超過 5 千萬個交易後 freeze
vacuum_freeze_table_age = 150000000      # 表的 age 超過 1.5 億時強制全表 vacuum
autovacuum_freeze_max_age = 200000000    # 超過 2 億時觸發 aggressive vacuum（不可跳過）

Anti-Wraparound VACUUM

當某張表的 relfrozenxid（最老的未 frozen txid）距離當前 txid 超過 autovacuum_freeze_max_age，autovacuum 會啟動 aggressive vacuum——掃描整張表（不管 Visibility Map），確保所有舊 tuple 都被 frozen。

如果 aggressive vacuum 也跟不上（比如表太大、IO 不夠），PostgreSQL 會在距離 wraparound 還剩 1000 萬個交易時關閉整個資料庫，拒絕任何新交易，強制你手動 vacuum。

-- 查看各表距離 wraparound 還有多遠
SELECT relname,
       age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

這是 PostgreSQL 獨有的維運挑戰，MySQL 完全沒有這個問題。

監控

-- Dead tuple 數量
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- 表膨脹估算（需要 pgstattuple extension）
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');

-- Autovacuum 是否跑得動
SELECT relname, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;

總結

面向	PostgreSQL	MySQL (InnoDB)
舊版本存放	同一張表（heap）	Undo Log
清理機制	VACUUM（必須）	Purge Thread（自動）
表膨脹	會（需要維運）	不會
Crash 回滾	不需要（標記 aborted）	需要 Undo Log 逆向回復
覆蓋索引	靠 Visibility Map	直接在 Secondary Index 讀
Transaction ID	32-bit，需要 freeze 防 wraparound	隱藏欄位，沒有 wraparound 問題
維運複雜度	高（autovacuum tuning 是必修課）	低