PostgreSQL 18 如何設計 autovacuum worker 容量治理？

題目與背景

一套 PostgreSQL 18 叢集承載高更新率訂單表和低頻歸檔表。最近出現死元組堆積、查詢變慢與磁碟 I/O 峰值。請設計 autovacuum worker 容量治理，說明 autovacuumworkerslots、autovacuummaxworkers、每表參數、平行維護和應急取捨。

面試官考察什麼

• 能否區分 worker 資源上限、同時執行數量、觸發閾值和單次 VACUUM 的平行度。
• 能否解釋 PostgreSQL 18 新增的 autovacuumworkerslots 與執行時調整邊界。
• 能否使用進度視圖、死元組、年齡和 I/O 指標定位瓶頸。
• 能否在防止事務 ID wraparound、業務延遲和磁碟壓力之間排序取捨。

先問清楚的澄清問題

工作負載

每個資料庫和資料表的更新、刪除、插入速率是多少？熱點表是否有大量索引、長事務或批次刪除？SLA 更關注查詢延遲、寫入吞吐還是磁碟增長？

資源邊界

CPU、I/O、記憶體和磁碟增長上限是多少？maxworkerprocesses、maxparallelworkers、maintenanceworkmem 以及容器或虛擬機限制如何配置？

風險與恢復

是否接近事務 ID wraparound？能否在低峰執行手工 VACUUM 或分區切換？哪些資料表允許暫時降低寫入，哪些指標必須保留稽核？

30 秒回答框架

我會先按資料表和資料庫量化死元組增長、autovacuum 延遲、事務年齡和 I/O，再分配 worker slots 與最大並發。熱點表使用較低的 scale factor 和合適 threshold，冷表保持較低排程頻率。提高並發前先核對 CPU、I/O 和維護記憶體，使用進度視圖驗證是否真的縮短 backlog。任何 wraparound 風險優先級最高，業務限流和手工維護作為有界應急措施。

深入解答步驟

1. 畫出資源層級

autovacuumworkerslots 在啟動時為 autovacuum worker 預留 backend slots；PostgreSQL 18 預設通常為 16，但可能受核心設定影響。autovacuummaxworkers 控制同時執行的 worker 數，不能高於 slots 的有效上限。maxworkerprocesses、CPU 和 I/O 仍是共享資源，不能只看一個參數。

2. 設計分層並發

先把 autovacuumworkerslots 設為能覆蓋峰值所需的資源池，再用 autovacuummaxworkers 控制常態並發。PostgreSQL 18 允許在不重啟的情況下把 autovacuummaxworkers 調到 slots 上限內，但 slots 自身只能在伺服器啟動時設定。擴容前預留背景程序、連線和作業系統信號量。

3. 按資料表設定觸發閾值

全域閾值適合作為安全底線，熱點資料表用儲存參數降低 autovacuumvacuumscalefactor 或設定更合適的 autovacuumvacuumthreshold。插入密集表還要評估 autovacuumvacuuminsertthreshold。按表配置應根據死元組增長率和表大小計算，避免小表等待太久或大表頻繁掃描。

4. 控制單次 VACUUM 的資源

普通 VACUUM 可與讀寫並行，但會產生 I/O。需要平行維護時，maxparallelmaintenanceworkers 同時適用於建立索引和非 FULL 的 VACUUM，實際 worker 還受 maxworkerprocesses 與 maxparallelworkers 限制。maintenancework_mem 對平行維護命令按整條命令計算，但 CPU 和 I/O 仍可能增加。

5. 處理 wraparound 與長事務

即使關閉常規 autovacuum，系統仍會為防止事務 ID wraparound 啟動必要的 autovacuum。監控最老事務年齡、凍結進度和阻塞 VACUUM 的長事務；wraparound 保護不能被業務鎖或普通延遲策略覆蓋。必要時先終止阻塞事務、暫停低優先級批次，再執行目標表維護。

6. 建立可操作的觀測

使用 pgstatprogressvacuum 查看階段、掃描區塊、已清理區塊、索引循環、死元組位元組和 cost delay。結合 pgstatalltables 的 ndeadtup、lastautovacuum、lastautoanalyze、表大小和事務年齡，計算 backlog 與完成時間。logautovacuumminduration 用於保留慢任務證據；pgstat_io 可區分 autovacuum worker 的 I/O 影響。

7. 滾動調參與回滾

一次只改一個維度：先提高可用 slots 或 max workers，再觀察 CPU、I/O、查詢延遲和 backlog；若資源爭用上升，回退並發、降低單表頻率或錯開批次任務。每次變更記錄版本、表範圍、指標視窗和回滾值，避免把短時 backlog 誤判為永久容量不足。

高品質示例回答

我會把 worker slots 視為資源池，把 max workers 視為常態並發，把表級 threshold 和 scale factor 視為工作生成器。熱點表按死元組增長率降低觸發閾值，冷表避免不必要掃描；先在目前硬體上驗證 worker 對 I/O 和查詢延遲的影響，再逐步提高並發。觀測上同時看進度階段、ndeadtup、事務年齡、autovacuum 日誌和 pgstatio。wraparound 風險優先於普通延遲，長事務阻塞時先處理阻塞源，所有調參都保留可回滾記錄。

常見錯誤

• 只把 autovacuummaxworkers 調大，卻沒有增加 slots 或核對共享 worker 池。
• 把 slots 當成實際並發，忽略它只能在啟動時配置。
• 所有資料表使用同一個 scale factor，導致小表過晚、大表過頻。
• 用 VACUUM FULL 作為日常方案，忽略鎖和重寫成本。
• 只看磁碟空間，不看事務年齡、死元組和進度階段。
• 為追求延遲關閉 autovacuum，遺漏 wraparound 保護。

追問與回答

autovacuumworkerslots 和 autovacuummaxworkers 有什麼區別？

前者是啟動時預留的 worker backend slots，後者是同時執行的 autovacuum 程序上限。max workers 高於 slots 不會帶來對應並發；PostgreSQL 18 支援在 slots 範圍內執行時調整 max workers。

並發越高，VACUUM 越快嗎？

不一定。並發可能縮短單項維護時間，卻增加 CPU、I/O 和業務爭用。應以 backlog 完成時間、查詢延遲和 I/O 峰值共同判斷。

為什麼要按表設定 scale factor？

比例閾值對大表會產生很大的絕對死元組數，對小表又可能過於稀疏。按表設定能讓觸發量匹配更新率、表大小和 SLA。

如何證明 worker 真的被 I/O 拖慢？

用 pgstatprogressvacuum 看階段與 delay time，結合 pgstat_io 的 autovacuum worker 行、磁碟延遲和業務查詢指標，建立同一時間視窗的因果證據。

什麼時候必須優先處理 wraparound？

當最老事務年齡逼近保護閾值、凍結進度落後或系統已運行防 wraparound autovacuum 時，必須優先解除阻塞並完成凍結維護；普通業務優化應讓位於此風險。