One might need to insert a large amount of data when first populating a database. This section contains some suggestions on how to make this process as efficient as possible. データベースにデータを初期投入するために、大量のテーブル挿入操作を行う必要がままあります。 本節では、この作業を効率良く行うためのちょっとした提言を示します。
When using multiple <command>INSERT</command>s, turn off autocommit and just do
one commit at the end. (In plain
SQL, this means issuing <command>BEGIN</command> at the start and
<command>COMMIT</command> at the end. Some client libraries might
do this behind your back, in which case you need to make sure the
library does it when you want it done.) If you allow each
insertion to be committed separately,
<productname>PostgreSQL</productname> is doing a lot of work for
each row that is added. An additional benefit of doing all
insertions in one transaction is that if the insertion of one row
were to fail then the insertion of all rows inserted up to that
point would be rolled back, so you won't be stuck with partially
loaded data.
複数回のINSERT
を実行するのであれば、自動コミットを無効にして最後に1回だけコミットしてください。
(普通のSQLでは、これはBEGIN
を開始時に、COMMIT
を最後に発行することを意味します。
クライアント用ライブラリの中にはこれを背後で実行するものもあります。
その場合は、要望通りにライブラリが行っているかどうかを確認しなければなりません。)
各挿入操作で個別にコミットすることを許すと、PostgreSQLは行を追加する度に多くの作業をしなければなりません。
1つのトランザクションですべての挿入を行うことによるもう1つの利点は、1つの行の挿入に失敗した場合、その時点までに挿入されたすべての行がロールバックされることです。
その結果、一部のみがロードされたデータの対処に困ることはありません。
COPY
の使用 #
Use <link linkend="sql-copy"><command>COPY</command></link> to load
all the rows in one command, instead of using a series of
<command>INSERT</command> commands. The <command>COPY</command>
command is optimized for loading large numbers of rows; it is less
flexible than <command>INSERT</command>, but incurs significantly
less overhead for large data loads. Since <command>COPY</command>
is a single command, there is no need to disable autocommit if you
use this method to populate a table.
単一コマンドですべての行をロードするために一連のINSERT
コマンドではなく、COPY
を使用してください。
COPY
コマンドは行を大量にロードすることに最適化されています。
このコマンドはINSERT
に比べ柔軟性に欠けていますが、大量のデータロードにおけるオーバーヘッドを大きく低減します。
COPY
コマンドでテーブルにデータを投入する場合、コマンドは1つなので、自動コミットを無効にする必要はありません。
If you cannot use <command>COPY</command>, it might help to use <link
linkend="sql-prepare"><command>PREPARE</command></link> to create a
prepared <command>INSERT</command> statement, and then use
<command>EXECUTE</command> as many times as required. This avoids
some of the overhead of repeatedly parsing and planning
<command>INSERT</command>. Different interfaces provide this facility
in different ways; look for <quote>prepared statements</quote> in the interface
documentation.
COPY
を使用できない場合、準備されたINSERT
文をPREPARE
を使用して作成し、必要な回数だけEXECUTE
を実行する方が良いでしょう。
これにより、繰り返し行われるINSERT
の解析と計画作成分のオーバーヘッドを省くことになります。
この機能のための方法はインタフェースによって異なります。
このインタフェースの文書の「準備された文」を参照してください。
Note that loading a large number of rows using
<command>COPY</command> is almost always faster than using
<command>INSERT</command>, even if <command>PREPARE</command> is used and
multiple insertions are batched into a single transaction.
COPY
を使用した大量の行のロードは、ほとんどすべての場合において、INSERT
を使用するロードよりも高速です。
たとえ複数の挿入を単一トランザクションにまとめたとしても、またその際にPREPARE
を使用したとしても、これは当てはまります。
<command>COPY</command> is fastest when used within the same
transaction as an earlier <command>CREATE TABLE</command> or
<command>TRUNCATE</command> command. In such cases no WAL
needs to be written, because in case of an error, the files
containing the newly loaded data will be removed anyway.
However, this consideration only applies when
<xref linkend="guc-wal-level"/> is <literal>minimal</literal>
as all commands must write WAL otherwise.
COPY
は、前もって行われるCREATE TABLE
またはTRUNCATE
コマンドと同一トランザクションで行った場合に、最速です。
この場合、エラーが起きた場合に新しくロードされるデータを含むファイルがとにかく削除されますので、WALを書き出す必要がありません。
しかし、wal_levelがminimal
に設定されている場合のみにこの方法は当てはまります。
この他の場合には、すべてのコマンドをWALに書き出さなければならないためです。
If you are loading a freshly created table, the fastest method is to
create the table, bulk load the table's data using
<command>COPY</command>, then create any indexes needed for the
table. Creating an index on pre-existing data is quicker than
updating it incrementally as each row is loaded.
新規に作成したテーブルをロードする時、最速の方法は、テーブルを作成し、COPY
を使用した一括ロードを行い、そのテーブルに必要なインデックスを作成することです。
既存のデータに対するインデックスを作成する方が、各行がロードされる度に段階的に更新するよりも高速です。
If you are adding large amounts of data to an existing table, it might be a win to drop the indexes, load the table, and then recreate the indexes. Of course, the database performance for other users might suffer during the time the indexes are missing. One should also think twice before dropping a unique index, since the error checking afforded by the unique constraint will be lost while the index is missing. 既存のテーブルに大量のデータを追加しているのであれば、インデックスを削除し、テーブルをロード、その後にインデックスを再作成する方がよいかもしれません。 もちろん、他のユーザから見ると、インデックスが存在しない間データベースの性能は悪化します。 また、一意性インデックスを削除する前には熟考しなければなりません。 一意性制約によるエラー検査がその期間行われないからです。
Just as with indexes, a foreign key constraint can be checked <quote>in bulk</quote> more efficiently than row-by-row. So it might be useful to drop foreign key constraints, load data, and re-create the constraints. Again, there is a trade-off between data load speed and loss of error checking while the constraint is missing. インデックスの場合と同様、外部キー制約は一行一行検査するよりも効率的に、「まとめて」検査することができます。 従って、外部キー制約を削除し、データをロード、そして、制約を再作成する方法は有用となることがあります。 繰り返しますが、データロードの速度と、制約が存在しない間のエラー検査がないという点とのトレードオフがあります。
What's more, when you load data into a table with existing foreign key constraints, each new row requires an entry in the server's list of pending trigger events (since it is the firing of a trigger that checks the row's foreign key constraint). Loading many millions of rows can cause the trigger event queue to overflow available memory, leading to intolerable swapping or even outright failure of the command. Therefore it may be <emphasis>necessary</emphasis>, not just desirable, to drop and re-apply foreign keys when loading large amounts of data. If temporarily removing the constraint isn't acceptable, the only other recourse may be to split up the load operation into smaller transactions. 外部キー制約をすでに持つテーブルにデータをロードする時、新しい行はそれぞれ(行の外部キー制約を検査するトリガを発行しますので)サーバの待機中トリガイベントのリスト内に項目を要求します。 数百万の行をロードすると、トリガイベントのキューが利用可能なメモリをオーバーフローさせてしまい、耐えられないほどのスワッピングが発生してしまう、最悪はそのコマンドが完全に失敗してしまう可能性があります。 したがって単に好ましいだけでなく、大量のデータをロードする時には外部キーを削除し再度適用することが必要かもしれません。 一時的な制約削除が受け入れられない場合に他に取り得る手段は、ロード操作をより小さなトランザクションに分割することだけかもしれません。
maintenance_work_mem
を増やす #
Temporarily increasing the <xref linkend="guc-maintenance-work-mem"/>
configuration variable when loading large amounts of data can
lead to improved performance. This will help to speed up <command>CREATE
INDEX</command> commands and <command>ALTER TABLE ADD FOREIGN KEY</command> commands.
It won't do much for <command>COPY</command> itself, so this advice is
only useful when you are using one or both of the above techniques.
大規模なデータをロードする時maintenance_work_mem設定変数を一時的に増やすことで性能を向上させることができます。
これは、CREATE INDEX
コマンドとALTER TABLE ADD FOREIGN KEY
の速度向上に役立ちます。
COPY
自体には大して役立ちませんので、この助言は、上述の技法の片方または両方を使用している時にのみ有用です。
max_wal_size
を増やす #
Temporarily increasing the <xref linkend="guc-max-wal-size"/>
configuration variable can also
make large data loads faster. This is because loading a large
amount of data into <productname>PostgreSQL</productname> will
cause checkpoints to occur more often than the normal checkpoint
frequency (specified by the <varname>checkpoint_timeout</varname>
configuration variable). Whenever a checkpoint occurs, all dirty
pages must be flushed to disk. By increasing
<varname>max_wal_size</varname> temporarily during bulk
data loads, the number of checkpoints that are required can be
reduced.
大規模なデータをロードする時max_wal_size設定変数を一時的に増やすことで高速化することができます。
大量のデータをPostgreSQLにロードすることで、通常のチェックポイントの頻度(checkpoint_timeout
設定変数により指定されます)よりも頻繁にチェックポイントが発生するためです。
チェックポイントが発生すると、すべてのダーティページ(ディスクに未書き込みの変更済みメモリページ)はディスクにフラッシュされなければなりません。
大量のデータロードの際に一時的にmax_wal_size
を増加させることで、必要なチェックポイント数を減らすことができます。
When loading large amounts of data into an installation that uses
WAL archiving or streaming replication, it might be faster to take a
new base backup after the load has completed than to process a large
amount of incremental WAL data. To prevent incremental WAL logging
while loading, disable archiving and streaming replication, by setting
<xref linkend="guc-wal-level"/> to <literal>minimal</literal>,
<xref linkend="guc-archive-mode"/> to <literal>off</literal>, and
<xref linkend="guc-max-wal-senders"/> to zero.
But note that changing these settings requires a server restart,
and makes any base backups taken before unavailable for archive
recovery and standby server, which may lead to data loss.
大量のデータをWALアーカイブ処理またはストリーミングレプリケーションを使用するインストレーションにロードする時、増加する大量のWALデータを処理するより、ロードが完了した後に新しくベースバックアップを取る方が高速です。
ロード中のWALログの増加を防ぐためには、wal_levelをminimal
に、archive_modeをoff
に、max_wal_sendersをゼロに設定することにより、アーカイブ処理とストリーミングレプリケーションを無効にしてください。
しかし、これらの変数を変更するにはサーバの再起動が必要となり、以前取得したベースバックアップがアーカイブリカバリやスタンバイサーバで使用できなくなりデータ消失につながる可能性があるため、注意してください。
Aside from avoiding the time for the archiver or WAL sender to process the
WAL data, doing this will actually make certain commands faster, because
they do not to write WAL at all if <varname>wal_level</varname>
is <literal>minimal</literal> and the current subtransaction (or top-level
transaction) created or truncated the table or index they change. (They
can guarantee crash safety more cheaply by doing
an <function>fsync</function> at the end than by writing WAL.)
こうすると、WALデータを処理する保管処理またはWAL送信処理にかかる時間がかからないことの他に、実際のところ、特定のコマンドをより高速にします。
wal_level
がminimal
の場合、これらのコマンドではWALへの書き出しは全く予定されないためです。
(これらは最後にfsync
を実行することで、WALへの書き込みより安価にクラッシュした場合の安全性を保証することができます。)
ANALYZE
を実行 #
Whenever you have significantly altered the distribution of data
within a table, running <link linkend="sql-analyze"><command>ANALYZE</command></link> is strongly recommended. This
includes bulk loading large amounts of data into the table. Running
<command>ANALYZE</command> (or <command>VACUUM ANALYZE</command>)
ensures that the planner has up-to-date statistics about the
table. With no statistics or obsolete statistics, the planner might
make poor decisions during query planning, leading to poor
performance on any tables with inaccurate or nonexistent
statistics. Note that if the autovacuum daemon is enabled, it might
run <command>ANALYZE</command> automatically; see
<xref linkend="vacuum-for-statistics"/>
and <xref linkend="autovacuum"/> for more information.
テーブル内のデータ分布を大きく変更した時は毎回、ANALYZE
を実行することを強く勧めます。
これは、テーブルに大量のデータをまとめてロードする場合も含まれます。
ANALYZE
(またはVACUUM ANALYZE
)を実行することで、確実にプランナがテーブルに関する最新の統計情報を持つことができます。
統計情報が存在しない、または古い場合、プランナは、そのテーブルに対する問い合わせの性能を損なわせる、お粗末な問い合わせ計画を選択する可能性があります。
自動バキュームデーモンが有効な場合、ANALYZE
が自動的に実行されます。
詳細は24.1.3および24.1.6を参照してください。
Dump scripts generated by <application>pg_dump</application> automatically apply several, but not all, of the above guidelines. To restore a <application>pg_dump</application> dump as quickly as possible, you need to do a few extra things manually. (Note that these points apply while <emphasis>restoring</emphasis> a dump, not while <emphasis>creating</emphasis> it. The same points apply whether loading a text dump with <application>psql</application> or using <application>pg_restore</application> to load from a <application>pg_dump</application> archive file.) pg_dumpで生成されるダンプスクリプトは自動的に上のガイドラインのいくつかを適用します(すべてではありません)。 pg_dumpダンプをできる限り高速にリストアするには、手作業で更に数作業が必要です。 (これらは作成時に適用するものではなく、ダンプを復元する時に適用するものです。 psqlを使用してテキスト形式のダンプをロードする時とpg_dumpのアーカイブファイルからpg_restoreを使用してロードする時にも同じことが適用できます。)
By default, <application>pg_dump</application> uses <command>COPY</command>, and when
it is generating a complete schema-and-data dump, it is careful to
load data before creating indexes and foreign keys. So in this case
several guidelines are handled automatically. What is left
for you to do is to:
デフォルトでは、pg_dumpはCOPY
を使用します。
スキーマとデータのダンプ全体を生成する場合、インデックスと外部キー制約を作成する前にデータをロードすることに注意してください。
ですので、この場合、ガイドラインのいくつかは自動的に行われます。
残された作業は以下のとおりです。
Set appropriate (i.e., larger than normal) values for
<varname>maintenance_work_mem</varname> and
<varname>max_wal_size</varname>.
maintenance_work_mem
およびmax_wal_size
を適切な(つまり通常よりも大きな)値に設定します。
If using WAL archiving or streaming replication, consider disabling
them during the restore. To do that, set <varname>archive_mode</varname>
to <literal>off</literal>,
<varname>wal_level</varname> to <literal>minimal</literal>, and
<varname>max_wal_senders</varname> to zero before loading the dump.
Afterwards, set them back to the right values and take a fresh
base backup.
WALアーカイブ処理またはストリーミングレプリケーションを使用する場合は、リストア時にこれを無効にすることを検討してください。
このためにはダンプをロードする前にarchive_mode
をoff
に、wal_level
をminimal
に、max_wal_senders
をゼロに設定してください。
その後それらを正しい値に戻し、新規にベースバックアップを取ってください。
Experiment with the parallel dump and restore modes of both
<application>pg_dump</application> and <application>pg_restore</application> and find the
optimal number of concurrent jobs to use. Dumping and restoring in
parallel by means of the <option>-j</option> option should give you a
significantly higher performance over the serial mode.
pg_dumpとpg_restoreで、並列ダンプとリストア方式を実験して、利用する並列なジョブの最適な数を見つけて下さい。
-j
オプションでダンプとリストアを並列に行なうのは逐次方式よりも大きく性能を向上させるでしょう。
Consider whether the whole dump should be restored as a single
transaction. To do that, pass the <option>-1</option> or
<option>--single-transaction</option> command-line option to
<application>psql</application> or <application>pg_restore</application>. When using this
mode, even the smallest of errors will rollback the entire restore,
possibly discarding many hours of processing. Depending on how
interrelated the data is, that might seem preferable to manual cleanup,
or not. <command>COPY</command> commands will run fastest if you use a single
transaction and have WAL archiving turned off.
ダンプ全体を単一トランザクションとしてリストアすべきかどうか検討してください。
このためにはpsqlまたはpg_restoreに-1
または--single-transaction
コマンドラインオプションを指定してください。
このモードを使用する場合、たとえ小さなエラーであっても、エラーがあればリストア全体がロールバックされます。
データ同士の関連性がどの程度あるかに依存しますが、手作業での整理の際には好まれるかと思います。さもなくばあまり勧めません。
単一トランザクションで実行し、WALアーカイブを無効にしている場合、COPY
コマンドは最も高速に行われます。
If multiple CPUs are available in the database server, consider using
<application>pg_restore</application>'s <option>--jobs</option> option. This
allows concurrent data loading and index creation.
データベースサーバで複数のCPUが利用できるのであれば、pg_restoreの--jobs
オプションの利用を検討してください。
これによりデータのロードとインデックスの作成を同時に行うことができます。
Run <command>ANALYZE</command> afterwards.
この後でANALYZE
を実行してください。
A data-only dump will still use <command>COPY</command>, but it does not
drop or recreate indexes, and it does not normally touch foreign
keys.
データのみのダンプもCOPY
コマンドを使用しますが、インデックスの削除と再作成を行いません。
また、通常は外部キー制約を変更しません。
[14]
So when loading a data-only dump, it is up to you to drop and recreate
indexes and foreign keys if you wish to use those techniques.
It's still useful to increase <varname>max_wal_size</varname>
while loading the data, but don't bother increasing
<varname>maintenance_work_mem</varname>; rather, you'd do that while
manually recreating indexes and foreign keys afterwards.
And don't forget to <command>ANALYZE</command> when you're done; see
<xref linkend="vacuum-for-statistics"/>
and <xref linkend="autovacuum"/> for more information.
したがって、データのみのダンプをロードする時、上の技法を使用したければ自らインデックスと外部キーを削除、再作成しなければなりません。
データをロードする時にmax_wal_size
を増やすことも有用です。
しかし、maintenance_work_mem
を増やすことは考えないでください。
これは、後でインデックスと外部キーを手作業で再作成する時に行う方がよいでしょう。
また、実行した後でANALYZE
を行うことを忘れないでください。
詳細は24.1.3および24.1.6を参照してください。
[14]
You can get the effect of disabling foreign keys by using
the <option>--disable-triggers</option> option — but realize that
that eliminates, rather than just postpones, foreign key
validation, and so it is possible to insert bad data if you use it.
--disable-triggers
オプションを使用して、外部キーを無効にさせることができます。
しかし、これは外部キー制約を遅らせるのではなく、除去することに注意してください。
そのため、これを使用すると不正なデータを挿入することができてしまいます。