Posted inデータベース

PostgreSQLのバルクインポート：数百万行のデータを待ち時間なしで移行する方法

Database tutorial - IT technology blog
Database tutorial - IT technology blog

ボトルネック：標準的なINSERTが大規模環境で失敗する理由

以前、Pythonスクリプトがレガシーデータベースのマイグレーションを延々と進めるのを、午後いっぱい眺めていたことがあります。1行ごとに1つの INSERT 文を実行する基本的なループを書いていたのです。30分後、件数を確認してみると、処理済みは5万件、残りは995万件でした。

そのペースでは、マイグレーションに4日近くかかる計算になります。標準的な INSERT 文は単一のトランザクションには最適ですが、バルク（一括）ロードには驚くほど不向きです。ステートメントごとに、データベースはSQLのパース、実行計画の作成、制約のチェック、そして先行書き込みログ（WAL）への書き込みを強制されるからです。

数百万行を扱う場合、このオーバーヘッドは文字通り「壁」となります。高速化するためには、個別のトランザクションからバッチ操作へと切り替える必要があります。この記事の目標は、ハードウェアのスループットを限界まで引き出し、数日ではなく数分でデータを移動させる方法を紹介することです。

決定版：COPYコマンドの活用

CSVやTSVのようなフラットファイルの場合、PostgreSQLの武器の中で最も高速なツールは COPY コマンドです。これはサーバー側のコマンドであり、標準的なSQLパースや実行計画をバイパスします。各行を個別のイベントとして処理するのではなく、データを直接テーブルにストリームとして流し込みます。

psql CLIからCOPYを使用する

データファイルがすでにデータベースサーバー上にある場合は、psql コンソールから直接インポートを実行できます。以下のようになります。

COPY users(first_name, last_name, email) 
FROM '/var/lib/postgres/data/users.csv' 
DELIMITER ',' 
CSV HEADER;

ローカルファイルの場合は、少し異なるアプローチが必要です。ファイルが手元のPCにあり、それをリモートのRDSやクラウドインスタンスに転送する必要がある場合は、\copy メタコマンドを使用します。これはファイルをローカルで読み取り、ネットワーク経由でサーバーにストリームをパイプします。

psql -h remote-db-instance -U admin -d production_db -c "\copy users FROM 'local_users.csv' WITH (FORMAT csv, HEADER true)"

アプリケーションレベルのパフォーマンス：マルチ行インサート

常にきれいなCSVファイルが用意されているとは限りません。データがライブAPIから取得されたり、アプリケーションコードによって動的に生成されたりすることもあります。ループ内でレコードごとに1つの INSERT を送信するのは、パフォーマンスを著しく低下させます。代わりに、レコードをバッチにまとめるべきです。

1行のインサートに1ミリ秒かかるとしても、1,000行のマルチ行インサートは合計で10ミリ秒しかかからないかもしれません。構文を変えるだけで100倍の高速化が期待できるのです。

避けるべきパターン：

INSERT INTO orders (id, amount) VALUES (1, 100);
INSERT INTO orders (id, amount) VALUES (2, 200);

推奨されるパターン：

INSERT INTO orders (id, amount) VALUES 
(1, 100), 
(2, 200), 
(3, 300);

私の経験では、1,000から5,000レコードのバッチサイズが最適です。これはメモリ消費量とネットワーク効率のバランスが取れた数値です。Pythonを使用している場合、psycopg3 ライブラリには execute_batch() よりも大幅に高速な copy() メソッドが用意されています。

インポート前のデータフォーマット処理

インポートが失敗する最も一般的な原因は、整理されていないデータです。エンコーディングが特殊だったり、カラムに直接マッピングできないネスト構造のファイルを受け取ることがよくあります。本格的な自動化パイプラインを構築する前に、私は toolcraft.app/ja/tools/data/csv-to-json を使ってデータ構造を素早く確認しています。これは完全にブラウザ上で動作するため、機密性の高いレコードを外部のサーバーにアップロードすることなくデータをチェックできます。

高度な最適化：データベースエンジンのチューニング

たとえ COPY コマンドであっても、データベースが書き込み速度よりも読み取りの安全性に重きを置いた設定になっていると、限界に達することがあります。数億行をインポートする際は、一時的に制限を緩める必要があります。

1. インデックスと制約の削除

インデックスは取り込み速度の敵です。行を挿入するたびに、PostgreSQLはそのテーブルに関連付けられたすべてのB-treeやハッシュインデックスを更新しなければなりません。1億行のインポートの場合、インデックスを一度削除し、インポートを実行してから、ゼロから再作成する方が10倍速いことがよくあります。

-- 最初にインデックスを削除
DROP INDEX idx_user_email;

-- COPYコマンドを実行
COPY users FROM 'massive_data.csv' CSV;

-- インデックスを再構築（PostgreSQLは一括処理を非常に効率的に行います）
CREATE INDEX idx_user_email ON users(email);

2. 先行書き込みログ（WAL）のチューニング

PostgreSQLは、データが確実にディスクに保存されるようチェックポイントを使用します。大規模なロード中、これらのチェックポイントが頻繁に発生しすぎて「チェックポイントスパイク」を引き起こし、インポートが停滞することがあります。max_wal_size を増やすことで、データベースに余裕を持たせることができます。

-- チェックポイントの頻度を減らすためにWALサイズを4GBに増やす
SET max_wal_size = '4GB';
SET checkpoint_timeout = '20min';

3. UNLOGGEDテーブルの使用

一時的なステージングテーブルにデータをインポートする場合は、UNLOGGED キーワードを使用してください。これらのテーブルはWALに書き込みを行いません。そのため非常に高速ですが、データベースがクラッシュするとデータは残りません。簡単に再作成できるステージングデータであれば、これは非常に大きなメリットになります。

CREATE UNLOGGED TABLE temp_import_staging (
    id SERIAL PRIMARY KEY,
    payload JSONB
);

高速インポートのチェックリスト

最良の結果を得るために、次回のビッグデータ移行では以下のワークフローに従ってください。

  • オートコミットを無効にする： マルチ行インサートは必ず1つのトランザクションブロックで囲みます。
  • 不要なインデックスを削除する： データが入った後にのみ再構築します。
  • maintenance_work_memを増やす： インデックスの再作成を速めるために、1GB以上に設定します。
  • COPYを使用する： 速度面で圧倒的な王者です。
  • ANALYZEを実行する： インポートが完了したら、ANALYZE table_name を実行します。これにより、クエリプランナが新しいデータの分布を正しく把握できるようになります。

行ごとの処理ロジッジからストリーミングの考え方に切り替えることで、インポート時間を6時間から15分未満に短縮した例を見てきました。PostgreSQLは膨大なスループットを処理できるように設計されています。あとは、その邪魔をしないようにするだけです。

Share:
Tags: