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