提高 PostgreSQL 數據倉庫 (RDS) 中的更新性能
我正在使用託管在 Amazon RDS 上的 Postgres 數據倉庫。當嘗試從同一數據庫中的另一個表更新事實表的一列（2500 萬行）時，查詢需要幾天時間才能執行。為什麼會發生這種情況，我該如何提高這種性能？我知道 PG 是為 OLTP 而設計的，而不是為 OLAP 設計的，但是在這張表上選擇查詢性能通常相當不錯。
有問題的查詢如下所示：
UPDATE a SET a.value = b.value FROM b WHERE a.id = b.id
b是一個不同模式的臨時表，但相同的數據庫具有相同的行數a。兩個表都有主鍵id。列上沒有索引或約束value。有依賴表a但沒有外鍵的視圖我在 RDS 上使用 PG 9.5。具有 256 GB 儲存空間的通用 (SSD)，因此在用盡我們最初的突發 IOPS 後，我應該得到略低於 800 IOPS。
IOPS 限制真的是這裡的問題嗎？在查看查詢執行時，我看到了大約 400 IOPS 的寫入性能和類似的讀取性能。25,000,000 行 / 400 IOPS = 17 小時，但此查詢執行時間超過 24 小時（大約 30 小時後取消以嘗試進行調整）。同一張表上還有一些其他的定期更新流量，但是當我看到這個查詢花費了多長時間時，我在大約 20 小時標記處停止了這個。
我想知道我的一般更新方法是否錯誤，或者是否有關於使用 postgres 操作數據倉庫（OLAP 工作負載）的一般建議。我可以通過放棄 RDS 並在 EC2 上執行 PG 來獲得更好的性能嗎？
更新：受回復和評論的啟發，我對 45k 行進行了測試（通過將 pk 限制在一定範圍內）
你可以在
explain analyze這裡看到結果。絕大多數時間都花在將實際更新寫入表中。現在我仍然傾向於將寫入 IOPS 作為限制因素，但我將深入研究 joanolo 提到的可能的複制問題。此圖顯示了 RDS 實例監控頁面。最近的峰值是有問題的查詢。
您是否考慮過WAL（Write-Ahead Logging）可能會減慢您的更新速度？另請參閱此更詳細的說明。
這是非常大的表上的 UPDATE、INSERT 和 ALTER TABLE 操作的常見問題。據我了解，對於受更新影響的任何行——即使該更新僅在單個列上——Postgres 存檔並替換整個行並更新所有索引列上的所有索引。雖然 Postgres 的 WAL 實現對於在事務設置中維護數據完整性非常有效，但它會嚴重降低大型表操作的性能——特別是對於涉及許多記錄的批量更新很常見的數據倉庫。
我需要更多地了解正在更新的表，以了解 WAL 是否是罪魁禍首。大部分記錄（比如 30% 或更多）是否受到更新的影響？您說 value 列沒有索引，但是其他列上是否有索引？如果這兩個問題的答案都是肯定的，那麼我強烈懷疑 WAL。
此問題的有效解決方案是使用此處描述的 CREATE TABLE AS 方法創建一個新的未索引表。您將需要在新表上重建索引、鍵和約束，但這仍然比就地更新快得多。另請參閱此處的相關答案。
CREATE TABLE AS 方法的缺點是一個簡單的 UPDATE 查詢變成了一個怪物多語句事務。後一種程式碼不僅繁瑣而且脆弱：每次使用這種方法的更新都必須重複表模式。想像一下具有數十個此類更新的數據倉庫管道。對錶模式的任何更改都必須硬編碼到每個更新操作中。
作為一種替代方法，我建議您首先嘗試剝離所有未參與更新的索引（連接或 where 子句），然後使用正常 UPDATE 語句並重建索引。根據您的表的尺寸、其索引、更新的複雜性和所涉及的行數，就地更新可能幾乎與“CREATE TABLE AS”方法一樣快，並且您的程式碼將更簡單、更多穩定的。
這並不是想成為一個真正的答案，但至少是一個參考比較點，以及一些在非 RDS 機器上極其簡化的設置的提示：
CREATE TABLE a ( id integer PRIMARY KEY, value float ) ; CREATE TABLE b ( id integer PRIMARY KEY, value float ) ; -- Fill table with 25M records INSERT INTO a (id, value) SELECT generate_series(1, 25e6) AS id, random(); -- 3 min 44 s -- Fill table with 25M records as well INSERT INTO b (id, value) SELECT generate_series(1, 25e6) AS id, random(); -- 4 min 15 s UPDATE a SET value = b.value FROM b WHERE b.id = a.id ; -- Query returned successfully: 25000000 rows affected, 08:06 minutes execution time.執行查詢時電腦使用的資源：
-- iostat => 22 to 24 KB/t, 366 to 428 tps, 8.65 MB/s .. 10 MB/s -- Activity Monitor, Disk tab -- Before starting... -- Process ReadBytes WrittenBytes -- postgres 4,12 GB 224 KB -- postgres 3,51 GB 227,6 MB -- postgres 1,98 GB 1,1 MB -- After finishing -- postgres 7,71 GB 2,85 GB -- postgres 6,59 GB 252 KB -- postgres 4,17 GB 2,3 MB -- Diff: Read Bytes: 8,86 GB, WrittenBytes 2,85 GB你可以在這裡查看執行計劃。
此測試已在“PostgreSQL 9.6.3 on x86_64-apple-darwin14.5.0，由 Apple LLVM 版本 7.0.0 (clang-700.1.76)，64 位編譯”，在配備 i7 處理器 @1 的 MacBook Air 上執行,7 GHz、512 GB SSD 和 8 GB RAM，以及 macOS Sierra 10.12.5。PostgreSQL 的設置是“開箱即用”（通過 Postgress.app 安裝），沒有進一步優化。
顯然，這種情況與 RDS 的情況非常不同。
時間上的差異是如此巨大，以至於表明：
- 我使用的簡化模型非常糟糕地代表了您的真實情況。
- 您對 Amazon RDS 上的 IOPS 的需求遠遠大於您的實際需求。
需要考慮的事項：
- 如果您正在執行 OLAP 程序，您很可能在其他地方擁有*原始數據，並進行了複製。*這意味著您不需要帶有複製的數據庫安裝。如果RDS已經設置了複製，我建議把它拿出來
- EC2 r3.large 實例將（相對）靠近我執行測試的電腦。
- 擁有自己的 PostgreSQL 設置意味著您將不得不處理備份、更新等……考慮到這一點。
- 如果設置 EC2 實例並執行一些試驗在您的現實可能性範圍內；我會試一試。