更新大型查找表內容的最佳方法
在我們的應用程序中，我們有大約 20 個表，其中包含查找資訊（應用程序不修改的只讀數據）。
每晚我們希望通過刪除所有內容並執行執行一系列插入和更新的腳本來更新此資訊。這個腳本可能有幾百萬行長。
我正在考慮在事務中執行它。
我應該從數據庫中獲得什麼行為？表會在事務結束時更新而不會停機嗎？
執行大型事務時有什麼我應該注意的嗎？
我使用的數據庫是 Postgres 9.3
謝謝。
假設腳本只 包含目標表的
INSERTs、UPDATEs 和s，並且應用程序從不在這些表上嘗試行鎖 ( ) 或 DML，那麼腳本不應該影響應用程序，除了增加的負載數據庫伺服器。DELETE``SELECT ... FOR UPDATE/SHARE當腳本送出時，更改將立即對新語句（如果事務處於
READ COMMITTED隔離狀態）或事務（如果它們處於SERIALIZABLE隔離狀態）可見。然而…
每晚我們希望通過刪除所有內容並執行執行一系列插入和更新的腳本來更新此資訊。這個腳本可能有幾百萬行長。
如果可能，您應該使用
COPY它而不是充滿插入和更新的腳本。只是：
- 從 mytable 中刪除；
- 真空表；
- 從’inputfile.csv’複製mytable（格式CSV）
或類似的。
它會快*很多。*由於下一點，您可能想要載入幾個較小的文件。
執行大型事務時有什麼我應該注意的嗎？
長時間執行的數據修改事務阻止
VACUUM清理他們可能能夠“看到”的其他表中的行。這會造成相當多的表膨脹。如果您使用
READ COMMITTED隔離並堅持使用不會花費很長時間的單個語句，則不會有問題，因為控制事務可以看到哪些行的快照在每個語句之後都會更新。如果您使用游標、單獨工作
SERIALIZABLE或執行執行時間極長的單個數據修改語句，這主要是一個問題。
如果數據表中的外鍵約束指向這 20 個表中的任何一個，您將無法從它們中批量刪除。
顯然，如果應用程序在刪除之後和插入新值之前嘗試讀取任何查找表，它將不會收到任何結果。如果發生這種情況，應用程序會崩潰嗎？
刪除和載入數百萬行會導致事務文件的大小變大。確保您有足夠的磁碟空間來處理此問題以及適當的日誌維護過程。
我想向你推荐一個更好的架構。將新數據載入到一組臨時表中。每個查找表都有一個臨時表，具有相同的列和數據類型。如果需要，對這些臨時表執行驗證。從每個登台表執行並“更新”（又名合併）到其相應的查找表。使用顯式事務一次以幾千行為單位執行此操作。理想的批量大小將取決於您的系統和要求。這將在發生錯誤時提供良好的重新啟動功能，並且可能會限制日誌文件的增長，具體取決於您的設置。完成後，截斷暫存表以備下次執行。