4 Non-standard Ways to Fix N+1 SQL Queries in Rails

I’m not sure if the world needed another post about N+1 queries in Ruby on Rails. To make up for the cliche topic, I’ll describe less common solutions to this problem. Read on if you want to learn how to reduce the number of cascading N+1 SQL queries without using includes or additional table join operations.

N+1 queries 101

N+1 queries are the top performance killer for Rails applications. ActiveRecord makes it just too easy to misuse your database structure. Consider the following code:

# app/models/post.rb

class Post < ApplicationRecord
  belongs_to :user

  def author_name
    user.name
  end
end

# app/models/user.rb

class User < ApplicationRecord
  has_many :posts
end

# app/controllers/posts_controller.rb

class PostsController < ApplicationController
  def index
    @posts = Post.published
  end
end

app/views/posts/index.html.erb

<ul>
  <% @posts.each do |post| %>
    <li>
      <%= post.title %>
      <%= post.author_name %>
    </li>
  <% end %>
</ul>

With the above implementation, you’ll see the following SQL queries in your logs:

  Post Load SELECT "posts".* FROM "posts" WHERE "state" = "published"
  User Load SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  User Load SELECT "users".* FROM "users" WHERE "users"."id" = 2 LIMIT 1
  User Load SELECT "users".* FROM "users" WHERE "users"."id" = 3 LIMIT 1
  ...

An additional User Load SQL query is generated for every single loaded post. Since ActiveRecord needs additional data from another table to render the page, it will implicitly run those queries. If you’re loading dozens of objects, a single request could generate hundreds of SQL queries overwhelming your database and killing the performance.

To fix it, you’ll have to change your controller’s code to use so-called eager loading:

class PostsController < ApplicationController
  def index
    @posts = Post.published.includes(:user)
  end
end

As a result, you’ll see the following SQL logs:

  Post Load SELECT "posts".* FROM "posts" WHERE "state" = "published"
  User Load SELECT "users".* FROM "users" WHERE "users"."id" IN (...)

You’ll notice that regardless of the number of objects, only two queries are now generated. One fetches all the required posts, the other its users.

Now that we’ve covered the basics, let’s discuss the promised non-standard solutions to the N+1 problem.

Cache and reuse duplicate queries

Spotting reusable query results is invaluable in optimizing the performance of your app. Consider the app with the following structure:

Posts have many Comments that belong to Users. Let’s assume that we have a page where we want to display users’ data and posts with comments.

app/controllers/posts_controller.rb

class PostsController < ApplicationController
  def index
    @posts = Post.published.includes(comments: :user)
    @users = User.active
  end
end

app/views/posts/index.html.erb

<div>
  <% @users.each do |user| %>
    <div>
      <%= user.nickname %>
      <%= user.email %>
    </div>
  <% end %>

  <% @posts.each do |post| %>
    <div>
      <%= post.title %>
      <%= post.content %>
      <%= post.comments.each do |comment| %>
        <%= comment.content %>
        <%= comment.user.nickname %>
      <% end %>
    </div>
  <% end %>
</div>

The above code is already eager loading all the required data and will generate four queries. One to load posts, one for comments, and two to load users’ data.

Assuming that users’ data loaded by two queries is identical, we could rewrite the above example in the following way:

app/controllers/posts_controller.rb

class PostsController < ApplicationController
  def index
    @posts = Post.published.includes(:comments)
    @users = User.active
    @users_cache = @users.reduce({}) do |agg, user|
      agg[user.id] = user
      agg
    end
  end
end

app/views/posts/index.html.erb

<div>
  <% @users.each do |user| %>
    <div>
      <%= user.nickname %>
      <%= user.email %>
    </div>
  <% end %>

  <% @posts.each do |post| %>
    <div>
      <%= post.title %>
      <%= post.content %>
      <%= post.comments.each do |comment| %>
        <%= comment.content %>
        <%= @users_cache.fetch(comment.user_id).nickname %>
      <% end %>
    </div>
  <% end %>
</div>

You render comments’ user data from an instance variable @users_cache generated from data fetched by a single query. This approach will cut an unnecessary Users query while still avoiding the N+1 issue.

Make sure always to measure the results of similar refactorings in production environment. In this particular example, we’ve removed an additional query. However, it might turn out that constructing the @cached_users hash could be slower than fetching the duplicate data using SQL.

I understand that the above example might seem a bit convoluted. However, in mature Rails apps, it’s not uncommon to fetch different nested models with interdependent relations to render a more complex HTML view.

You can check out this blog post for more info on how to speed up ActiveRecord performance by caching subqueries.

Use primitive values instead of objects

Let’s consider the following scope method:

class Post < ApplicationRecord
  scope :by_same_author, -> (post) {
    where(user_id: post.user.id)
  }
end

Can you spot what’s wrong?

The problem is that you cannot use this method without executing an additional query. You’re passing a full-blown ActiveRecord Post object that must instantiate another User object from its relation.

To minimize the number of unnecessary queries, you could rewrite this method like that:

class Post < ApplicationRecord
  scope :by_author, -> (user_id) {
    where(user_id: user_id)
  }
end

Now you’re passing primitive values. Fewer queries and less memory are needed to execute this method. Designing your public APIs on low-level objects will make them simpler to use and potentially more performant.

Add shortcut relationships

The Law of Demeter is not always strictly followed in legacy Rails apps. It’s not uncommon to encounter code like that:

class Post < ApplicationRecord
  belongs_to :user

  def author_last_active_at
    user.account.activity.last_active_at
  end
end

Executing this method will generate three additional SQL queries to fetch and instantiate required objects. These kinds of methods are the worse. Rendering even a dozen of objects could potentially overflow your database with hundreds of queries.

A potential solution to this issue is to add a shortcut to your database structure to account for data access requirements. If your Post model necessarily needs to access data from Account and additional queries are killing the performance, you can consider creating a direct connection between models. It’s kind of a dirty trick, but it could help you speed things up significantly for bottleneck endpoints.

With simplified relationships, the above example could now look like that:

class Post < ApplicationRecord
  belongs_to :user
  belongs_to :activity

  def author_last_active_at
    activity.last_active_at
  end
end

Watch out, since overusing this approach can quickly lead to your database structure becoming an unmaintainable mess.

Duplicate relationship data

An even more extreme variant of the above technique is duplicating data and putting it directly in a model that needs it. The upside is that it allows you to eliminate any additional queries completely. In this approach Post model itself is a caching layer for the Account model attribute.

However, it comes with a separate range of challenges for syncing data between models. You’ll have to update the Post object whenever his Account changes. I’d only resort to this technique for some extreme bottleneck endpoints for which reducing an additional query will make a difference.

Summary

Adding includes to your database call is usually the most straightforward and best choice for common N+1 issues. However, for some more rare and complex cases, these other techniques could be worth considering.