Postgres 在一張非常大的桌子上選擇
我們目前在 Postgres 12.5 上有一個非常大的表，其中包含超過 22 億行。表（包括索引）的總大小為 500 GB。為了從數據集中找到一組有效行並對其進行更新，我們需要執行一個查詢。查詢看起來像這樣：
select id, col4 from table where col1=$1 and col2=$2 and col3='f' and col4>0 order by col5 limit 10為了服務這個查詢，表上有一個索引，
ON (col1, col2, col5)查詢使用這個索引。到現在為止還挺好。當數據庫在緩衝區未命中時需要進行大量磁碟尋軌時，就會出現問題。這導致查詢等待DataFileRead。到目前為止，我們使用的是 16 個 vCPU 和 128 GB 機器，具有 io1 儲存類型，預置 IOPS 為 20000 用於託管（它託管在 AWS RDS 上）。我們從大約 3000 的預置 IOPS 開始，並不斷增加它，希望通過積極的自動清理和數據本地化，它將穩定在某個值。autovacuum 的配置方式是每隔幾天在此表上執行一次。我們最近遇到了讀取 IOPS 開始達到 20000 並且應用程序變得太慢的問題。我們升級到一台更大的機器，它的大小正好是原來的兩倍，因為我們不能再在之前的機器上提供超過 20000 的 IOPS。
在更大的機器上，我們觀察到即使讀取 IOPS 現在也已降至約 5000，機器現在在峰值時間消耗大約 6000 的整體 IOPS，查詢時間精確減半。
shared_buffer我們假設，這肯定與 postgres 現在可用於將熱引用行保留在記憶體中的更高級別有關。問題是我們現在使用的機器以大約 5% 的 CPU 負載執行，還有 184 GB 的 RAM 仍未使用。總而言之，這台機器的使用率嚴重不足。我們希望通過對參數進行任何更改來使用較小的機器，以便此查詢可以在一些可容忍的延遲限制下執行。我們在之前的機器上嘗試了多次記憶體調整，以充分利用 RAM。但是增加到
shared_buffer超過 40% 的 RAM 總是會導致查詢變得非常慢，我們總是不得不將它恢復到以前的值。分享一些 Postgres 數據庫參數（目前在更大的機器上）：
effective_cache_size: 130GB shared_buffers: 66GB work_mem: 4MB maintenance_work_mem: 8.5GB**PS：**數據增長大約是每天 3000 萬個條目，所以情況會變得更糟。該數據庫正好在 3 個月前投入生產使用。我們還需要有關建構可持續解決方案的建議。由於應用程序的性質，我們不能對錶進行分區，除非它是 6 個月或更長時間。分片將是我們最後的手段，但我們希望在轉向此解決方案之前用盡所有選擇。
**編輯：**附加查詢計劃（第一個當緩衝區中沒有數據時，第二個是立即後續查詢命中）。由於我們使用的是較大的機器，因此性能看起來非常可接受，但在較小的機器上需要超過 1 秒。
不，我們不需要一次更新十億行。我們只需要更新一些。很難判斷更新期間受影響的行數，但對於特定事務不會超過 20。我可以給出一個想法，我們在這裡試圖實現什麼。它就像一個有容量的袋子
C，我們試圖用col4只取有效條目（col3is'f'和col4> 0）的值來填充它。我們一次從數據庫中查看 10 個條目，如果需要，可能會對數據庫進行後續相同的查詢以獲取下一個有效條目。在此過程中，僅col4更新可以設置為零，因為它已被消耗或低於其目前值的數字。尋找任何想法或建議。提前致謝。
您目前正在使用 col3 和 col4 上的過濾器刪除 855 行，以便找到通過該過濾器的 10 行。因此，正如我所擔心的那樣，未能通過過濾器的東西可能比其他東西更罕見，但它們正好擋住了路。下次您還需要 10 件東西時，它們仍然會妨礙您。而下一次。不僅每次執行所需的工作量要多 85 倍，而且您訪問的頁面數量也多 85 倍。如果 col1 和 col2 的所有其他組合都發生同樣的情況，那麼難怪您會不斷耗盡記憶體空間和 IOPS。當然，它沒有理由停在那裡，如果你沒有什麼可以擺脫它們的話，你可能會累積超過 850 個。
您可以使用部分索引來避免每次訪問這些行：
create index on t (col1, col2, col5) where col3='f' and col4>0;或者，每次 col3 變為 true 或 col4 變為 0 時，您可以只刪除該行，並且（可能）將其插入到某個歷史記錄表中，如果您需要保留一些記錄。