PostgreSQL 分區消耗大量記憶體
我有一個相當大的（~10M 行）表，最近開始放慢速度。作為解決方案，我們決定將該表劃分為 1,000 個表，按客戶端 ID 的雜湊進行分區。這樣，我們通常每個表只有幾個客戶端。我們在生產中與現有表一起創建了這個分區，並填充了所有數據，並使它們與觸發器函式保持同步。對分區表手動執行查詢顯示出巨大的希望，大多數人看到 10-100 倍以上的加速。認為一切都很好，我們在生產環境中交換了兩個表名，它立即使我們的數據庫崩潰。
- 可釋放記憶體立即從 6 GB 降至 0
- 交換使用量從 0 GB 躍升至 3 GB
- 數據庫變慢了，以至於我們不得不完全關閉網路應用程序，因此沒有與數據庫的連接並恢復表名交換。
我已經完成了使用該表的查詢，並且所有查詢都有一個明確的
WHERE client_id = <client_id>，因此他們應該只使用他們的分區而不是父表。他們都沒有選擇跨多個分區。什麼可能使用這麼多記憶體？Postgres 分區有那麼多記憶體成本嗎？根據我的閱讀，Postgres 應該能夠處理數万個分區，所以我的 1,000 個應該不是問題。
這一切都在具有 4 個 vCPU 和 16 GB RAM 的 RDS M5.xlarge 實例上。
更新： 我們將實例大小升級為具有 32 GB RAM 的實例，並將分區數量從 1,000 個縮減到僅 250 個，然後再次嘗試。這次我們看到記憶體立即下降，但在趨於平穩之前只有大約 3-4 GB，這表明記憶體隨著分區數量的增加而擴展。同樣，在交換錶名之前，我們已經通過觸發器對分區執行了大量的 INSERT/UPDATE/DELETE 命令，並且沒有看到明顯的影響。只有當表名被交換以便 SELECT 開始命中它時，我們才看到記憶體的巨大下降。這似乎與說明記憶體命中對於 UPDATE 和 DELETE 命令特別糟糕的文件不一致。
是的，更多的分區意味著更多的記憶體成本。
請參閱以下從文件中獲取的文本：
在查詢計劃和執行期間考慮分區的成本也很重要。查詢規劃器通常能夠處理多達幾百個分區的分區層次結構。**隨著更多分區的添加，規劃時間變得更長，記憶體消耗也變得更高。對於 UPDATE 和 DELETE 命令尤其如此。另一個需要關注大量分區的原因是伺服器的記憶體消耗可能會在一段時間內顯著增長，尤其是在許多會話涉及大量分區的情況下。**這是因為每個分區都需要將其元數據載入到每個與其接觸的會話的本地記憶體中。
對於數據倉庫類型的工作負載，使用比 OLTP 類型的工作負載更多的分區更有意義。通常，在數據倉庫中，查詢計劃時間不太重要，因為大部分處理時間都花在查詢執行期間。對於這兩種類型的工作負載中的任何一種，儘早做出正確的決定很重要，因為重新分區大量數據可能會非常緩慢。預期工作負載的模擬通常有利於優化分區策略。永遠不要假設更多的分區比更少的分區更好，反之亦然。
參考： 5.11。表分區 - 5.11.6。聲明式分區的最佳實踐（PostgreSQL 14 | 文件）