What is an N+1 query and why does it survive code review in a monolith?

An N+1 query happens when code loads a collection with one query, then issues one more query per record to fetch an association. Rendering 50 orders and then reading order.customer for each one produces 51 round trips where two would do. The cause is lazy loading: ORMs such as Active Record defer loading an association until something reads it, so the extra queries look like ordinary attribute access in the code.

The pattern survives tests because test fixtures are small. With three records, an N+1 adds three fast queries against a local database, and the suite stays green. The cost only shows up when N reaches hundreds or thousands and each query pays real network latency.

It survives review because the lazy load is rarely in the diff that matters. The controller loads the collection, but the association is read inside a serializer, a view partial, or a helper several files away. A reviewer reading the controller sees one clean query.

In a monolith, shared components make this worse. A developer adds author.avatar_url to a comment partial used by a dozen pages, and every one of those pages picks up a new query per comment. The change is one line, and the regression spreads to every endpoint that renders the partial.

This article is for backend and platform engineers who own a large monolith. No single tool catches every N+1, so treat detection as a layered CI gate.

# app/controllers/orders_controller.rb
# The controller looks clean: one query for the collection.
def index
  @orders = Order.order(created_at: :desc).limit(50)
end

# app/views/orders/_order.html.erb (rendered once per order)
# The association read lives here, outside the controller diff.
#   <%= order.customer.name %>

# Development log for one request (abridged):
#   Order Load     SELECT "orders".* FROM "orders" ORDER BY "orders"."created_at" DESC LIMIT $1
#   Customer Load  SELECT "customers".* FROM "customers" WHERE "customers"."id" = $1 LIMIT $2
#   Customer Load  SELECT "customers".* FROM "customers" WHERE "customers"."id" = $1 LIMIT $2
#   ... one Customer Load per order: 1 + 50 = 51 queries

# Fix: preload the association in the query that builds the collection.
def index
  @orders = Order.includes(:customer).order(created_at: :desc).limit(50)
end

# Development log after the fix:
#   Order Load     SELECT "orders".* FROM "orders" ORDER BY "orders"."created_at" DESC LIMIT $1
#   Customer Load  SELECT "customers".* FROM "customers" WHERE "customers"."id" IN ($1, $2, ...)
#   2 queries, regardless of how many orders are rendered

An Active Record controller that looks like a single query, a partial that lazily loads order.customer once per row, and the includes change that cuts the request from 51 queries to 2.

How does a layered n+1 query detection gate work?

The layers run in the same CI job as your existing test suite. No separate pipeline is needed. Each layer fails the build for a different reason, so a regression that slips past one tends to trip another.

  • Bullet watches ActiveRecord while tests execute. It flags associations that were lazily loaded inside a loop, and eager loads that were never used. It is heuristic: it only sees code paths your tests exercise, and it guesses intent.
  • Query count assertions wrap a request or job and fail when the count exceeds a budget. A stronger variant is the scaling assertion: run the same path with N records, then 2N, and require the count to stay flat. This catches N+1s that Bullet's patterns miss, such as queries issued from serializers or custom methods.
  • Strict loading makes a lazy load raise an error instead of silently querying. This turns a performance bug into a correctness failure that no test can ignore.

Production and staging instrumentation sits outside CI. It sees real data shapes and real traffic, but only after the code has shipped.

Layer Coverage False-positive risk Runtime cost in CI Maintenance cost
Bullet Broad, pattern-based, tested paths only Medium (unused-eager-load warnings) Low Low: safelist entries
Query count / scaling assertions Narrow, hot paths only Low if scaling-based; higher with fixed counts Medium (extra records per test) Medium: budgets drift
Strict loading Every model or scope where enabled Low, but noisy on legacy code Negligible High during rollout
Production monitoring alone All real traffic Low None in CI Low, but regressions reach users first

Relying on production monitoring alone means every N+1 costs at least one deploy and one round of incident triage. The CI layers move that cost to the pull request, where the author still has context.

+-- CI job: existing test suite --------------------------------------------+
|                                                                          |
|  +-------------+    +-----------------------+    +--------------------+  |
|  | Test run    |--->| Layer 1: Bullet       |--->| lazy load in loop  |  |
|  | (requests,  |    | watches ActiveRecord  |    | or unused eager    |  |
|  |  jobs)      |    +-----------------------+    | load -> FAIL       |  |
|  |             |                                 +--------------------+  |
|  |             |    +-----------------------+    +--------------------+  |
|  |             |--->| Layer 2: query count  |--->| count over budget  |  |
|  |             |    | + scaling (N vs 2N)   |    | or grows with N    |  |
|  |             |    +-----------------------+    | -> FAIL            |  |
|  |             |                                 +--------------------+  |
|  |             |    +-----------------------+    +--------------------+  |
|  |             |--->| Layer 3: strict       |--->| lazy load raises   |  |
|  |             |    | loading               |    | error -> FAIL      |  |
|  +-------------+    +-----------------------+    +--------------------+  |
|                                                                          |
+------------------------------------+-------------------------------------+
                                     | all layers pass
                                     v
                          +---------------------+
                          | merge and deploy    |
                          +----------+----------+
                                     |
                                     v
+-- outside CI ------------------------------------------------------------+
| Staging/production instrumentation: real data shapes, real traffic       |
| but N+1s surface only after the code has shipped                         |
+--------------------------------------------------------------------------+

All three detection layers run against the existing test suite in one CI job, and each can fail the build for a different reason. Staging and production instrumentation sits after deploy and only sees N+1s once the code has shipped.

How do you configure the Bullet gem to fail tests?

Bullet works by watching which associations get loaded during a request and comparing that against the records the request touched. In the test environment you enable it inside config.after_initialize in config/environments/test.rb, setting Bullet.enable = true and Bullet.raise = true. With raise on, a detection becomes an exception, so the test fails instead of writing a warning nobody reads.

Outside of controller and request specs there is no request cycle, so Bullet has nothing to track. Wrap each test in hooks that call Bullet.start_request before the example and Bullet.perform_out_of_channel_notifications if Bullet.notification? followed by Bullet.end_request after it. This also covers model, service and job tests, where many N+1s actually live.

Fixtures matter. Bullet flags an N+1 when the same association is lazily loaded across several records of one parent query. With a single parent record the query runs once and nothing looks wrong. A test that wants Bullet's coverage needs at least two parents, each with children.

Beyond N+1s, Bullet reports unused eager loading (an includes whose data is never read) and missing counter caches (calling size on an association where a cached count would avoid the query). These checks are noisier, so many teams first enable them with Bullet.bullet_logger = true and raise off, review the log, safelist known cases with Bullet.add_safelist, and then make them blocking.

Bullet's checks are runtime heuristics. It cannot see code paths your tests never run. It also produces false positives when an association is eager loaded for one branch and skipped in another.

# config/environments/test.rb
Rails.application.configure do
  config.after_initialize do
    Bullet.enable = true
    Bullet.bullet_logger = true   # writes log/bullet.log for review
    Bullet.raise = true           # a detection becomes an exception, so the test fails

    # N+1 detection blocks from day one; the noisier checks stay off until reviewed
    Bullet.n_plus_one_query_enable = true
    Bullet.unused_eager_loading_enable = false
    Bullet.counter_cache_enable = false

    # Known false positive: comments are eager loaded on only one branch
    Bullet.add_safelist type: :unused_eager_loading,
                        class_name: "Post", association: :comments
  end
end

# spec/support/bullet.rb
# Model, service and job specs have no request cycle, so open one per example.
RSpec.configure do |config|
  config.before(:each) do
    Bullet.start_request if Bullet.enable?
  end

  config.after(:each) do
    if Bullet.enable?
      Bullet.perform_out_of_channel_notifications if Bullet.notification?
      Bullet.end_request
    end
  end
end

# spec/services/post_digest_spec.rb
# Two parents with children: one parent would hide the N+1 from Bullet.
RSpec.describe PostDigest do
  it "builds without N+1 queries" do
    2.times { create(:post, comments: build_list(:comment, 2)) }
    PostDigest.new(Post.all).call
  end
end

Ruby config and RSpec hooks (shown as text) that make Bullet raise on N+1 queries in every spec, keep the noisier checks off until reviewed, and use two parent records so the N+1 can be detected.

How do you write query count assertions that stay stable?

Query count assertions cover what Bullet misses: they measure what the database actually received, regardless of which code path produced it. The mechanism is Rails instrumentation. ActiveSupport::Notifications.subscribed attaches a callback to the sql.active_record event only for the duration of a block. Every query executed inside that block increments a counter, and the subscription is removed when the block exits, so other tests are unaffected.

A raw count drifts for reasons unrelated to your code, so filter it before comparing. Drop events whose payload name is SCHEMA, because column introspection depends on whether the schema cache is warm. Drop BEGIN, COMMIT, ROLLBACK and savepoint statements, because transactional test fixtures add these. Skip query cache hits, flagged by payload[:cached] or a CACHE name, because they never reach the database. Put this logic in one helper, such as a count_queries method or a custom make_database_queries matcher, so every spec applies the same filters.

Prefer scaling assertions over fixed numbers

expect { get "/orders" }.to make_database_queries(count: 7) is brittle. When someone adds an authorization lookup or an audit write, the test fails and the easiest fix is to change 7 to 8 without reading the diff. A scaling assertion tests the property you care about. Seed N records and count the queries for the request. Then seed 2N records and count again. Require the two counts to be equal. Any per-record query, at any depth of the call stack, breaks that equality.

The trade-off is cost. Each scaling test needs two rounds of data setup and two requests, so apply it to collection endpoints and batch jobs. Don't run it on every action.

# Ruby (RSpec + FactoryBot). spec/support/query_counter.rb
module QueryCounter
  IGNORED_NAMES = %w[SCHEMA CACHE].freeze
  TRANSACTION_SQL = /\A\s*(BEGIN|COMMIT|ROLLBACK|SAVEPOINT|RELEASE)/i

  # Count only queries that actually reached the database during the block.
  def count_queries(&block)
    count = 0
    counter = lambda do |*, payload|
      next if IGNORED_NAMES.include?(payload[:name]) # schema introspection, cache hits
      next if payload[:cached]                       # query cache hit, never hit the DB
      next if payload[:sql].match?(TRANSACTION_SQL)  # added by transactional fixtures
      count += 1
    end
    # The subscription exists only while the block runs, so other tests are unaffected.
    ActiveSupport::Notifications.subscribed(counter, "sql.active_record", &block)
    count
  end
end

RSpec.configure { |c| c.include QueryCounter, type: :request }

# spec/requests/orders_spec.rb
RSpec.describe "GET /orders", type: :request do
  it "issues the same number of queries for N and 2N orders" do
    create_list(:order, 5)
    get "/orders" # warm-up request so one-time loading does not skew the first count
    small = count_queries { get "/orders" }

    create_list(:order, 5) # 10 orders now
    large = count_queries { get "/orders" }

    # A per-record query anywhere in the call stack makes large exceed small.
    expect(large).to eq(small)
  end
end

A count_queries helper that filters schema, cache and transaction events, plus a scaling spec that requires equal query counts for 5 and 10 orders.

When should you use strict loading, and when should you avoid turning it on everywhere?

Strict loading moves detection from observation to enforcement. Instead of a test noticing extra queries after the fact, Active Record raises ActiveRecord::StrictLoadingViolationError the moment code lazily loads an association. You can apply it at three levels: per relation with .strict_loading, per association with strict_loading: true on the declaration, or per model with self.strict_loading_by_default = true. Narrow scopes are the best starting point. Enable it on models introduced in new code, or on the relations behind your highest-traffic collection endpoints. That way the error points directly at the code that just changed.

Turning on config.active_record.strict_loading_by_default across an existing monolith at once produces failures in every view, serializer and callback that relied on lazy loading. Many of those failures sit in admin screens, rarely run jobs and error paths that your test suite barely exercises, so some will first appear in production. Setting action_on_strict_loading_violation to :log first gives you an inventory without breaking requests. Incremental rollout leaves the remaining code unprotected during the transition, and the earlier layers have to cover it.

Some per-record loading is correct. A batch job that processes records in chunks, or code that fetches each record before calling an external API, may load associations deliberately. Exempt these explicitly with .strict_loading(false) or record.strict_loading!(false), and add a comment explaining why. Disabling strict loading globally to get one job passing removes the protection for everything else.

Avoid answering every violation with includes. Eager loading associations that a request never reads pulls large object graphs into memory. Preloading thousands of IDs produces IN lists that the database may plan poorly. A request can issue fewer queries and still run slower. Check latency and memory alongside query count before deciding a fix worked.

Which endpoints and jobs should get query budgets?

Query budgets cost time to write and maintain, so put them where query counts multiply. Three kinds of target usually justify the cost:

Target Why it earns a budget Setup cost
Highest-traffic API endpoints One extra query per row is multiplied by every request Fixtures with realistic collection sizes
Shared serializers Many endpoints reuse them, so one budget protects all of those callers Moderate: test the serializer directly with several records
Background jobs that iterate collections N grows with data volume, not traffic, and jobs often run unobserved Seed enough rows to show growth

Single-record CRUD actions, admin screens and rarely hit internal tools seldom repay the effort. A budget on every action adds fixtures that drift and failures nobody prioritises.

Each budgeted test needs at least two or three associated records per parent. With a single row, an N+1 looks the same as a correct query. That setup slows the suite. Run detection in a separate CI configuration with Bullet and query logging enabled. The extra instrumentation clutters stack traces and timing output, and in the main suite it can obscure real failures.

Illustrative estimate (assumed numbers, not a benchmark)

Net hours saved per year = (regressions caught × hours to debug after release) − (budgets × hours to write) − (budgets × yearly maintenance hours)

Assume 15 budgets, 1.5 hours to write each, 0.5 hours of maintenance each per year, 4 regressions caught per year, and 12 hours to diagnose, fix and redeploy each one after release:

(4 × 12) − (15 × 1.5) − (15 × 0.5) = 48 − 22.5 − 7.5 = 18 hours saved

Under these assumptions, raising the count to 60 budgets makes the net result negative. Coverage pays off only when it stays targeted.

How do you roll out N+1 checks in CI without creating a noisy gate?

Stage 1: Report, don't block

Run the detectors on every build, but have them write findings to a CI artifact or job summary instead of failing. Leave this in place for a couple of weeks. You learn how many violations already exist, which ones are real, and which are false positives from fixtures or test setup. If you skip this step and go straight to blocking, the first week of red builds teaches the team that the check is in the way.

Stage 2: A safelist that can only shrink

Turn the existing findings into a safelist file checked into the repository. Each entry should be keyed by model, association and call site, and should name an owning team. Add a separate CI check that compares the file against the main branch and fails if any entry was added. Deleting an entry is always allowed. New code can't add violations, and fixing an old one shrinks the list. Ownership matters because entries with no owner never get removed. Put each entry's team into the file itself, and have the owning teams review changes to it.

Stage 3: Block

Once new violations are rejected, make the detectors fail the build. Every failure message should state the model, the association, the suggested fix (for example, "add includes(:line_items)"), and the first application frame in the backtrace, not an ORM-internal frame. If an engineer can go from the CI log straight to the right line, they fix the problem. If the message is vague, they add a safelist entry or retry the build. A gate that fails often for unclear reasons is worse than having no gate. People learn to rerun or bypass it, and then they also ignore the real failures.

What can't CI catch, and how do you fill the gap?

CI runs against fixtures that are small and uniform. A factory creates one author with two posts. Production has authors with thousands of posts, polymorphic associations that point at several types, and nullable associations that are actually populated. An N+1 that only appears when a collection mixes record types, or when a serializer takes a branch your fixtures never reach, will pass every test. Feature flags cause the same problem. If the suite runs with a flag off, the new code path never executes in CI.

To close the gap, measure the same thing outside CI: queries per unit of work. Count SQL statements per request and per background job. In Rails, a subscriber to the sql.active_record notification that increments a request-scoped counter is enough. Emit the count with the route or job class in structured logs or metrics. Alert on shifts in the distribution, such as an endpoint's p99 query count doubling after a deploy, rather than on single outliers. Large legitimate requests produce outliers all the time.

Turn each finding into a test. Rebuild the data shape that triggered it in a factory, add a query count assertion for that endpoint, and set the budget from the count you see when the endpoint is behaving normally. This way, production data decides where budgets go instead of guesswork.

Some teams should skip the CI gate for now. With a small app, a handful of endpoints and no one to maintain realistic fixtures, a gate costs more than it catches. Run Bullet in development, log query counts per request, and review the worst endpoints periodically.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to n+1 query detection, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.