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

背景

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

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

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

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

對(duì)于 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 不會(huì)將表的寫入操作記錄到預(yù)寫式日志（WAL）。這可以使加載過程非?？?。但是，由于未記錄操作日志，因此，如果在加載期間服務(wù)器發(fā)生崩潰或不正常的停機(jī)，則無(wú)法恢復(fù)數(shù)據(jù)。PostgreSQL 將在重新啟動(dòng)后自動(dòng)截?cái)嗳魏?UNLOGGED 模式的表。

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

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

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

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

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

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

現(xiàn)有索引可能會(huì)導(dǎo)致批量數(shù)據(jù)插入期間出現(xiàn)嚴(yán)重延遲。這是因?yàn)樵谔砑用恳恍袝r(shí)，相應(yīng)的索引記錄也必須更新。

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

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

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

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

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

除非受業(yè)務(wù)規(guī)則限制，否則我們建議從目標(biāo)表中刪除所有外鍵，在單個(gè)事務(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)表中刪除記錄）可能會(huì)導(dǎo)致批量數(shù)據(jù)加載延遲。這是因?yàn)?，每個(gè)觸發(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 命令，從一個(gè)或多個(gè)文件加載數(shù)據(jù)。COPY 針對(duì)批量數(shù)據(jù)加載進(jìn)行了優(yōu)化。它比運(yùn)行大量 INSERT 語(yǔ)句或者多行 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

對(duì)于批量數(shù)據(jù)加載來(lái)說(shuō)，運(yùn)行幾千或幾十萬(wàn)個(gè) INSERT 語(yǔ)句，可能是一個(gè)糟糕的選擇。這是因?yàn)?，每個(gè)單獨(dú)的 INSERT 命令都必須由查詢優(yōu)化器解析和準(zhǔn)備，完成所有約束檢查，作為單獨(dú)的事務(wù)運(yùn)行，并記錄在 WAL 中。使用多行的單個(gè) INSERT 語(yǔ)句可以節(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)有索引的影響。我們建議在運(yùn)行命令之前刪除索引，然后在運(yùn)行之后重新創(chuàng)建索引。

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

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

方法 7: 運(yùn)行 ANALYZE

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

最后的思考

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