在短暫的“忙于其他事情”（換工作，基因組倍增）之后，我認為是時候再次敲鍵盤以避免在寫作時變得過于“生疏”，同時也避免我的大腦在未來衰老，因為如果你忘記了一些細節，用谷歌搜索你自己過去的文章實際上是很好的，因此，從理論上講，最終甚至可能是凈正“投資”。

我認為做一些“考古學”會很酷，看起來有點超過通常的“verX vs verX+1”比較目標，從v10開始！這肯定很有趣，因為在這段時間里有很多變化——確切地說，大約有2000項“值得發布說明”的東西……令人難以置信！但令人遺憾的是，發布說明和文檔總體上對一些性能提升非常粗略（在黑客郵件中，你可以找到一些東西），甚至可能是出于一個很好的原因-肯定有無數不同的用例、配置調整和硬件組合，因此，在很短的時間內，這些籠統的語句可能看起來非?？尚?記住，640KB的RAM應該足夠任何人使用。

那么，如何處理性能數據的模糊性呢？沒錯，正如你可能已經猜到的，是時候卷起袖子，把難以解決的事物放在一起進行一些試驗了！因此，請繼續閱讀我的簡單測試設置的一些細節，或者直接跳到底部了解我的一些發現。

請注意，我不想測試任何接近“大數據”的東西，而是“在內存中”，因此，如果您處理大量數據，并主要訪問磁盤，那么我猜整個練習可能看起來毫無意義。不過，它應該顯示出Postgres如何創建執行計劃、索引和完整掃描、解包元組以及進行一些數字處理的可能變化。

測試設置

有些事情在一年內不會改變—我選擇的武器是一把瑞士軍刀“pgbench”，這把刀可以用來進行一些涉及Postgres的“快速和骯臟/個人使用”風格測試，盡管這一次將一些自定義SQL混合到pot中，作為默認的OLTP風格測試場景有點片面，并且沒有說明Postgres也可以成功地用于更多的分析用例（盡管有一定的限制-默認情況下，它不使用列存儲或過于激進的壓縮）。因此，我添加了一些連接和“海量數據”類型的查詢，這在當時對我來說似乎是“常見”的，但并沒有給出太多，盡管老實說，因為很難得到一些確定的結果，所以請記住這一點。

測試查詢

-- Standard "tcp-b"-like --skip-some-updates 
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;
SELECT abalance FROM pgbench_accounts WHERE aid = $1;

-- Full scan  
SELECT count(*), avg(abalance) FROM pgbench_accounts;

-- Assumes a 4x reduced (aid % 4 = 0) clone of pgbench_accounts
SELECT count(*) FROM pgbench_accounts JOIN pgbench_accounts_reduced USING (aid);

-- Top 5 accounts for each branch - assumes a new index on pgbench_accounts(bid)
SELECT bid, abalance FROM pgbench_branches b JOIN LATERAL (SELECT abalance FROM pgbench_accounts WHERE bid = b.bid ORDER BY abalance DESC LIMIT 5) a ON true;

硬件

5x GCP e2-standard-4虛擬機，配備4個vCPU和16GB RAM，50GB標準永久磁盤（IOPS非常低）

軟件

操作系統：Debian 11(Bullseye)

Postgres:v10到v15，最新的次要版本和v15的Beta 2（在編寫本文時不再是最新版本）。在給定硬件的情況下，還應用了一些基本的“最佳實踐”配置調整（25%RAM shared_buffers，random_page_cost=1.25，max_parallel_workers_per_gather=1）。

工作集大小：每個查詢3個大小-適合共享緩沖區，適合RAM，稍微超過RAM，因此對慢速磁盤的一些輕度訪問應該會減弱Postgres方面可能的算法磁盤訪問改進。

測試運行時間：每個“工作集大小/規?！?小時，查詢協議（普通與擴展），查詢，PG版本組合

并行級別：我選擇了固定的“活動客戶端”計數2，以避免CPU最大化并避免一些上下文切換隨機性。

測量方法：Postgres內置“pg_stats_statements”擴展統計聚合

此外，這一次，與“舊時代”相比，您可能已經注意到，我決定在云虛擬機上運行測試，而不是真正的硬件……這似乎也在慢慢成為數據庫世界的一種常態，或者至少情況正在發生變化。

同樣，在選定的數據集大小上-是的，它幾乎都是“在內存中”，數據集很小，因此您可能會認為這與現實生活中的用例有點格格不入-但請記住，云存儲延遲/隨機性要比本地磁盤高得多，因此很難對軟件進行實際基準測試。此外，理論上（如果你有足夠的資金），你可以很容易地“點擊”自己。例如，現在AWS上有多TB的RAM，即使是大數據，也會給你一種非常類似的“大部分在內存中”的感覺。

高級別成果

在按下“回車”鍵后，我花了8天時間才終于能夠興奮地搓手……但令我恐懼的是，最初的喜悅很快變成了幾乎絕望-因為顯然有大量數據（900行）需要分析，比我預想的要多得多-組合數學有時肯定會令人驚訝。所以在一個小時的數據挖掘和比較各個行之后，我終于可以制定出我真正想要的，并可以使用窗口函數編寫一些分析查詢（注：如果你還沒有掌握它們，一定要去學習它們——它們是SQL最好的部分之一，說真的）。因此，不用多說，下面是一些比較數字。

請注意，最后，為了簡單起見，我實際上決定忽略“中間”的Postgres版本，現在只看v10和v15，因為它似乎太多了，無法很好地總結它……希望在未來解決它，因為這里有完整的數據，所以如果有時間，可以隨意查看下面的數字。

兩個異常

pgbench_accounts_reduced上的慢速加入

在查看查詢結果時，我注意到的第一件事當然是連接查詢的速度下降了123%——我想這一定是我的測試的原因！但在手動重新檢查之后-事實上，Postgres v15測試版選擇了比v10更糟糕的計劃！當然，這些事情時有發生，通?？梢越鉀Q-但從3個自定義查詢中，我選擇了已經命中一個…
但不要太擔心-基于我10年來每天與Postgres合作的經驗，我可以證明這是一件非常罕見的事情，而且它是一個測試版。

順便說一下，從技術方面-在運行“解釋-分析”之后，我看到JIT已經啟動，所以我禁用了它，并認為這一定是它…但仍然得到了一個更慢的計劃，選擇了哈希連接而不是合并連接！雖然它的成本估算比v10合并計劃低（順便說一下，這通常是一種罕見的現象，也是一種“回退”連接類型），但從技術上講，Postgres做了它必須做的事情。只有在禁用哈希連接之后，我才能看到v15確實快了大約20%，這實際上非常好！

分析查詢的標準差惡化

嗯，stddev中有相當大的200%以上的變化……因此，為了以防萬一，再次在兩個版本上運行查詢：

select count(*), avg(abalance) from pgbench_accounts

Hhmm兩個版本都有完全相同的“并行序列掃描”計劃，因為這是一個非常簡單的查詢-而且奇怪的是，v15總是更快…但不知何故更加跳躍。我沒有想到什么明顯的東西-也許是“云”/“噪音鄰居”效應？

SELECT count(*) FROM pgbench_accounts JOIN pgbench_accounts_reduced USING (aid)

這里更容易理解，因為在前面的“慢速連接”段落中解釋了相同的查詢-JIT不必要地啟動+選擇了稍微慢一點的計劃。

SELECT bid, abalance FROM pgbench_branches b JOIN LATERAL (SELECT abalance FROM pgbench_accounts WHERE bid = b.bid ORDER BY abalance DESC LIMIT $1) a ON

同樣，JIT似乎啟動得太早了……但一旦在v15上禁用，它就正?；?，在運行時實際領先v10 40%！

試圖得出結論

好了，好了，現在來看看最難的部分-那么我從這個練習中學到了什么，如果有的話？一些想法：

• 請記住，我測試了一個V15beta2版本（遺憾的是，beta3在我開始測試后不久就發布了）

• 在從測試結果中排除具有上述詳細規劃異常的查詢后，所有查詢和規模的加速率僅為4%。不多對令人驚訝的不-事實上，這非常符合邏輯，因為PostgreSQL在我使用它的整個十年中一直堅如磐石，并且（大部分）優化得很好！此外，它是一個非常靜態的、主要是只讀的、幾乎是“內存中”的測試設置，其中許多最近的主要優化（索引重復數據消除、自下而上的索引清理等）無法顯現。

• Postgres planner并不是最復雜的，它仍然可能會誤判事物，因此，與以往一樣，重要的是了解你的方法，解釋計劃、計劃常數和某些結構（如橫向結構），這些結構將Postgres推向你喜歡的計劃。

• 查詢協議（普通SQL與準備好的語句）對于更小/更快的查詢仍然發揮著巨大的作用-僅通過使用準備好的聲明，兩個非分析性簡單索引訪問查詢就獲得了20%和50%的提升！當然，這里沒有什么新鮮事，只是重復一下：）

• 對于這種相對較短的運行測試來說，云虛擬機似乎不太值得信賴-不同5臺主機上所有查詢/規模的統計變異系數（~7%）實際上高于測得的Postgres 4%的加速率（不包括離群查詢）…因此，下一次我仍然會再次啟動一些真正的硬件。

• 總而言之，一個非常不錯的性能從良好的舊v10，這實際上將在幾個月內下線！因此，那些仍然在安全網絡中運行的人（沒有安全更新，記?。。┒也幌胱屚C時間升級，至少不必太擔心性能部分。

• 請記住，測試性能很難，而且有很多事情我沒有測試-分區、嚴重并發/多用戶訪問、由于膨脹導致的長期性能下降。因此，我想象出來的自定義SQL查詢現在對我來說似乎相當片面（更多的是在分析方面），所以可能把它當作“某物”。

順便說一下，如果您想嘗試類似的東西，可以在這里找到我使用的腳本。此外，您只需單獨創建一個可從測試虛擬機訪問的Postgres“結果數據庫”，即可將結果推送到單個表中，以便通過SQL進行分析。此外，如果您在我的邏輯中看到一些明顯的錯誤或有其他想法，在評論部分獲得ping將是非常好的。

這次就這樣！希望在v15最終版發布時，能帶著類似的作品回來-同時好好照顧你的數據庫和其他寵物。

PS：特別感謝我的雇主Cognite對我的博客很友好，讓我可以在一周內燒掉這些CPU！順便說一句，我們還招聘了一些不同引擎上的數據庫工程師職位，所以如果你不介意我作為同事，為什么不去看看呢。

原文標題：5 years in PostgreSQL major versions performance - anything surprising?
原文作者：Kaarel Moppel
原文鏈接：https://kmoppel.github.io/2022-09-05-5-years-in-postgresql-major-versions-performance-anything-surprising/