嘿。
我叫 Vanya，是一名 Java 開發人員。 碰巧我對 PostgreSQL 的工作很多——設定資料庫、最佳化結構、效能，以及在周末玩一點 DBA。
最近整理了我們微服務中的幾個資料庫，寫了一個java庫 ，這使這項工作變得更容易，節省了我的時間，並幫助我避免了開發人員犯的一些常見錯誤。 今天我們要講的就是這個庫。
免責聲明
我使用的 PostgreSQL 主要版本是 10。 我使用的所有 SQL 查詢也在版本 11 上進行了測試。 支援的最低版本是 9.6。
底
這一切都始於大約一年前，當時的情況對我來說很奇怪：索引的競爭性創建突然以錯誤結束。 像往常一樣，索引本身在資料庫中仍處於無效狀態。 日誌分析顯示存在不足 。 然後我們開始......深入挖掘，我發現了資料庫配置中的一大堆問題，並且捲起袖子，開始修復它們，我的眼睛閃閃發光。
問題一-預設配置
可能每個人都已經厭倦了 Postgres 的比喻，它可以在咖啡機上運行，但是......預設配置確實引發了許多問題。 至少值得關注 維護工作記憶體, 臨時文件限制, 語句超時 и 鎖逾時.
在我們的例子中 維護工作記憶體 預設為 64 MB，且 臨時文件限制 大約 2 GB - 我們根本沒有足夠的記憶體來在大表上建立索引。
因此，在 pg-索引-健康 我收集了一個系列 ，我認為，每個資料庫應該配置的參數。
問題二——重複索引
我們的資料庫位於 SSD 磁碟機上，並且我們使用 HA-配置多個資料中心、主控主機和 n- 副本數量。 磁碟空間對我們來說是非常寶貴的資源； 它的重要性不亞於效能和 CPU 消耗。 因此，一方面我們需要索引來快速讀取，另一方面我們也不希望在資料庫中看到不必要的索引，因為它們會佔用空間並減慢資料更新速度。
而現在，一切都恢復了 而且已經看夠了 ，我決定整理一次「大」清洗。 事實證明，開發人員不喜歡閱讀資料庫文件。 他們不太喜歡它。 因此，會出現兩個典型的錯誤 - 主鍵上手動建立的索引和唯一列上類似的「手動」索引。 事實上，它們是不需要的——Postgres 會自己做所有的事情。 可以安全地刪除此類索引，並且為此目的出現了診斷程序 .
問題三 - 相交索引
大多數新手開發人員都會在單一列上建立索引。 漸漸地，在徹底體驗了這項業務之後，人們開始優化查詢並添加包含多個列的更複雜的索引。 這就是列上索引的顯示方式 A, A + B, A + B + C 等等。 這些索引中的前兩個可以安全地丟棄，因為它們是第三個索引的前綴。 這也節省了大量的磁碟空間，並且有針對此的診斷 .
問題四-沒有索引的外鍵
Postgres 允許您建立外鍵約束而無需指定後備索引。 在許多情況下，這不是問題，甚至可能不會表現出來......暫時......
我們也是一樣：只是在某個時間點，主控主機開始向我們「新增」一個按計畫運行並清除測試訂單資料庫的作業。 CPU和IO浪費了，請求減慢並且超時，服務五百。 快速分析 顯示查詢如下：
delete from <table> where id in (…)當然，本例中目標表中存在按id的索引，根據條件刪除的記錄很少。 似乎一切都應該有效，但是，可惜的是，事實並非如此。
奇妙的人來救援了 解釋分析 並表示除了刪除目標表中的記錄外，還有引用完整性檢查，並且在其中一個相關表上此檢查失敗 順序掃描 由於缺乏合適的索引。 於是診斷學誕生了 .
問題五——索引中的空值
預設情況下，Postgres 在 btree 索引中包含空值，但通常不需要它們。 因此，我努力嘗試拋出這些空值（診斷 )，按類型在可為空的列上建立部分索引 where <A> is not null。 透過這種方式，我能夠將其中一個索引的大小從 1877 MB 減少到 16 KB。 在其中一項服務中，由於從索引中排除了空值，資料庫大小總共減少了 16%（絕對數量減少了 4.3 GB）。 透過非常簡單的修改即可節省大量磁碟空間。 🙂
問題六——缺少主鍵
由於機制的性質 這種情況是有可能的 當表的大小由於大量死記錄而快速增長時。 我天真地以為這不會威脅到我們，也不會發生在我們的基地，因為我們，哇！！！，都是普通的開發者……我是多麼愚蠢和天真啊……
有一天，一次精彩的遷移獲取並更新了一個大型且經常使用的表中的所有記錄。 我們的表格大小突然增加了 100 GB。 這真是太遺憾了，但我們的不幸經驗並沒有就此結束。 15 小時後，該桌子上的自動清理結束後，很明顯物理位置不會回來。 我們無法停止服務並使 VACUUM FULL，因此我們決定使用 。 然後事實證明 pg_repack 不知道如何處理沒有主鍵或其他唯一性限制的表，而且我們的表沒有主鍵。 於是診斷學誕生了 .
在庫版本中 0.1.5 新增了從大量資料表和索引中收集資料並及時回應的能力。
問題七、八——索引不足和未使用的索引
以下兩個診斷是： и – 最近才以最終形式出現。 關鍵是它們不能只是被拿走和添加。
正如我已經寫過的，我們使用具有多個副本的配置，並且不同主機上的讀取負載根本不同。 結果發現，某些主機上的某些表和索引實際上並沒有被使用，為了分析，需要收集叢集中所有主機的統計資料。 這對於叢集中的每台主機也是必要的；您不能僅在主主機上執行此操作。
這種方法允許我們透過刪除從未使用過的索引以及向很少使用的表添加缺少的索引來節省數十GB的空間。
作為結論
當然，對於幾乎所有診斷，您都可以配置 。 這樣，您可以快速在應用程式中實施檢查，防止出現新錯誤，然後逐步修復舊錯誤。
一些診斷可以在推出資料庫遷移後立即在功能測試中執行。 這也許是我的圖書館最強大的功能之一。 可以在以下位置找到使用範例 .
僅在真實資料庫上對未使用或遺失的索引以及膨脹進行檢查才有意義。 收集到的值可以記錄在 或發送至監控系統。
我真的希望 pg-索引-健康 將是有用的並且有需求。 您還可以透過報告您發現的問題並提出新的診斷建議來為庫的發展做出貢獻。
來源： www.habr.com