A Practical Guide to Fixing N+1 Queries in Rails

Ever deployed your Rails app only to find it crawling along like a snail? Chances are you’ve got some N+1 queries lurking in your code. Let’s fix that!

What’s an N+1 Query?

An N+1 query occurs when your code:

1. Loads a collection of records (that’s your N)
2. Then runs an additional query for each record in that collection (that’s your +1)

Here’s a classic example:

# This innocent-looking code is actually an N+1 disaster
@posts = Post.all
@posts.each do |post|
  puts post.author.name  # 💥 This triggers a separate query for EACH post
end

This code will execute the following SQL:

SELECT * FROM posts;  -- First query (N)
SELECT * FROM users WHERE id = 1;  -- +1 query for first post
SELECT * FROM users WHERE id = 2;  -- +1 query for second post
SELECT * FROM users WHERE id = 3;  -- +1 query for third post
# ... and so on

How to Spot N+1 Queries

1. Use the Rails Development Log

Watch your development.log while running your code. If you see the same query pattern repeating over and over, you’ve probably got an N+1.

2. Use the Bullet Gem

Add this to your Gemfile:

group :development do
  gem 'bullet'
end

Configure it in config/environments/development.rb:

config.after_initialize do
  Bullet.enable = true
  Bullet.alert = true
  Bullet.rails_logger = true
end

Bullet will notify you when it detects N+1 queries in your application.

Fixing N+1 Queries: The Three Tools

1. includes: Your Go-To Solution

Use includes when you need the associated data and aren’t sure which loading strategy is best:

# Before (N+1 problem):
def index
  @posts = Post.all
end

# In view:
<% @posts.each do |post| %>
  <%= post.author.name %>
  <%= post.comments.count %>
<% end %>

# After (Fixed):
def index
  @posts = Post.includes(:author, :comments)
end

2. preload: When You Need Separate Queries

Use preload when you want to force separate queries or when you need to load associations conditionally:

# Load authors and recent comments in separate queries
@posts = Post.preload(:author, :comments)

# Conditional preloading
@posts = Post.preload(:author)
@posts = @posts.preload(:comments) if params[:show_comments]

3. eager_load: When You Need to Filter on Associations

Use eager_load when you need to filter based on associated data:

# Find all posts with authors who are active
@posts = Post.eager_load(:author).where(authors: { active: true })

Real-World Examples

Example 1: Complex Associations

class BlogController < ApplicationController
  def index
    # Before (N+1 city):
    @posts = Post.includes(:author)
    
    # Better (includes nested associations):
    @posts = Post.includes(author: [:city, :profile])
                 .includes(comments: :user)
  end
end

Example 2: API Responses

class Api::V1::PostsController < ApiController
  def index
    # Prevent N+1 in your JSON responses
    @posts = Post.includes(:author, 
                          comments: [:user, :reactions],
                          tags: :category)
    
    render json: @posts, 
           include: ['author', 'comments.user', 'tags']
  end
end

Using Strict Loading to Prevent Future N+1s

Rails 6.1+ introduces strict loading to catch potential N+1 queries during development:

# In your model
class Post < ApplicationRecord
  belongs_to :author, strict_loading: true
  has_many :comments, strict_loading: true
end

# Or enable globally in config/application.rb
config.active_record.strict_loading_by_default = true

This will raise an error if you try to load an association that hasn’t been preloaded:

@post = Post.first
@post.author # Raises StrictLoadingViolationError

Best Practices

1. Use includes by Default: Start with includes unless you have a specific reason to use preload or eager_load.

2. Watch Your Scopes: Don’t forget about N+1 queries in model scopes: ```ruby

   Bad

   scope :with_author_name, -> { all.map { |post| post.author.name } }

Good

scope :with_author_name, -> { includes(:author) }

3. **Be Careful with JSON**: APIs are common sources of N+1 queries. Always check your serializers:
```ruby
# Bad
class PostSerializer < ActiveModel::Serializer
  attributes :id, :title, :author_name

  def author_name
    object.author.name  # N+1 alert!
  end
end

# Good
class PostsController < ApplicationController
  def index
    @posts = Post.includes(:author)
    render json: @posts
  end
end

1. Use Counter Caches for counts: ```ruby class Post < ApplicationRecord belongs_to :author, counter_cache: true end

Now you can use author.posts_count instead of author.posts.count

```

Conclusion

N+1 queries are one of the most common performance problems in Rails applications, but they’re also one of the easiest to fix once you know what to look for. Use Bullet in development, start with includes, and consider enabling strict loading to catch issues early. Your database (and your users) will thank you!

Remember: Performance optimization is an iterative process. Start by fixing the most obvious N+1 queries, measure the impact, and then move on to the next optimization.

Bonus SQL join types meme

Picture worth a thousand words.