Finding and Fixing N+1 Queries in Rails for Good

An N+1 query is the performance bug that every Rails application grows into. It rarely shows up in development, where the database has fifty rows and everything feels instant. It shows up in production, six months later, as a page that takes four seconds to render and a database sitting at 90% CPU.

The pattern is simple: you load a collection with one query, then trigger one additional query per record while rendering. One query for the collection, N for its associations — hence N+1.

Recognising the shape

Here is the version everyone writes first:

# app/controllers/posts_controller.rb
def index
  @posts = Post.published.limit(25)
end

<%# app/views/posts/index.html.erb %>
<% @posts.each do |post| %>
  <article>
    <h2><%= post.title %></h2>
    <p>by <%= post.author.name %></p>
  </article>
<% end %>

That renders 26 queries: one for the posts, then one SELECT * FROM users WHERE id = ? for every single post. In the log it looks like this:

Post Load (1.2ms)  SELECT "posts".* FROM "posts" WHERE "posts"."published" = TRUE LIMIT 25
User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 7 LIMIT 1
User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 3 LIMIT 1
...

Each query is fast. That is exactly why this bug survives code review — nothing looks slow. The cost is the round trip, repeated twenty-five times, and it scales linearly with the page size.

Detecting them before your users do

Do not rely on reading logs. Add tooling that fails loudly.

Bullet

Bullet watches your queries and tells you when you should have eager loaded:

# Gemfile
group :development, :test do
  gem "bullet"
end

# config/environments/development.rb
config.after_initialize do
  Bullet.enable = true
  Bullet.bullet_logger = true
  Bullet.rails_logger = true
  Bullet.add_footer = true
end

The setting that actually changes behaviour is turning it on in tests and letting it raise:

# config/environments/test.rb
config.after_initialize do
  Bullet.enable = true
  Bullet.raise = true # fail the test suite on an N+1
end

# spec/rails_helper.rb
RSpec.configure do |config|
  config.before(:each) { Bullet.start_request if Bullet.enable? }
  config.after(:each) do
    if Bullet.enable?
      Bullet.perform_out_of_channel_notifications if Bullet.notification?
      Bullet.end_request
    end
  end
end

Now an N+1 is a failing build rather than a slow page.

Counting queries in a test

For hot paths, assert the query count directly. It documents the intent and catches regressions:

# spec/support/query_counter.rb
module QueryCounter
  def count_queries(&block)
    count = 0
    counter = ->(_name, _start, _finish, _id, payload) do
      count += 1 unless payload[:name].in?(%w[CACHE SCHEMA]) ||
                        payload[:sql].start_with?("BEGIN", "COMMIT")
    end
 
    ActiveSupport::Notifications.subscribed(counter, "sql.active_record", &block)
    count
  end
end

# spec/requests/posts_spec.rb
RSpec.describe "GET /posts" do
  include QueryCounter
 
  it "renders the index with a constant number of queries" do
    create_list(:post, 5, :published)
    expect(count_queries { get posts_path }).to be <= 4
 
    create_list(:post, 20, :published)
    expect(count_queries { get posts_path }).to be <= 4
  end
end

The second assertion is the important one. A constant query count as the data grows is the actual property you care about.

The three eager loading strategies

Rails gives you three tools, and they are not interchangeable.

`preload` — separate queries, always

Post.published.preload(:author).limit(25)

SELECT "posts".* FROM "posts" WHERE "posts"."published" = TRUE LIMIT 25
SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 3, 7, ...)

Two queries, regardless of the association. preload never joins, which means you cannot reference the association in a WHERE or ORDER BY:

# Raises: missing FROM-clause entry for table "users"
Post.preload(:author).where(users: { admin: true })

`eager_load` — a single LEFT OUTER JOIN

Post.published.eager_load(:author).where(users: { admin: true })

SELECT "posts"."id" AS t0_r0, ..., "users"."id" AS t1_r0, ...
FROM "posts"
LEFT OUTER JOIN "users" ON "users"."id" = "posts"."author_id"
WHERE "posts"."published" = TRUE AND "users"."admin" = TRUE

One query, and the joined table is filterable. The cost is row duplication: with a has_many, the parent columns repeat once per child, and Rails de-duplicates in Ruby. For a post with 200 comments, you ship the post's body 200 times over the wire.

`includes` — Rails decides

Post.published.includes(:author)

includes picks preload by default, and switches to eager_load if it detects that you referenced the table. That detection is the problem: it works when you use the hash form, and silently fails when you write a SQL string.

# Works — Rails sees the reference and switches to a join
Post.includes(:author).where(users: { admin: true })
 
# Breaks — Rails preloads, then PostgreSQL rejects the unknown table
Post.includes(:author).where("users.admin = TRUE")
 
# Fixed — declare the reference explicitly
Post.includes(:author).references(:author).where("users.admin = TRUE")

My rule: use preload when you are only rendering the association, and eager_load when you are filtering or sorting on it. Reach for includes only when you genuinely do not care, and never combine it with a raw SQL condition.

The cases people miss

Counting children

<%# One COUNT query per post %>
<%= post.comments.count %>

count always hits the database, even when the association is loaded. size uses the loaded collection if it has one:

<%# Uses the preloaded array — no query %>
<%= post.comments.size %>

If you only need the number and never the records, a counter cache is better than loading them:

class Comment < ApplicationRecord
  belongs_to :post, counter_cache: true
end

class AddCommentsCountToPosts < ActiveRecord::Migration[7.1]
  def change
    add_column :posts, :comments_count, :integer, null: false, default: 0
 
    reversible do |dir|
      dir.up do
        execute <<~SQL
          UPDATE posts SET comments_count = (
            SELECT COUNT(*) FROM comments WHERE comments.post_id = posts.id
          )
        SQL
      end
    end
  end
end

Now post.comments.size reads an integer column and issues no query at all.

Polymorphic associations

Polymorphic belongs_to cannot be joined, because the target table is only known per row. eager_load fails; preload works and issues one query per distinct type:

# One query for activities, then one per concrete type
Activity.preload(:subject).limit(50)

Nested and conditional loading

Preloading is recursive, and it accepts a hash:

Post
  .preload(:author, comments: [:author, { replies: :author }])
  .limit(25)

If you only need a subset of the children, associate a scope rather than filtering in Ruby:

class Post < ApplicationRecord
  has_many :comments
  has_many :approved_comments,
           -> { where(approved: true).order(created_at: :asc) },
           class_name: "Comment"
end

Post.preload(:approved_comments).limit(25)

Filtering a preloaded association in Ruby — post.comments.select(&:approved?) — is fine. Calling post.comments.where(approved: true) throws the preloaded records away and queries again.

`find_each` resets preloading

# The preload is discarded: find_each re-queries in batches
Post.preload(:author).find_each { |post| puts post.author.name }

Batch the ids and preload per batch instead:

Post.in_batches(of: 500) do |batch|
  batch.preload(:author).each { |post| puts post.author.name }
end

Loading only what you render

Eager loading fixes the query count but still loads every column. For an index page showing a title and an author name, that is wasteful:

Post
  .published
  .joins(:author)
  .select("posts.id", "posts.title", "posts.created_at", "users.name AS author_name")
  .limit(25)

One query, four columns, no association objects. The trade-off is that you get read-only records with an ad-hoc attribute, so keep this for genuinely hot endpoints rather than making it the default.

Where to start

If you inherit an application with a performance problem, work in this order:

Turn on Bullet in development and the test suite, with raise = true.
Add query-count assertions to your three slowest endpoints.
Fix the collection pages first — they scale with page size, so they degrade fastest.
Replace count with size on loaded associations, and add counter caches where you only ever need the number.
Only then look at column selection.

N+1 queries are not really a database problem; they are a problem of doing work in a loop that could have been done in a set. Once the tooling fails your build, they stop reaching production, and that is the fix that lasts.

Recognising the shape#

Detecting them before your users do#

Bullet#

Counting queries in a test#

The three eager loading strategies#

preload — separate queries, always#

eager_load — a single LEFT OUTER JOIN#

includes — Rails decides#

The cases people miss#

Counting children#

Polymorphic associations#

Nested and conditional loading#

find_each resets preloading#

Loading only what you render#

Where to start#