資料工程面試題：如何評估與調校 PostgreSQL 18 非同步 I/O？
題干
線上 PostgreSQL 18 的報表查詢、bitmap heap scan 與 vacuum 受儲存延遲影響。請設計評估非同步 I/O（AIO）收益並安全上線的方案：如何選擇 iomethod、設定並發、驗證 pgstatio 與 pgaios，區分 CPU、快取和儲存瓶頸，並在延遲惡化時回滾？
面試官在考察什麼
題目考察資料庫效能實驗與生產變更能力。AIO 可讓後端並行發出多個讀取請求，但不保證所有工作負載都變快；要理解 worker、io_uring、sync，以可重複 workload、統計視圖和回滾閾值證明收益。
先確認這些問題
- 主要是順序掃描、bitmap heap scan、vacuum 還是隨機點查？
- 儲存是本地 NVMe、網路區塊儲存還是容器卷？核心是否支援
io_uring？ - 目標是吞吐、P95 延遲、vacuum 完成時間還是 CPU 降低？
- 能否在唯讀副本或影子實例壓測？改啟動參數是否需要重啟？
30 秒回答框架
先固定基線，測查詢延遲、吞吐、CPU、I/O 等待與 vacuum 時長，再分方法選擇、並發控制、觀測和回滾回答。比較 worker、iouring、sync，調校 effectiveioconcurrency、iomaxconcurrency、ioworkers，用 pgstatio、pg_aios 與 SLO 閘門做決策。
逐步拆解方案
1. 建立可比基線
固定 PostgreSQL 18 小版本、資料量、索引、統計與客戶端並發，分別測冷快取與熱快取。記錄 EXPLAIN (ANALYZE, BUFFERS, WAL)、pgstatio、磁碟延遲與 CPU，將順序掃描、bitmap heap scan、vacuum 分開。
2. 選擇 I/O 方法
iomethod=worker 使用 PostgreSQL I/O worker，是相容性起點；iomethod=iouring 需要 liburing 建置和核心支援；iomethod=sync 可作對照與回滾。先驗證建置與權限，再在相同 workload 執行多輪。
3. 控制並發度
effectiveioconcurrency 與 maintenanceioconcurrency 影響工作並發提示；iomaxconcurrency 限制單進程同時 I/O；io_workers 只在 worker 方法生效。小幅提升並觀察儲存佇列、P95 和 CPU，不可全部設最大。
4. 解讀觀測訊號
用 pgstatio 判斷不同 backend、物件和操作的讀寫量與等待，用 pg_aios 查看準備、執行或完成中的 AIO handle。查詢變快但儲存佇列與尾延遲升高代表爭用；統計無變化可能是未走支援路徑或快取命中過高。
5. 實驗與容量預算
在副本或影子環境逐步增加客戶端並發，比較吞吐、P95/P99、CPU、I/O 深度和 vacuum backlog。對網路儲存加入突發限額、多租戶噪聲與讀放大，為 WAL、checkpoint、autovacuum、備份保留餘量。
6. 上線閘門與回滾
為每類 workload 設定收益與回歸閾值：P95 不得惡化、儲存佇列不可持續飽和、vacuum backlog 不可增長。採小批實例、維護窗口和 owner；超過閾值就切回 sync 或舊參數並保留前後統計。
7. 說明限制與後續
PostgreSQL 18 AIO 只改善可並發發起 I/O 的路徑，不能取代索引、查詢計畫、快取與儲存升級。記錄核心、編譯選項、io_method 和參數快照，升級時重跑多類 workload 基準。
合格回答示例
「我會在唯讀副本固定資料、統計和快取狀態，分別測冷熱快取的順序掃描、bitmap heap scan 與 vacuum，記錄 EXPLAIN BUFFERS、pgstatio、磁碟延遲和 CPU。先用 worker 作相容基線，再確認 liburing 和核心支援後比較 iouring，sync 作對照與回滾。逐步調高 effectiveioconcurrency、maintenanceioconcurrency，約束 iomaxconcurrency 和 ioworkers，觀察儲存佇列與 P99。pg_aios 用於確認 handle，達到閾值且無飽和才灰度；尾延遲或 backlog 回歸就切回 sync。」
常見失分點
- 只說把並發參數調最大，沒有基線和容量預算。
- 忽略 io_uring 的建置與核心條件。
- 只看平均延遲，不看 P95/P99、儲存佇列和 backlog。
- 用 pg_aios 證明所有查詢都走 AIO，忽略支援路徑與快取。
- 沒有灰度、閾值、owner 與 sync 回滾。
追問方向
何時選 worker 而非 io_uring？
核心或建置不符合 liburing 要求，或需要保守相容路徑時先選 worker，再用實測決定。
為何分開冷熱快取？
熱快取主要測 CPU 和記憶體，冷快取才暴露儲存並發與尾延遲，混合會誤判收益。
effectiveioconcurrency 越大越好嗎？
不是。過大可能放大儲存爭用與全域尾延遲，必須依裝置和 workload 校準。
如何證明 vacuum 得益？
固定膨脹、死元組和維護窗口，比較完成時間、I/O 等待、鎖影響與 backlog。
pg_aios 適合長期監控嗎？
它主要展示當前 handle，適合診斷抽樣；長期趨勢要結合 pgstatio、系統指標和 workload 標籤。
參考資料
PostgreSQL 18《Release Notes》《Resource Consumption Configuration》與《pg_aios System View》。