PostgreSQL update strategies in and around Rails

benchmark.rb

require 'active_record'
require 'activerecord-import'
require 'benchmark'
require 'pg'

include ActiveRecord

Base.establish_connection adapter: 'postgresql',
                          encoding: 'unicode',
                          pool:     5,
                          database: 'benchmark'

unless Base.connection.table_exists? :records
  Migration.new.create_table :records do |t|
    t.string :data
    t.timestamps
  end
end

Record = Class.new Base

10000.times { Record.create data: SecureRandom.hex(32) } unless Record.first

Benchmark.bmbm(10) do |b|
  b.report("update") do
    Record.transaction do
      Record.limit(1000)
            .map { |r| r.data = SecureRandom.hex(32); r }
            .each(&:save!)
    end
  end

  b.report("batch update") do
    stmts = Record.limit(1000).map do |r|
      attributes_with_values =
        r.send :arel_attributes_values, false, false, r.attribute_names
      Record.unscoped
            .where(Record.arel_table[Record.primary_key].eq(r.id))
            .arel.compile_update(attributes_with_values)
    end
    sql = 'BEGIN;'
    sql << stmts.map(&:to_sql).join(';')
    sql << ';COMMIT;'

    Record.connection.execute sql
  end

  b.report("batch prepared update") do
    sql = %q{
      PREPARE    fooplan (int, text) AS
        UPDATE   records
        SET      data=$2,
                 updated_at=current_timestamp
        WHERE    id=$1;
        BEGIN;}
    Record.limit(1000).each do |r|
      sql << "EXECUTE fooplan(#{r.id}, '#{SecureRandom.hex(32)}');"
    end
    sql << 'COMMIT;DEALLOCATE fooplan;'

    Record.connection.execute sql
  end

  b.report("batch insert") do
    deletes = Record.limit(1000)
    inserts = 1000.times.map { Record.new data: SecureRandom.hex(32) }

    Record.transaction do
      Record.delete_all id: deletes.map(&:id)
      Record.import inserts
    end
  end
end

out

Rehearsal ---------------------------------------------------------
update                  0.580000   0.040000   0.620000 (  0.811713)
batch update            0.200000   0.000000   0.200000 (  0.276307)
batch prepared update   0.030000   0.000000   0.030000 (  0.092410)
batch insert            0.280000   0.010000   0.290000 (  0.319137)
------------------------------------------------ total: 1.140000sec

                            user     system      total        real
update                  0.540000   0.030000   0.570000 (  0.713661)
batch update            0.190000   0.000000   0.190000 (  0.273144)
batch prepared update   0.020000   0.000000   0.020000 (  0.084136)
batch insert            0.250000   0.010000   0.260000 (  0.297645)

Thanks for this!