How To Find, Fix, and Prevent, N+1 Queries on Rails

A shortlist of actionable advice on how to get rid of N+1 Queries in a Rails application

This article is a part of the series: Guide to Rails Performance ⚡️

What's an N+1 query? 🤷‍♂️

An N+1 query is a type of performance issue that often occurs when using an object-relational mapper (ORM) such as Active Record in a Rails application. It occurs when the ORM executes a separate query for each of the objects coming from a list of objects, instead of using a single query to load all of them at once.

For example, consider the following code:

# Get all posts
posts = Post.all

# For each post, a new author query will be triggered
posts.each do |post|
  post.author.name
end

If the posts table has 1000 records, this code will execute 1000 separate queries to get the author for each post, which can be very inefficient. This is an example of an N+1 query.

N+1 queries can occur when you are working with associations, such as has_many or belongs_to if you are not careful about how you load the associated objects.

N+1 queries can also greatly reduce the performance of a Rails application, especially if the queries are executing expensive operations like JOINs or aggregations. To optimize the performance of a Rails application, it is important to identify and fix any N+1 queries that are causing performance issues. That's exactly what we'll talk about in this post!

How to identify N+1 queries in a Rails app? 🕵🏻‍♂️

There are several ways you can detect N+1 queries in a Rails application:

1. Using a tool like the bullet gem (or Rails built-in strict loading mode), which can automatically detect and alert you of N+1 queries.
2. Using NewRelic to monitor the performance of your application and, while on it, identify N+1 queries.
3. Similarly to New Relic, you can locally use both the rack-mini-profiler and the stackprof gems to plot flame graphs and, thus, detect N+1s.
4. Manually review your application’s log files and look for repeated queries that are being executed.

Keep in mind that it’s not always possible to completely eliminate N+1 queries from a Rails application. Still, by using these tools and techniques you should be able to identify the N+1 queries causing performance issues.

How to fix N+1 queries on Rails? 👨🏻‍🏭

There are several common techniques you can use to fix N+1 queries in a Rails application:

Eager loading associations (preferred)

Use the includes method to eager load associated records, which will reduce the number of queries needed to load the data.

Attention tho! There are three different methods allowing you to achieve pretty much the same results, but by doing completely different things. These methods are preload, eager_load, and includes.

As a rule of thumb, if you don't know what you're doing, go with includes as it will decide on its own whether to split your query into two subqueries, or a single query with a left outer join. In any case, I strongly recommend you to read this article from BigBinary on the topic.

Russian doll caching (a bold alternative)

It's a controversial opinion, but one could argue that N+1 queries are a feature in Rails. And that is exactly what David, the creator of Ruby on Rails, explains in this live discussion about performance with Nate Berkopec, a specialist and renowned author on Rails performance:

“N+1 is a feature” - DHH
https://www.youtube.com/watch?v=ktZLpjCanvg&t=267s (at 4:27)

If you opt for that, you can try the Russian Doll Caching strategy.

Russian Doll Caching works by using fragment caching to cache the entire rendered output of a page or partial, along with any nested partials that it includes. It optimizes the performance by reducing the number of database queries needed to render a page, as well as reducing the amount of processing needed to generate the HTML output.

Here is an example of how you can use Russian Doll Caching in a Rails application:

# app/views/posts/show.html.erb

<% cache @post do %>
  <h1><%= @post.title %></h1>
  <p><%= @post.body %></p>
  <% cache @post.comments do %>
    <% @post.comments.each do |comment| %>
      <% cache comment do %>
        <h2><%= comment.title %></h2>
        <p><%= comment.body %></p>
      <% end %>
    <% end %>
  <% end %>
<% end %>

In this example, the posts/show view includes a list of comments for the post. When the view is rendered:

1. The outermost cache block caches the entire rendered output of the view, using the @post object as the cache key.
2. The second cache block then caches the rendered output of the comments partial, using the @post.comments object as the cache key.
3. Finally, the innermost cache block caches the rendered output of each individual comment, using the comment object as the cache key.

This allows the entire rendered output of the view to be cached, along with the rendered output of any nested partials, using a hierarchy of cache keys. When the view is rendered again, the cache will be checked for each level of the hierarchy, and the cached output will be used if it is available, reducing the number of database queries needed to render the view.

However, there are some trade-offs to consider:

1. Cache invalidation: With Russian Doll Caching, you need to be careful and properly plan the cache key hierarchy to ensure that the cache keys are properly invalidated when the data changes. Rails' got your back, but if you do not invalidate the cache keys correctly, you may end up with stale data being displayed to users.
2. Memory usage: Caching the entire rendered output of a page or partial can consume a significant amount of memory, especially for pages with large amounts of data or complex layouts. You might need to monitor the memory usage of your application and ensure that you have enough memory to accommodate the cache.
3. Costs: If you are using Russian Doll Caching to cache large amounts of data in a high-traffic application, you may need to use a large number of Redis instances or a high-memory machine to accommodate the cache. This can increase the cost of running the application.
4. Deployment considerations: When you deploy a new version of your application, you may need to clear the cache to ensure that the new version is properly displayed to users. This can cause temporary performance degradation while the cache is warming up again.

Overall, Russian Doll Caching can be a powerful tool for optimizing the performance of a Rails application, but it is important to carefully evaluate the trade-offs and ensure that it is the right approach for you.

Database views (to be avoided)

As a last resource, you could also opt to create a database view that pre-aggregates data which can then be more easily reused by the view.

For example, suppose you have a posts table and a comments table, and you want to retrieve a list of all posts along with the number of comments for each post. Instead of executing a separate query for each post to count the comments, you can create a view that pre-joins the posts and comments tables and aggregates the information you need into a comment_count column.

IMHO, for the vast majority of cases, this solution is not desirable. Creating and maintaining database views come with a high complexity cost. You will need to write custom SELECT statements to define the views, then ensure that the views are updated whenever the underlying data changes.

Vanilla Active Record is plenty!
(and here's another great article if you missed the ref)

How to prevent N+1 queries on Rails? ‍👨🏻‍⚕️

Here are some strategies you can use to prevent N+1 queries in a Rails application:

Use the bullet gem (old but gold)

The bullet gem can automatically detect and alert you to N+1 queries in your application. It also suggests ways to optimize the queries, such as by using the already mentioned includes. However, if you're using a more recent version of Rails, the next option might work best for you.

Use strict_loading (preferred)

Strict loading was introduced in Rails 6.1. Prior to Rails 6.1, there was no built-in way to enable strict loading in a Rails application. You would have to manually check if the association had been loaded before accessing it, or use a tool like the bullet gem to detect N+1 queries.

By default, Rails will not raise an error if you try to access an association that has not been loaded. However, you can manually use the strict_loading option to cause an error to be raised if you try to access an unloaded association. This can help prevent N+1 queries by alerting you about cases where you accidentally executed unnecessary queries.

To use strict loading in a Rails application, you can set the strict_loading option to true for the association you want to enable strict loading for.

For example, suppose you have a Post model that has many Comment models, and you want to enable strict loading for the comments association. You can set the strict_loading option as follows:

# Enable strict loading for the comments association on the Post model
class Post < ApplicationRecord
  has_many :comments, strict_loading: true
end


# This will raise a StrictLoadingError exception, because the comments have not been loaded
Post.first.comments.each do |comment|
  puts comment.body
end

If you don't want to activate strict loading mode for all queries coming from this association, you can also do it on a per-query basis:

post = Post.strict_loading.first

# This will raise an ActiveRecord::StrictLoadingViolationError
post.comments.to_a

You could also choose to activate strict loading for the whole app, or only on logs, it's really up to you. If you're interested in more details about these options, once again, I recommend another BigBinary article.

Was this article helpful to you? Consider signing up for Medium:

Join Medium with my referral link - Flavio Wuensche

Or just hit that subscribe button below ⚡️