1. pg_bulkload とは
pg_bulkload は PostgreSQL に大量のデータを高速にロードすることができるツールです。
pg_bulkload は PostgreSQL のサポート中バージョンで利用可能です。今のところ、新たな PostgreSQLメジャーバージョンがリリースされるたびに、それに対応した pg_bulkloadバージョンが順次リリースされています。古い PostgreSQLバージョンとしては 8.3.x 以降で使用が可能です。ただし、pg_bulkload の最新バージョンで検証が行なわれていて正式にサポートが謳われているのは、PostgreSQL のサポート中バージョンのみとなります。最新 PostgreSQLメジャーバージョンが 18.x である執筆時点であれば 14.x 以降が該当します。
2. 使用方法
◆ インストール
pg_bulkload は以下のページから対応する PostgreSQL バージョンと Linux のバージョンごとの rpm パッケージもしくはソースコードをダウンロードすることができます。また、パッケージは PostgreSQL Yum Repository からも取得可能となっています。ここでは、ソースコードからビルドする方法を示します。
ソフトウェア配布ページ： https://github.com/ossc-db/pg_bulkload/releases
ソースコードをダウンロード後は以下の手順で他のツールと同様に対象の PostgreSQL に導入することができます。インストール終了後は、pg_bulkload を使用したいデータベースにて CREATE EXTENSION を実行してください。
$ tar zxf pg_bulkload-VERSION3_1_23.tar.gz $ cd pg_bulkload-VERSION3_1_23 $ make USE_PGXS=1 $ su # make USE_PGXS=1 install $ psql db1 db1=# CREATE EXTENSION pg_bulkload; CREATE EXTENSION
◆ 使用方法
pg_bulkload では提供されるOSコマンドを実行して、テーブルにデータの読み込みを行います。対象テーブルは空データである必要はなく、既にレコードを持つテーブルにデータを追加するときにも使用できます。
対象テーブルやデータファイル、諸条件はコマンドラインオプションまたは制御ファイルで指定します。制御ファイルに記述する方がオプション指定よりも細かな設定を行うことができます。
コマンド書式は以下のようになります。
$ pg_bulkload [オプション] [制御ファイル]
制御ファイルを使う場合には、コマンドラインオプションに指定すべきは以下表に示す接続オプションのみです。これらは psql などの PostgreSQL クライアントツールと同様の記述ができます。指定を省略した場合には環境変数が使われたり、OS ユーザ名と同じユーザ名が使われる等も同様の振る舞いです。
| 接続オプション | 説明 |
|---|---|
| -d DBNAME –dbname=DBNAME |
接続するデータベース名を指定します。 |
| -h HOSTNAME –host=HOSTNAME |
サーバが稼働しているマシンのホスト名を指定します。 |
| -p PORT –port=PORT |
サーバが接続を監視する TCP ポートもしくは Unix ドメインソケットファイルを指定します。 |
| -U USERNAME –username=USERNAME |
接続するユーザ名を指定します。 |
| -W –password |
データベースに接続する前に、強制的にパスワード入力を促します。 |
制御ファイルの例を示します。以下は CSV 形式のファイル (t_bulkload.dat) から、テーブル t_bulkload にデータの読み込みを行います。 行の # (シャープ) 以降はコメントとして扱われます。
# # Control file to load CSV # OUTPUT = t_bulkload INPUT = /var/lib/pgsql/t_bulkload.dat TYPE = CSV DELIMITER = ","
以下表は制御ファイルの指定項目の説明です。TYPE と DELIMITER に関しては、記載がない場合でもデフォルトで CSV と ” , (カンマ)” が選択されます。
| 項目 | 説明 |
|---|---|
| OUTPUT | ロード先のテーブルを指定します。 table_name |
| INPUT | 読み込むファイルのパスを記載します。相対パスでファイルを指定した場合、この制御ファイルがある場所がカレントになります。 |
| TYPE | 入力データのタイプを以下のいずれかで指定します。
|
| DELIMITER | TYPE で CSV を指定した場合のデリミタを指定します。デフォルトはカンマです。 |
CSV を TYPE として選択した場合、以下の項目も設定できます。
| 項目 | 説明 |
|---|---|
| QUOTE = quote_character | 引用符を指定します。デフォルトは「”」です。 |
| ESCAPE = escape_character | エスケープ文字を指定します。デフォルトは「”」です。 |
| NULL = null_string | NULL 値を表す文字列を指定します。デフォルトは空文字列です。 |
| FORCE_NOT_NULL = column | 入力ファイル中の表現が NULL 値文字列であっても NULL として扱わないカラムを 1 行 1 カラムで指定することができます。 |
TYPE で BINARY を選んだ際の設定項目も各種あります。こちらについては pg_bulkload のマニュアルドキュメントを参照ください。
◆ COPY との比較
pg_bulkload がどれくらい高速でロードを行うことができるのか COPY を使った場合と比べてみます。今回テストでは PostgreSQL 18.3 を使用して、以下のように 500万件のデータを持つテーブルを作成して、ここから 500万件の行をもつ CSVファイルを作成しました。主キーの整数と1つの文字列とにインデックスを持ちます。これをロードします。いずれも、最初にテーブルを空にしてからロードするものとします。
db1=# CREATE TABLE t_bulkload (id int PRIMARY KEY, c1 text, c2 text); db1=# INSERT INTO t_bulkload SELECT g, md5(g::text), md5((g*2)::text) FROM generate_series(1, 5000000) g; db1=# CREATE INDEX ON t_bulkload USING btree (c1); db1=# COPY t_bulkload TO '/var/lib/pgsql/t_bulkload.dat' WITH (FORMAT CSV) COPY 5000000 db1=# TRUNCATE t_bulkload ;
まず COPY でロードした場合を見てます。
$ time psql -d db1 -c "COPY t_bulkload FROM '/var/lib/pgsql/t_bulkload.dat' WITH (FORMAT CSV)" COPY 5000000 real 0m41.886s user 0m0.129s sys 0m0.346s
次に pg_bulkload を単純にファイルとテーブルだけ指定して実行した場合の結果が以下です。COPY を使用した場合、40秒ほどかかっていましたが、pg_bulkload では 6.4秒ほどでロードすることができました。速度は 6～7倍といったところです。
$ time pg_bulkload -d db1 ./t_bulkload.ctl
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
0 Rows skipped.
5000000 Rows successfully loaded.
0 Rows not loaded due to parse errors.
0 Rows not loaded due to duplicate errors.
0 Rows replaced with new rows.
real 0m6.367s
user 0m0.000s
sys 0m0.011s
COPYを使った場合でも、インデックスや主キー制約を除去してから実行すると、もう少し高速にロードが可能です。データロードに 3.6秒、インデックス再作成に 1.2秒、2.7秒で、合計 7.5秒という結果でした。それでも pg_bulkload には及びません。
$ time psql -d db1 -c "COPY t_bulkload FROM '/var/lib/pgsql/t_bulkload.dat' WITH (FORMAT CSV)" COPY 5000000 real 0m3.600s user 0m0.104s sys 0m0.255s $ time psql -d db1 -c 'ALTER TABLE t_bulkload ADD PRIMARY KEY (id)' ALTER TABLE real 0m1.172s user 0m0.003s sys 0m0.005s $ time psql -d db1 -c 'CREATE INDEX ON t_bulkload USING btree (c1)' CREATE INDEX real 0m2.702s user 0m0.003s sys 0m0.013s
◆ 並列処理
pg_bulkload は入力ファイルの読み込みとテーブルへのデータ書き込みを別スレッドで並列に実行することができ、マシンリソースに余裕があれば、さらに高速なロードが可能になります。
並列処理をさせるための制御ファイルの書き方が二つあります。
1つは、以下のように WRITER を設定することです。
WRITER = PARALLEL
もうひとつは、以下のように MULTI_PROCESS を設定することです。
MULTI_PROCESS = YES
1 つ目の方法を制御ファイルに記載している場合 MULTI_PROCESS は記載があったとしても無視されます。MULTI_PROCESS を有効にするには WRITER は DIRECT が設定されている必要があります。DIRECT は WRITER のデフォルト値なので、明示的に記載する必要はありません。
なお、1つのテーブルへの投入データを行数範囲で分割して並列に読み込み・書き込みを行う、といった並列処理を行う機能はありません。
また、異なるテーブルに対する pg_bulkload コマンドを並列に実行することは可能です。
◆ 制約の検査
pg_bulkload はデフォルトではロード先テーブルの制約について検査を行いません。したがって、制約違反になるかもしれないデータを投入する場合には安全ではありません。
制約の検査をするには制御ファイルで以下を設定します。
CHECK_CONSTRAINTS = YES
投入途中で制約違反が検出された場合、デフォルトでは直ちに処理が中断されます。たとえ制約違反やその他エラーになっても、そのレコードだけスキップしてロード継続を行う設定方法も用意されています。
PARSE_ERRORS = n DUPLICATE_ERRORS = n
n には何件のエラーまでスキップして処理継続するかを記載します。デフォルトは 0 です。
設定した件数以上のエラーが出た場合の動作がエラー種別によって異なる点は注意が必要です。投入元レコードの構文解析エラー（PARSE_ERRORS）の場合は、その時点でコミットが行われそれ以降のデータはロードされません。キー重複エラー（DUPLICATE_ERRORS）の場合は、その時点でロールバックされてロード処理全体がなかったことになります。
スキップされた投入元レコードは以下項目で設定されたファイルに書き出されます。デフォルトでは $PGDATA/pg_bulkload/ 以下に、DB名・スキーマ名・テーブル名と、構文エラーなら「.prs.」、キー重複なら「.dup.」を含む名前のファイルが作られます。
PARSE_BADFILE = /path/of/bad/file DUPLICATE_BADFILE = /path/of/duplicate/file
なお、制約の検査はWRITER=BINARY と設定している場合には利用できません。
3. 注意事項
◆ クラッシュ時
pg_bulkload の実行途中で PostgreSQLがクラッシュ終了したり、pg_bulkload実行中にサーバダウンが生じた場合、データベースクラスタディレクトリ内に残骸データが残ってしまいます。このことは、$PGDATA/pg_bulkload に .loadstatusファイル（16384.16524.loadstatus など）が残っているかで判別できます。
pg_bulkload には -r オプションがあり、これで不正な状態の解消（リカバリと呼びます）ができるのですが実行すべきタイミングは pg_ctl start をする前です。この時の推奨方法として postgresql スクリプトが用意されています。中身は pg_bulkload -r を実行し、pg_ctl start を行っているだけですが、起動前に pg_bulkload のリカバリをし忘れない目的で利用できます。
◆ PITR / レプリケーション
pg_bulkload はデータ投入を行ったことに対応した WAL を出力しません。
したがって、PITR 方式のオンラインバックアップを使っている場合には、pg_bulkload でのデータロードが完了した後にベースバックアップを再度取得する必要があります。同様に、ストリーミングレプリケーションを使っている場合には、pg_bulkload の完了後にスタンバイサーバを作り直す必要があります。
◆ マージされたインデックス
pg_bulkloadコマンドはテーブル行のロードが終わると、通常はそのテーブルインデックス再作成を行ないます。しかしながら、btreeインデックスに限って、追加したデータに対応するエントリだけを加えるマージの仕組みがあります。これにより、既存行がある場合に効率的な動作が可能になります。マージが行なわれたかどうかは debug1 レベルのログメッセージとして以下のように報告されます。
DEBUG: pg_bulkload: build "t_bulkload_pkey" with merge (without wal) DEBUG: pg_bulkload: build "t_bulkload_c1_idx" with merge (without wal)
マージが行なわれたインデックスは正しく動作して問題なく使用できるのですが、PostgreSQL自体によって作られたインデックスとは作り方に違いがあるため、本稿執筆時点のバージョンでは、amcheck を使ってインデックスを検査した場合に「ERROR: item order invariant violated for index …」などとエラー報告される場合があります。
4. まとめ
pg_bulkload はデータを高速にロードすることに特化したツールです。PITR やレプリケーションで運用上の制約があるものの、サーバ移行の際に、元のサーバからすべてのデータを移動させるときに有用です。また定期的に溜まったデータをテーブルに流し込む際や、テスト用の大量のデータを少しでも早く読み込ませたいときなどにもその力を発揮してくれるはずです。
本文書では触れませんでしたが、pg_bulkload には作成した任意のフィルタプログラムを指定して、データを変換しながら読み込む機能もあります。pg_bulkload のその他の機能・設定オプションについてはソフトウェア付属ドキュメントに詳細に記載されています。
pg_bulkloadドキュメント：http://ossc-db.github.io/pg_bulkload/pg_bulkload-ja.html