Hacker News
This article challenges the common practice of lowering random_page_cost for SSDs in PostgreSQL, demonstrating through experiments that the actual cost ratio of random to sequential reads is often much higher than the default value. It explores how inaccurate cost parameters can lead the query planner to choose inefficient execution plans and discusses the complex relationship between prefetching and I/O costs.
AI 生成摘要
這篇文章挑戰了在 PostgreSQL 中針對 SSD 調低 random_page_cost 的常見做法，透過實驗證明隨機讀取與順序讀取的實際成本比例通常遠高於預設值。我探討了不準確的成本參數如何導致查詢規劃器選擇低效的執行計畫，並討論了預取功能與 I/O 成本之間複雜的關係。
PostgreSQL 的 random_page_cost 參數預設值為 4.0，這項設定自 25 年前推出以來幾乎未曾變動。隨著儲存技術從傳統硬碟演進至 SSD，許多開發者直覺認為應將此數值調低至 1.1 甚至 1.0，以反映隨機讀取效能的提升。然而，Tomas Vondra 透過實驗指出，在排除快取影響的真實 I/O 測試中，SSD 的隨機讀取成本實際上遠高於預設值，若不正確調整，可能導致查詢規劃器選擇錯誤的執行計畫。
針對這項實驗結果，社群討論呈現出理論與實務應用之間的微妙拉鋸。有留言者對實驗數據感到驚訝，因為這與業界普遍建議調低數值的慣例背道而馳。然而，討論很快轉向「真實工作負載」與「純粹 I/O 測試」之間的差異。部分開發者指出，雖然在極端測試中隨機讀取的成本很高，但在實際生產環境中，資料並非完全隨機分布，且作業系統與資料庫的快取機制會大幅抵銷隨機讀取的開銷。這也是為什麼許多經驗豐富的工程師仍傾向將參數設為 1.1，因為在考量快取命中率後，這樣的設定往往能產生更符合預期的查詢結果。
在自動化調優方面，社群提出了具備前瞻性的構想。有觀點認為，手動管理這些參數過於繁瑣，或許可以透過代理層（如 ProxySQL）或側車程序（Sidecar）來動態監控查詢效能。這種做法能藉由分析 Prometheus 統計數據或定期執行 EXPLAIN ANALYZE，針對特定查詢自動附加 SET LOCAL 指令來調整參數。這種「查詢重寫」的思路，試圖在不更動全域配置的情況下，解決規劃器在特定選擇度下選錯計畫的問題。
此外，討論也延伸到了其他資料庫系統的類似參數。例如 MySQL 的 innodb_io_capacity 參數，官方文件建議根據硬體的 IOPS 能力來設定，但實務上 Percona 等專家卻建議維持預設值。這反映出資料庫參數調整的一個共性：過度追求硬體極限的參數設定，有時反而會增加背景寫入負擔，甚至加速 SSD 損耗，卻不一定能帶來顯著的效能提升。整體而言，社群共識傾向於認為，雖然 Vondra 的實驗揭示了硬體底層的真實成本，但參數設定仍需回歸到索引設計與快取效率的綜合考量，而非單純追求反映硬體數值。
innodb_io_capacity 的配置說明：了解資料庫如何引導背景操作。