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

# First

Inserting
              user     system      total        real
Numeric   0.060000   0.020000   0.080000 (  0.977884)
UUID      0.070000   0.010000   0.080000 (  1.147398)

Retrieving by id
              user     system      total        real
Numeric   0.640000   0.920000   1.560000 (  9.457490)
UUID      0.570000   0.800000   1.370000 (  9.059300)

# Second

Inserting
              user     system      total        real
Numeric   0.060000   0.020000   0.080000 (  0.925771)
UUID      0.080000   0.020000   0.100000 (  1.235454)

Retrieving by id
              user     system      total        real
Numeric   0.650000   0.950000   1.600000 (  9.480395)
UUID      0.660000   0.930000   1.590000 (  9.930824)

numeric_vs_uuid.rb

require 'pg'
require 'benchmark'

pg = PG.connect dbname: 'uuid_test'

pg.exec 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"'
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'