|
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" |
Thank you for creating this!