Skip to content

Instantly share code, notes, and snippets.

Forked from jgaskins/numeric_vs_uuid
Created September 17, 2022 09:50
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?
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.
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 = { |i| i.to_s }
names_values ={ |name| "('#{name}')" }.join(',')
numeric_ids = []
uuid_ids = []
puts 'Inserting'
Benchmark.bmbm do |x| 'Numeric' do
numeric_ids = pg.exec("INSERT INTO numeric_ids (name) VALUES #{names_values} RETURNING id").map { |row| row['id'] }
end 'UUID' do
uuid_ids = pg.exec("INSERT INTO uuid_ids (name) VALUES #{names_values} RETURNING id").map { |row| row['id'] }
puts 'Retrieving by id'
Benchmark.bmbm do |x| 'Numeric' do
numeric_ids.each do |id|
pg.exec "SELECT * FROM numeric_ids WHERE id = '#{id}'"
end 'UUID' do
uuid_ids.each do |id|
pg.exec "SELECT * FROM uuid_ids WHERE id = '#{id}'"
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