# 14.4\. 向數據庫中添加記錄 第一次填充數據庫時可能需要做大量的表插入。下面是一些建議，可以盡可能高效地處理這些事情。 ## 14.4.1\. 關閉自動提交 當使用多條`INSERT`時，關閉自動提交，并且只在每次(數據拷貝)結束的時候做一次提交。 在純SQL里，這就意味著在開始的時候發出`BEGIN`并且在結束的時候執行`COMMIT`。 有些客戶端的庫可能背著你干這些事情， 這種情況下你必須確信只有在你確實要那些庫干這些事情的時候它才做。 如果你允許每個插入都獨立地提交，那么PostgreSQL會為所增加的每行記錄做大量的處理。 在一個事務里完成所有插入的動作的最大的好處就是，如果有一條記錄插入失敗， 那么，到該點為止的所有已插入記錄都將被回滾， 這樣你就不會很難受地面對一個只裝載了一部分數據的表。 ## 14.4.2\. 使用`COPY` 使用[COPY](#calibre_link-777)在一條命令里裝載所有記錄， 而不是一連串的`INSERT`命令。`COPY`命令是為裝載數量巨大的數據行優化過的； 它沒`INSERT`那么靈活，但是在大量裝載數據的情況下，導致的荷載也少很多。 因為`COPY`是單條命令，因此填充表的時候就沒有必要關閉自動提交了。 如果你不能使用`COPY`，那么使用[PREPARE](#calibre_link-625)來創建一個預備`INSERT`， 然后使用`EXECUTE`多次效率更高。這樣就避免了重復分析和規劃`INSERT`的開銷。 不同的接口提供便利的方式不同；查看接口文檔的"已準備語句"。 請注意，在裝載大量數據行的時候，`COPY`幾乎總是比`INSERT`快， 即使使用了`PREPARE`并且把多個`INSERT`命令綁在一個事務中也是這樣。 當在相同事務中作為較早的`CREATE TABLE`或者`TRUNCATE`命令 使用的時候，`COPY`是最快的。在這種情況下，沒有WAL 需要寫入，因為在錯誤情況下，這些文件包含新加載的數據將被刪除。 然而，這種考慮只適用于當所有命令必須寫WAL時，[wal_level](#calibre_link-1039)是`最小的`。 ## 14.4.3\. 刪除索引 如果你正在裝載一個新創建的表，最快的方法是創建表， 用`COPY`批量裝載，然后創建表需要的任何索引。 在已存在數據的表上創建索引要比遞增地更新所裝載的每一行記錄要快。 如果你對現有表增加大量的數據，可能先刪除索引，裝載表， 然后重新創建索引更快些。當然，在缺少索引的期間，其它數據庫用戶的數據庫性能將有負面的影響。 并且我們在刪除唯一索引之前還需要仔細考慮清楚，因為唯一約束提供的錯誤檢查在缺少索引的時候會消失。 ## 14.4.4\. 刪除外鍵約束 和索引一樣，"批量地"檢查外鍵約束比一行行檢查更高效。因此，也許我們先刪除外鍵約束， 裝載數據，然后重建約束會更高效。同樣，裝載數據和缺少約束而失去錯誤檢查之間也有一個平衡。 更重要的是，當你將數據加載到已有外鍵約束的表中的時候， 每個新行需要等待觸發事件的服務器列表中的項（因為它是觸發器的觸發，檢查行的外鍵約束）。 裝載數以百萬計的行可以引起觸發事件隊列溢出可用內存，導致無法忍受的交換， 甚至命令的徹底失敗。因此它可能是_必要的_，不只是理想的，當加載大量數據的時候， 刪除并且重新申請外鍵約束。如果暫時刪除約束是不能接受的， 唯一的其他資源可能會將負載操作分裂為更小的事務。 ## 14.4.5\. 增大`maintenance_work_mem` 在裝載大量的數據的時候，臨時增大[maintenance_work_mem](#calibre_link-1150)配置變量可以改進性能。 這個參數也可以幫助加速 `CREATE INDEX`和`ALTER TABLE ADD FOREIGN KEY`命令。 它不會對`COPY`本身有多大作用，所以這個建議只有在你使用上面的兩個技巧時才有效。 ## 14.4.6\. 增大`checkpoint_segments` 臨時增大[checkpoint_segments](#calibre_link-1208)配置變量也可以讓大量數據裝載得更快。 這是因為向PostgreSQL里面裝載大量的數據可以導致檢查點操作 (由配置變量`checkpoint_timeout`聲明) 比平常更加頻繁發生。在發生一個檢查點的時候，所有臟數據都必須刷新到磁盤上。 通過在大量數據裝載的時候臨時增加`checkpoint_segments`， 所要求的檢查點的數目可以減少。 ## 14.4.7\. 禁用WAL歸檔和流復制 當加載大量數據到使用WAL歸檔或流復制的安裝過程時， 加載完成之后采取新的基礎備份比處理大量增量WAL數據可能會更快。當加載時為防止增量WAL日志，關閉歸檔和流復制，通過設置 [wal_level](#calibre_link-1039)到`最小`，[archive_mode](#calibre_link-1038)到`off`, [max_wal_senders](#calibre_link-470)為零。 但是請注意，更改這些設置需要重新啟動服務器。 除了為歸檔或者WAL發送處理WAL數據來避免時間， 這樣做實際上將使某些命令更快，因為如果`wal_level` 是`最小的`，他們設計不寫WAL（他們可以保證碰撞安全性最后通過`fsync`比寫WAL更便宜）。 這適用于以下命令： * `CREATE TABLE AS SELECT` * `CREATE INDEX` (正如 `ALTER TABLE ADD PRIMARY KEY`) * `ALTER TABLE SET TABLESPACE` * `CLUSTER` * `COPY FROM`，當目標表在同一事務之前已經被創建或截斷。 ## 14.4.8\. 事后運行`ANALYZE` 不管什么時候，如果你在更新了表中的大量數據之后，運行[ANALYZE](#calibre_link-589)都是個好習慣。 這包含大量加載數據到表。運行`ANALYZE` (或者`VACUUM ANALYZE`) 可以保證規劃器有表數據的最新統計。 如果沒有統計數據或者統計數據太陳舊，那么規劃器可能選擇很差勁的查詢規劃， 導致表的錯誤或者不存在數據的性能惡化。 請注意如果啟動autovacuum守護進程，可能自動運行`ANALYZE`；獲取詳情請 參閱[Section 23.1.3](#calibre_link-446)和[Section 23.1.6](#calibre_link-77)。 ## 14.4.9\. pg_dump的一些注意事項 pg_dump生成的轉儲腳本自動使用上面的若干個技巧，但不是全部。 要盡可能快地裝載pg_dump轉儲，我們需要手工做幾個事情。請注意， 這些要點適用于恢復一個_轉儲_，而不是_創建_一個轉儲的時候。 同樣的要點也適用于使用psql或者pg_restore從pg_dump 歸檔文件裝載文本復制的時候。 缺省的時候，pg_dump使用`COPY`，在它生成一個完整的模式和數據的轉儲的時候， 它會很小心地先裝載數據，然后創建索引和外鍵。因此，在這個情況下，頭幾條技巧是自動處理的。 剩余的是你要做的： * 設置比正常狀況大的`maintenance_work_mem`和`checkpoint_segments`值。 * 如果使用WAL歸檔或流復制，可以考慮在恢復過程中禁用他們。要做到這一點，設置`archive_mode`為`off`， `wal_level`為`最小的`，并且設置 `max_wal_senders`在裝載前為零。隨后，設置它們返回正確的值，并采取新的基礎備份。 * 使用pg_dump和pg_restore并行轉儲和恢復兩種模式的實驗，并找到 并行作業使用的最佳數目。平行轉儲和恢復通過`-j`選項應提供給你串行模式中的更高的性能。 * 考慮整個轉儲是否應作為單個事務進行恢復。要做到這一點，通過`-1`或者 `-single-transaction`命令行選項的psql或者pg_restore。 當使用此模式時，即使是最小的誤差將回滾整個恢復，可能丟棄很多時間的處理。 取決于如何相互關聯數據，這可能最好是手動清理或者沒有。如果你使用單一事務并且WAL歸檔關閉，則`COPY`命令將運行速度最快。 * 如果在數據庫服務器中有多個CPU，可以考慮使用pg_restore的`-jobs` 選項。 這允許并發數據加載和索引的創建。 * 之后運行`ANALYZE`。 只保存數據的轉儲仍然會使用`COPY`，但是它不會刪除或者重建索引， 并且它不會自動修改外鍵。 \[1\] 因此當裝載只有數據的轉儲時候，如果你想使用這些技術，刪除以及重建索引和外鍵完全取決于你。 當加載數據時，增大`checkpoint_segments`仍然是有用的， 但是增大`maintenance_work_mem`就沒什么必要了； 相反，你只是應該在事后手工創建索引和外鍵， 最后結束時不要忘記`ANALYZE`命令。參閱[Section 23.1.3](#calibre_link-446) 和[Section 23.1.6](#calibre_link-77)獲取更多詳情。 ### Notes \[1\] 你可以通過使用`-disable-triggers`選項的方法獲取關閉外鍵的效果。 不過要意識到這么做是消除，而不只是推遲違反外鍵約束， 因此如果你使用這個選項，將有可能插入壞數據。