Производительность использования текстового и UUID типа в postgres

README

Запуск тестов:
~ createdb text_vs_uuid
~ ruby text_vs_uuid.rb

Тесты: 
MacBook i7 16 GB:

== WITHOUT INDEX ==

NOTICE:  extension "uuid-ossp" already exists, skipping
Inserting
Rehearsal ---------------------------------------------
TEXT type   0.020000   0.000000   0.020000 (  0.090244)
UUID type   0.000000   0.000000   0.000000 (  0.080284)
------------------------------------ total: 0.020000sec

                user     system      total        real
TEXT type   0.010000   0.010000   0.020000 (  0.084042)
UUID type   0.010000   0.000000   0.010000 (  0.080787)

Retrieving by uuid
Rehearsal ---------------------------------------------
TEXT type   0.200000   0.200000   0.400000 ( 25.383774)
UUID type   0.190000   0.190000   0.380000 ( 21.887027)
------------------------------------ total: 0.780000sec

                user     system      total        real
TEXT type   0.200000   0.190000   0.390000 ( 25.980163)
UUID type   0.190000   0.160000   0.350000 ( 21.525007)

Table: text_ids | Table size: 1528 kB | Indexes size: 456 kB | Total size: 1984 kB
Table: uuid_ids | Table size: 1200 kB | Indexes size: 456 kB | Total size: 1656 kB

== WITH INDEX ==

NOTICE:  extension "uuid-ossp" already exists, skipping
NOTICE:  table "text_ids" does not exist, skipping
NOTICE:  table "uuid_ids" does not exist, skipping
Inserting
Rehearsal ---------------------------------------------
TEXT type   0.010000   0.000000   0.010000 (  0.116703)
UUID type   0.010000   0.000000   0.010000 (  0.101831)
------------------------------------ total: 0.020000sec

                user     system      total        real
TEXT type   0.010000   0.000000   0.010000 (  0.115588)
UUID type   0.010000   0.010000   0.020000 (  0.102046)

Retrieving by uuid
Rehearsal ---------------------------------------------
TEXT type   0.360000   0.090000   0.450000 (  1.169155)
UUID type   0.070000   0.100000   0.170000 (  0.842912)
------------------------------------ total: 0.620000sec

                user     system      total        real
TEXT type   0.070000   0.100000   0.170000 (  0.906396)
UUID type   0.080000   0.100000   0.180000 (  0.934766)

Table: text_ids | Table size: 1528 kB | Indexes size: 1944 kB | Total size: 3472 kB
Table: uuid_ids | Table size: 1200 kB | Indexes size: 1240 kB | Total size: 2440 kB

text_vs_uuid.rb

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

puts
puts '== WITHOUT INDEX =='
puts
benchmark1 = TextVSUUID.new
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
benchmark2.get_table_sizes
benchmark2.clean_db