PostgreSQLで数十万行を一括更新する効率的な方法 – `WITH`句と`UNNEST`を使いこなせ！

私が携わっている XD.GROWTH では、
PostgreSQLを使用しているのですが、
大量のデータを一括で更新する場面が多々あります。

数十万行のデータを1行ずつ処理するのは非効率であり、
パフォーマンス上の大きな問題となります。

かといって、単純にループでクエリを組み立てて実行すると、
データベースの制約（特に クエリパラメーター数の上限 ）に直面することになります。

本記事では、この問題を解決し、効率的かつ安全に大量データを一括処理するための強力なテクニックを紹介します。

鍵となるのは、 WITH句 (Common Table Expression - CTE)、配列型、そして UNNEST関数 の組み合わせです。

なぜクエリパラメーターの制限が問題になるのか？

多くのデータベース接続ライブラリやプロトコルには、 1つのクエリでバインドできるパラメーターの数に上限 が設けられています。

例えば、PostgreSQLのプロトコルでは約65,000個、一部のORMライブラリでは32,767個といった制限があります。

INSERT INTO table (col1, col2) VALUES (?, ?), (?, ?), ... のようにして数十万行を一度に挿入しようとすると、
パラメーターの数が数十万、あるいは数百万に達し、この上限を超えてしまいます。
結果として、クエリが失敗するか、ライブラリ側で不必要な分割処理が必要になり、シンプルさが失われてしまいます。

解決策: WITH句と配列、UNNESTの強力なタッグ

この問題の解決策は、 データをカラムごとの配列として少数のパラメーターにまとめ、データベース側でその配列を展開してテーブルのように扱う ことです。

1. データの構造化と配列化

まず、更新したいデータを カラムごとに配列化 します。
この方法は、JSONなどを使う方法と比べて、アプリケーション側での構築コストとデータベース側での分解コストが低く、非常に効率的です。

例として、idとnew_value を更新するデータを考えます。

id	new_value
101	'A'
102	'B'
103	'C'
...	...

これをアプリケーション側で、以下のような カラムごとの配列パラメーター として準備します。

-- アプリケーションからバインドされるパラメーター
-- id用の配列: { 101, 102, 103, ... }
:id_array
-- new_value用の配列: { 'A', 'B', 'C', ... }
:value_array

この方法の利点は以下の通りです：

• シンプル: 既存の配列をそのまま使えるため、JSONへの変換やシリアライズが不要
• 高速: データベース側でのパース処理が不要で、型も明確
• 省メモリ: 余計なメタデータがないため、メモリ効率が良い

2. WITH句で仮想的な「一時テーブル」を作成

次に、これらの配列パラメーターをクエリ内で受け取り、WITH句を使って一時的なテーブル（Common Table Expression - CTE）のように展開します。ここで UNNEST関数 が活躍します。

UNNEST関数は、複数の配列を同時に受け取り、それらを並行して行として展開できます。

WITH update_data AS (
    SELECT
        id_col AS id_to_update,
        value_col AS new_value_to_set
    FROM
        unnest(
            :id_array::INT[],
            :value_array::TEXT[]
        ) AS t(id_col, value_col)
)
-- ... 続く更新処理 ...

このupdate_data CTEは、数十万行のデータセットをあたかも通常のテーブルのように扱える 仮想的なテーブル になります。
そして、 クエリパラメーターとして使用したのは、カラム数分の配列だけ です！

ポイント: 配列の要素数は必ず揃えてください。要素数が異なると、短い方に合わせられてしまいます。

3. 各種更新処理への適用

このCTEを、通常のSQLクエリと同じように利用することで、すべての更新処理で効率的な一括処理が可能になります。

3.1. UPDATEの例

最も一般的な使い方です。UPDATE文のFROM句でCTEを参照し、結合条件（WHERE句）を使って一括更新します。

WITH update_data AS (
    SELECT
        id_col AS id_to_update,
        value_col AS new_value_to_set
    FROM
        unnest(
            :id_array::INT[],
            :value_array::TEXT[]
        ) AS t(id_col, value_col)
)
UPDATE target_table tt
SET
    value_column = ud.new_value_to_set,
    updated_at = now()
FROM
    update_data ud
WHERE
    tt.id = ud.id_to_update; -- 結合条件（idにはインデックスが必要）

注意点: WHERE句で結合するカラム（この例ではid）には、必ずインデックスを作成しておきましょう。インデックスがないと、数十万行の更新で深刻なパフォーマンス問題が発生します。

3.2. UPSERT (INSERT ON CONFLICT) の例

INSERT INTO ... SELECT ... の形式にすることで、CTEからのデータを効率的に挿入・更新できます。

WITH upsert_data AS (
    SELECT
        id_col AS id_to_upsert,
        value_col AS new_value_to_set
     FROM
        unnest(
            :id_array::INT[],
            :value_array::TEXT[]
        ) AS t(id_col, value_col)
)
INSERT INTO target_table (id, value_column)
SELECT
    ud.id_to_upsert,
    ud.new_value_to_set
FROM
    upsert_data ud
ON CONFLICT (
    id
)
DO UPDATE SET
    value_column = EXCLUDED.value_column,
    updated_at = now();

3.3. 複数カラムを更新する場合

3つ以上のカラムを扱う場合も、同じパターンで簡単に拡張できます。

WITH update_data AS (
    SELECT
        id_col AS id_to_update,
        name_col AS new_name,
        age_col AS new_age,
        status_col AS new_status
    FROM
        unnest(
            :id_array::INT[],
            :name_array::TEXT[],
            :age_array::INT[],
            :status_array::TEXT[]
        ) AS t(id_col, name_col, age_col, status_col)
)
UPDATE users u
SET
    name = ud.new_name,
    age = ud.new_age,
    status = ud.new_status,
    updated_at = now()
FROM
    update_data ud
WHERE
    u.id = ud.id_to_update;

このテクニックのメリット

1. パラメーター数の制限を回避: 数十万行のデータ全体をカラム数分の配列パラメーターとして扱うため、クエリパラメーターの上限を気にせず済みます。
2. パフォーマンスの向上: データベースへのネットワーク往復回数 (Round Trips) が1回で済みます。これは大量データ処理において最も重要なパフォーマンス要素の一つです。
3. アトミックな操作: すべての更新操作が1つのトランザクション内で実行されるため、数十万行の更新が全体として成功または失敗する、安全な操作となります。
4. SQL標準への準拠: PostgreSQLの強力な機能（CTE, UNNEST）を使用しているため、複雑なプロシージャなどを書く必要がなく、可読性の高いSQLで実現できます。
5. 効率的なデータ形式: JSONなどを組み立てて一括でパラメータにわたすことと比べて、アプリケーション側での構築コストとデータベース側での分解コストが低く、メモリ効率も良好です。

注意点と制約

このテクニックを使う際は、以下の点に注意してください：

1. 配列サイズの制限: PostgreSQLの配列にも実質的なサイズ制限（約1GB）があります。極端に大きなデータセットの場合は、適切なバッチサイズに分割してください。
2. メモリ使用量: 数十万行のデータを配列化する際、アプリケーション側とデータベース側の両方でメモリを消費します。適切なバッチサイズを検討しましょう。
3. 配列要素数の一致: すべての配列の要素数は必ず一致させる必要があります。不一致の場合、予期しない結果になります。
4. インデックスの重要性: 結合条件で使用するカラムには必ずインデックスを作成してください。

まとめ

PostgreSQLで大量データを一括更新する際は、クエリパラメーターの制限を恐れる必要はありません。
データをカラムごとの配列として渡し、WITH句内で UNNEST関数 を使って仮想テーブルを作成し、そのCTEに対して各種更新処理を行うことで、 高速かつ安全なバッチ処理 を実現できます。

カラムごとに配列化する方法は、JSONなどを使う方法と比べて、構築と分解のコストが低く、非常に効率的です。

このテクニックをマスターし、あなたのPostgreSQLアプリケーションのパフォーマンスを一段と向上させましょう！