DmitryTsepelev/0_unique_data.md

## 0_unique_data.md

      
    Raw
  

              0_unique_data.md
            
          
    How to run examples


Run $ createdb uniq-db-test to create DB
Run example with Ruby (e.g., $ ruby 1_find_or_create_by_single_thread.rb)

Benchmark output

With many successful INSERTs
Warming up --------------------------------------
  create_or_find_by!            58.000  i/100ms
with_lock + find_or_create_by!  33.000  i/100ms
    create! + rescue            52.000  i/100ms
Calculating -------------------------------------
  create_or_find_by!            694.517  (±22.9%) i/s -      9.570k in  15.013421s
with_lock + find_or_create_by!  358.963  (±16.2%) i/s -      5.181k in  15.053770s
    create! + rescue            803.886  (±15.7%) i/s -     11.648k in  15.055700s

Comparison:
    create! + rescue:           803.9 i/s
  create_or_find_by!:           694.5 i/s - same-ish: difference falls within error
with_lock + find_or_create_by!: 359.0 i/s - 2.24x  (± 0.00) slower

When row already exists
Warming up --------------------------------------
  create_or_find_by!            57.000  i/100ms
with_lock + find_or_create_by!  59.000  i/100ms
    create! + rescue            50.000  i/100ms
Calculating -------------------------------------
  create_or_find_by!            525.534  (±12.7%) i/s -      7.695k in  15.003879s
with_lock + find_or_create_by!  530.299  (±21.1%) i/s -      7.257k in  15.065549s
    create! + rescue            491.853  (± 6.1%) i/s -      7.350k in  15.000505s

Comparison:
with_lock + find_or_create_by!: 530.3 i/s
  create_or_find_by!:           525.5 i/s - same-ish: difference falls within error
    create! + rescue:           491.9 i/s - same-ish: difference falls within error


## 1_find_or_create_by_single_thread.rb
begin
  require "bundler/inline"
rescue LoadError => e
  $stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
  raise e
end

gemfile(true) do
  source "https://rubygems.org"

  gem "rails"
  gem "pg"
end

require "active_record"
require "action_controller/railtie"

ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "uniq-db-test")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  enable_extension "plpgsql"

  create_table "time_tracks", force: :cascade do |t|
    t.bigint "user_id", null: false
    t.bigint "task_id", null: false
    t.integer "hours", null: false
    t.date "date", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  create_table "users", force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "time_tracks", "users"
end

class User < ActiveRecord::Base
  has_many :time_tracks
end

class TimeTrack < ActiveRecord::Base
end

# -------------

TimeTrack.delete_all
User.delete_all

user = User.create

100.times do
  date = Date.new(2020, 1, rand(1..10))
  user.time_tracks.create_with(hours: 10).find_or_create_by!(date: date, task_id: 1)
end

not_unique_count =
  TimeTrack.from(
    TimeTrack.group(:date, :user_id).having('COUNT(*) > 1').select(:date, :user_id)
  ).select('COUNT(subquery.date) AS not_unique').to_a.first.not_unique

puts "\nFound #{not_unique_count} not unique tracks"

## 2_find_or_create_by_many_threads.rb
begin
  require "bundler/inline"
rescue LoadError => e
  $stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
  raise e
end

gemfile(true) do
  source "https://rubygems.org"

  gem "rails"
  gem "pg"
end

require "active_record"
require "action_controller/railtie"

ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "uniq-db-test", pool: 100)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  enable_extension "plpgsql"

  create_table "time_tracks", force: :cascade do |t|
    t.bigint "user_id", null: false
    t.bigint "task_id", null: false
    t.integer "hours", null: false
    t.date "date", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  create_table "users", force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "time_tracks", "users"
end

class User < ActiveRecord::Base
  has_many :time_tracks
end

class TimeTrack < ActiveRecord::Base
end

# -------------

TimeTrack.delete_all
User.delete_all

threads = []
wait_for_it = true

user = User.create

20.times do
  date = Date.new(2020, 1, rand(1..4))

  threads << Thread.new(date) do
    true while wait_for_it

    user.time_tracks.create_with(hours: 10).find_or_create_by!(date: date, task_id: 1)
  end
end

wait_for_it = false

threads.each(&:join)

not_unique_count =
  TimeTrack.from(
    TimeTrack.group(:date, :user_id).having('COUNT(*) > 1').select(:date, :user_id)
  ).select('COUNT(subquery.date) AS not_unique').to_a.first.not_unique

puts "\nFound #{not_unique_count} not unique tracks"

## 3_find_or_create_by_many_threads_with_lock.rb
begin
  require "bundler/inline"
rescue LoadError => e
  $stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
  raise e
end

gemfile(true) do
  source "https://rubygems.org"

  gem "rails"
  gem "pg"
end

require "active_record"
require "action_controller/railtie"

ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "uniq-db-test", pool: 100)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  enable_extension "plpgsql"

  create_table "time_tracks", force: :cascade do |t|
    t.bigint "user_id", null: false
    t.bigint "task_id", null: false
    t.integer "hours", null: false
    t.date "date", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  create_table "users", force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "time_tracks", "users"
end

class User < ActiveRecord::Base
  has_many :time_tracks
end

class TimeTrack < ActiveRecord::Base
end

# -------------

TimeTrack.delete_all
User.delete_all

threads = []
wait_for_it = true

user = User.create

20.times do
  date = Date.new(2020, 1, rand(1..4))

  threads << Thread.new(date) do
    true while wait_for_it

    user.with_lock do
      user.time_tracks.create_with(hours: 10).find_or_create_by!(date: date, task_id: 1)
    end
  end
end

wait_for_it = false

threads.each(&:join)

not_unique_count =
  TimeTrack.from(
    TimeTrack.group(:date, :user_id).having('COUNT(*) > 1').select(:date, :user_id)
  ).select('COUNT(subquery.date) AS not_unique').to_a.first.not_unique

puts "\nFound #{not_unique_count} not unique tracks"

## 4_create_or_find_by.rb
begin
  require "bundler/inline"
rescue LoadError => e
  $stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
  raise e
end

gemfile(true) do
  source "https://rubygems.org"

  gem "rails"
  gem "pg"
end

require "active_record"
require "action_controller/railtie"

ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "uniq-db-test", pool: 100)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  enable_extension "plpgsql"

  create_table "time_tracks", force: :cascade do |t|
    t.bigint "user_id", null: false
    t.bigint "task_id", null: false
    t.integer "hours", null: false
    t.date "date", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false

    t.index ["user_id", "task_id", "date"], name: "index_time_tracks_on_keys_and_date", unique: true
  end

  create_table "users", force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "time_tracks", "users"
end

class User < ActiveRecord::Base
  has_many :time_tracks
end

class TimeTrack < ActiveRecord::Base
end

# -------------

TimeTrack.delete_all
User.delete_all

threads = []
wait_for_it = true

user = User.create

20.times do
  date = Date.new(2020, 1, rand(1..4))

  threads << Thread.new(date) do
    true while wait_for_it

    user.time_tracks.create_with(hours: 10).create_or_find_by!(date: date, task_id: 1)
  end
end

wait_for_it = false

threads.each(&:join)

not_unique_count =
  TimeTrack.from(
    TimeTrack.group(:date, :user_id).having('COUNT(*) > 1').select(:date, :user_id)
  ).select('COUNT(subquery.date) AS not_unique').to_a.first.not_unique

puts "\nFound #{not_unique_count} not unique tracks"

## 5_find_or_create_by_with_validator.rb
begin
  require "bundler/inline"
rescue LoadError => e
  $stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
  raise e
end

gemfile(true) do
  source "https://rubygems.org"

  gem "rails"
  gem "pg"
end

require "active_record"
require "action_controller/railtie"

ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "uniq-db-test", pool: 100)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  enable_extension "plpgsql"

  create_table "time_tracks", force: :cascade do |t|
    t.bigint "user_id", null: false
    t.bigint "task_id", null: false
    t.integer "hours", null: false
    t.date "date", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  create_table "users", force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "time_tracks", "users"
end

class User < ActiveRecord::Base
  has_many :time_tracks
end

class TimeTrack < ActiveRecord::Base
  validates :task_id, uniqueness: { scope: %i[date user_id] }
end

# -------------

TimeTrack.delete_all
User.delete_all

threads = []
wait_for_it = true

user = User.create

40.times do
  date = Date.new(2020, 1, rand(1..4))

  threads << Thread.new(date) do
    true while wait_for_it

    begin
      user.time_tracks.create_with(hours: 10).find_or_create_by!(date: date, task_id: 1)
    rescue ActiveRecord::RecordInvalid
      puts 'RecordInvalid rescued!'
    end
  end
end

wait_for_it = false

threads.each(&:join)

not_unique_count =
  TimeTrack.from(
    TimeTrack.group(:date, :user_id).having('COUNT(*) > 1').select(:date, :user_id)
  ).select('COUNT(subquery.date) AS not_unique').to_a.first.not_unique

puts "\nFound #{not_unique_count} not unique tracks"

## 6_create_or_find_by_with_validator.rb
begin
  require "bundler/inline"
rescue LoadError => e
  $stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
  raise e
end

gemfile(true) do
  source "https://rubygems.org"

  gem "rails"
  gem "pg"
end

require "active_record"
require "action_controller/railtie"

ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "uniq-db-test", pool: 100)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  enable_extension "plpgsql"

  create_table "time_tracks", force: :cascade do |t|
    t.bigint "user_id", null: false
    t.bigint "task_id", null: false
    t.integer "hours", null: false
    t.date "date", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false

    t.index ["user_id", "task_id", "date"], name: "index_time_tracks_on_keys_and_date", unique: true
  end

  create_table "users", force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "time_tracks", "users"
end

class User < ActiveRecord::Base
  has_many :time_tracks
end

class TimeTrack < ActiveRecord::Base
  validates :task_id, uniqueness: { scope: %i[date user_id] }
end

# -------------

TimeTrack.delete_all
User.delete_all


user = User.create

# inserting a first record
user.time_tracks.create_with(hours: 10).create_or_find_by!(date: Date.new(2020, 1, 1), task_id: 1)

# trying to add a second one
user.time_tracks.create_with(hours: 10).create_or_find_by!(date: Date.new(2020, 1, 1), task_id: 1)
# => Validation failed: Task has already been taken (ActiveRecord::RecordInvalid)

# trying non–bang version
user.time_tracks.create_with(hours: 10).create_or_find_by(date: Date.new(2020, 1, 1), task_id: 1).inspect
# => #<TimeTrack id: nil, user_id: 1, task_id: 1, hours: 10, date: "2020-01-01", created_at: nil, updated_at: nil>

## 7_upsert.rb
begin
  require "bundler/inline"
rescue LoadError => e
  $stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
  raise e
end

gemfile(true) do
  source "https://rubygems.org"

  gem "rails"
  gem "pg"
end

require "active_record"
require "action_controller/railtie"

ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "uniq-db-test", pool: 100)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  enable_extension "plpgsql"

  create_table "time_tracks", force: :cascade do |t|
    t.bigint "user_id", null: false
    t.bigint "task_id", null: false
    t.integer "hours", null: false
    t.date "date", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false

    t.index ["user_id", "task_id", "date"], name: "index_time_tracks_on_keys_and_date", unique: true
  end

  create_table "users", force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "time_tracks", "users"
end

class User < ActiveRecord::Base
  has_many :time_tracks
end

class TimeTrack < ActiveRecord::Base
end

# -------------

TimeTrack.delete_all
User.delete_all

threads = []
wait_for_it = true

user = User.create

20.times do
  date = Date.new(2020, 1, rand(1..4))

  threads << Thread.new(date) do
    true while wait_for_it

    user.time_tracks.upsert(
      {
        hours: 10,
        date: date,
        task_id: 1,
        created_at: Time.current,
        updated_at: Time.current,
      },
      unique_by: %i[user_id task_id date]
    )
  end
end

wait_for_it = false

threads.each(&:join)

not_unique_count =
  TimeTrack.from(
    TimeTrack.group(:date, :user_id).having('COUNT(*) > 1').select(:date, :user_id)
  ).select('COUNT(subquery.date) AS not_unique').to_a.first.not_unique

puts "\nFound #{not_unique_count} not unique tracks"

## 8_create_with_rescue.rb
begin
  require "bundler/inline"
rescue LoadError => e
  $stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
  raise e
end

gemfile(true) do
  source "https://rubygems.org"

  gem "rails"
  gem "pg"
end

require "active_record"
require "action_controller/railtie"

ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "uniq-db-test", pool: 100)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  enable_extension "plpgsql"

  create_table "time_tracks", force: :cascade do |t|
    t.bigint "user_id", null: false
    t.bigint "task_id", null: false
    t.integer "hours", null: false
    t.date "date", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false

    t.index ["user_id", "task_id", "date"], name: "index_time_tracks_on_keys_and_date", unique: true
  end

  create_table "users", force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "time_tracks", "users"
end

class User < ActiveRecord::Base
  has_many :time_tracks
end

class TimeTrack < ActiveRecord::Base
end

# -------------

TimeTrack.delete_all
User.delete_all

threads = []
wait_for_it = true

user = User.create

20.times do
  date = Date.new(2020, 1, rand(1..4))

  threads << Thread.new(date) do
    true while wait_for_it

    begin
      user.time_tracks.create!(date: date, task_id: 1, hours: 10)
    rescue ActiveRecord::RecordNotUnique
      user.time_tracks.find_by(date: date, task_id: 1)&.update(hours: 10)
    end
  end
end

wait_for_it = false

threads.each(&:join)

not_unique_count =
  TimeTrack.from(
    TimeTrack.group(:date, :user_id).having('COUNT(*) > 1').select(:date, :user_id)
  ).select('COUNT(subquery.date) AS not_unique').to_a.first.not_unique

puts "\nFound #{not_unique_count} not unique tracks"

## 9_bench.rb
begin
  require "bundler/inline"
rescue LoadError => e
  $stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
  raise e
end

gemfile(true) do
  source "https://rubygems.org"

  gem "rails"
  gem "pg"
  gem "benchmark-ips"
end

require "active_record"
require "action_controller/railtie"

ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "uniq-db-test", pool: 100)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  enable_extension "plpgsql"

  create_table "time_tracks", force: :cascade do |t|
    t.bigint "user_id", null: false
    t.bigint "task_id", null: false
    t.integer "hours", null: false
    t.date "date", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false

    t.index ["user_id", "task_id", "date"], name: "index_time_tracks_on_keys_and_date", unique: true
  end

  create_table "users", force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "time_tracks", "users"
end

class User < ActiveRecord::Base
  has_many :time_tracks
end

class TimeTrack < ActiveRecord::Base
end

# -------------

require 'benchmark/ips'
ActiveRecord::Base.logger = nil

User.delete_all

user = User.create

puts "\nWith many successful INSERTs\n"

Benchmark.ips do |x|
  x.config(time: 15, warmup: 3)

  TimeTrack.delete_all
  date = Date.new(2000, 1, 1)

  x.report 'create_or_find_by!' do
    user.time_tracks.create_with(hours: 10).create_or_find_by!(date: date, task_id: 1)
    date += 1.day
  end

  TimeTrack.delete_all
  date = Date.new(2000, 1, 1)

  x.report 'with_lock + find_or_create_by!' do
    user.with_lock do
      user.time_tracks.create_with(hours: 10).find_or_create_by!(date: date, task_id: 1)
      date += 1.day
    end
  end

  TimeTrack.delete_all
  date = Date.new(2000, 1, 1)

  x.report 'create! + rescue' do
    date += 1.day

    begin
      user.time_tracks.create!(date: date, task_id: 1, hours: 10)
    rescue ActiveRecord::RecordNotUnique
      user.time_tracks.find_by(date: date, task_id: 1)&.update(hours: 10)
    end
  end

  x.compare!
end

TimeTrack.delete_all

puts "\nWhen row already exists\n"

Benchmark.ips do |x|
  x.config(time: 15, warmup: 3)

  date = Date.new(2020, 1, 1)
  user.time_tracks.create_with(hours: 10).create_or_find_by!(date: date, task_id: 1)

  x.report 'create_or_find_by!' do
    user.time_tracks.create_with(hours: 10).create_or_find_by!(date: date, task_id: 1)
  end

  x.report 'with_lock + find_or_create_by!' do
    user.with_lock do
      user.time_tracks.create_with(hours: 10).find_or_create_by!(date: date, task_id: 1)
    end
  end

  x.report 'create! + rescue' do
    user.time_tracks.create!(date: date, task_id: 1, hours: 10)
  rescue ActiveRecord::RecordNotUnique
    user.time_tracks.find_by(date: date, task_id: 1)&.update(hours: 10)
  end

  x.compare!
end
	begin
	require "bundler/inline"
	rescue LoadError => e
	$stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
	raise e
	end

	gemfile(true) do
	source "https://rubygems.org"

	gem "rails"
	gem "pg"
	end

	require "active_record"
	require "action_controller/railtie"

	ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "uniq-db-test")
	ActiveRecord::Base.logger = Logger.new(STDOUT)

	ActiveRecord::Schema.define do
	enable_extension "plpgsql"

	create_table "time_tracks", force: :cascade do \|t\|
	t.bigint "user_id", null: false
	t.bigint "task_id", null: false
	t.integer "hours", null: false
	t.date "date", null: false
	t.datetime "created_at", precision: 6, null: false
	t.datetime "updated_at", precision: 6, null: false
	end

	create_table "users", force: :cascade do \|t\|
	t.datetime "created_at", precision: 6, null: false
	t.datetime "updated_at", precision: 6, null: false
	end

	add_foreign_key "time_tracks", "users"
	end

	class User < ActiveRecord::Base
	has_many :time_tracks
	end

	class TimeTrack < ActiveRecord::Base
	end

	# -------------

	TimeTrack.delete_all
	User.delete_all

	user = User.create

	100.times do
	date = Date.new(2020, 1, rand(1..10))
	user.time_tracks.create_with(hours: 10).find_or_create_by!(date: date, task_id: 1)
	end

	not_unique_count =
	TimeTrack.from(
	TimeTrack.group(:date, :user_id).having('COUNT(*) > 1').select(:date, :user_id)
	).select('COUNT(subquery.date) AS not_unique').to_a.first.not_unique

	puts "\nFound #{not_unique_count} not unique tracks"