對多個 PostgreSQL 表中存在的規範化數據進行批量更新
我們有幾個網站可以下載 csv 文件——比如 100 個這樣的網站。我們無法控制他們的日程安排，因此我們每隔幾個小時就從他們那裡下載文件。可以假設每個這樣的文件都有 20GB 的數據，其中 1 行應該在 avg 上。4KB。所以每個文件大約有 500 萬行。
第一次執行此活動時，我們從 PostgreSQL 數據庫中的 10 個表中的所有這些文件中讀取、轉換和規範化數據。現在，每次我們從這些網站中的每一個獲得新文件時，我們都想更新我們的數據庫。這將涉及添加任何可用的新行，如果相應列發生更改則更新現有行，或者如果相應行突然從源中不存在，則用標誌標記表中的行。
最好的方法是使用一些差異工具（可能是 UNIX
diff）來找出添加了什麼，刪除了什麼以及改變了什麼，然後只為那些創建批量INSERT/UPDATE查詢並將它們觸發到數據庫中？如果說文件是下載的，是否有推薦的文件格式或工具或某種開箱即用的解決方案AWS S3？或者是否有更快的方法，例如從新版本的文件創建臨時規範化表，然後對主要面向客戶的表進行某種比較和更新？
或者只是創建臨時但未規範化的表，並在那裡與新版本的數據進行差異化，然後轉換和更新主要的規範化表？
我是數據庫新手，還讀到我們可以編寫數據庫可以執行的伺服器端函式並為我們做一些事情 - 即，將轉換 + 規範化邏輯烘焙到此類伺服器端函式中並創建一個觸發器來執行它們等等
建議是什麼？
最好盡可能多地在數據庫之外做，因為數據庫會增加很多成本。但是 Unix
diff可能不適合，因為它需要 RAM 中的所有數據，至少在存在多個差異的情況下是這樣。我發現sort並且uniq是處理非常大的文本數據文件的一種比diff. 這確實取決於每一行都是一條記錄，即沒有嵌入的換行符。例如，要使所有新行與舊行不同：
sort new old old | uniq -u由於您包含
old了兩次，因此每條舊行將至少出現兩次，因此無法在-u. 並且每條與舊線相同的新線將作為一個組出現3次，因此也無法倖存-u。只留下真正新的新線。但是，如果您對整個數據文件執行此操作，結果會將新行和更改行混合在一起。但是，如果您僅隔離主鍵列，那麼它將為您提供所有新的主鍵。如果您使用“INSERT…ON CONFLICT…DO UPDATE”，那麼您不需要區分這兩種情況，因為數據庫將解決這兩種情況，而不必費心處理所有可證明相同的數據。
獲取需要標記為已刪除的數據：
extract_pk new new old | sort | uniq -u將為您提供舊數據中不再出現在新數據中的主鍵列表，然後必須將其標記為已刪除。