狠狠色丁香婷婷综合尤物/久久精品综合一区二区三区/中国有色金属学报/国产日韩欧美在线观看 - 国产一区二区三区四区五区tv

數據庫的應用開發者和 DBA，在日常工作中，經常都會要進行數據的批量導入。而導入的過程往往非常耗時，既可能對業務系統的運行產生影響，同時也會影響工作效率。

背景

有時，PostgreSQL 數據庫需要通過單個或最少的步驟，來導入大量數據。這通常稱為批量數據導入，其中數據源通常是一個或多個大文件。這個過程有時可能會慢得令人無法接受。

導致性能如此糟糕的原因有很多，例如：索引、觸發器、外鍵、GUID 主鍵，甚至預寫式日志（WAL）也可能導致延遲。

在本文中，我們將介紹將數據批量導入 PostgreSQL 數據庫的一些最佳實踐技巧。但是，在某些情況下，這些方法也可能都不是那么有效。我們建議您在應用任何方法之前，先考慮好它的優缺點。

方法 1: 將目標表更改為 UNLOGGED 模式

對于 PostgreSQL 9.5 及更高版本，可以先將目標表更改為 UNLOGGED，然后在加載完數據后將其更改回 LOGGED：

ALTER TABLE <target table> SET UNLOGGED
<bulk data insert operations…>
ALTER TABLE <target table> LOGGED

UNLOGGED 模式可確保 PostgreSQL 不會將表的寫入操作記錄到預寫式日志（WAL）。這可以使加載過程非常快。但是，由于未記錄操作日志，因此，如果在加載期間服務器發生崩潰或不正常的停機，則無法恢復數據。PostgreSQL 將在重新啟動后自動截斷任何 UNLOGGED 模式的表。

此外，UNLOGGED 模式的表不會同步到備用服務器。在這種情況下，必須在加載之前刪除現有的復制，并在加載后重新創建現有復制。根據主節點中的數據量和備用節點的數量，重新創建復制的時間可能相當長，并且無法滿足高可用的要求。

我們建議采用以下最佳實踐，將數據批量插入到 UNLOGGED 模式的表中：

• ? 在將表和數據更改為 UNLOGGED 模式之前對其進行備份

• ? 數據加載完成后，重新創建到備用服務器的任何復制

• ? 對可以輕松重新填充的表（例如，大型查找表或維度表），才使用 UNLOGGED 模式的批量插入

方法 2: 刪除并重新創建索引

現有索引可能會導致批量數據插入期間出現嚴重延遲。這是因為在添加每一行時，相應的索引記錄也必須更新。

我們建議在開始批量插入之前，盡可能刪除目標表中的索引，并在加載完成后重新創建索引。同樣，在大型表上創建索引可能很耗時，但通常比在加載期間更新索引更快。

DROP INDEX <index_name1>, <index_name2> … <index_name_n>
<bulk data insert operations…>
CREATE INDEX <index_name> ON <target_table>(column1, …,column n)

在創建索引之前，臨時調大 maintenance_work_mem 配置參數可能是值得的。增加的工作內存有助于更快地創建索引。

另一個安全的措施是，在同一數據庫中創建目標表的副本，其中包含現有數據和索引。然后，可以使用這個新復制的表，對批量插入測試兩種情況：刪除并重新創建索引，或動態更新索引。然后，就可以將性能驗證更好的方法應用到生產表上面。

方法 3: 刪除并重新創建外鍵

與索引一樣，外鍵約束也會影響批量加載性能。這是因為必須檢查每個插入行中的每個外鍵是否存在相應的主鍵。在后臺，PostgreSQL 使用觸發器來執行檢查。加載大量行時，必須為每行觸發此觸發器，這會增加開銷。

除非受業務規則限制，否則我們建議從目標表中刪除所有外鍵，在單個事務中加載數據，然后在提交事務后重新創建外鍵。

ALTER TABLE <target_table>
    DROP CONSTRAINT <foreign_key_constraint>

BEGIN TRANSACTION
    <bulk data insert operations…>
COMMIT

ALTER TABLE <target_table>
    ADD CONSTRAINT <foreign key constraint>
    FOREIGN KEY (<foreign_key_field>)
    REFERENCES <parent_table>(<primary key field>)...

同樣，調大 maintenance_work_mem 配置參數，可以提高重新創建外鍵約束的性能。

方法 4: 禁用觸發器

INSERT 或 DELETE 觸發器（如果加載過程還涉及從目標表中刪除記錄）可能會導致批量數據加載延遲。這是因為，每個觸發器在每行被 INSERT 或 DELETE 后，都有需要檢查的邏輯和需要立即完成的操作。

我們建議，在批量加載數據之前禁用目標表中的所有觸發器，并在加載完成后啟用它們。禁用的所有觸發器也包括強制執行外鍵約束檢查的內部觸發器。

ALTER TABLE <target table> DISABLE TRIGGER ALL
<bulk data insert operations…>
ALTER TABLE <target table> ENABLE TRIGGER ALL

方法 5: 使用 COPY 命令

我們建議使用 PostgreSQL 的 COPY 命令，從一個或多個文件加載數據。COPY 針對批量數據加載進行了優化。它比運行大量 INSERT 語句或者多行 INSERT 都要更加高效。

COPY <target table> [( column1>, … , <column_n>)]
    FROM  '<file_name_and_path>'
    WITH  (<option1>, <option2>, … , <option_n>)

使用 COPY 的其他好處包括：

• ? 它支持文本和二進制文件導入

• ? 它天然是事務性的

• ? 它允許指定輸入文件的結構

• ? 它可以使用 WHERE 子句有條件地加載數據

方法 6: 使用多行 INSERT

對于批量數據加載來說，運行幾千或幾十萬個 INSERT 語句，可能是一個糟糕的選擇。這是因為，每個單獨的 INSERT 命令都必須由查詢優化器解析和準備，完成所有約束檢查，作為單獨的事務運行，并記錄在 WAL 中。使用多行的單個 INSERT 語句可以節省此開銷。

INSERT INTO <target_table> (<column1>, <column2>, …, <column_n>) 
VALUES 
    (<value a>, <value b>, …, <value x>),
    (<value 1>, <value 2>, …, <value n>),
    (<value A>, <value B>, …, <value Z>),
    (<value i>, <value ii>, …, <value L>),
    ...

多行 INSERT 的性能受現有索引的影響。我們建議在運行命令之前刪除索引，然后在運行之后重新創建索引。

另一個需要注意的方面是，PostgreSQL 可用于運行多行 INSERT 的內存大小。當運行多行 INSERT 時，內存中必須要容納大量的輸入值，除非有足夠的可用內存，否則該過程可能會失敗。

我們建議將 effective_cache_size 參數設置為機器總內存的 50%，shared_buffer 參數設置為總內存的 25%。此外，為了安全起見，最好運行一系列的多行 INSERT，每條語句都有 1000 行的值。

方法 7: 運行 ANALYZE

這與提高批量數據導入性能無關，但我們強烈建議，在批量導入后立即對目標表運行 ANALYZE 命令。大量新行會顯著改變列中的數據分布，并導致表上的任何現有統計信息過時。當查詢優化器使用過時的統計信息時，查詢性能可能會差得令人無法接受。運行 ANALYZE 命令，可以確保任何現有的統計信息得到更新。

最后的思考

數據庫應用程序可能并非每天都要進行批量數據導入，但運行時會對查詢的性能產生影響。這就是為什么有必要盡可能減少加載時間的原因。為了最大限度地減少任何意外，DBA 可以做的一件事是，在具有類似服務器規格和 PostgreSQL 配置的開發環境或灰度環境中，測試負載的優化效果。每個數據加載方案都是不同的，最好嘗試下每種方法，并找出最有效的方法。