題目與適用範圍
一個 PostgreSQL 18 的 orders 資料表有 5 億筆存活資料，並持續發生更新。團隊觀察到四個事實：
ndeadtup持續增加；- autovacuum worker 週期性出現；
- 一般清理完成後，
pgrelationsize('orders')沒有下降； - 一個應用程式連線已處於
idle in transaction狀態 6 小時，並顯示很舊的backend_xmin。
請說明從 MVCC 可見性到實體清理的完整因果鏈，診斷這次故障，提出安全的復原順序，並說明需要哪些證據才能宣布復原完成。題目中的數字是面試條件，不是通用維運門檻。
這道題適用於後端、資料庫、平台與 SRE 面試，要求候選人把並行語意與正式環境的儲存行為串起來。SQL 只是檢查手段。
面試官在評估什麼
第一，候選人是否知道 UPDATE 會建立版本。PostgreSQL 的元組標頭包含插入交易 ID（xmin），以及刪除或淘汰該版本的交易 ID（xmax）等中繼資料。快照結合交易邊界與提交狀態，判斷哪個版本可見。單純選擇 xmin 最大的資料，無法表達真實可見性規則。
第二，候選人能否把可見性與清理串起來。只要某個作用中快照仍可能讀取舊版本，該版本就不能移除。長交易、預備交易或複寫槽都可能拖住清理邊界。autovacuum 可以成功執行，同時仍回報一批已經失效但暫時無法移除的元組。
第三，候選人的維運判斷是否準確。一般 VACUUM 通常把死亡元組占用的空間留在關聯內部重用，關聯檔案通常不會因此縮小。VACUUM FULL 會重寫關聯，需要額外暫存磁碟，並取得 ACCESS EXCLUSIVE 鎖。它屬於需要另外規劃的維護作業，不能一看到死亡元組估算增加就立即執行。
最後，候選人需要區分四類訊號：預估元組數、可回收空間、關聯大小、使用者可感知效能。它們彼此相關，但不能互相取代。ndeadtup 下降無法證明作業系統檔案已縮小，檔案大小不變也無法證明 vacuum 失敗。
回答前先確認
- 業務使用哪些隔離層級？ Read Committed 通常為每個敘述取得新快照；Repeatable Read 與 Serializable 使用交易層級快照。閒置交易即使沒有執行工作，也可能保留快照邊界。
- 這個舊
backend_xmin是否真的是全域最老阻塞者？ 需要用證據建立關聯。預備交易、邏輯或實體複寫槽，以及其他連線可能持有更舊的邊界。 - 統計資訊是否足夠新？
ndeadtup與nlivetup是估算值。應結合上次清理時間、目前進度、日誌、關聯大小與負載變化判斷。 - 目標是重用磁碟，還是立刻縮小檔案？ 日常 vacuum 追求穩定重用。向作業系統歸還大量空間通常需要重寫，或經過設計的線上重建方案。
- 業務是否允許終止這個 6 小時交易？ 先找到負責人與業務操作。取消或終止連線會回復未提交工作，也可能影響使用者流程。
- 負載最近發生什麼變化？ 更新速率、被索引欄位、資料列寬度、autovacuum 參數、worker 飽和程度與交易時長，都會影響版本產生與清理能力。
- 允許多大的鎖與 I/O 影響？ 復原計畫還要守住延遲、複寫、磁碟餘量與可用性，不能只追求維護工作盡快結束。
30 秒回答框架
“PostgreSQL 的 MVCC 讓每個敘述讀取一致快照，同時讓更新建立新的元組版本。只要任何作用中快照仍可能讀取舊版本，舊版本就必須保留。這裡的 6 小時交易可能拖住 backend_xmin，導致 autovacuum 雖然掃描了資料表，卻無法移除仍可能可見的版本。
我會先確認所有連線、預備交易與複寫槽中的最老邊界，再把它與資料表統計、vacuum 進度、日誌和關聯大小對齊；確認阻塞交易可以結束後，透過所屬應用程式關閉它。接著讓一般 vacuum 在受控 I/O 下追上積壓，驗證死亡元組估算下降且空間重用恢復穩定。一般 VACUUM 主要讓空間可重用，檔案通常不會縮小。VACUUM FULL 會重寫並獨占鎖定資料表，需要另外做維護決策。最後，我會限制交易時長、依熱門資料表調整參數、監控 XID 年齡，並保留凍結機制，避免舊 XID 越過回捲安全邊界。”
分步詳解
第一步：追蹤一次更新的 MVCC 過程
假設交易 100 插入一筆訂單版本，元組標頭會在 xmin 記錄插入 XID。之後交易 220 更新這筆訂單。PostgreSQL 建立一個後繼元組，並透過包含 xmax 在內的交易中繼資料，將舊版本標示為已淘汰；邏輯模型中的一筆更新不會直接覆寫原始位元組。
讀取者根據自己的快照與交易提交狀態，判斷版本是否可見。簡化來看，由未提交交易插入，或位於快照“未來”的版本不可見；如果刪除交易對目前快照尚不可見，讀取者仍可能看到舊版本。真實規則還會處理目前交易、已回復交易、命令 ID 與 hint bits，因此只比較 xmin、xmax 數字無法實作正確的可見性判斷。
這個模型減少讀寫鎖衝突：一般讀取不會阻塞寫入，寫入也不會阻塞一般讀取。寫寫衝突仍然存在。兩個交易更新同一邏輯資料列時可能等待或衝突，較高隔離層級也可能主動回復交易，以維持其保證。
第二步：推導安全清理邊界
交易 220 提交後，舊元組對新快照已經失效。如果一個更早開始的快照仍能讀取它，該元組暫時不能移除。Vacuum 會根據最舊的相關邊界選擇安全截止點。晚於安全邊界的版本可能屬於“recently dead”：對目前工作已經失效，但清理時機尚未到來。
持續 6 小時的 idle in transaction 連線很危險，因為客戶端留下未結束交易。伺服器即使正在等待下一個客戶端命令，它的 backend_xmin 仍可能保留舊快照。調查範圍還應包括：
pgpreparedxacts：預備交易可能持有舊 XID；pgreplicationslots：xmin或catalog_xmin可能保留業務資料列或系統目錄版本；- 其他
pgstatactivity連線：檢查很舊的backendxid或backendxmin； - 備援回饋與邏輯解碼設定：複寫需求也可能影響清理。
完整因果鏈是：長生命週期邊界 → 舊版本仍可能可見 → vacuum 無法回收 → heap 與索引維護積壓 → 快取效率與掃描成本可能變差。需要用對齊的時間戳與邊界證明這條鏈，不能只憑某個閒置連線名稱下結論。
第三步：分別檢查估算、進度與大小
先蒐集唯讀的活動連線與關聯統計快照：
SELECT pid,
usename,
application_name,
state,
xact_start,
age(backend_xid) AS xid_age,
age(backend_xmin) AS xmin_age,
wait_event_type,
wait_event,
left(query, 120) AS query_sample
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY GREATEST(
COALESCE(age(backend_xid), 0),
COALESCE(age(backend_xmin), 0)
) DESC;
SELECT relid::regclass AS relation,
n_live_tup,
n_dead_tup,
n_tup_upd,
n_tup_hot_upd,
last_vacuum,
last_autovacuum,
vacuum_count,
autovacuum_count
FROM pg_stat_user_tables
WHERE relid = 'orders'::regclass;
SELECT pg_size_pretty(pg_relation_size('orders')) AS heap_size,
pg_size_pretty(pg_indexes_size('orders')) AS index_size,
pg_size_pretty(pg_total_relation_size('orders')) AS total_size;ndeadtup 是估算值，不能直接當成精確膨脹位元組數。last_autovacuum 只能證明 worker 完成過一次執行，無法證明它移除了所有失效版本。只要釋放出的頁面能以新版本產生的速率持續重用，大型關聯也可能處於健康狀態。關聯大小穩定時，延遲或索引 churn 仍可能繼續上升。
Vacuum 執行期間，透過 pgstatprogressvacuum 檢查階段與已掃描 heap block。結合 autovacuum 日誌或 VACUUM (VERBOSE) 輸出，確認移除了多少元組、保留了多少暫不可移除元組、凍結邊界是否推進。還要在同一時間軸檢查 pgstatalltables、關聯大小歷史、查詢延遲、buffer 與 I/O 壓力、WAL 速率、複寫延遲和磁碟餘量。
第四步：依最安全的順序復原
先確認最老交易的負責人與用途。已經遭遺棄的交易，應透過應用程式或連線負責人關閉。仍在處理業務的交易，取消前要判斷回復是否可接受。PostgreSQL 提供 pgcancelbackend 與 pgterminatebackend，擁有函式權限不代表可以任意中斷正式環境交易。
接著透過所屬系統處理更老的預備交易或廢棄複寫槽。刪除仍有效的複寫槽可能造成備援需要重建，也可能遺失預期的解碼位置，因此必須當成明確的復原決策處理。
邊界推進後，讓 autovacuum 追趕積壓，或在受控時段對目標資料表執行一般 VACUUM (VERBOSE, ANALYZE) orders。持續觀察延遲、I/O、WAL、複寫延遲、vacuum 進度與剩餘磁碟。第一個維護工作耗時較長時，不應盲目啟動多個競爭工作。
接著驗證結果：
- 最老的相關
backend_xmin或複寫槽邊界已經推進； - vacuum 回報原先保留的死亡版本已可清理並被移除；
- 統計更新後，
ndeadtup趨勢下降； - 新更新開始重用可用空間，關聯成長回到預期穩態；
- 請求延遲、索引掃描成本、WAL 與備援延遲均在約定範圍內；
age(relfrozenxid)與資料庫 XID 年齡保留安全餘量。
取得這些證據後，再評估是否需要實體壓縮。VACUUM FULL orders 會建立新的緊湊副本，在重寫期間使用額外磁碟，並持有 ACCESS EXCLUSIVE 鎖。5 億筆資料的資料表可能需要線上重建，或有計畫的分割區替換。選擇取決於停機時段、可用磁碟、複寫、外鍵、寫入收斂與回復能力，不能只圍繞一個大小指標決策。
第五步：把 autovacuum 說清楚
Autovacuum 根據累積統計觸發。針對更新與刪除，PostgreSQL 18 使用以下形式的門檻：
vacuum threshold = min(
autovacuum_vacuum_max_threshold,
autovacuum_vacuum_threshold
+ autovacuum_vacuum_scale_factor * pg_class.reltuples
)插入驅動的 vacuum 有獨立門檻，並考量插入量與未凍結頁面比例。即使某張資料表關閉了一般 autovacuum，系統也會依 XID 年齡強制執行防回捲 vacuum。
對超大且更新密集的關聯，全域 scale factor 可能累積過多變更後才觸發，也可能形成脈衝式工作。應根據實際版本產生速率與 vacuum 處理能力設定資料表層級的儲存參數。同時檢查 worker 可用性和 cost delay：觸發條件正確，不保證 worker 立即啟動，也不保證清理速率高於垃圾產生速率。
預防也要進入寫入路徑。保持交易短小，交易內不要等待網路呼叫或使用者操作。對合適的應用程式角色選擇性設定 idleintransactionsessiontimeout，並驗證連線池與合法長工作能正確處理連線終止。只更新必要欄位。當更新沒有修改任何索引欄位，而且後繼元組能放入同一個 heap page 時，HOT update 可以避免新增索引項目；降低 fillfactor 能增加這個機會，但會預留更多頁內空間，需要以實測權衡。
第六步：把凍結與 XID 回捲串起來
一般交易 ID 是 32 位元，並在環形空間中比較。每個一般 XID 約有 20 億個 ID 被視為更舊，另外 20 億個被視為更新。如果某個元組永久保留一般插入 XID，經過足夠長的時間後，一個極舊值可能被解讀為“未來”。
Vacuum 會凍結足夠老且已提交的元組版本，以避免這種情況。現代 PostgreSQL 以元組狀態表示凍結，同時保留原始 xmin 供鑑識查看；凍結後的版本在可見性判斷中被視為早於所有一般交易。資料表層級與資料庫層級的 frozen-XID 標記，記錄這項工作的推進位置。
凍結關係到資料正確性，不能當成可選的空間最佳化。應監控 age(pgclass.relfrozenxid) 與 age(pgdatabase.datfrozenxid)，調查防回捲 vacuum，並為其保留足夠處理能力。提高 freeze 上限只會延後工作、縮短安全時段，無法消除環形 XID 的限制。
高品質示範回答
“我會把這次故障建模為版本產生速率與安全回收速率之間的失衡。PostgreSQL MVCC 為敘述或交易提供快照。一次更新建立後繼元組，並透過交易中繼資料標記舊版本。讀取者根據 xmin、xmax、提交狀態與快照選擇可見版本。一般讀寫因此不需要彼此衝突的讀鎖，但同一資料列上的寫入仍可能等待或回復。
只要任何相關快照仍可能看到舊版本，舊版本就不能移除。我會檢查所有連線中的舊 backendxid 與 backendxmin，再檢查預備交易與複寫槽。這個 6 小時閒置交易是重要嫌疑，因為開放交易可以保留快照邊界；結束它之前，我會先證明它確實是最老阻塞者。
我會把該邊界與 pgstatusertables、pgstatprogressvacuum、autovacuum 日誌、heap 與索引大小、關聯成長、延遲、I/O、WAL 和複寫延遲對齊。ndeadtup 是估算值，autovacuum 時間戳只證明執行發生過。業務負責人確認交易已遭遺棄後，我會關閉它，處理任何更老邊界，並讓一般 vacuum 在可控負載下追上積壓。
一般 VACUUM 會移除已經安全可移除的版本，並讓空間可重用；關聯檔案通常維持原大小。VACUUM FULL 會重寫關聯、占用暫存磁碟並獨占鎖定資料表，因此只有在規劃好停機或線上重建策略後才考慮。
預防措施包括限制交易時長、為合適角色設定閒置交易逾時、監控最老邊界與 XID 年齡、根據熱門資料表的實測 churn 調整 autovacuum，並在結構與負載允許時提高 HOT update 比例。Vacuum 還會凍結足夠老的已提交版本，使其 XID 永遠被視為過去，從而防止回捲。完成標準是阻塞邊界推進、可移除元組被清理、空間重用讓成長恢復穩定、服務 SLO 保持健康、frozen-XID 年齡擁有安全餘量。”
常見錯誤與改進
- 認為
UPDATE會原地修改一筆資料 → PostgreSQL 通常會建立新 heap 元組版本 → 沿著 MVCC 中繼資料說明前驅與後繼。 - 把可見性簡化成
xmin < current_xid→ 提交狀態、快照邊界、作用中交易、xmax與命令規則都會參與 → 準確說明快照判斷，避免創造數字捷徑。 - 聲稱讀寫永遠不會阻塞 → MVCC 減少一般讀寫鎖衝突，同一資料列寫入與明確鎖定仍會衝突 → 說明適用範圍。
- 認為 autovacuum 完成後所有死亡元組都已刪除 → 舊邊界可能讓部分版本暫不可移除 → 檢查保留元組、阻塞邊界、日誌與進度。
- 把
ndeadtup當成精確膨脹位元組數 → 它是預估資料列數 → 分別度量 heap、索引、成長、重用與效能。 - 把檔案大小不變判定為 vacuum 失敗 → 一般 vacuum 通常把釋放空間留在關聯內重用 → 先判斷穩態重用，再決定是否壓縮。
- 立即執行
VACUUM FULL→ 重寫需要額外磁碟與獨占鎖 → 先解除阻塞並讓一般 vacuum 追上，再規劃壓縮。 - 只調整全域 scale factor → 熱門資料表與 worker 容量不同 → 依版本速率、完成時間與 SLO 證據設定資料表層級參數。
- 未核對歸屬就終止最老 PID → 它的交易會回復，客戶端流程也可能失敗 → 先確認用途、影響與復原路徑。
- 把 freeze 當成儲存最佳化 → 凍結保護環形 XID 比較下的資料正確性 → 把 frozen-XID 年齡與防回捲工作納入安全監控。
追問
追問一：為什麼 VACUUM 成功後資料表仍然一樣大？
一般 vacuum 會把死亡元組空間標示為關聯內部可重用。在有限條件下，它可以歸還實體末端完全空閒的頁面；日常行為仍以內部重用為主。關聯中間的空閒空間要透過重寫或重組才能整體縮小。檔案大小穩定、延遲穩定而且空間持續重用，可以屬於健康狀態。
追問二：為什麼 autovacuum 執行了，仍留下很多死亡版本？
舊快照、預備交易或複寫邊界可能仍需要這些版本；工作負載也可能以高於 worker 清理能力的速率產生版本。鎖與負載還可能延遲或中斷 worker。可透過 verbose 日誌、進度、最老邊界、worker 飽和程度與版本產生速率區分這些情況。
追問三：VACUUM 與 ANALYZE 有什麼差異？
Vacuum 回收可重用空間，維護索引與 visibility map，並凍結舊交易中繼資料。Analyze 對資料取樣並更新最佳化器統計。VACUUM (ANALYZE) 會依序執行兩者；準確統計無法清除死亡元組，釋放空間也無法保證資料分布模型準確。
追問四：HOT update 如何降低 vacuum 壓力？
當更新沒有修改任何索引欄位，而且後繼元組能放在同一個 heap page 時，PostgreSQL 可以避免增加新索引項目。HOT chain 的中間版本也可能在一般頁面存取期間被剪枝。HOT 仍然依賴 MVCC 與 vacuum，但能減少索引 churn 和清理工作。應比較 ntuphot_upd 與總更新量，並用空間與快取成本驗證 fillfactor 調整。
追問五：可以關閉 autovacuum，只在夜間執行工作嗎？
這對波動負載風險很高，也無法關閉防回捲維護。白天的突發更新可能產生超過夜間時段處理能力的舊版本，靜態資料表最終也需要凍結。保留 autovacuum，根據真實資料表 churn 調整參數；只有負載證據支持時，才補充受控維護工作。
追問六：什麼保護措施可以治理閒置交易？
idleintransactionsessiontimeout 可以終止在開放交易中等待過久的連線。應依相容角色設定，並驗證連線池行為、重試與合法長工作。應用程式邊界也要修正：接近資料庫操作時才開始交易，及時提交或回復，持有交易期間不要等待使用者輸入或遠端服務。