數(shù)據(jù)庫的應(yīng)用開發(fā)者和 DBA，在日常工作中，經(jīng)常都會要進(jìn)行數(shù)據(jù)的批量導(dǎo)入。而導(dǎo)入的過程往往非常耗時，既可能對業(yè)務(wù)系統(tǒng)的運行產(chǎn)生影響，同時也會影響工作效率。

背景

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

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

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

方法 1: 將目標(biāo)表更改為 UNLOGGED 模式

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

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

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

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

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

• ? 在將表和數(shù)據(jù)更改為 UNLOGGED 模式之前對其進(jìn)行備份

• ? 數(shù)據(jù)加載完成后，重新創(chuàng)建到備用服務(wù)器的任何復(fù)制

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

方法 2: 刪除并重新創(chuàng)建索引

現(xiàn)有索引可能會導(dǎo)致批量數(shù)據(jù)插入期間出現(xiàn)嚴(yán)重延遲。這是因為在添加每一行時，相應(yīng)的索引記錄也必須更新。

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

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

在創(chuàng)建索引之前，臨時調(diào)大 maintenance_work_mem 配置參數(shù)可能是值得的。增加的工作內(nèi)存有助于更快地創(chuàng)建索引。

另一個安全的措施是，在同一數(shù)據(jù)庫中創(chuàng)建目標(biāo)表的副本，其中包含現(xiàn)有數(shù)據(jù)和索引。然后，可以使用這個新復(fù)制的表，對批量插入測試兩種情況：刪除并重新創(chuàng)建索引，或動態(tài)更新索引。然后，就可以將性能驗證更好的方法應(yīng)用到生產(chǎn)表上面。

方法 3: 刪除并重新創(chuàng)建外鍵

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

除非受業(yè)務(wù)規(guī)則限制，否則我們建議從目標(biāo)表中刪除所有外鍵，在單個事務(wù)中加載數(shù)據(jù)，然后在提交事務(wù)后重新創(chuàng)建外鍵。

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>)...

同樣，調(diào)大 maintenance_work_mem 配置參數(shù)，可以提高重新創(chuàng)建外鍵約束的性能。

方法 4: 禁用觸發(fā)器

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

我們建議，在批量加載數(shù)據(jù)之前禁用目標(biāo)表中的所有觸發(fā)器，并在加載完成后啟用它們。禁用的所有觸發(fā)器也包括強(qiáng)制執(zhí)行外鍵約束檢查的內(nèi)部觸發(fā)器。

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

方法 5: 使用 COPY 命令

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

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

使用 COPY 的其他好處包括：

• ? 它支持文本和二進(jìn)制文件導(dǎo)入

• ? 它天然是事務(wù)性的

• ? 它允許指定輸入文件的結(jié)構(gòu)

• ? 它可以使用 WHERE 子句有條件地加載數(shù)據(jù)

方法 6: 使用多行 INSERT

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

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 的性能受現(xiàn)有索引的影響。我們建議在運行命令之前刪除索引，然后在運行之后重新創(chuàng)建索引。

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

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

方法 7: 運行 ANALYZE

這與提高批量數(shù)據(jù)導(dǎo)入性能無關(guān)，但我們強(qiáng)烈建議，在批量導(dǎo)入后立即對目標(biāo)表運行 ANALYZE 命令。大量新行會顯著改變列中的數(shù)據(jù)分布，并導(dǎo)致表上的任何現(xiàn)有統(tǒng)計信息過時。當(dāng)查詢優(yōu)化器使用過時的統(tǒng)計信息時，查詢性能可能會差得令人無法接受。運行 ANALYZE 命令，可以確保任何現(xiàn)有的統(tǒng)計信息得到更新。

最后的思考

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