如何在PostgreSQL中更新大表

  • 2019 年 11 月 6 日
  • 筆記

本文來源：www.codacy.com/blog/how-to…

在Postgres中更新大型表並不像看起來那樣簡單。如果您的表包含數億行，您將發現很難及時進行簡單的操作，例如添加列或更改列類型。

在不停機的情況下進行這類操作是一個更大的挑戰。在這篇博客文章中，我將嘗試概述一些策略，以在管理大型數據集的同時最大程度地減少表不可用性。

一般準則

當您更新列中的值時，Postgres將在磁盤中寫入一個新行，棄用舊行，然後繼續更新所有索引。此過程等同於INSERT加上每一行後再DELETE，這會佔用大量資源。

除此之外，需要更新大表時還應了解的事項列表：

  • 從頭開始創建新表比更新每一行要快。順序寫比稀疏更新快，並且最後不會出現死行。
  • 表約束和索引嚴重延遲了每次寫入。如果可能，應在更新運行時刪除所有索引，觸發器和外鍵，並在最後重新創建它們。
  • 添加沒有默認值的可空列是一種廉價的操作。寫入列的實際數據是昂貴的部分。
  • 更新行時，不會重寫存儲在TOAST中的數據
  • 從Postgres 9.2開始，在某些數據類型之間進行轉換不需要重寫整個表。例如：從VARCHAR（32）轉換為VARCHAR（64）。

考慮到這一點，讓我們看一些可以用來有效更新表中大量數據行的策略：

增量更新

如果您可以使用例如順序ID對數據進行細分，則可以批量更新行。由於您只需要保持較短時間的鎖定，因此可以最大化表的可用性。如果添加新列，則可以將其臨時設置為可為空，然後開始逐漸用新值填充它。

這種方法的主要問題是性能，這是一個非常緩慢的過程，因為就地更新成本很高。在遷移期間，它可能還需要更複雜的應用程序邏輯。

創建一個新表

更新大表的最快方法是創建一個新表。

如果可以安全地刪除現有表，並且有足夠的磁盤空間，則執行更新的最簡單方法是將數據插入到新表中，然後對其進行重命名。以下是此操作的基本執行腳本：

create table user_info_copy (LIKE user_info INCLUDING INDEXES INCLUDING COMMENTS);    INSERT INTO user_info_copy  SELECT user_no, idcard_no, real_name, bankcard_no, bind_mobile       , false, bind_status, user_identity, create_time, creator       , edit_time, editor, is_del, VERSION, customer_id       , id_card_type, source_id, platform_no, one_passport_no, bank_code  FROM user_info;    drop TABLE user_info;    alter table user_info_copy rename to user_info;

重新創建現有表

如果由於不想重新創建視圖或由於其他限制而不能刪除原始表，則可以使用臨時表保存新值，截斷舊錶並在那裡重寫數據。當您有未決的寫請求時，此方法也有一些優點，如我們將在下一部分中看到的。

如果您的表可以容納在內存中，則應在此事務期間增加temp_buffers屬性。使用RAM代替磁盤來存儲臨時表將明顯提高性能：

SET temp_buffers = 3000MB; ----相應地更改此值

# 創建臨時表  CREATE TABLE temp_user_info(     user_no BIGINT,     PRIMARY KEY( user_no )  );  # 如果需要提速可以從表中刪除索引  # 複製數據到臨時表中  insert into temp_user_info select user_no from user_info;    # 改變表結構，比如需要添加新列  TRUNCATE user_no;  # 執行插入列字段語句  # 再把數據反寫到user_info表

處理並發寫入

即使進行了上述優化，重新創建表仍然是緩慢的操作。如果您正在實時數據庫中運行查詢，則可能需要處理並發寫入請求。

最簡單的方法是在事務期間在表上強制使用SHARE LOCK, 語句如下

LOCK TABLE user_info IN SHARE MODE;

如果花費太長時間，所有寫請求將一直等到鎖釋放或超時為止。如果未刪除原始表，則一旦事務結束，將執行未超時的請求。請注意，即使使用相同的名稱創建新表，請求仍將失敗，因為它們使用表OID。

根據寫請求的性質，您還可以創建自定義規則來存儲對錶所做的更改。例如，您可以設置一個規則，以在開始數據遷移之前記錄已刪除的行：

CREATE RULE deleted_rule AS ON DELETE  TO tbl  DO INSERT INTO tbl_deletes VALUES  (    OLD.id  );

遷移結束時，您只需從tbl_deletes中讀取ID，然後在新表上將其刪除。可以使用類似的方法來處理其他類型的請求。

結論

一旦達到一定大小，曾經瞬時的操作可能需要幾個小時來準備和執行。個人實驗結論：

  • 用存儲過程批量更新 560w ， 1455秒結束
  • 用複製表改名方法操作 560w數據， 120秒左右就結束了；

VirMach 便宜 VPS

QNews

QNews