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.


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

class CreateUsers < ActiveRecord::Migration[5.1]  
def change
create_table :users do |t|
t.string :login
t.string :full_name

and the following UsersController:

def create
@user = User.new(user_params)
unless @user.valid?
render json: {
status: 422,
message: "Validation error",
errors: @user.errors
@user.save! render json: {
status: 200,
message: "Created"
endprivatedef user_params

Now let’s try to create 2 users with the same login.

When perform a POST request 2nd time you will get the following error:

This is correct, but let’s check what is going on under the hood — let’s look into Rails console. The following set of logs are there:

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)

As you can see there is an extra request to DB which tries to find a user with provided login. This is exactly how this line “validates :login, uniqueness: true” works. Before each ‘record save’ it performs an additional request to DB to validate the provided data for uniqueness.

Everything works super fast when you have 10, 100, 1000 users. And you even will not notice any performance issues.

But things will start going worse and worse with more users you have. Performance of this API endpoint will degrade.

The following ‘EXPLAIN’ output proves that — this request does not use any indexes hence it scans the whole Users table:

0|0|0|SCAN TABLE users


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:

Let’s check now what the ‘EXPLAIN’ command tells us:

0|0|0|SEARCH TABLE users USING COVERING INDEX index_users_on_login (login=?)

Bingo! Now our validation will work quite fast and does not matter how many users we have. Because now we use an index!

#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:

and modify a little bit our controller’s ‘create’ action:


render json: {
status: 200,
message: “Created”
rescue ActiveRecord::RecordNotUnique => e
render json: {
status: 422,
message: “Validation error: login has already been taken”,

Now let’s try to create a new record with existing login — we are getting the same validation error response. But let’s look under the hood, what’s going on in Rails console:

(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)

There is no extra DB query now, but effect is the same — it works quite fast the big number of users :) .

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.

Well, sometimes things can go wrong when use Rails uniqueness validations and you actually will find NOT unique data in your DB :( . You can find more information why it happens in this article . From my own experience, we recently migrate from Rails uniqueness validations to unique DB indexes. With 30M records we found 20K not valid and our Rails migration failed. That was a lot of pain then to clean it… I hope the Rails team will come up with some better solution in near future, otherwise this feature is not strong enough and can lead to many issues. So I recommend to use 2nd solution from day1.

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

Let’s run it on an initial version of the project (w/o indexes):

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

It tells you that you do not have a proper index for ‘login’ validation. So now you know how to add it.

It’s very useful to use this gem in a big projects where it’s just impossible to analize what indexes are missed.


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

Please ask me if you have any questions. Cheers!

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