大量データをデータベースに一括登録したいとき、BULK INSERT がよく利用されます。今回は INSERT ではなく UPDATE の処理速度について検証してみました。
Ruby on Rails + PostgreSQL での検証です。

というわけではじめまして。インゲージには9月に入社したばかりの knsk765 です。前職では C# と SQL Server での開発がほとんどでした。はじめての Rails + PostgreSQL に色々戸惑いながらも勉強し続ける日々です。

前置き
- BULK INSERTって？
- じゃあ、BULK UPDATE もあるの？
  - UPSERT
  - BULK UPSERT
本題
まとめ

前置き

BULK INSERTって？

INSERT文を1件ずつ実行するのではなく、1回のコマンドで複数件のデータを登録するしくみです。実現方法はデータベース（RDBMS）ごとに結構な違いがあります。

SQL Server だと BULK INSERT 構文でデータファイルを読み込んで一括登録します。

BULK INSERT Sales.Orders
FROM 'C:\Hoge\Fuga\orders.dat';

BULK INSERT (Transact-SQL) - SQL Server | Microsoft Learn

PostgreSQL だとひとつの INSERT 文に複数の VALUE を指定する形式になります。

INSERT INTO users (id, name) VALUES
(1, '松崎 しげろ'),
(2, '竹内 刀'),
(3, '空条 Q太郎')

じゃあ、BULK UPDATE もあるの？

あります。これもデータベースごとに実現方法が違います。PostgreSQL の場合について説明しますが、その前に UPSERT に触れておきます。

UPSERT

INSERT しようとしてキーが重複したら（すでにレコードがあったら）、UPDATE に切り替えるしくみを UPSERT と言います。

INSERT INTO users (id, name) VALUES
(2, '竹肉 力')
ON CONFLICT (id)
DO UPDATE SET name=EXCLUDED.name

ON CONFLICT … DO UPDATE SET … の構文についての詳細は今回は割愛します。

BULK UPSERT

この ON CONFLICT と前述の BULK INSERT を組み合わせることで BULK UPSERT が実現できます。
さっき BULK UPDATE はあると言いましたが、厳密には BULK UPSERT ですね。

INSERT INTO users (id, name) VALUES
(1, '松崎 しげゑ'),
(2, '竹肉 力'),
(3, '空条 O次郎')
ON CONFLICT (id)
DO UPDATE SET name=EXCLUDED.name

PostgreSQL 14.0文書 SQLコマンド INSERT

本題

大量に高速 UPDATE したい

BULK INSERT が通常の INSERT を複数回繰り返すよりも速いというのは知っていましたが、一括更新したい場合はどうなのか？

大量データを UPSERT したいわけではなく、UPDATE したい。
でも、前述の BULK UPSERT って INSERT に引っ掛かってから UPDATE をするので、そんなにパフォーマンス出ないんじゃね？
件数分、すなおに複数回 UPDATE してもかわらないんじゃね？っていう疑問が生じたので検証してみました。

Ruby on Rails での実現方法

Rails 6 からは標準で BULK INSERT/UPSERT ができる*1のですが、今回はそれ以前から使われていた activerecord-import での検証です。

import (BULK INSERT)

User.import users

こんな感じで import メソッドにモデルの配列を渡すことで BULK INSERT できます。
配列の中身は Active Record モデルでもハッシュでもいけます。

import (BULK UPSERT)

import メソッドのオプション指定で、UPSERT も実現できます。

User.import users,
  on_duplicate_key_update: { conflict_target: [:id], columns: [:name] }

登録しようとして id が重複したら（conflict_target: [:id]）、name を更新する（columns: [:name]）ってことですね。

これで実際にはさっきの例のような SQL が発行されます。

INSERT INTO users (id, name) VALUES
(1, '松崎 しげゑ'),
(2, '竹肉 力'),
(3, '空条 O次郎')
ON CONFLICT (id)
DO UPDATE SET name=EXCLUDED.name

で、下記のようにぐるぐる回して1件ずつ更新するのとどっちが速いの？ってことを検証します。

users.each do |user|
  user.update_column(:name, ユーザーごとの名前)
end

検証コード

require 'benchmark'

class BulkImportBenchmark
  def initialize(times)
    @times = times
    ActiveRecord::LogSubscriber.logger.level = Logger::ERROR
  end

  def compare_bulk_update
    ActiveRecord::Base.transaction do

      Benchmark.bm 23 do |r|
        users_as_hash = []

        r.report "prepare models" do
          @times.times do
            users_as_hash << FactoryBot.build(:user).attributes
          end
        end

        r.report "bulk insert by hash" do
          User.import users_as_hash
        end

        updating_users = User.all.map { |u| u }

        r.report "update model for each" do
          updating_users.each do |u|
            u.memo08 = 'hogeee'
            u.save
          end
        end

        r.report "update column for each" do
          updating_users.each do |u|
            u.update_column(:memo08, 'fugaaa')
          end
        end

        r.report "bulk update by model" do
          User.import updating_users,
                      validate: false, raise_error: true, timestamps: false,
                      on_duplicate_key_update: { conflict_target: [:id], columns: [:memo08] }
        end

        updating_users_as_hash = updating_users.map { |u| u.attributes }

        r.report "bulk update by hash" do
          User.import updating_users_as_hash,
                      validate: false, raise_error: true, timestamps: false,
                      on_duplicate_key_update: { conflict_target: [:id], columns: [:memo08] }
        end
      end

      raise ActiveRecord::Rollback
    end
  end
end

処理内容

prepare models 最初に FactoryBot でモデルを作成
bulk insert by hash import メソッドで BULK INSERT
update model for each 1件ずつモデルの save メソッドで UPDATE する速度の検証
update column for each 1件ずつモデルの update_column メソッドで UPDATE する速度の検証
bulk update by model import に Active Record モデル配列を渡して BULK UPDATE する速度の検証
bulk update by hash import にハッシュ配列を渡して BULK UPDATE する速度の検証

検証結果

検証環境
MacBook Pro M1Pro 14インチ（8コアCPU、メモリ16GB）で検証しました。 Rails 7 + PostgreSQL 14.5 で環境構築しています。

更新レコード数
10,000件

以下、5回実行した平均値です。

	user	system	total	real
update model for each	3.3676	0.2716	3.6392	(6.6716)
update column for each	1.4030	0.2308	1.6338	(4.3271)
bulk update by model	1.3385	0.0177	1.3562	(4.1822)
bulk update by hash	0.8160	0.0095	0.8256	(4.0461)

bulk update by hash ... 最速
import にハッシュ配列を渡して BULK UPDATE

update model for each ... ほぼ同等（数%↓）
1件ずつモデルの save メソッドで UPDATE

ソース一式

docker で実行できるソース一式をここに置きました。今回の記事では触れていませんが、BULK INSERT と通常 INSERT の比較もあります。

github.com

まとめ

import での一括更新はそんなに速くない

UPSERT であるがゆえか、通常の UPDATE 繰り返しに比べてそんなに速くはなかったです。
もちろん更新のみではなく登録と更新が混在する場合は import が有用です。

わかったこと

1件ずつの通常 UPDATE の場合
- save よりも update_column メソッドの方がだいぶ速い
一括更新（import）の場合
- Active Record 配列よりもハッシュ配列の方が多少速い

今回は簡易な検証なのでもっと条件を突き詰めれば結果は変わってくるかもしれませんが、倍以上の差が出ることはなさそうです。

もっと改善策があるといった情報をお持ちの方はぜひ教えてください。

それではまた。

*1:insert_all, upsert_all というメソッドが用意されています（Ruby on Rails 6.0 リリースノート - Railsガイド）

インゲージ開発者ブログ

【Rails+PostgreSQL】importでの一括UPDATE速度検証

前置き