Skip to content

Instantly share code, notes, and snippets.

@jgaskins
Created January 17, 2014 01:38
Show Gist options
  • Save jgaskins/8467016 to your computer and use it in GitHub Desktop.
Save jgaskins/8467016 to your computer and use it in GitHub Desktop.
Comparing numeric vs UUID in Postgres. Rehearsal benchmarks are thrown out. Only the second run is kept. Insertions are done all in one query to minimize the impact of I/O. This is harder to do with retrieval, but the ids are randomized in each one to minimize the effects of caching inside the database.
Inserting
user system total real
Numeric 0.070000 0.010000 0.080000 ( 0.586738)
UUID 0.070000 0.020000 0.090000 ( 3.101085)
Retrieving by id
user system total real
Numeric 0.810000 0.980000 1.790000 ( 6.831551)
UUID 0.830000 0.990000 1.820000 ( 6.981944)
require 'pg'
require 'benchmark'
pg = PG.connect dbname: 'jamie'
pg.exec 'DROP TABLE IF EXISTS numeric_ids'
pg.exec 'DROP TABLE IF EXISTS uuid_ids'
pg.exec 'CREATE TABLE numeric_ids (id SERIAL PRIMARY KEY, name TEXT)'
pg.exec 'CREATE TABLE uuid_ids (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), name text)'
names = Array.new(100_000) { |i| i.to_s }
names_values = names.map{ |name| "('#{name}')" }.join(',')
numeric_ids = []
uuid_ids = []
GC.disable
puts 'Inserting'
Benchmark.bmbm do |x|
x.report 'Numeric' do
numeric_ids = pg.exec("INSERT INTO numeric_ids (name) VALUES #{names_values} RETURNING id").map { |row| row['id'] }
end
x.report 'UUID' do
uuid_ids = pg.exec("INSERT INTO uuid_ids (name) VALUES #{names_values} RETURNING id").map { |row| row['id'] }
end
end
puts
puts 'Retrieving by id'
numeric_ids.shuffle!
uuid_ids.shuffle!
Benchmark.bmbm do |x|
x.report 'Numeric' do
numeric_ids.each do |id|
pg.exec "SELECT * FROM numeric_ids WHERE id = '#{id}'"
end
end
x.report 'UUID' do
uuid_ids.each do |id|
pg.exec "SELECT * FROM uuid_ids WHERE id = '#{id}'"
end
end
end
pg.exec 'DROP TABLE numeric_ids'
pg.exec 'DROP TABLE uuid_ids'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment