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.

numeric_vs_uuid

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)

numeric_vs_uuid.rb

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'