題目與範圍
負載出現掃描變慢、讀取延遲升高，OLTP 與分析流量混合。PostgreSQL 18 增強 I/O 可見性，包括 pgstatio 的位元組資料與每後端統計。請結合查詢計畫、等待事件和作業系統指標形成可證偽診斷，不要憑直覺調整單一參數。
核心能力是資料庫可觀測性、負載推理與安全效能實驗，因此歸入 data。
面試官考察什麼
第一，能否識別 pgstatio 列的維度，避免把不同上下文相加？backend type、object、context 與 operation 描述不同 I/O 來源。
第二，是否區分累計計數器與時間窗口？速率需要兩個帶時間的樣本，並記錄重設或重啟。
第三，能否把資料庫證據連到查詢？帶 I/O timing 與 buffers 的 EXPLAIN ANALYZE 能說明讀寫和預取，但不能替代系統層證據。
第四，能否區分快取未命中、checkpoint 壓力、vacuum 工作與儲存飽和？
第五，能否一次改一個變數，保護正確性，並用代表性負載驗證？
先釐清的問題
- 變慢的是單一查詢、某類負載，還是整個實例？
- Schema、統計資訊、資料量、查詢比例或 PostgreSQL 版本是否改變？
- 儲存層讀寫延遲、吞吐和佇列深度是多少？
- 對比期間是否重設統計、重啟實例或發生故障切換？
- 瓶頸是 CPU、記憶體、I/O、鎖還是客戶端並發？
- 修復受哪些正確性和延遲 SLO 約束？
30 秒回答框架
「我會建立前後可比窗口，記錄重啟與統計重設，然後按 backend type、object、context 和 operation 採樣 pgstatio。把增量與 EXPLAIN ANALYZE 的 I/O timing、buffer、等待事件、checkpoint 和 vacuum 活動關聯，先分類瓶頸，再改一個可回滾控制，重放代表性負載，比較吞吐、尾延遲、正確性與容量餘裕。」
分步作答
第一步：建立可比窗口
記錄 PostgreSQL 版本、負載形狀、查詢 ID、重啟時間、統計重設時間和儲存拓撲。間隔採集兩次 pgstatio 以計算速率，保留原始快照，避免把重設誤認為改善。
SELECT backend_type, object, context, reads, read_bytes,
writes, write_bytes, read_time, write_time
FROM pg_stat_io;欄位與權限依目標大版本而定；監控查詢必須固定版本文件與實作。
第二步：按維度歸因 I/O
分別比較不同 backend type 與 context。客戶端、checkpointer、background writer、autovacuum 和維護任務代表不同修復方向。表資料、索引與暫存檔也有不同物理行為，不能只看一個全域 I/O 數字。
第三步：關聯慢查詢
在安全環境執行代表性 EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)，並注意 I/O timing 的量測成本。比較實際行數、讀取區塊、命中區塊、預取和耗時。大量讀取可能是掃描本意，關鍵是速率與延遲是否違反預算。
第四步：區分競爭原因
讀取高但設備延遲低，可能是快取壓力或計畫、資料量變化；讀取延遲和佇列深度都高，可能是儲存飽和。checkpoint 寫入、vacuum 或暫存檔也會與前台查詢競爭。等待事件和 CPU 使用率能區分 I/O、鎖與執行器瓶頸。
第五步：形成可證偽假設
提出可測量主張，例如「新分區掃描超過快取容量造成隨機讀取」或「checkpoint 寫入突發延遲前台讀取」。選擇對照實驗：計畫調整、受控預熱、checkpoint 節奏、索引或分區修復、負載隔離。
第六步：套用可回滾修復
一次只改一個控制項，設定回滾值與觀察窗口。不要讓記憶體、worker 或 checkpoint 設定超過主機容量；若根因是查詢或資料布局回歸，應先修復根因。
第七步：驗證並保留證據
重放代表性混合負載，比較 p50 與尾延遲、吞吐、錯誤率、讀寫位元組、等待事件和 OS 指標，確認結果與複製行為正確。保留兩段窗口和決策記錄，使後續重啟或重設可見。
示例回答
「我會先記錄版本、重啟與重設時間、負載、儲存指標與兩次 pgstatio 快照。按 backend type、object、context 和 operation 比較增量，再把疑似查詢與 EXPLAIN ANALYZE 的 buffer、I/O timing、WAL、等待、checkpoint 和 vacuum 關聯。我不會把累計計數器或單一命中率當成診斷。
完成快取壓力、儲存延遲、checkpoint 競爭、vacuum 或計畫回歸分類後，只做一次可回滾改動並重放代表性負載。成功標準包括尾延遲下降、正確性穩定、吞吐與容量餘裕改善，同時保留證據和回滾值。」
常見錯誤
- 相加所有 pgstatio 列 → 維度不相容 → 按 backend、object、context、operation 分組。
- 沒有時間窗口比較計數器 → 速率被臆造 → 定時採樣並記錄重設。
- 把命中率當證據 → 隱藏儲存延遲與計畫形狀 → 關聯位元組、延遲、等待與 OS 指標。
- 盲目在生產跑 EXPLAIN ANALYZE → 影響負載 → 使用副本或受控樣本。
- 同時改多個設定 → 失去因果 → 一次改一個可回滾變數。
- 忽略 vacuum 和 checkpoint → 錯怪查詢 → 分別歸因 backend context。
- 用更多記憶體掩蓋問題 → 主機壓力上升 → 先驗證容量、查詢與資料布局。
追問
追問 1：pgstatio 是 PostgreSQL 18 才有嗎？
該視圖早於 18，18 繼續增強 I/O 可見性，例如位元組欄位與每後端統計。始終以部署大版本文件為準。
追問 2：如何計算速率？
帶時間採集兩次快照，做計數器差值除以間隔，並標註期間的重啟或統計重設。
追問 3：read_bytes 高就表示查詢有問題嗎？
不一定。大掃描可能是設計行為，要結合計畫、行數、延遲、快取和 SLO 判斷。
追問 4：為什麼看 backend_type？
前台客戶端、checkpointer、background writer、autovacuum 和維護任務會產生不同競爭模式和修復選項。
追問 5：何時不宜開啟 EXPLAIN I/O timing？
繁忙生產路徑中量測開銷會扭曲延遲，可使用副本、採樣查詢或受控窗口，並說明限制。
追問 6：什麼證明修復有效？
重複代表性負載後尾延遲與吞吐改善，且正確性、複製和容量無回歸，並保留前後證據。