Rails: make sure you have proper DB indexes for your model’s unique validations

In this article I’m going to explain you what performance issues you may have when use unique validations in Rails models.

Problem

Let’s start with a small example. We have the following User model:

class User < ApplicationRecord 
validates :login, uniqueness: true
end
class CreateUsers < ActiveRecord::Migration[5.1]
def change
create_table :users do |t|
t.string :login
t.string :full_name
t.timestamps
end
end
end
class UsersController < ApplicationControllerdef create
@user = User.new(user_params)
unless @user.valid?
render json: {
status: 422,
message: "Validation error",
errors: @user.errors
}.to_json
return
end
@user.save! render json: {
status: 200,
message: "Created"
}.to_json
endprivatedef user_params
params.require(:user).permit(:login)
end
end
curl -X POST http://0.0.0.0:3000/users -d user[login]=garry
{“status”:422,”message”:”Validation error”,”errors”:{“login”:[“has already been taken”]}}
Started POST “/users” for 127.0.0.1 at 2017–09–13 16:16:01 +0300
Processing by UsersController#create as */*
Parameters: {“user”=>{“login”=>”garry”}}
User Exists (0.2ms) SELECT 1 AS one FROM “users” WHERE “users”.”login” = ? LIMIT ? [[“login”, “garry”], [“LIMIT”, 1]]Completed 200 OK in 5ms (Views: 0.1ms | ActiveRecord: 0.2ms)
sqlite> EXPLAIN QUERY PLAN SELECT 1 AS one FROM “users” WHERE “users”.”login” = “garry” LIMIT 1;0|0|0|SCAN TABLE users

Solution

You actually have 2 ways to fix it:

#1. Add proper DB index

To fix it you have to add an index by login field. The following migration does it:

class AddLoginIndexToUsers < ActiveRecord::Migration[5.1]
def change
add_index :users, [:login]
end
end
sqlite> EXPLAIN QUERY PLAN SELECT 1 AS one FROM “users” WHERE “users”.”login” = “garry” LIMIT 1;0|0|0|SEARCH TABLE users USING COVERING INDEX index_users_on_login (login=?)

#2. Move unique validation to DB level

This time we can go further — we will move our unique validation to DB level. Let’s remove this line from Users model “validates :login, uniqueness: true” and add the following migration:

class AddLoginUniqIndexToUsers < ActiveRecord::Migration[5.1]
def change
remove_index :users, column: :login
add_index :users, [:login], unique: true
end
end
def create
@user = User.new(user_params)
begin
@user.save!

render json: {
status: 200,
message: “Created”
}.to_json
rescue ActiveRecord::RecordNotUnique => e
render json: {
status: 422,
message: “Validation error: login has already been taken”,
}.to_json
end
end
Started POST “/users” for 127.0.0.1 at 2017–09–13 16:59:01 +0300
Processing by UsersController#create as */*
Parameters: {“user”=>{“login”=>”garry”}}
(0.0ms) begin transactionSQL (0.4ms) INSERT INTO “users” (“login”, “created_at”, “updated_at”) VALUES (?, ?, ?) [[“login”, “garry”], [“created_at”, “2017–09–13 13:59:01.333136”], [“updated_at”, “2017–09–13 13:59:01.333136”]](0.0ms) rollback transactionCompleted 200 OK in 11ms (Views: 0.1ms | ActiveRecord: 1.3ms)

Which solution to choose

In my opinion, the first one is good because it obviously shows that our model has an unique login validation hence you do not need to dig into schema.rb to understand this. But 2nd solution is better because it does not perform an extra query to DB. So choose that one the suites better for you. If you need extra performance then probably 2nd approach is better for you.

How to identify these issues in your existing project

I made a small gem that helps find above issues with missed indexes: https://github.com/soulfly/unique_validation_inspector

$ rake inspect_unique_validationsYou have the following unique validations:Model ‘User’:
[:login] (scope ‘’). Index exists: false

Conclusion

You can download this project source code https://github.com/soulfly/unique_validation_inspector_demo

Software craftsman, RTC pro, love programming and clouds, XMPP, WebRTC, secure messaging, chat bots

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store