在使用PostgreSQL的時候，我們某些時候會往庫里插入大量數據，例如，導入測試數據，導入業務數據等等。本篇文章介紹了在導入大量數據時的一些可供選擇的優化手段?？梢越Y合自己的情況進行選擇。

一、關閉自動提交

關閉自動提交，并且只在每次(數據拷貝)結束的時候做一次提交。

如果允許每個插入都獨立地提交，那么PostgreSQL會為所增加的每行記錄做大量的處理。而且在一個事務里完成所有插入的動作的最大的好處就是，如果有一條記錄插入失敗， 那么，到該點為止的所有已插入記錄都將被回滾，這樣就不會面對只有部分數據，數據不完整的問題。

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

二、導入階段不創建索引，或者導入階段刪除索引

如果你正導入一張表的數據，最快的方法是創建表，用COPY批量導入，然后創建表需要的索引。在已存在數據的表上創建索引要比遞增地更新表的每一行記錄要快。

如果你對現有表增加大量的數據，可以先刪除索引，導入表的數據，然后重新創建索引。當然，在缺少索引的期間，其它數據庫用戶的數據庫性能將有負面的影響。并且我們在刪除唯一索引之前還需要仔細考慮清楚，因為唯一約束提供的錯誤檢查在缺少索引的時候會消失。（慎重考慮索引帶來的影響）

三、刪除外鍵約束

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

四、增大maintenance_work_mem

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

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

五、單值insert改多值insert

減少SQL解析的時間。

六、關閉歸檔模式并降低wal日志級別

當使用WAL歸檔或流復制向一個安裝中錄入大量數據時，在導入數據結束時，執行一次新的basebackup比執行一次增量WAL更快。

為了防止錄入時的增量WAL，可以將wal_level暫時調整為minimal， archive_modet關閉，max_wal_senders設置為0來禁用歸檔和流復制。但需修改這些設置需要重啟服務。

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配置變量也可以讓大量數據載入更快。這是因為向PostgreSQL中載入大量的數據將導致檢查點的發生比平常（由checkpoint_timeout配置變量指定）更頻繁。

發生檢查點時，所有臟頁都必須被刷寫到磁盤上。通過在批量數據載入時臨時增加max_wal_size，減少檢查點的數目。

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

八、使用copy替代insert

COPY針對批量數據加載進行了優化。

COPY命令是為裝載數量巨大的數據行優化過的；它沒INSERT那么靈活，但是在大量裝載數據的情況下，導致的荷載也少很多。因為COPY是單條命令，因此填充表的時候就沒有必要關閉自動提交了。

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

九、禁用觸發器

導入數據之前先DISABLE掉相關表上的觸發器，導入完成后重新讓他ENABLE。

ALTER?TABLE?tab_1?DISABLE?TRIGGER?ALL;
導入數據
ALTER?TABLE?tab_1?ENABLE?TRIGGER?ALL;

十、相關導數工具：pg_bulkload

pg_bulkload 是 PostgreSQL 的一個高速數據加載工具，相對于 copy 命令。最大的優勢是速度。在 pg_bulkload 的直接模式下，它將跳過共享緩沖區和 WAL 緩沖區，直接寫入文件。它還包括數據恢復功能，可在導入失敗時進行恢復。

地址：https://github.com/ossc-db/pg_bulkload

十一、導入數據后，使用analyze

運行ANALYZE 或者VACUUM ANALYZE可以保證規劃器有表數據的最新統計。

如果沒有統計數據或者統計數據太陳舊，那么規劃器可能選擇性能很差的執行計劃，導致表的查詢性能較差。