使用PostgreSQL的時候，我們某些時候會往庫里插入大量數(shù)據(jù)，例如，導(dǎo)入測試數(shù)據(jù)，導(dǎo)入業(yè)務(wù)數(shù)據(jù)等等。本篇文章介紹了在導(dǎo)入大量數(shù)據(jù)時的一些可供選擇的優(yōu)化手段?？梢越Y(jié)合自己的情況進(jìn)行選擇。

一、關(guān)閉自動提交

關(guān)閉自動提交，并且只在每次(數(shù)據(jù)拷貝)結(jié)束的時候做一次提交。
如果允許每個插入都獨(dú)立地提交，那么PostgreSQL會為所增加的每行記錄做大量的處理。 而且在一個事務(wù)里完成所有插入的動作的最大的好處就是，如果有一條記錄插入失敗， 那么，到該點(diǎn)為止的所有已插入記錄都將被回滾，這樣就不會面對只有部分?jǐn)?shù)據(jù)，數(shù)據(jù)不完整的問題。

postgres=#  echo :AUTOCOMMIT
on
postgres=# set AUTOCOMMIT off
postgres=#  echo :AUTOCOMMIT
off

二、導(dǎo)入階段不創(chuàng)建索引，或者導(dǎo)入階段刪除索引

如果你正導(dǎo)入一張表的數(shù)據(jù)，最快的方法是創(chuàng)建表，用COPY批量導(dǎo)入，然后創(chuàng)建表需要的索引。 在已存在數(shù)據(jù)的表上創(chuàng)建索引要比遞增地更新表的每一行記錄要快。
如果你對現(xiàn)有表增加大量的數(shù)據(jù)，可以先刪除索引，導(dǎo)入表的數(shù)據(jù)，然后重新創(chuàng)建索引。 當(dāng)然，在缺少索引的期間，其它數(shù)據(jù)庫用戶的數(shù)據(jù)庫性能將有負(fù)面的影響。 并且我們在刪除唯一索引之前還需要仔細(xì)考慮清楚，因?yàn)槲ㄒ患s束提供的錯誤檢查在缺少索引的時候會消失。（慎重考慮索引帶來的影響）

三、刪除外鍵約束

和索引一樣，整體地檢查外鍵約束比檢查遞增的數(shù)據(jù)行更高效。 所以我們也可以刪除外鍵約束，導(dǎo)入表地數(shù)據(jù)，然后重建約束會更高效。

四、增大maintenance_work_mem

在裝載大量的數(shù)據(jù)的時候，臨時增大 maintenance_work_mem 可以改進(jìn)性能。 這個參數(shù)也可以幫助加速CREATE INDEX和ALTER TABLE ADD FOREIGN KEY命令。 它不會對COPY本身有很大作用，但是它可以加速創(chuàng)建索引和外鍵約束。

postgres=# show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 64MB
(1 row)

五、單值insert改多值insert

減少SQL解析的時間。

六、關(guān)閉歸檔模式并降低wal日志級別

當(dāng)使用WAL歸檔或流復(fù)制向一個安裝中錄入大量數(shù)據(jù)時，在導(dǎo)入數(shù)據(jù)結(jié)束時，執(zhí)行一次新的basebackup比執(zhí)行一次增量WAL更快。
為了防止錄入時的增量WAL，可以將wal_level暫時調(diào)整為minimal， archive_modet關(guān)閉，max_wal_senders設(shè)置為0來禁用歸檔和流復(fù)制。 但需修改這些設(shè)置需要重啟服務(wù)。

postgres=# show wal_level;
 wal_level 
-----------
 minimal
(1 row)

postgres=# show  archive_mode;
 archive_mode 
--------------
 off
(1 row)

postgres=# show max_wal_senders;
 max_wal_senders 
-----------------
 0
(1 row)

七、增大max_wal_size

臨時增大max_wal_size配置變量也可以讓大量數(shù)據(jù)載入更快。 這是因?yàn)橄騊ostgreSQL中載入大量的數(shù)據(jù)將導(dǎo)致檢查點(diǎn)的發(fā)生比平常（由checkpoint_timeout配置變量指定）更頻繁。
發(fā)生檢查點(diǎn)時，所有臟頁都必須被刷寫到磁盤上。 通過在批量數(shù)據(jù)載入時臨時增加max_wal_size，減少檢查點(diǎn)的數(shù)目。

postgres=# show max_wal_size;
 max_wal_size 
--------------
 1GB
(1 row)

八、使用copy替代insert

COPY針對批量數(shù)據(jù)加載進(jìn)行了優(yōu)化。
COPY命令是為裝載數(shù)量巨大的數(shù)據(jù)行優(yōu)化過的； 它沒INSERT那么靈活，但是在大量裝載數(shù)據(jù)的情況下，導(dǎo)致的荷載也少很多。 因?yàn)镃OPY是單條命令，因此填充表的時候就沒有必要關(guān)閉自動提交了。

如果不能使用COPY，可以使用PREPARE來創(chuàng)建一個預(yù)備INSERT， 然后使用EXECUTE多次效率更高。 這樣就避免了重復(fù)分析和規(guī)劃INSERT的開銷。

九、禁用觸發(fā)器

導(dǎo)入數(shù)據(jù)之前先DISABLE掉相關(guān)表上的觸發(fā)器，導(dǎo)入完成后重新讓他ENABLE。

ALTER TABLE tab_1 DISABLE TRIGGER ALL; 
導(dǎo)入數(shù)據(jù)
ALTER TABLE tab_1 ENABLE TRIGGER ALL;

十、相關(guān)導(dǎo)數(shù)工具：pg_bulkload

pg_bulkload 是 PostgreSQL 的一個高速數(shù)據(jù)加載工具，相對于 copy 命令。最大的優(yōu)勢是速度。在 pg_bulkload 的直接模式下，它將跳過共享緩沖區(qū)和 WAL 緩沖區(qū)，直接寫入文件。它還包括數(shù)據(jù)恢復(fù)功能，可在導(dǎo)入失敗時進(jìn)行恢復(fù)。
地址：https://github.com/ossc-db/pg_bulkload

十一、導(dǎo)入數(shù)據(jù)后，使用analyze

運(yùn)行ANALYZE 或者VACUUM ANALYZE可以保證規(guī)劃器有表數(shù)據(jù)的最新統(tǒng)計。
如果沒有統(tǒng)計數(shù)據(jù)或者統(tǒng)計數(shù)據(jù)太陳舊，那么規(guī)劃器可能選擇性能很差的執(zhí)行計劃，導(dǎo)致表的查詢性能較差。