pg_bulkloadの性能測定（PostgreSQL12）

# pg_bulkload

pg_bulkloadは大量データの高速ロードするツールです。
大量データをロードするには他にCOPYコマンドを使用する方法もありますが、pg_bulkloadを使えばCOPYコマンドよりも高速にデータをロードすることができます。

pg_bulkloadは、詳細なドキュメントが以下に準備されており、特にここで追加して説明する必要はないかなと思います。
今回はpg_bulkloadをインストールして、COPYコマンドとの性能比較を実施したいと思います。
COPYコマンドとの性能比較は以下のページにも結果が記載されていますが、PostgreSQL9.6で実行されているので、ここではPostgreSQL12で実行しています。

- http://ossc-db.github.io/pg_bulkload/index_ja.html


# 環境

以下の環境で試しています。

- CentOS 7.5(CPU 2core, MEM 8GB)
- PostgreSQL 12
- pg_bulkload 3.1.16


# pg_bulkloadのインストール

今回はCentOSなので、以下からRPMファイル（pg_bulkload-3.1.16-1.pg12.rhel7.x86_64.rpm）をダウンロードしインストールします。

https://github.com/ossc-db/pg_bulkload/releases

JITを有効にしてPostgreSQL 11、12を使用している場合は、pg_bulkload-llvmjitも一緒にインストールするといいらしいのですが、今回は使用していません。

rpmコマンドでインストールします。

```
# rpm -ivh pg_bulkload-3.1.16-1.pg12.rhel7.x86_64.rpm
```

データベースに接続して、CREATE EXTENSIONコマンドで拡張をインストールします。

```
# CREATE EXTENSION pg_bulkload;
```


# テストデータの作成

インポート用のCSVファイルは一旦テーブルにデータを作成し、COPYコマンドで作成しました。
DBT-2のcustomerテーブルを利用しています。

最初にcustomerテーブルを作成し、2000万件のデータを作成します。

```sql
-- drop table customer;
create table customer (
    c_id INTEGER,
    c_d_id INTEGER,
    c_w_id INTEGER,
    c_first VARCHAR(16),
    c_middle char(2),
    c_last VARCHAR(16),
    c_street_1 VARCHAR(20),
    c_street_2 VARCHAR(20),
    c_city VARCHAR(20),
    c_state char(2),
    c_zip char(9),
    c_phone char(16),
    c_since TIMESTAMP,
    c_credit char(2),
    c_credit_lim NUMERIC(24, 12),
    c_discount REAL,
    c_balance NUMERIC(24, 12),
    c_ytd_payment NUMERIC(24, 12),
    c_payment_cnt REAL,
    c_delivery_cnt REAL,
    c_data VARCHAR(500));

insert into customer(c_id, c_d_id, c_w_id, c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, c_data)
select
  i,
  i,
  i,
  'first name',
  'mr',
  'last name',
  'street name 1',
  'street name 2',
  'city name 1',
  '99',
  '999999999',
  '9999999999999999',
  clock_timestamp(),
  '99', -- credit
  123456,
  123456,
  123456,
  123456,
  123456,
  123456,
  'test data 1234567890123456789012345678901234567890'
from
  generate_series(1, 20000000) as i -- 2000万レコード
;

-- drop index i_customer;
-- alter table customer drop constraint pk_customer;

alter table customer
  add constraint pk_customer
  primary key(c_w_id, c_d_id, c_id);
create index i_customer
  on customer(c_w_id, c_d_id, c_last, c_first, c_id);
```

データ投入後、pg_total_relation_size関数でテーブル＋インデックスのサイズをはかると6,455MBでした。

```sql
select pg_total_relation_size('customer');
 pg_total_relation_size 
------------------------
             6769287168
(1 row)
```

最後にCOPYコマンドでCSVファイルに出力します。出力されたCSVファイルは5,363MBでした。

```
postgres=# COPY customer TO '/var/lib/pgsql/customer.csv' WITH CSV DELIMITER ',';
COPY 20000000
```


# 計測方法

計測はcustomerテーブルに2000万行のCSVファイルをロードして実施します。
以下の4ケースで計測しています。

- COPYコマンドでロード（インデックスあり）
- COPYコマンドでロード（インデックスは後から作成）
- pg_bulkloadでロード（ノンパラレル）
- pg_bulkloadでロード（パラレル）


## COPYコマンドの実行方法

COPYコマンドの実行は、実行時間の出力のため以下の無名関数で実行しています。

```sql
DO $$
DECLARE
  ttime timestamp;
BEGIN
  select clock_timestamp() into ttime;
  RAISE NOTICE '%', ttime;
  COPY customer_imp FROM '/var/lib/pgsql/customer.csv' WITH CSV DELIMITER ',';
  select clock_timestamp() into ttime;
  RAISE NOTICE '%', ttime;
END
$$
;
```

実行の出力結果は以下のようになります。

```
NOTICE:  2020-03-21 03:30:26.20532
NOTICE:  2020-03-21 03:33:33.425405
DO
```

インデックスの作成時間は以下のように計測しました。

```sql
DO $$
DECLARE
  ttime timestamp;
BEGIN
  select clock_timestamp() into ttime;
  RAISE NOTICE '%', ttime;

  alter table customer_imp
  add constraint pk_customer
  primary key (c_w_id, c_d_id, c_id);

  create index i_customer
  on customer_imp (c_w_id, c_d_id, c_last, c_first, c_id);

  select clock_timestamp() into ttime;
  RAISE NOTICE '%', ttime;
END
$$
;
```


## pg_bulkloadの実行方法

pg_bulkloadコマンドを実行するには、ロードの設定等について記載された制御ファイルを準備します。
以下が制御ファイルの例です。各行のコメントの":"以降はデフォルト値を示しています。

```
OUTPUT = customer_imp    # 出力先のテーブル
TYPE = CSV               # 入力データのタイプ: CSV
INPUT = /var/lib/pgsql/customer.csv     # 入力データソース
LOADER = DIRECT          # ロード方式: DIRECT
SKIP = 0                 # 先頭からスキップする行数を指定: 0
LIMIT = INFINITE                                # ロード行数：INFINITE（全件）
CHECK_CONSTRAINTS = NO                          # ロード時に CHECK 制約を検査するかどうかを指定: NO
PARSE_ERRORS = 0                                # パース処理等の許容件数を指定: 0
                                                # エラーの件数がこの値を超えた場合は、その時点でコミットして残りの入力データのロードは行わない。-1だと無制限。
DUPLICATE_ERRORS = 0                            # 一意制約違反の許容件数を指定: 0
                                                # エラーの件数がこの値を超えた場合は、ロールバックする。-1だと無制限。
ON_DUPLICATE_KEEP = NEW                         # 一意制約違反のレコードが存在した場合の挙動。: NEW
                                                # NEWだと新しくロードしたデータ、OLDは元々のデータを残す。
LOGFILE = ./pg_bulkload.log                  # ログファイルのパス
PARSE_BADFILE = ./PARSE_BADFILE.csv          # BADファイルのパスを指定
DUPLICATE_BADFILE = ./DUPLICATE_BADFILE.csv  # 一意制約違反の不良レコードを記録するBADファイルのパスを指定
TRUNCATE = NO                                   # YESの場合はデータロード前にテーブルから全ての行を削除: NO
MULTI_PROCESS = NO                              # YESの場合は並行処理を行う: NO
DELIMITER = ","                                 # デリミタを指定: ,
QUOTE = "\""                                    # 引用符を指定: "
ESCAPE = \                                      # エスケープ文字を指定: "
```

なお、制約については公式ドキュメントに以下の記述があります。

>デフォルトでは、ロード時のデータの整合性は、一意制約と非NULL制約のみ適用します。 CHECK 制約を適用する場合は "CHECK_CONSTRAINTS=YES" を指定してください。 外部キー制約は適用しません。

制御ファイルを引数に指定してpg_bulkloadコマンドを実行します。
ログファイルはデフォルトで$PGDATA/pg_bulkloadに保存されます。

```
$ pg_bulkload -d postgres -h 127.0.0.1 -U postgres sample_csv.ctl 
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        20000000 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.
```

実行時間は以下のようにログへ出力されます。

```
$ cat pg_bulkload.log
  0 Rows skipped.
  20000000 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.

Run began on 2020-03-21 03:36:05.284753+01
Run ended on 2020-03-21 03:38:20.920093+01

CPU 5.41s/125.25u sec elapsed 135.64 sec　★この数値を記録する
```


# 計測結果

計測結果は以下のようになりました。

| ケース                                           | 実行時間                        |
|--------------------------------------------------|---------------------------------|
| COPYコマンドでロード（インデックスあり）         | 405秒                           |
| COPYコマンドでロード（インデックスは後から作成） | 259秒（インデックス作成72秒を含む） |
| pg_bulkloadでロード（ノンパラレル）              | 215秒                           |
| pg_bulkloadでロード（パラレル）                  | 191秒                           |

COPYコマンドでロードする場合、インデックスは後から作成した方が高速です。
pg_bulkloadはそれよりも高速でパラレル実行すると更に高速になります。
pg_bulkloadはノンパラレルとパラレルであまり時間が変わりませんでしたが、コア数が多ければ（今回は2コア）もっと差がついたかもしれません。


pg_bulkloadはmaintenance_work_memパラメータにも影響を受けるということで、maintenance_work_memを変更した場合も計測しました。
maintenance_work_memをデフォルトの64MBから1GB変更した場合、pg_bulkloadでロード（ノンパラレル）のケースで215秒から193秒と、10%程度速くなりました。


# 参考

- [pg_bulkload ホームページ](http://ossc-db.github.io/pg_bulkload/index_ja.html)
- [大量のデータを高速に投入するには(
Let's Postgres
)](https://lets.postgresql.jp/documents/technical/bulkload/1)
- [pg_bulkload (データの高速読み込みツール)(SRA OSS Tech Blog)](https://www.sraoss.co.jp/tech-blog/pgsql/pg_bulkload/)