基於另一個表的大更新和刪除

我有一個非常活躍的 PostgreSQL 10.4 DB，通過 SQLAlchemy 處理，所以 SQL 不是我的強項。

在它的表中，有master一個主鍵id和一些非索引字元串列value，以及slave一個外鍵mid指向master.id. 該表master僅略短於 100M 行，並且應用程序非常頻繁地訪問（通常每秒幾次，但總是一次只有幾行），而slave大約有兩倍並且以類似的方式訪問。

我想做兩件事：

1. slave從和刪除master：

delete from slave
   using master
   where
       slave.mid = master.id
       and master.value in ('some', 'set', 'of', 'values')
delete from master
   where value in ('the', 'same', 'set', 'of', 'values')

SQLAlchemy 沒有為 創建任何級聯刪除規則master，因此我不能只執行兩個查詢中的後者，因為它會導致外鍵約束違規。 2. 更新master：

update master
   set value=<case expression>
   where value in ('a', 'different', 'set', 'of', 'values')

要刪除的行數估計為幾百萬（可能是 1 mil 或 2 mil；當然不會更多）。

我有另一個我想保留的約束（但如果絕對必要可以放棄它）：空執行與實時執行的方式相同，除了我呼叫rollback而不是commit. 因此，例如，update top如果我在每次呼叫後都繼續回滾，那麼這樣做最終會陷入無限循環。

上面的程式碼在用於測試程式碼是否按預期工作的小型數據庫上執行良好，但在生產數據庫的副本上它們非常慢。所以，我的第一個問題是性能。

我的第二個問題是鎖定，因為生產受到很多打擊，如果能夠無縫地執行此操作，而不會導致應用程序出現任何停機和/或超時，那就太好了。

這讓我開始考慮批量刪除/更新，但這超出了我對 PostgreSQL（或一般 SQL）的了解。我怎樣才能讓這些按照我想要的方式工作？

編輯

經過一番考慮，我將從表（其中有兩個）更改為級聯刪除，從而解決了刪除問題。仍然存在有效更新的問題，很可能是分塊更新。

帶有 IN 條件的刪除可能比 JOIN 更快：

delete from slave
where mid in (select master.id
             from master 
             where master.value in ('some', 'set', 'of', 'values'));

delete from master
   where value in ('the', 'same', 'set', 'of', 'values');

另一個可能更快的選項是在單個語句中刪除兩個表。這也可以用於刪除多個從站。

with removed_master (id) as (
 delete from master
 where value in ('some', 'set', 'of', 'values')
 returning id
), remove_slave1 as (
 delete from slave_one
 where mid in (select id from removed_master)
)
delete from slave_two
where mid in (select id from removed_master);

這是因為在語句完成時評估外鍵約束，而不是在刪除每個單獨的行時。這具有額外的好處，您只需指定一次“主值”。

遺憾的是，PostgreSQL 沒有UPDATE TOP(n)orUPDATE...LIMIT n

您將不得不使用類似的東西來模擬：

update master set ... where pk in (select pk from master where ... LIMIT n);

你真正想做的是“執行更新，直到你檢測到有人在等你，然後結束它而不拋出錯誤”。這樣你就不必猜測正確的值是多少n。但是沒有辦法做到這一點。而且實現起來並不容易，特別是因為 FK 檢查在更新結束時排隊並執行，所以簡單地結束而沒有錯誤可能需要很長時間。

引用自：https://dba.stackexchange.com/questions/215569