Created
August 15, 2017 08:37
-
-
Save adis-io/6b36c75eb9de221d11047310fa7bff98 to your computer and use it in GitHub Desktop.
~ createdb text_vs_uuid
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
begin | |
require 'pg' | |
require 'benchmark' | |
rescue LoadError | |
$stderr.puts "You don't have gem pg installed in your system. Please use `gem install pg` to install it" | |
raise | |
end | |
class TextVSUUID | |
DB_NAME = 'text_vs_uuid' | |
def initialize | |
@pg = PG.connect dbname: DB_NAME | |
end | |
def prepare_db | |
@pg.exec 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp";' | |
@pg.exec 'DROP TABLE IF EXISTS text_ids' | |
@pg.exec 'DROP TABLE IF EXISTS uuid_ids' | |
@pg.exec 'CREATE TABLE text_ids (id SERIAL PRIMARY KEY, uuid TEXT DEFAULT uuid_generate_v4(), name TEXT)' | |
@pg.exec 'CREATE TABLE uuid_ids (id SERIAL PRIMARY KEY, uuid UUID DEFAULT uuid_generate_v4(), name TEXT)' | |
end | |
def prepare_test_data | |
names = Array.new(10_000) { |i| i.to_s } | |
@names_values = names.map{ |name| "('#{name}')" }.join(',') | |
@text_ids = [] | |
@uuid_ids = [] | |
GC.disable | |
end | |
def start_benchmarking | |
puts 'Inserting' | |
Benchmark.bmbm do |x| | |
x.report 'TEXT type' do | |
@text_ids = @pg.exec("INSERT INTO text_ids (name) VALUES #{@names_values} RETURNING uuid").map { |row| row['uuid'] } | |
end | |
x.report 'UUID type' do | |
@uuid_ids = @pg.exec("INSERT INTO uuid_ids (name) VALUES #{@names_values} RETURNING uuid").map { |row| row['uuid'] } | |
end | |
end | |
puts | |
puts 'Retrieving by uuid' | |
@text_ids.shuffle! | |
@uuid_ids.shuffle! | |
Benchmark.bmbm do |x| | |
x.report 'TEXT type' do | |
@text_ids.each do |id| | |
@pg.exec "SELECT * FROM text_ids WHERE uuid = '#{id}'" | |
end | |
end | |
x.report 'UUID type' do | |
@uuid_ids.each do |id| | |
@pg.exec "SELECT * FROM uuid_ids WHERE uuid = '#{id}'" | |
end | |
end | |
end | |
end | |
def get_table_sizes | |
sql = <<~SQL | |
SELECT | |
table_name, | |
pg_size_pretty(table_size) AS table_size, | |
pg_size_pretty(indexes_size) AS indexes_size, | |
pg_size_pretty(total_size) AS total_size | |
FROM ( | |
SELECT | |
table_name, | |
pg_table_size(table_name) AS table_size, | |
pg_indexes_size(table_name) AS indexes_size, | |
pg_total_relation_size(table_name) AS total_size | |
FROM ( | |
SELECT table_name | |
FROM information_schema.tables | |
WHERE table_catalog = 'text_vs_uuid' AND table_name in ('text_ids', 'uuid_ids') | |
) AS all_tables | |
ORDER BY total_size DESC | |
) AS pretty_sizes | |
SQL | |
results = @pg.exec(sql) | |
puts | |
results.each do |result| | |
puts "Table: #{result['table_name']} | Table size: #{result['table_size']} | Indexes size: #{result['indexes_size']} | Total size: #{result['total_size']}" | |
end | |
end | |
def add_indexes | |
@pg.exec 'CREATE UNIQUE INDEX text_uuid ON text_ids (uuid)' | |
@pg.exec 'CREATE UNIQUE INDEX uuid_uuid ON uuid_ids (uuid)' | |
end | |
def clean_db | |
@pg.exec 'DROP TABLE text_ids' | |
@pg.exec 'DROP TABLE uuid_ids' | |
end | |
end | |
benchmark1 = TextVSUUID.new | |
puts | |
puts '== WITHOUT INDEX ==' | |
puts | |
benchmark1.prepare_db | |
benchmark1.prepare_test_data | |
benchmark1.start_benchmarking | |
benchmark1.get_table_sizes | |
benchmark1.clean_db | |
puts | |
puts '== WITH INDEX ==' | |
puts | |
benchmark2 = TextVSUUID.new | |
benchmark2.prepare_db | |
benchmark2.add_indexes | |
benchmark2.prepare_test_data | |
benchmark2.start_benchmarking | |
benchmark1.get_table_sizes | |
benchmark2.clean_db |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment