Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
PostgreSQL update strategies in and around Rails
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
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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment