Eliminating N+1 Queries in Ruby on Rails: A Complete Guide

N+1 queries are one of the most common performance bottlenecks in Rails applications. Learn how to identify, fix, and prevent them with practical examples from production systems.

What Are N+1 Queries?

N+1 queries occur when your application makes one query to fetch a collection of records, and then makes an additional query for each record in that collection. For example, fetching 100 users and then making 100 separate queries to get each user's posts.

Identifying N+1 Queries

Using Bullet Gem

The Bullet gem is an excellent tool for detecting N+1 queries in development:

# Gemfile
gem 'bullet', group: 'development'

# config/environments/development.rb
config.after_initialize do
  Bullet.enable = true
  Bullet.alert = true
  Bullet.console = true
end

Reading Rails Logs

Look for patterns like this in your logs:

User Load (0.5ms)  SELECT "users".* FROM "users"
Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 1]]
Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 2]]
Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 3]]

Solutions

1. Eager Loading with includes

The most common solution is to use includes to load associations:

# Bad - N+1 query
users = User.all
users.each do |user|
  puts user.posts.count
end

# Good - Eager loading
users = User.includes(:posts).all
users.each do |user|
  puts user.posts.count
end

2. Using preload

When you know you'll need the associations but don't need to query on them:

users = User.preload(:posts, :comments).all

3. Using eager_load

For LEFT OUTER JOIN queries:

users = User.eager_load(:posts).where(posts: { published: true })

4. Counter Caches

For simple counts, use counter caches:

class Post < ApplicationRecord
  belongs_to :user, counter_cache: true
end

# Migration
add_column :users, :posts_count, :integer, default: 0

Real-World Example

Here's a real optimization from a production system:

# Before - 1 + 100 queries
def index
  @users = User.all
  # In view: user.posts.count causes N+1
end

# After - 2 queries
def index
  @users = User.includes(:posts).all
end

Nested Associations

For deeply nested associations:

# Load users with posts and their comments
users = User.includes(posts: :comments).all

# Load users with multiple associations
users = User.includes(:posts, :profile, comments: :likes).all

Best Practices

• Always use Bullet in development to catch N+1 queries early
• Test with production-like data to identify performance issues
• Profile your queries using tools like rack-mini-profiler
• Use counter caches for simple counts
• Add database indexes on foreign keys
• Monitor query performance in production

Conclusion

N+1 queries can severely impact application performance. By using eager loading, counter caches, and monitoring tools like Bullet, you can significantly improve your Rails application's performance and provide a better user experience.

Remember: always profile your queries and test with realistic data volumes to catch these issues before they reach production.