PostgreSQL Serial/UUID as primary key benchmark

postgres_uuid_primary_key_benchmark.rb

require "pg"
require "benchmark"
require "faker"

def say(msg)
  puts msg
end

conn = PG.connect(dbname: "postgres")

tables = {
  serial:         {type: "SERIAL"},
  bigserial:      {type: "BIGSERIAL"},
  uuid_ossp_v1mc: {type: "UUID", default: "uuid_generate_v1mc()"},
  uuid_ossp_v4:   {type: "UUID", default: "uuid_generate_v4()"},
  pgcrypto:       {type: "UUID", default: "gen_random_uuid()"},
}

# Create DB and tables.
conn.exec "DROP DATABASE IF EXISTS benchmark_uuid_pk"
conn.exec "CREATE DATABASE benchmark_uuid_pk"

db = PG.connect(dbname: "benchmark_uuid_pk")
db.exec "CREATE EXTENSION IF NOT EXISTS pgcrypto"
db.exec "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\""

tables.each do |table, opts|
  default = ''
  default = " DEFAULT #{opts[:default]}" if opts.key?(:default) && !opts[:default].nil?

  db.exec <<-SQL
CREATE TABLE "#{table}" (
  id #{opts[:type]} PRIMARY KEY#{default},
  name TEXT,
  email TEXT
)
SQL
end

# Populate tables and get informations.

say ""
say "       Table        |    rows    |    size    | index size | insert time (m) "
say "-----------------------------------------------------------------------------"

tables.keys.each do |table|
  duration = Time.now
  (1..10000).each do |i|
    lines = []
    (1..100).each do
      lines << %(('#{db.escape_string(Faker::Name.name)}', '#{db.escape_string(Faker::Internet.email)}'))
    end
    db.exec %(INSERT INTO #{table} (name, email) VALUES #{lines.join(", ")})
  end
  duration = Time.at(Time.now - duration).gmtime.strftime("%R:%S")

  trows = db.exec(%(SELECT COUNT(*) FROM #{table})).values.first.first
  tsize = db.exec(%(SELECT pg_size_pretty(pg_relation_size('#{table}')))).values.first.first
  isize = db.exec(%(SELECT pg_size_pretty(pg_total_relation_size('#{table}')))).values.first.first

  say sprintf "%19s | %10s | %10s | %10s | %15s ", table, trows, tsize, isize, duration
end

  db.exec <<-SQL
CREATE TABLE "#{table}" (
  id #{opts[:type]} PRIMARY KEY#{default},
  name TEXT,
  email TEXT
)
SQL
end

# Populate tables and get informations.

say ""
say "       Table        |    rows    |    size    | index size | insert time (m) "
say "-------------------------------------------------------------------------"

tables.keys.each do |table|
  duration = Time.now
  (1..10000).each do |i|
    lines = []
    (1..100).each do
      lines << %(('#{db.escape_string(Faker::Name.name)}', '#{db.escape_string(Faker::Internet.email)}'))
    end
    db.exec %(INSERT INTO #{table} (name, email) VALUES #{lines.join(", ")})
  end
  duration = Time.at(Time.now - duration).gmtime.strftime("%R:%S")

  trows = db.exec(%(SELECT COUNT(*) FROM #{table})).values.first.first
  tsize = db.exec(%(SELECT pg_size_pretty(pg_relation_size('#{table}')))).values.first.first
  isize = db.exec(%(SELECT pg_size_pretty(pg_total_relation_size('#{table}')))).values.first.first

  say sprintf "%19s | %10s | %10s | %10s | %15s ", table, trows, tsize, isize, duration
end

# Remove databse.
conn.exec "DROP DATABASE IF EXISTS benchmark_uuid_pk"

results.md

First time with 10 000 entries.

Table	rows	size	index size	insert time (m)
serial	10000	752 kB	1024 kB	00:00:02
bigserial	10000	784 kB	1056 kB	00:00:01
uuid_ossp_v1mc	10000	864 kB	1224 kB	00:00:01
uuid_ossp_v4	10000	872 kB	1344 kB	00:00:01
pgcrypto	10000	864 kB	1320 kB	00:00:01

Second time with 10 000 entries.

Table	rows	size	index size	insert time (m)
serial	10000	752 kB	1024 kB	00:00:02
bigserial	10000	784 kB	1056 kB	00:00:01
uuid_ossp_v1mc	10000	864 kB	1224 kB	00:00:01
uuid_ossp_v4	10000	864 kB	1328 kB	00:00:01
pgcrypto	10000	864 kB	1296 kB	00:00:01

First time with 1 000 000 entries.

Table	rows	size	index size	insert time (m)
serial	1000000	73 MB	94 MB	00:03:01
bigserial	1000000	76 MB	98 MB	00:03:00
uuid_ossp_v1mc	1000000	84 MB	115 MB	00:03:03
uuid_ossp_v4	1000000	84 MB	123 MB	00:03:04
pgcrypto	1000000	84 MB	123 MB	00:03:05

|

Second time with 1 000 000 entries.

Table	rows	size	index size	insert time (m)
serial	1000000	73 MB	94 MB	00:03:05
bigserial	1000000	76 MB	98 MB	00:03:03
uuid_ossp_v1mc	1000000	84 MB	127 MB	00:03:00
uuid_ossp_v4	1000000	84 MB	123 MB	00:03:03
pgcrypto	1000000	84 MB	123 MB	00:03:02

This is very useful thanks!