大量のデータを高速に投入するには
FORCIA, Inc. 板垣 貴裕
PostgreSQL に大量のデータを高速に取り込む方法を紹介します。 COPY という専用のコマンドを使うと INSERT よりもずっと高速です。 また、COPY を使う際にひと工夫すると、さらに速くなります。
高速にデータをロードするコマンド
INSERT の代わりに COPY FROM を使う
PostgreSQL には COPY FROM という大量データ取り込み専用のコマンドがあります。 COPY コマンドは外部からタブ区切り (.tsv) やカンマ区切り (.csv) のファイルを読み込める上に、INSERT よりもずっと高速です。
同様に、pg_dump コマンドも -d や --inserts オプションを付けると INSERT を並べたダンプファイルを作ってしまいます。 オプションを付けないデフォルトでは高速な COPY 形式なので、うっかりこれらのオプションを指定しないよう注意しましょう。 もし他のDBMS製品にデータを移動するのでなければ、COPY を使うデフォルトの形式でダンプさせたほうが良いでしょう。
インデックスを張らない状態でロードする
初期ロードの際には、インデックスを張ってからロードするよりも、張らずにロードして後から CREATE INDEX したほうが速い場合が多いようです。 このとき、主キー (PRIMARY KEY)、一意性制約 (UNIQUE)、排他制約 (EXCLUDE) も暗黙的にインデックスが追加されることに注意してください. 外部キーの追加もロード後のほうが良いでしょう。
具体的には、以下のような流れでデータをロードすることになります。
- CREATE TABLE (テーブル定義 + CHECK制約のみ)
- COPY FROM によるデータロード
- インデックスやインデックスを含む制約、外部キー制約の追加
また、テーブルを作成した後にインデックスを伴う制約を追加するには、以下のようなSQLを使うことができます。
=# ALTER TABLE tbl ADD PRIMARY KEY (id); =# ALTER TABLE tbl ADD UNIQUE (ukey); =# ALTER TABLE tbl ADD EXCLUDE USING gist (ekey WITH &&); =# ALTER TABLE tbl ADD FOREIGN KEY fkey REFERENCES rtbl (rid);
WALをスキップして高速化する
空のテーブルに対してしか使えないワザなので、データの初期ロードにしか使えませんが、非常に効果の高いデータロード方法があるので紹介します。
PostgreSQL では、データファイルの変更を行うたびに WAL (Write-Ahead Log) を出力します。 更新 (UPDATE) だけでなく、データロードを含む挿入 (INSERT, COPY FROM) も「変更」扱いです。
WAL の仕組みにより、更新処理のコミット直後にサーバが異常停止した場合にも、更新結果を復元することができるのですが、大量のデータロードの際には大きなオーバーヘッドになります。 大量のデータロードの途中でクラッシュした場合には、どうせデータをロードしていない状態までロールバックされるわけですから、必ずしも WAL が必要でないケースもあるはずです。 実際、PostgreSQL 8.3 以降では、特定の設定パラメータと処理の組み合わせの際に、WAL の書き出しをスキップする仕組みがあります。 これを利用できると、処理時間が 1/2 以下になることも珍しくありません。
WALのスキップができるのは、以下の条件です。
パラメータ設定
アーカイブモードを無効にしておく必要があります。 postgresql.conf で以下の設定を行います。
archive_mode = off wal_level = minimal -- 9.0以降
archive_mode の変更には PostgreSQL の再起動が必要なことに注意してください。 アーカイブ・リカバリを使いたい場合には、初期ロードが終わってから設定を変更し、再起動しましょう。
上記のようなパラメータ設定が必要なため、アーカイブログによるバックアップ、ウォーム・スタンバイ、9.0 のレプリケーションを使うような場合には、WAL をスキップできないことに注意してください。 これらのケースでは、初期ロードを除き、いったん運用に入ってしまうと、WALスキップによる高速化は利用できないと考えたほうが良いでしょう。
可能な処理
以下の処理でWALをスキップできます。 COPY FROM では可能ですが、INSERT, SELECT INTO ではスキップできないことに注意してください。
- 同一トランザクションで作成したもしくは TRUNCATE したテーブルへの COPY FROM
- CREATE TABLE AS SELECT ...
- CREATE INDEX, REINDEX, CLUSTER
- (9.0以降) VACUUM FULL, ALTER TABLE
例えば、既存の空のテーブルに対してロードする場合は、以下のようなコマンドの流れになります。
=# CREATE TABLE tbl ( ... ); -- インデックスはまだ定義しない =# BEGIN; =# TRUNCATE tbl; =# COPY tbl FROM ...; -- TRUNCATEと同じトランザクション内でCOPYする =# COMMIT; =# CREATE INDEX ... ON tbl ...; -- データロード後、インデックスを作成する
pg_restore でも有用
WALスキップによる高速化は、バックアップを pg_restore でリストアする場合にも効果があります。設定パラメータを調整したうえで、-1, --single-transaction または --jobs 2以上のオプションでリストアをすると、WALスキップが働きます。
pg_bulkload : さらなる高速化のためのツール
初期ロードでは TRUNCATE + COPY FROM のワザを使うとだいぶ速くなりますが、追加ロードが必要な場合には TRUNCATE できないため、やはり時間がかかってしまいます。 そんな時には、拡張ツールとして公開されている pg_bulkload が使えるかもしれません。
詳細は上のリンクから辿れますが、かなりチューニングした COPY と比べても、初期ロードで 15%、追加ロードでは 65% もロード時間を短縮できています。 アーカイブリカバリができない (レプリケーションも不可) という欠点はありますが、大量のデータを素早くロードしたいという場合には役立つツールです。
pg_bulkload を使うコツは、COPY とは異なり、インデックスを張ったままロードしたほうが高速なことです。 また、最新版のバージョン 3.0 では、COPY だけでは実現が難しい、エラー行のスキップや、データを加工しながら取り込む機能も追加されているため、単なる高速化以上の目的でも利用できます。
PostgreSQL に大量のデータをロードする (取り込む) 方法を紹介しました。 データロードを使う機会は、初期構築、定期的なバッチ処理、データのリストアなど、けっこう機会は多いと思います。 今回紹介したワザをつかって、効率よく運用しましょう!
関連リンク
- Bulk Loading and Restores/ja (PostgreSQL Wiki)
(2010年9月6日公開)