Skip to content

Instantly share code, notes, and snippets.

Embed
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)
@s-andringa

This comment has been minimized.

Copy link

commented Jun 3, 2014

Thanks for this!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.