關鍵字：批量數據導入，數據加載，大量插入，加快，提升速度
多元化選擇時代，人生里很多事物都是如此，凡事都沒有一成不變的方式和方法。不管白貓黑貓，能抓老鼠的就是好貓，適合自己的就是最好的。
提升批量數據導入的方法亦是如此，沒有何種方法是最優的，應用任何方法前根據自己的實際情況權衡利弊，做出選擇。
批量導入數據之前，無論采取何種方式，務必做好相應的備份。
導入完成后亦需對相應對象進行ANALYZE操作，這樣查詢優化器才會按照最新的統計信息生成正確的執行計劃。

下面正式介紹提升批量數據導入性能的n種方法。

方法1：禁用自動提交。

psql
\set AUTOCOMMIT off

其他
BEGIN;
執行批量數據導入
COMMIT;

方法2：設置表為UNLOGGED。

導入數據之前先把表改成UNLOGGED模式，導入完成后改回LOGGED模式。

ALTER TABLE tablename SET UNLOGGED;
執行批量數據導入
ALTER TABLE tablename LOGGED;

優點：
導入信息不記錄WAL日志，極大減少io，提升導入速度。
缺點：
1.在replication環境下，表無法設置為UNLOGGED模式。
2.導入過程一旦出現停電死機等會導致數據庫不能干凈關庫的情況，數據庫中所有UNLOGGED表的數據將丟失。

方法3：重建索引。

導入數據之前先刪除相關表上的索引，導入完成后重新創建之。

DROP INDEX indexname;
執行批量數據導入
CREATE INDEX ...;

查詢表上索引定義的方法

select * from pg_indexes where tablename ='tablename' and schemaname = 'schemaname';

方法4：重建外鍵。

導入數據之前先刪除相關表上的外鍵，導入完成后重新創建之。

ALTER TABLE ...
 DROP CONSTRAINT ... ;
執行批量數據導入
ALTER TABLE ...
 ADD CONSTRAINT ... 
 FOREIGN KEY ...
 REFERENCES ...; 

相關信息可查詢pg_constraint。

方法5：停用觸發器

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

ALTER TABLE tablename DISABLE TRIGGER ALL; 
執行批量數據導入
ALTER TABLE tablename ENABLE TRIGGER ALL;

相關信息可查詢pg_trigger。

方法6：insert改copy

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

COPY ... FROM 'xxx';

方法7：單值insert改多值insert

減少sql解析的時間。

方法8：insert改PREPARE

通過使用PREPARE預備語句，降低解析消耗。

PREPARE fooplan (int, text, bool, numeric) AS
 INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

方法9：修改參數

增大maintenance_work_mem，增大max_wal_size。

方法10：關閉歸檔模式，降低wal日志級別。

修改archive_mode參數控制歸檔開啟和關閉。降低wal_level值為minimal來減少日志信息記錄。
此法需要重啟數據庫，需要規劃停機時間。此外如有replication備庫，還需考慮對其影響。

到此這篇關于PostgreSQL提升批量數據導入性能的n種方法的文章就介紹到這了,更多相關PostgreSQL批量數據導入內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！