How to Fix N+1 Queries in Rails: Patterns From Six Real Pull Requests

You know what an N+1 query is: one query to fetch a collection of users, then one more query per user to fetch their posts. That’s the tutorial version, and it’s what preload exists to fix.

Real codebases have nastier variants, and fixing them is where most of the actual work is. Here are the ones I have been fixing recently. Each pattern in this post is drawn from a pull request that was merged upstream into Forem, Discourse, OpenProject, or Gumroad, and each one comes with measured before-and-after query counts. Full write-ups with complete benchmarks live in the case studies below each section.

The N+1s that were actually there

Notice the shape of these. None of them is the naive users.each { |u| u.posts } from the textbooks. They are N+1s hiding inside serializers, N queries hiding behind count and exists?, redundant looks for data that was already in memory, and role checks issued through a gem that never asks whether the data is loaded.

When an endpoint is slow: rack-mini-profiler

The trigger in every one of these cases was the same: a page or endpoint was slow, and nobody knew where the time was going. The tool I reach for first is rack-mini-profiler, and the reason is that the very first step after “this endpoint is slow” is separating time spent in Ruby from time spent in the database.

Add it to the Gemfile after your database gems so it can patch the adapter — if it loads before them, no SQL will show up at all:

gem 'pg'
gem 'rack-mini-profiler'          # must come after pg/mysql2

# optional extras:
gem 'stackprof'                   # for the wall-time flamegraph

That’s all the setup Rails needs in development — every request is profiled and shown, no config file required. Visit the slow endpoint and you get a speed badge reporting total request time, the share of it spent in SQL, and — the first thing I look at — how many queries the page generated, broken down per layout and per partial, with a link from each one to the exact SQL and the line that triggered it. several queries against the same table back to back is the first tell that a loop over N records is hitting the database per record, and a database-dominated bar is the same signal before you’ve read a single query.

Expanding the request’s profile shows every SQL statement it executed, with elapsed times, plus the call stack that issued each one. That is where an N+1 announces itself: the same query shape, fired dozens or hundreds of times inside a loop. Two options make this more useful:

# config/initializers/mini_profiler.rb
Rack::MiniProfiler.config.backtrace_threshold_ms = 1   # capture a backtrace for any slow query

and, for a wall-time flamegraph of the whole request (requires the stackprof gem), append ?pp=flamegraph to the URL. The vertical axis is stack depth, the horizontal axis is time, and each legend line’s percentage is the share of the request spent inside that stack frame — so a serializer or ORM showing up at 20-30% tells you where to look before you read a single frame. One wrinkle that matters for this post: several of the endpoints above are JSON APIs, and ?pp=flamegraph replaces the HTML response outright. For non-HTML requests use ?pp=async-flamegraph instead — the API responds as normal and the flamegraph is stored for viewing from the profiler’s UI afterwards. (There is also ?pp=help, which lists all of these URL triggers.)

The flamegraph shows which code path issues the queries — controller, serializer loop, model method — which is exactly the information the fingerprint count in the next section identifies. In the OpenProject case, this is how I confirmed the work-packages endpoint was where the query volume lived and how I tied the redundant ProjectType / Variant SQL to the type_variant call in the serializer.

A few practical notes:

• Profile production mode against production-scale data. A Rails app runs 3-5x slower in development thanks to reloading and per-request asset work, and small development databases quietly change the problem: a query that returns 100 rows locally returns 100,000 in production, and that is where the repeated queries hurt. When an endpoint actually matters (and every one in this post does), boot the app in production mode against a sanitized copy of the production database instead of trusting development timings.

• The results are per-process, so in development (a single booted server) everything a request does shows up intact — in Rails that defaults to files under tmp/miniprofiler. In production it requires shared storage (Redis/Memcache multi-server) and explicit authorization:

  before_action do
    if current_user&.admin?
      Rack::MiniProfiler.authorize_request
    end
  end
  

• Snapshot sampling handles the “rarely slow” endpoint: with Rack::MiniProfiler.config.snapshot_every_n_requests = 100, one in a hundred requests is profiled invisibly and stored to be reviewed later at /rack-mini-profiler-resources/snapshots — no need to reproduce a slow request on demand.

• It profiles at the SQL and call-stack level, not at the query-plan level. Once you’ve found the repeated queries here, read their EXPLAIN (ANALYZE) separately — see my post on decoding EXPLAIN (ANALYZE) output.

Count the queries, not just the latency

The OpenProject case is the warning here. The work packages endpoint ran 922 SQL events for a response of 20 work packages — but 827 of them were served by Rails’ query cache, so the endpoint’s total duration looked far less bad than the query volume suggested. Wall-clock time lies to you whenever the query cache or a warm connection pool is in the picture.

What does not lie is counting queries. In all six cases I measured with the same primitive: subscribe to Rails’ SQL notifications and count.

queries = []
ActiveSupport::Notifications.subscribed(
  ->(*, payload) { queries << payload[:sql] }, "sql.active_record"
) do
  # exercise the code path
end

queries.size                        # SQL events
queries.tally.values.sum { |s| s - 1 } # duplicate query fingerprints

The Discourse endpoint is a good example of the fingerprint count doing its job: a page of 75 active users produced 159 SQL events with 148 duplicate fingerprints. Duplicate fingerprints — the same query text firing over and over — is the tell for any class of problem in this post, not just classical N+1.

The same fingerprint trick is how you find the problem in your own app before you fix it: exercise a slow index page or API response in a script, tally the SQL, and sort by frequency. My posts on strict loading and reading EXPLAIN (ANALYZE) cover two more tools in the finding toolkit; strict loading is especially useful here, because it raises on the lazy loads you forget about. The always-on development-time guard, Bullet, gets its own section next.

Catch the next one in development: Bullet

Everything above is reactive: the endpoint is already slow, then you profile, then you count, then you fix. The tool that changes that ordering is the Bullet gem — it hooks ActiveRecord’s association access paths in the running app, so an N+1 announces itself in the request that produces it, while you’re still on the page you just wrote.

The setup is small:

# Gemfile — added after your ORM gem, since it hooks into ActiveRecord
gem 'bullet', group: 'development'

then

bundle exec rails g bullet:install

which writes the initializer and installs Bullet::Rack, which opens a request context around every web request. Trim it to the notification channels you want — nothing fires until you say so:

config.after_initialize do
  Bullet.enable         = true
  Bullet.bullet_logger  = true   # log/bullet.log
  Bullet.rails_logger   = true   # the dev log too
  Bullet.console        = true   # the browser console
  Bullet.raise          = true   # make the warning an exception
end

When a per-record association query fires, you get the model and association, the includes line it wants, and the stack of the loop that read it:

USE eager loading detected:
  Sale => [:product_review]
  Add to your query: .includes([:product_review])

That is the same information the fingerprint count identifies — model, association, and the line that is reading it — but delivered in the request it happened in, instead of the next profiling session.

Three detectors run by default, each individually disable-able (Bullet.n_plus_one_query_enable, Bullet.unused_eager_loading_enable, Bullet.counter_cache_enable):

• N+1 queries. A per-record read is flagged only when the record owning it was itself loaded from a multi-row result in this request — a lone find followed by one lazy load is marked exempt — so the radar aims at exactly the index-page shape that hurts. And counts as a read: the association reader and the collection proxy query methods count, size, empty?, any?, include? — a map { |product| product.alive_variants.count } inside a serializer is exactly the thing it exists to catch.
• Unused eager loading. The half that matters for this post: if you includes an association the request never touches, it says AVOID eager loading detected: Post => [:comments] — remove it from your query. The Sales API tree grew to eight levels, and keeping it exact instead of a superset is part of the ongoing maintenance; this is the detector that keeps that honest.
• Counter cache candidates. A per-record size on an unloaded collection association (post.comments.size in a view) gets a “Need Counter Cache” suggestion. Treat that suggestion as conservative: a cache column is a denormalized count, and a scoped number — live variants of live categories, active users only — does not map cleanly onto one. The one-grouped-query-into-a-Set from Pattern 2 is usually the better answer.

What it does not see matters just as much: the hooks sit on ActiveRecord’s association access paths, so a per-record query that never touches them stays invisible — hand-built grouped relations like the one Pattern 2’s fix settled on, and scoped relations that a gem assembles inside its own code, like Rolify’s role checks. Bullet is a guard against the class of association N+1s, not a general query-volume monitor, and it shouldn’t be cited as one.

A few practical notes:

• It is a development (or custom staging/profile) environment tool, by its own guidance — the last thing you want is a client seeing the alert. In development, Bullet.raise = true is the setting that changes behavior: a page you just wrote with an N+1 in it crashes with the fix spelled out at the top of the exception, instead of shipping quietly.

• In tests it becomes a strict mode for the whole suite. Enable it in the test environment with Bullet.raise = true and any spec that triggers a per-record association read fails — including for endpoints your query-count spec does not cover:

  # config/environments/test.rb
  config.after_initialize do
    Bullet.enable        = true
    Bullet.bullet_logger = true
    Bullet.raise         = true
  end
  

  Controller and integration tests work automatically through the middleware; model tests and other non-request code need the manual pair:

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

  (one-off scripts and background jobs can wrap the block in Bullet.profile { ... } instead.)

• Keep the safelist small and deliberate. A flagged read you consciously do not want to preload — a report page whose parent list is short, a path that lives inside a gem you cannot change — goes on the safelist rather than a monkey-patched method:

  Bullet.add_safelist :type => :n_plus_one_query, :class_name => "Product", :association => :alive_variants
  

  Every entry is a place you chose to accept per-record queries. For a one-off action, around_action with Bullet.skip { yield } is the thread-safe escape hatch; do not toggle Bullet.enable at runtime — it is a global flag, and not thread-safe.

• It is the targeted version of strict_loading!. Strict loading raises on any lazy load, including legitimate single-record ones, which is why it is a per-endpoint tool. Bullet only fires when the lazy load has the N+1 shape — a multi-row parent in the same request — which is why it can run app-wide in development and in tests without making you pay a tax on every show page.

Put the toolkit together and the loop closes: the profiler tells you where the time is, the fingerprint count tells you how big the problem is, the query-count spec pins the fix to an endpoint, and Bullet is what tells you on the day you break it.

The default Rails fix, and why real N+1s outgrow it

Rails has a first-class answer to the textbook N+1: eager loading.

User.all                        # 1 query
users = User.all.map { |u| u.posts }   # +1 query per user = N+1

users = User.preload(:posts)    # 2 queries total

preload collects the associated ids after the parent query and fetches them in a single WHERE id IN (...) query, then loads them into each record’s association cache in memory. includes(:posts) is the lazy version of the same mechanism — Rails picks preload unless the query actually needs a join. eager_load(:posts) forces the join form (LEFT OUTER JOIN) and loads both tables in one query, which is the right tool when you have to filter, order, or count on the associated columns in the SQL itself:

User.eager_load(:posts).where(posts: { published: true }).order('posts.created_at DESC')

Rails also ships three defensive layers that only catch N+1s after they’ve happened:

• The query cache serves repeated identical queries from in-memory within a request instead of executing them. This is why N+1s are hard to feel: the OpenProject endpoint above ran 922 events that the cache absorbed 827 of, so the extra 762 lookups looked like one or two unique queries instead of hundreds, and the page merely felt “a bit slow”. The cache hides the problem without fixing it — which is exactly why it tends to linger until query volume at peak data size makes it impossible to ignore.
• The debug-mode N+1 warning. In development, when Rails sees you reading an unloaded association while other records of the same class are being enumerated, it logs something like Performing an additional database query to be able to load your association "posts" (this will be a N+1 query!) and points you at preload/includes. It’s a decent tripwire, but it has no visibility into per-record count, exists?, or model methods that build their own query — and it only fires in development.
• strict_loading! makes any lazily-triggered association query raise instead of running, which you can turn on per-query for the endpoints you’re tuning. See my post on using strict loading to prevent N+1.

And that’s where the default toolkit stops. Fixing an N+1 with preload requires knowing exactly which associations the code path touches, and in every case in this post that path included a serializer, a grouping query, a model helper, or a gem — places where preload alone is either not enough or doesn’t apply. The default layers above are still worth keeping on: strict_loading! during work on an endpoint, the debug warning, and the query cache at least for reads. But the actual fixes in this post are the patterns that follow.

Pattern 1: Preload exactly what serialization touches

The Gumroad Sales API already preloaded some associations for a page of sales — but the serializer then touched eight more: buyer, product, variants, custom fields, followers, upsells, shipments, reviews. Ten full sales cost 155 SQL events.

The fix was to expand the controller’s preload tree to match what Purchase#as_json actually reads:

:offer_code,
:shipment,
:product_review,
{ link: [:user, :variant_categories_alive, :product_review_stat] },
{ upsell_purchase: [:upsell, :selected_product,
                    { upsell_variant: :selected_variant }] },
:refunds,

Two details from that PR are worth copying.

Batch the non-association lookups too. Follower status was being resolved once per sale. The controller now resolves the whole page’s follower status in one query and a Set:

following_emails = Follower.active
  .where(followed_id: current_resource_owner.id, email: sales.map(&:email))
  .pluck(:email)
  .to_set { |email| email.to_s.downcase }

And Purchase#as_json was given an opt-in so the prefetched answer is used when it is available, with the old per-record lookup left as the fallback for every other caller:

is_following: options.key?(:is_following) ? options[:is_following] : is_following?,

Stop building a new relation when the data is already loaded. Variant serialization for each sale did variant_attributes.not_is_default_sku, which is a fresh scoped relation — and scoped relations query even when the base association is already loaded. The fix:

def variants_and_quantity
  variants = if variant_attributes.loaded?
    variant_attributes.reject(&:is_default_sku?)
  else
    variant_attributes.not_is_default_sku
  end
  variants_and_quantity_displayable(variants, quantity)
end

Result for a page of ten populated sales: 155 → 40 SQL events, −50.9% mean request duration, SQL time down 82.7%, with the JSON response byte-for-byte in the same contract. Full case study →

The Gumroad Products API is the same pattern played at deeper nesting. A page of products fetched in one query still triggered 70 per-product queries because the serializer traversed prices, thumbnails, files, checkout custom fields, SKUs, and variant prices. The controller’s preload constant grew to a full tree, all the way down to Active Storage:

{ thumbnail: { file_attachment:
    { blob: { variant_records: { image_attachment: :blob } } } } },
{ variant_categories_alive: [{ alive_variants: [:alive_prices] }] },

One subtlety: the preloaded associations had to match the serializer’s filters, not just its model names. The serializer only ever reads live prices and live SKUs, so the PR introduced scoped associations like has_many :skus_alive, -> { alive }, class_name: "Sku" and has_many :global_checkout_custom_fields, -> { global.not_is_post_purchase }, ... and preloaded those instead. Otherwise you’d preload everything and filter in memory, or — worse — the serializer would use its own unscoped call and still query per product.

Result: 103 → 23 SQL events, 100% of the per-product target queries eliminated, request duration down 40.4%. PR →

Pattern 2: Replace count in a loop with one grouped query

Gumroad’s upsells dashboard answered “does this product have more than one version?” for every product in the seller’s catalog — inside the serialization loop:

products: pundit_user.seller.products.visible_and_not_archived
  .map { product_props(_1) }

def product_props(product)
  {
    # ...
    has_multiple_versions: product.alive_variants.limit(2).count > 1,
  }
end

count executes a SELECT COUNT(*). So an 81-product catalog ran 81 variant-count queries. The .limit(2) was a hopeful optimization and a complete red herring — the count query runs either way.

When the question is “for every row in this set, true or false about its children”, the answer should be computed once, in set form. One grouped query replaces the whole loop:

def product_props
  products = pundit_user.seller.products.visible_and_not_archived.to_a
  product_ids_with_multiple_versions = Variant.alive
    .joins(:variant_category)
    .merge(VariantCategory.alive)
    .where(variant_categories: { link_id: products.map(&:id) })
    .group("variant_categories.link_id")
    .having("COUNT(base_variants.id) > 1")
    .pluck("variant_categories.link_id")
    .to_set

  products.map do |product|
    # ...
    has_multiple_versions: product_ids_with_multiple_versions.include?(product.id),
  end
end

The Set matters: the serialization loop is now constant-time per product instead of one round trip.

Measured at 81 products: 198 ms → 99 ms mean request duration (−50.0%), variant queries 81 → 1, allocations down 62%. At 4 products the gain was smaller (−14.2% duration), which is exactly the profile you expect from an N+1: the fix’s payoff scales with the collection size, so benchmark at a size that resembles your worst real data. Full case study →

Pattern 3: Turn per-record exists? into a preloaded collection

Discourse’s admin active-users endpoint reports whether each user has 2FA enabled. Per user, SecondFactorManager ran two existence checks:

def totp_enabled?
  !SiteSetting.enable_discourse_connect && SiteSetting.enable_local_logins &&
    user_second_factors&.totps&.exists?
end

def security_keys_enabled?
  # ...
  security_keys&.where(factor_type: UserSecurityKey.factor_types[:second_factor],
                       enabled: true)&.exists?
end

Seventy-five users, 150 EXISTS queries, and — I measured this — those 150 queries were 148 of the request’s 148 duplicate query fingerprints. Nearly the entire request was the N+1.

The fix has two halves. First, the admin user query preloads the collections:

query = klass.includes(:security_keys, :totps).order(order.reject(&:blank?).join(","))

Second, the model methods learned to use preloaded data when it exists, and fall back to the original query when it does not:

def totp_enabled?
  !SiteSetting.enable_discourse_connect && SiteSetting.enable_local_logins &&
    totps.any?
end

def security_key_factor_enabled?(factor_type)
  if security_keys.loaded?
    security_keys.any? { |security_key| security_key.factor_type == factor_type }
  else
    security_keys.where(factor_type: factor_type).exists?
  end
end

This loaded? branch is the single most reusable idea in this post, so I’m flagging it: a model method should check whether its association is already loaded, use the in-memory collection when it is, and keep the original database path as a fallback. It is how you make an optimization safe for every other caller of that method — you never know who else calls security_keys_enabled?, and the fallback means nobody’s behavior can change.

Result for 75 active users: 159 → 10 SQL events, 150 existence checks eliminated, server duration 146 ms → 49 ms (−66.5%), with the passkey-versus-security-key distinction preserved. Full case study →

Pattern 4: Teach a hot model method to trust the eager-loaded graph

OpenProject’s API v3 serializer called Project#type_variant while building every work package. Each call could re-find the project’s project types by project and type, and then load that type’s variant by id — data that barely varies across a page:

def type_variant(type)
  return if type.nil?

  project_types.find_by(type_id: type.id)&.variant || type.default_variant
end

For one page of 20 work packages, the two lookup patterns each fired 381 times. The query cache absorbed 827 of the request’s 922 events, which is why this one was invisible to a latency-only reading of the app.

The application already had a dedicated eager-loading path for work package collections (it preloaded projects, phases, enabled modules). The fix was simply to extend that tree to the associations type_variant needs, and to let the model use it:

@projects_by_id ||= ::Project
    .includes(:enabled_modules, { project_types: :variant, phases: :definition })
    .where(id: project_ids)
    .to_a
    .index_by(&:id)

def type_variant(type)
  return if type.nil?

  project_type = if association(:project_types).loaded?
                   project_types.find { |candidate| candidate.type_id == type.id }
                 else
                   project_types.find_by(type_id: type.id)
                 end

  project_type&.variant || type.default_variant
end

Same loaded?-with-fallback pattern as Discourse. The association(:project_types).loaded? check — not project_types.any? or a method call — is what avoids accidentally triggering the load on an unused branch.

Result: 922 → 148 SQL events (−83.9%), both 381-times patterns reduced to zero, median server duration down 55.7%, response contents unchanged. Full case study →

Pattern 5: Load once per object and share it across every check

Forem’s role story is a different animal: the repeated queries were issued inside a gem. Authorization went through Rolify’s has_role? / has_any_role?, which hit the database every time — even when the user’s complete role collection was already in memory from an earlier load. One admin page asking “is this user an admin? a tag moderator? a subforem moderator? a trusted user?” per user produced 568 role queries.

The fix had two layers.

First, make the check layer association-aware. The Authorizer’s private role helpers now route through the loaded collection when it exists, using Rolify’s own cached-role method (has_cached_role?), with the original database-backed behavior untouched for callers who did not preload:

def has_role?(*args)
  return user.has_cached_role?(*args) if roles_loaded?

  user.__send__(:has_role?, *args)
end

def roles_loaded?
  user.association(:roles).loaded?
end

Second, at the entry points that ask many role questions, load the collection once at the boundary:

# admin member list
User::Filter::UserFilter.new(...).preload(:roles).page(params[:page]).per(50)

# CSV export
@users = User.registered
  .select(ATTRIBUTES_FOR_CSV + ATTRIBUTES_FOR_LAST_ACTIVITY)
  .preload(:organizations, :roles)

# async user payload: one explicit load before the checks that share it
@user.roles.load

The pattern here is: when one object is asked the same family of questions many times, load the collection once at the boundary and let every check share it. One shared load at the top is what turns five separate N+1s into a single bounded load — if each call site had “fixed” its own query, you’d have written five load sites instead of one.

Across Forem’s four measured paths: role queries fell 568 → 4 on the admin member list, 139 → 3 on its CSV export, 13 → 3 on the async user payload, and 4 → 1 on the reCAPTCHA decision. Full case study →

Lock it in: regression specs that assert query counts

Every one of these fixes shipped with a spec that fails if the repeat queries come back. Three styles worth knowing.

A built-in query limit (OpenProject):

expect { expect(preloaded_project.type_variant(resolved_type)).to eq(expected_variant) }
  .to have_a_query_limit(0)

A helper that captures the SQL (Discourse):

queries = track_sql_queries { get "/admin/users/list/active.json" }
expect(queries.count { |q| q.include?('FROM "user_second_factors"') }).to eq(1)
expect(queries.count { |q| q.include?('FROM "user_security_keys"') }).to eq(1)

A raw notification subscriber (Forem, Gumroad), which lets you assert on very specific fingerprints:

scoped_role_queries = []
subscriber = lambda do |_name, _started, _finished, _unique_id, payload|
  sql = payload[:sql]
  scoped_role_queries << sql if sql.match?(/FROM "roles".*"users_roles".*"users_roles"\."user_id" =/)
end

ActiveSupport::Notifications.subscribed(subscriber, "sql.active_record") { get admin_users_path }

expect(response).to have_http_status(:ok)
expect(scoped_role_queries).to be_empty

The Gumroad Sales spec goes furthest: it builds a full page of distinct, populated sales and asserts bounded per-table query counts — "followers" => 1, "shipments" => 1, "upsell_purchases" => 1, and so on — while also verifying the serialized JSON. Two cautions from that spec are worth keeping in mind when you write your own:

• Use distinct parent records per row. If every purchase belongs to the same product, Active Record’s association cache will absorb the per-product query and your assertion will pass on broken code.
• Assert both directions. Check the response content and the query count. Asserting only the count does not prove you preserved behavior; asserting only the content does not prove you removed the N+1.

The checklist

1. Count queries with sql.active_record and rank by duplicate fingerprints. Do it before you believe a latency number — the query cache can hide the majority of the volume, and it hid OpenProject’s worst endpoint.
2. Walk the serializer, not just the controller. In the Sales and Products API cases the initial fetch was already batched; the N+1s lived in what the JSON layer read. Preload a tree that matches exactly what as_json traverses, using scoped associations whose scopes match the serializer’s filters.
3. Replace per-row count / exists? / find_by with either one grouped query or a preloaded collection plus an in-memory check. Turn the result into a Set/Set-of-ids so the loop stays O(1).
4. Prefer association(:x).loaded? branches with the original query as fallback inside shared model methods, so the optimization is safe for every caller.
5. When one object is asked many related questions, load once at the boundary (a collection preload, or one explicit .load) and make the checks layer association-aware.
6. Ship a query-count regression spec that verifies the response at the same time, with data distinct enough that association caching cannot hide a relapse.
7. Keep Bullet running in development and in the test suite, with raise on in both — specs defend the endpoints they cover; Bullet is what catches the next N+1 on the day it is written.

The six source pull requests: Gumroad #7486, Gumroad #7471, Gumroad #7506, Discourse #43027, OpenProject #24894, Forem #23817. The case studies carry the full benchmark tables, validation notes, and per-path breakdowns for each.

Related: Using ActiveRecord Strict Loading to explicitly prevent N+1, Understanding EXPLAIN (ANALYZE) As a Rails Developer, and the Bullet gem for the always-on development-time guard.