qEQQsCW4

1require "pg"
2require "benchmark"
3require "faker"
4
5def say(msg)
6  puts msg
7end
8
9conn = PG.connect(dbname: "postgres")
10
11tables = {
12  serial:         {type: "SERIAL"},
13  bigserial:      {type: "BIGSERIAL"},
14  uuid_ossp_v1mc: {type: "UUID", default: "uuid_generate_v1mc()"},
15  uuid_ossp_v4:   {type: "UUID", default: "uuid_generate_v4()"},
16  pgcrypto:       {type: "UUID", default: "gen_random_uuid()"},
17}
18
19# Create DB and tables.
20conn.exec "DROP DATABASE IF EXISTS benchmark_uuid_pk"
21conn.exec "CREATE DATABASE benchmark_uuid_pk"
22
23db = PG.connect(dbname: "benchmark_uuid_pk")
24db.exec "CREATE EXTENSION IF NOT EXISTS pgcrypto"
25db.exec "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\""
26
27tables.each do |table, opts|
28  default = ''
29  default = " DEFAULT #{opts[:default]}" if opts.key?(:default) && !opts[:default].nil?
30
31  db.exec <<-SQL
32CREATE TABLE "#{table}" (
33  id #{opts[:type]} PRIMARY KEY#{default},
34  name TEXT,
35  email TEXT
36)
37SQL
38end
39
40# Populate tables and get informations.
41
42say ""
43say "       Table        |    rows    |    size    | index size | insert time (m) "
44say "-----------------------------------------------------------------------------"
45
46tables.keys.each do |table|
47  duration = Time.now
48  (1..10000).each do |i|
49    lines = []
50    (1..100).each do
51      lines << %(('#{db.escape_string(Faker::Name.name)}', '#{db.escape_string(Faker::Internet.email)}'))
52    end
53    db.exec %(INSERT INTO #{table} (name, email) VALUES #{lines.join(", ")})
54  end
55  duration = Time.at(Time.now - duration).gmtime.strftime("%R:%S")
56
57  trows = db.exec(%(SELECT COUNT(*) FROM #{table})).values.first.first
58  tsize = db.exec(%(SELECT pg_size_pretty(pg_relation_size('#{table}')))).values.first.first
59  isize = db.exec(%(SELECT pg_size_pretty(pg_total_relation_size('#{table}')))).values.first.first
60
61  say sprintf "%19s | %10s | %10s | %10s | %15s ", table, trows, tsize, isize, duration
62end
63
64  db.exec <<-SQL
65CREATE TABLE "#{table}" (
66  id #{opts[:type]} PRIMARY KEY#{default},
67  name TEXT,
68  email TEXT
69)
70SQL
71end
72
73# Populate tables and get informations.
74
75say ""
76say "       Table        |    rows    |    size    | index size | insert time (m) "
77say "-------------------------------------------------------------------------"
78
79tables.keys.each do |table|
80  duration = Time.now
81  (1..10000).each do |i|
82    lines = []
83    (1..100).each do
84      lines << %(('#{db.escape_string(Faker::Name.name)}', '#{db.escape_string(Faker::Internet.email)}'))
85    end
86    db.exec %(INSERT INTO #{table} (name, email) VALUES #{lines.join(", ")})
87  end
88  duration = Time.at(Time.now - duration).gmtime.strftime("%R:%S")
89
90  trows = db.exec(%(SELECT COUNT(*) FROM #{table})).values.first.first
91  tsize = db.exec(%(SELECT pg_size_pretty(pg_relation_size('#{table}')))).values.first.first
92  isize = db.exec(%(SELECT pg_size_pretty(pg_total_relation_size('#{table}')))).values.first.first
93
94  say sprintf "%19s | %10s | %10s | %10s | %15s ", table, trows, tsize, isize, duration
95end
96
97# Remove databse.
98conn.exec "DROP DATABASE IF EXISTS benchmark_uuid_pk"