データベースにデータを初期投入するために、大量のテーブル挿入操作を行う必要がままあります。 本節では、この作業を効率良く行うためのちょっとした提言を示します。
自動コミットを無効にし、最後に1回だけコミットします （普通のSQLでは、これはBEGINを開始時に、COMMITを最後に発行することを意味します。 クライアント用ライブラリの中にはこれを背後で実行するものもあります。 その場合は、要望通りにライブラリが行っているかどうかを確認しなければなりません）。 各挿入操作で別個にコミットすることを許すと、PostgreSQLは行を追加する度に多くの作業をしなければなりません。 1つのトランザクションで全ての挿入を行うことによるもう1つの利点は、1つの行の挿入に失敗した場合、その時点までに挿入された全ての行がロールバックされることです。 その結果、一部のみがロードされたデータの対処に困ることはありません。
単一コマンドで全ての行をロードするために一連のINSERTコマンドではなく、COPYを使用してください。 COPYコマンドは行を大量にロードすることに最適化されています。 このコマンドはINSERTに比べ柔軟性に欠けていますが、大量のデータロードにおけるオーバーヘッドを非常に低減します。 COPYコマンドでテーブルにデータを投入する場合、コマンドは1つなので、自動コミットを無効にする必要はありません。
COPYを使用できない場合、準備されたINSERT文をPREPAREを使用して作成し、必要数回だけEXECUTEを実行する方が良いでしょう。 これにより、繰り返し行われるINSERTの解析と計画作成分のオーバーヘッドを省くことになります。
COPYを使用した大量の行のロードは、ほとんど全ての場合において、INSERTを使用するロードよりも高速です。 たとえ複数のINSERTコマンドを単一トランザクションにまとめたとしても、またその際にPREPAREを使用したとしても、これは当てはまります。
新規に作成したテーブルをロードする時、最速の方法は、テーブルを作成し、COPYを使用した一括ロードを行い、そのテーブルに必要なインデックスを作成することです。 既存のデータに対するインデックスを作成する方が、各行がロードされる度に段階的に更新するよりも高速です。
既存のテーブルに大量のデータを追加しているのであれば、インデックスを削除し、テーブルをロード、その後にインデックスを再作成することができます。 もちろん、他のユーザから見ると、インデックスが存在しない間データベースの性能は悪化します。 また、一意性インデックスを削除する前には熟考しなければなりません。 一意性制約によるエラー検査がその期間行われないからです。
インデックスの場合と同様、外部キー制約は一行一行検査するよりも効率的に、"まとめて"検査することができます。 従って、外部キー制約を削除し、データをロード、そして、制約を再作成する方法は有用となることがあります。 繰り返しますが、データロードの速度と、制約が存在しない間のエラー検査がないという点とのトレードオフが必要です。
大規模なデータをロードする時maintenance_work_mem設定変数を一時的に増やすことで性能を向上させることができます。 これは、CREATE INDEXコマンドとALTER TABLE ADD FOREIGN KEYの速度向上に役立ちます。 COPY自体には大して役立ちませんので、この助言は、上述の技法の片方または両方を使用している時にのみ有用です。
大規模なデータをロードする時checkpoint_segments設定変数を一時的に増やすことで高速化することができます。 大量のデータをPostgreSQLにロードすることで、通常のチェックポイントの頻度（checkpoint_timeout設定変数により指定されます）よりも頻繁にチェックポイントが発生するためです。 チェックポイントが発生すると、全てのダーティページ（ディスクに未書き込みの変更済みメモリページ）はディスクに吐き出されなければなりません。 大量のデータロードの際に一時的にcheckpoint_segmentsを増加させることで、必要なチェックポイント数を減らすことができます。
テーブル内のデータ分布を大きく変更した時は毎回、ANALYZEを実行することを強く勧めます。 これは、テーブルに大量のデータをまとめてロードする場合も含まれます。 ANALYZE（またはVACUUM ANALYZE）を実行することで、確実にプランナがテーブルに関する最新の統計情報を持つことができます。 統計情報が存在しない、または古い場合、プランナは、そのテーブルに対する問い合わせの性能を損なわせる、お粗末な問い合わせ計画を選択する可能性があります。
pg_dumpで生成されるダンプスクリプトは自動的に上のガイドラインのいくつかを適用します（すべてではありません）。 pg_dumpダンプをできる限り高速にリロードするには、手作業で更に数作業が必要です。 （これらは作成時に適用するものではなく、ダンプを復元する時に適用するものです。 pg_dumpのアーカイブファイルからpg_restoreを使用してロードする時にも同じことが適用できます。）
デフォルトでは、pg_dumpはCOPYを使用します。 スキーマとデータのダンプ全体を生成する場合、インデックスと外部キー制約を作成する前にデータをロードすることに注意してください。 ですので、この場合、ガイドラインの最初のいくつかは自動的に行われます。 行わなければならない残件は、ダンプスクリプトをロードする前にmaintenance_work_memとcheckpoint_segmentsの値を適切に（通常よりも大きく）設定し、後でANALYZEを実行することです。
データのみのダンプもCOPYコマンドを使用しますが、インデックスの削除と再作成を行いません。 また、通常は外部キー制約を変更しません。 [1] したがって、データのみのダンプをロードする時、上の技法を使用したければ自らインデックスと外部キーを削除、再作成しなければなりません。 データをロードする時にcheckpoint_segmentsを増やすことも有用です。 しかし、maintenance_work_memを増やすことは考えないでください。 これは、後でインデックスと外部キーを手作業で再作成する時に行う方がよいでしょう。 また、実行した後でANALYZEを行うことを忘れないでください。
| [1] | --disable-triggersオプションを使用して、外部キーを無効にさせることができます。 しかし、これは外部キー制約を遅らせるのではなく、除去することに注意してください。 そのため、これを使用すると不正なデータを挿入することができます。 |