SQLAlchemyを使って、外部APIから取得したデータをPostgreSQLに登録する処理を作っていたときのお話です。
SQLAlchemyでもUpsert処理ができると知ったので、ON CONFLICTを使ったupsert処理を導入しました。
これまでなら、事前に既存データを確認しておき、APIから取得したデータが既存ならupdate, 新規ならinsert と分けて処理していたところです。
ところが、実際に動かしてみると想定していたよりもかなり遅い。これはちょっと納品できる代物ではない、、
せっかくなので、実際にどのくらい違うのか計測してみました。
今回の処理内容
ざっくりとした処理の説明。
外部APIからデータ取得（商品,キャンペーン）
↓
取得したデータをバッチに分割
↓
SQLAlchemyでバッチごとに一括処理
↓
PostgreSQLに保存
という流れです。
登録するデータは3種類で、
- Product (約10万件)
- Campaign (約50件)
- association_table (ProductとCampaignを関連付ける中間テーブル)
です。
ProductとCampaignは多対多のrelationshipなので、中間テーブルにも大量のレコードを登録します。
upsertしていた処理内容
最初はSQLAlchemyで、
stmt = insert(Product).values(id=1, campaign_id=10)
stmt.on_conflict_do_update(
index_elements=["id"],
set_={
"campaign_id": stmt.excluded.campaign_id
}
)のような形で、既存データがあれば更新するようにしていました。
ところが実際に計測してみると、Product約250件の登録で約27秒。association約1,500件程度の登録で約150秒。
250件の商品データにキャンペーン情報を関連づけるだけで約3分。さすがにこれはちょっとない。
しかも商品データは約10万件なので、到底現実的な時間ではありません。
まず普通のINSERTで試してみる
そこでupsertをやめて、ついでにテーブルも一度初期化して、単純なINSERTで計測してみました。
すると、
Product:約0.3秒, association_table: 約0.3秒
くらいまで短くなりました。さっきの3分はどこに行った？？
もちろん、単純なINSERTとupsertではやっている処理が違うので、これだけで「upsertは遅い」と結論づけることはできません。
ただ、今回のケースでは明らかにupsertを使っていたことが原因になっているようです。
バッチサイズを大きくしてみる
最後にbatchサイズを調整し、最終的にbatch=1000として計測。
結果は、
start upsert process @2026-08-07 16:50:50
product insert: 0.50s (1000 rows)
association insert: 0.91s (6297 rows)
commit/transaction: 0.09s
process done. @2026-08-07 16:50:52
となりました。
最初の非実用的な速度から現実的な数字になることができました。
今回わかったこと
新規と更新が混ざっているからと何も考えずにUpsertしてしまうのはあまりに浅慮でした。
- データ量
- 更新頻度
- 更新内容
- 一意制約
- インデックス
- 差分更新が必要か
- 全件再構築できるか
などによって、適した方法は変わりそうです。実際に計測してから優れた方法を選ぶという基本をさぼってはいけないなと反省しました。
