필사 모드: 大規模データ処理完全ガイド:COPY、チャンクバッチ、そして元に戻せる作業

• はじめに
• 1. 大量処理が失敗する3つの方式
• 2. 投入 — COPYとINSERTの違い
• 3. 初期ロード手順
• 4. 不良行を許容する投入
• 5. 大量UPDATEとDELETEをチャンクに分ける
• 6. 削除後の後片付け
• 7. バックフィル作業を安全に設計する
• 8. 元に戻せるようにする
• クイズ:理解度を確認しましょう
• まとめ
• 参考資料
• 続けて読む

はじめに

大規模データ作業は、開発環境ではうまく動いていたスクリプトが本番でサービスを止める典型的な領域です。100万行で30秒かかった作業は、3億行でも単純に300倍の時間がかかるわけではありません。ある地点を境に、動作の仕組みそのものが変わります。WALが急増してチェックポイントが押し寄せ、デッドタプルが積み上がってテーブルが膨張し、1つのトランザクションが長すぎてVACUUMが止まり、レプリケーション遅延が広がります。

この記事はその地点を順番に扱います。投入、更新、削除、バックフィル。それぞれの作業で何がボトルネックになり、PostgreSQLのドキュメントがどんな手順を推奨し、失敗したときにどう戻すか。

基準となるエンジンは PostgreSQL 18 であり、引用したオプションと既定値はすべてPostgreSQL 18のドキュメントで確認しています。

1. 大量処理が失敗する3つの方式

まず失敗の類型を押さえておきましょう。対応戦略はここで分かれます。

第一に、1つのトランザクションが大きすぎる。3億行を1つのトランザクションで更新すると、そのトランザクションは何時間も開いたままになります。そのあいだデータベース全体でデッドタプルの回収が妨げられ、ロールバックが発生すればその何時間分がまるごと無駄になります。途中で失敗すれば、最初からやり直すしかありません。

第二に、資源消費が瞬間的に爆発する。大量書き込みはWALを大量に生み、WALが max_wal_size(既定値1GB)を超えるたびにチェックポイントが発生します。チェックポイントが押し寄せると、ダーティページをディスクへ押し出す作業でI/Oが飽和し、サービスクエリの遅延が跳ね上がります。

第三に、後片付けが残る。大量削除や更新はその分だけデッドタプルを生みます。削除自体は終わっても、テーブルサイズはそのままで、シーケンシャルスキャンは相変わらず以前と同じだけのページを読みます。作業が「終わった」と報告された時点と、システムが正常に戻った時点のあいだには数日の隔たりが生まれます。

この3つに対する対応が、それぞれチャンク分割、資源パラメータの調整と速度制限、VACUUM計画です。

2. 投入 — COPYとINSERTの違い

PostgreSQLドキュメントの「Populating a Database」章には、大量投入の標準手順が記されています。最初の2項目が最も重要です。

自動コミットを切り、1つのトランザクションにまとめる。ドキュメントの説明どおり、PostgreSQLは個別にコミットされる行ごとにかなりの作業を行うため、自動コミットを切ればそのオーバーヘッドが減ります。

COPY を使う。ドキュメントの表現を借りれば、COPY は大量投入に最適化されており、複数の INSERT より「はるかに少ないオーバーヘッド」で済みます。単一の COPY コマンドを使う場合、自動コミットを切る必要すらありません。COPY が使えない場合は、PREPARE で準備した INSERT を作り EXECUTE を繰り返すことで、パースと計画作成のオーバーヘッドを避けるよう勧めています。

COPY の主なオプションと、ドキュメント基準の既定値です。

# 파일에서 서버로: psql의 \copy는 클라이언트 파일을 읽는다
psql -d appdb -c "\copy orders_staging FROM 'orders.csv' WITH (FORMAT csv, HEADER true)"

-- サーバーのファイルシステムから直接読み込む(サーバー権限が必要)
COPY orders_staging FROM '/var/lib/pgsql/import/orders.csv'
  WITH (FORMAT csv, HEADER true);

-- プログラムの出力から読み込む
COPY orders_staging FROM PROGRAM 'zcat /var/lib/pgsql/import/orders.csv.gz'
  WITH (FORMAT csv, HEADER true);

HEADER には MATCH を指定できます。この場合ヘッダー名がテーブルの列名と厳密に一致している必要があるため、列の並び順が入れ替わったCSVを静かに誤って取り込んでしまう事故を防げます。外部から受け取るファイルには常に HEADER MATCH を使うことを勧めます。

3. 初期ロード手順

空のテーブルや新しいテーブルに大量投入するとき、ドキュメントが推奨する順序です。

ステップ1 — インデックスは後から作る。ドキュメントの表現では、「テーブルを作り、COPY で大量投入したあと、インデックスを作成する。既存データにインデックスを作るほうが、行ごとに段階的に更新するより速い。」すでにデータがあるテーブルに大量追加する場合も、インデックスを削除して投入し、あとで作り直すほうが速いことがありますが、そのあいだ他のユーザーの性能が悪化するという注意書きが付きます。

ステップ2 — 外部キー制約も後から作る。ドキュメントは、これが単なる最適化ではない場合があると警告しています。外部キーは「行ごとに検査するより一括で検査するほうが効率的」であり、数百万行を投入するときは「トリガーイベントのキューが使用可能なメモリを超え、耐えられないスワッピングや完全な失敗を引き起こしかねない」としています。

ステップ3 — maintenance_work_mem を一時的に上げる。既定値は64MBです。ドキュメントによれば、この値は VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY といった保守作業が使うもので、大量投入の最中に一時的に上げるとインデックス作成と外部キー追加が速くなります。COPY 自体は速くなりません。

ステップ4 — max_wal_size を一時的に上げる。既定値は1GBです。ドキュメントの説明どおり、大量投入は普段よりチェックポイントを頻繁に発生させるため、この値を上げると必要なチェックポイントの回数が減ります。

ステップ5 — 終わったら ANALYZE を実行する。ドキュメントは「大量投入のあとは ANALYZE(または VACUUM ANALYZE)を実行し、プランナーが最新の統計情報を持つようにせよ」としており、統計情報がないとプランナーが悪い計画を立てかねないと警告しています。autovacuumが有効なら自動で走ることもありますが、投入直後すぐにクエリを受ける必要があるなら、自分で実行するほうが確実です。

-- 初期ロードのテンプレート
SET maintenance_work_mem = '2GB';   -- セッション限定

BEGIN;
CREATE TABLE orders_new (LIKE orders INCLUDING DEFAULTS);
COPY orders_new FROM '/import/orders.csv' WITH (FORMAT csv, HEADER match);
COMMIT;

CREATE INDEX idx_orders_new_tenant ON orders_new (tenant_id, created_at DESC);
ALTER TABLE orders_new ADD PRIMARY KEY (id);
ANALYZE orders_new;

RESET maintenance_work_mem;

警告: ドキュメントは wal_level を minimal に、archive_mode を off に、max_wal_senders を0にして、WALアーカイブとストリーミングレプリケーションを止める方法も紹介しています。しかし同じドキュメントが注意書きを付けています。この変更はサーバーの再起動が必要で、それまでのベースバックアップをアーカイブリカバリやスタンバイサーバーに使えなくし、データ損失につながりかねません。本番データベースではこの項目を飛ばしてください。これは構築中の新しいクラスタにのみ当てはまる助言です。

COPY FREEZE も初期投入専用のオプションです。ドキュメントによれば、現在のサブトランザクションでテーブルが作成または切り詰められている必要があり、開いたカーソルがあってはならず、トランザクションがそれより古いスナップショットを保持していてはならず、パーティションテーブルや外部テーブルには使えません。そしてドキュメントが明記するとおり、投入が成功した瞬間、他のすべてのセッションがそのデータを見えるようになり、通常のMVCC可視性ルールに反します。

4. 不良行を許容する投入

外部から受け取ったファイルには必ず壊れた行があります。既定の動作は最初のエラーで全体が失敗することです。ON_ERROR の既定値が stop だからです。

ignore に変えると、エラーになった行を捨てて続行します。ドキュメントによれば、この値は COPY FROM で text または csv 形式のときにのみ適用されます。

COPY orders_staging FROM '/import/orders.csv'
  WITH (FORMAT csv, HEADER match,
        ON_ERROR ignore,
        LOG_VERBOSITY verbose,
        REJECT_LIMIT 1000);

LOG_VERBOSITY は default、verbose、silent のいずれかで、ON_ERROR が ignore のときどの程度ログを残すかを決めます。REJECT_LIMIT は許容する最大エラー数で、ドキュメントによれば ON_ERROR=ignore と一緒に使う必要があり、正の整数でなければなりません。この句を省略すると、エラー数に制限がなくなり、不良データをすべて静かにスキップしてしまいます。

REJECT_LIMIT を必ず指定してください。制限がないと、ファイルのエンコーディングがまるごと間違っている場合でも「投入成功、0行」という結果を受け取ることになります。

より安全なパターンは、すべての列を text で受け取るステージングテーブルを用意することです。

CREATE UNLOGGED TABLE orders_raw (
  id_txt text, tenant_txt text, amount_txt text, created_txt text
);

COPY orders_raw FROM '/import/orders.csv' WITH (FORMAT csv, HEADER match);

-- 検証と変換をSQLで行い、不良行は残して調査する
INSERT INTO orders (id, tenant_id, total_amount, created_at)
SELECT id_txt::bigint, tenant_txt::bigint,
       amount_txt::numeric, created_txt::timestamptz
FROM orders_raw
WHERE id_txt ~ '^[0-9]+$'
  AND amount_txt ~ '^[0-9]+(\.[0-9]+)?$';

UNLOGGED テーブルはWALをほとんど使わないため、ステージングに適しています。ただし、クラッシュ時には内容が消え、レプリカにも伝播しません。捨てて作り直せるデータにのみ使ってください。

5. 大量UPDATEとDELETEをチャンクに分ける

3億行を1文で更新してはいけない理由は1節で見たとおりです。チャンクに分けるのが標準的な対応です。

チャンク分割の設計には4つの要件があります。

要件1 — 各チャンクは独立したトランザクションでなければなりません。そうすれば、途中で失敗してもすでに処理した部分は残ります。

要件2 — 再開可能でなければなりません。どこまで処理したかを記録しておき、再開したらその地点から続けます。

要件3 — 進捗が分かる必要があります。何時間もかかる作業で「あとどれくらい残っているか」に答えられなければ、運用は成り立ちません。

要件4 — 速度を調整できる必要があります。サービスの遅延が跳ねたら、チャンク間の待機時間を延ばして負荷を下げられる必要があります。

キー範囲で分ける方式が最も堅牢です。OFFSET を使うと後ろへ行くほど遅くなるので、使わないでください。

-- 進捗状態をテーブルに残す
CREATE TABLE backfill_progress (
  job_name   text PRIMARY KEY,
  last_id    bigint NOT NULL DEFAULT 0,
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO backfill_progress (job_name) VALUES ('orders_channel_backfill')
ON CONFLICT DO NOTHING;

-- 1チャンク分: アプリケーションやスケジューラがこのブロックを繰り返し呼び出す
BEGIN;
SET LOCAL statement_timeout = '60s';
SET LOCAL lock_timeout = '3s';

WITH bounds AS (
  SELECT last_id FROM backfill_progress
  WHERE job_name = 'orders_channel_backfill'
  FOR UPDATE
),
target AS (
  SELECT o.id
  FROM orders o, bounds b
  WHERE o.id > b.last_id
    AND o.channel IS NULL
  ORDER BY o.id
  LIMIT 5000
),
updated AS (
  UPDATE orders o
     SET channel = 'WEB'
   WHERE o.id IN (SELECT id FROM target)
  RETURNING o.id
)
UPDATE backfill_progress
   SET last_id = COALESCE((SELECT max(id) FROM updated), last_id),
       updated_at = now()
 WHERE job_name = 'orders_channel_backfill'
RETURNING last_id;

COMMIT;

チャンクサイズを決める基準は、1つのチャンクが1秒以内に終わるかどうかです。それより長いとロック保持時間が伸びてサービスに影響します。実測して調整してください。SET LOCAL lock_timeout を設定しておくことも重要です。ロック待ちでチャンクが長くかかると、それ自体がサービスの遅延になります。

大量削除も同じ構造です。ただし削除にはより良い選択肢があることが多くあります。パーティションテーブルであれば、DROP TABLE や DETACH PARTITION のほうが圧倒的に優れています。そしてテーブルの大部分を削除するのであれば、残す行だけを新しいテーブルにコピーして名前を付け替えるほうが速くなります。

6. 削除後の後片付け

DELETE は行を消しません。死んだと印を付けるだけです。空間の回収はVACUUMの仕事です。

autovacuumがいつ動くかは、閾値の計算式が決めます。PostgreSQL 18のドキュメント基準で、autovacuum_vacuum_threshold の既定値は50タプル、autovacuum_vacuum_scale_factor の既定値は0.2、つまりテーブルの20%です。PostgreSQL 18には autovacuum_vacuum_max_threshold が追加され既定値は1億タプルで、この上限のおかげで非常に大きなテーブルでもvacuumが無期限に先送りされることはありません。

大量削除の直後は、自分で実行したほうがよいでしょう。

-- 並列でインデックスまで整理し、統計情報も更新する
VACUUM (ANALYZE, VERBOSE, PARALLEL 4) orders;

問題は、空間がOSに返らないことです。通常のVACUUMはデッドタプルが占めていた空間を再利用可能と印を付けるだけで、テーブルファイルのサイズはそのままです(テーブル末尾の空ページは例外的に返却されます)。

警告: 空間を実際に取り戻すには VACUUM FULL が必要ですが、このコマンドは対象テーブルに ACCESS EXCLUSIVE ロックをかけます。読み取りまですべてブロックされ、テーブル全体を新しく書き直すため、元のサイズ分の空きディスクが追加で必要になります。PostgreSQLドキュメント自体が「したがって一般に管理者は標準の VACUUM を使うよう努め、VACUUM FULL は避けるべきである」と明記しています。CLUSTER も同じロックを取得します。サービス中に必要であれば pg_repack のような外部ツールを検討してください。ただしツールの挙動と制約は、そのツール自身のドキュメントで確認してください。

実務的な結論はこうです。定期的に大量削除が発生するテーブルはパーティション化してください。パーティションをまるごと切り離せば、デッドタプルもVACUUMもbloatも発生しません。これがパーティショニングを導入する最も実用的な理由の1つです。

7. バックフィル作業を安全に設計する

バックフィルとは「すでにあるデータに新しいルールを適用する」作業です。新規列の埋め込み、誤って保存された値の修正、新しいインデックス用の正規化列の生成などがこれに含まれます。

設計原則は5つあります。

原則1 — 新規データから先に止める。バックフィルを始める前に、アプリケーションが新しいルールで書き込むように先にデプロイします。そうしないと、バックフィルが進んでいるあいだも新しいデータが古いルールで入り続け、終わりません。

原則2 — 冪等にする。同じチャンクを2回実行しても結果が同じでなければなりません。WHERE channel IS NULL のような条件を入れて、すでに処理済みの行に触れないようにすれば、自然に冪等になります。

原則3 — 負荷を観測しながら調整する。チャンク間の待機時間を設定として外に出しておき、レプリケーション遅延とサービス遅延を見ながらリアルタイムで調整できるようにします。特にレプリケーション遅延を監視してください。

-- スタンバイのレプリケーション遅延を確認(プライマリで実行)
SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

原則4 — 進捗と完了予定時刻を残す。backfill_progress テーブルに処理件数と時刻を記録すれば、残り時間を計算できます。

原則5 — 検証を作業の一部として含める。「終わった」ではなく「終わって、検証した」が完了条件です。

-- バックフィル完了の検証: 未処理行が0か
SELECT count(*) AS remaining FROM orders WHERE channel IS NULL;

-- 値の分布が想定どおりか
SELECT channel, count(*) FROM orders GROUP BY 1 ORDER BY 2 DESC;

8. 元に戻せるようにする

大量作業のロールバックは「逆方向の文」ではありません。すでにコミットされたチャンクは、トランザクションのロールバックでは戻りません。

実務で使われる安全網は3つです。

第一に、元の値を残す。値を上書きするバックフィルなら、古い値を別テーブルか新しい列に保管します。保存コストを払って、元に戻す能力を買うということです。作業が終わり検証まで済んだあとに片付けます。

-- 元に戻すためのスナップショット: 変更する行の古い値だけを保存
CREATE UNLOGGED TABLE orders_channel_backup AS
SELECT id, channel FROM orders WHERE channel IS NOT NULL;

第二に、ドライランを先に走らせる。実際には更新せず、影響を受ける行数とサンプルを確認します。EXPLAIN ANALYZE で確認する場合は、ドキュメントが勧めるとおりトランザクションで包んでロールバックしてください。

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET channel = 'WEB' WHERE channel IS NULL AND id BETWEEN 1 AND 5000;
ROLLBACK;

ドキュメントはこのパターンを明示的に案内しています。「ANALYZE オプションを使うと文が実際に実行される点を忘れないこと。EXPLAIN は SELECT が返すはずの出力は捨てるが、それ以外の副作用は通常どおり発生する。」

第三に、ステージングで同じ規模のリハーサルを行う。100万行で測った時間に300を掛けるのは、たいてい間違っています。インデックス更新のコストとキャッシュヒット率が規模に応じて非線形に変わるからです。少なくとも同じ桁数のデータで測定してください。

作業時間帯の選択も安全網の一部です。トラフィックが少ない時間帯は魅力的ですが、夜間は対応要員も少ないという点を併せて考える必要があります。元に戻せるように設計してあるなら、むしろ人が多い時間帯に行うほうがよい場合もあります。

クイズ:理解度を確認しましょう

CREATE TABLE archive_new (LIKE archive INCLUDING ALL);
INSERT INTO archive_new SELECT * FROM archive WHERE created_at >= '2026-01-01';
-- 短いロック区間で入れ替える
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE archive RENAME TO archive_old;
ALTER TABLE archive_new RENAME TO archive;
COMMIT;

SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

まとめ

大規模データ作業の原則は3つの文にまとめられます。一度にすべてやらない。元に戻せるようにする。終わったあとの後片付けまで作業に含める。

特に3つ目を強調します。削除が終わった時点とシステムが正常に戻った時点は違います。デッドタプルの回収、統計情報の更新、インデックスの整理までが作業の一部です。「DELETE 3時間、VACUUM 1日」と計画に書いておけば、あとで驚かずに済みます。

そして繰り返される大量削除があるなら、それはパーティショニングを検討すべきという合図です。パーティションを切り離す作業には、デッドタプルもVACUUMもありません。

CSV変換が必要なら CSV/JSON変換ツール を、大容量のテストデータが必要なら モックデータ生成ツール を活用してください。

参考資料

• PostgreSQL 18, Populating a Database: https://www.postgresql.org/docs/18/populate.html (2026-08-15 確認)
• PostgreSQL 18, COPY: https://www.postgresql.org/docs/18/sql-copy.html (2026-08-15 確認)
• PostgreSQL 18, Routine Vacuuming: https://www.postgresql.org/docs/18/routine-vacuuming.html (2026-08-15 確認)
• PostgreSQL 18, Automatic Vacuuming: https://www.postgresql.org/docs/18/runtime-config-autovacuum.html (2026-08-15 確認)
• PostgreSQL 18, Resource Consumption: https://www.postgresql.org/docs/18/runtime-config-resource.html (2026-08-15 確認)
• PostgreSQL 18, Write Ahead Log: https://www.postgresql.org/docs/18/runtime-config-wal.html (2026-08-15 確認)
• PostgreSQL 18, EXPLAIN: https://www.postgresql.org/docs/18/sql-explain.html (2026-08-15 確認)

続けて読む

• 前編: データベースキャッシュ戦略完全ガイド — 無効化がすべて
• 次編: データモデリング完全ガイド — 論理モデルから物理モデルまで
• パーティショニングとシャーディング完全ガイド — 大量削除をDROPに変える
• PostgreSQL VACUUMとMVCC内部構造 — デッドタプルが積み上がる仕組み
• CSV/JSON変換ツール — 投入用ファイルの変換
• モックデータ生成ツール — 大容量テストデータの作成