Strict Loading Guards Against Lazy Queries #

You fixed the N+1 on the orders page last quarter with an eager load. This quarter a template change added order.customer.account_manager.name, and pg_stat_statements shows SELECT … FROM users WHERE id = $1 at 38,000 calls per hour again. The fix was correct; nothing stopped the regression from walking back in.

The plan-level symptoms of lazy loading — thousands of identical single-row index scans — are described in lazy loading plan artifacts. This page is about making the ORM refuse to produce them.

The Condition That Produces Lazy Queries #

Lazy loading issues a query the first time code touches an association that was not loaded. In a loop over N parents, touching one association per parent emits N queries — each a well-planned, fast Index Scan using users_pkey whose cost is invisible in any single EXPLAIN, as shown in when lazy loading triggers repeated index scans.

A strict loading guard changes the ORM’s reaction to that first touch from “run a query” to “raise or log”. The framework options:

Guards shift detection from production telemetry to the test suite, where fixing costs minutes rather than an incident.

What a guard changes Code touches an unloaded association. Without a guard, the ORM silently issues a query per parent, which reaches the database as thousands of identical index scans. With a guard, the ORM raises in tests or logs a violation in production, and the developer replaces the access with an explicit eager load that produces one batched query. order.customer not preloaded no guard silent query per parent 38,000 index scans/hour found in production strict loading raise in tests · log in prod explicit eager load 1 batched query

Annotated Evidence #

The regression as it appears on the database side:

SELECT calls, round(mean_exec_time::numeric, 3) AS mean_ms,
       round(total_exec_time::numeric) AS total_ms, left(query, 70)
FROM pg_stat_statements
ORDER BY calls DESC LIMIT 3;
  calls  | mean_ms | total_ms |                          left
---------+---------+----------+---------------------------------------------------------
 1843220 |   0.041 |    75572 | SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2
   48410 |   2.104 |   101854 | SELECT "orders".* FROM "orders" WHERE "orders"."account_id" = $1 …
-- calls on users ≈ 38 × calls on orders → one user lookup per order row rendered
-- mean 0.041 ms looks harmless; 75 s total per day is not

The guard in a Rails test:

orders = Order.strict_loading.where(account_id: account.id).includes(:customer)
orders.each { |o| o.customer.account_manager.name }
# => ActiveRecord::StrictLoadingViolationError:
#    `Customer` is marked for strict_loading. The User association named
#    `:account_manager` cannot be lazily loaded.

In SQLAlchemy:

stmt = (select(Order)
        .where(Order.account_id == account_id)
        .options(selectinload(Order.customer), raiseload("*")))
for o in session.scalars(stmt):
    o.customer.account_manager.name
# sqlalchemy.exc.InvalidRequestError:
#   'Customer.account_manager' is not available due to lazy='raise'

The fixed load issues one extra batched query, whose plan is an index lookup on an array:

Index Scan using users_pkey on users  (actual time=0.02..0.61 rows=212 loops=1)
  Index Cond: (id = ANY ('{81,92,…}'::bigint[]))
-- 1 execution returning 212 rows, replacing 38,000 single-row executions
pg_stat_statements before and after Left: the lazy lookup by id runs 1.8 million times a day at 0.041 milliseconds, 75 seconds in total. Right: the batched lookup by an array of ids runs about 48 thousand times at 0.61 milliseconds, about 30 seconds in total. lazy: WHERE id = $1 calls 1,843,220 mean 0.041 ms total 75.6 s per day batched: WHERE id = ANY($1) calls ≈ 48,410 mean 0.61 ms total ≈ 29.5 s per day the per-call time rises while the total falls

Step-by-Step Resolution #

  1. Locate the lazy statement in pg_stat_statements by its call ratio to the parent query, as above. The detection method is in N+1 query detection.

  2. Turn the guard on for the code path in tests. Rails: .strict_loading on the relation. SQLAlchemy: raiseload("*") on the query. Django:

    from django.db import connection

class QueryBudget:
    def __init__(self, limit): self.limit, self.count = limit, 0
    def __call__(self, execute, sql, params, many, context):
        self.count += 1
        if self.count > self.limit:
            raise AssertionError(f"query budget {self.limit} exceeded: {sql[:80]}")
        return execute(sql, params, many, context)

with connection.execute_wrapper(QueryBudget(3)):
    render_orders_page(account)

  3. Replace each violation with an explicit eager load — includes/preload, selectinload/joinedload, select_related/prefetch_related. The plan trade-offs of join-based versus batched loading are in select_related vs prefetch_related plans.

  4. Verify the database side. Capture the new statements with auto_explain or the ORM’s explain helper and confirm the batched query uses an index on the key.

  5. Roll out globally in stages. Enable strict loading by default with log mode in production, and raise mode in test and development. Triage logged violations until they stop appearing.

Rolling out guards without breaking production Three stages left to right. Stage one: guard a single hot code path in its tests. Stage two: enable strict loading by default in log mode in production and collect violations. Stage three: switch tests and development to raise mode once violations are fixed. 1 · one hot path raise in its tests 2 · default on, log collect prod violations 3 · raise everywhere tests and development production stays in log mode: a missed path degrades, it does not fail

Before and After #

-- BEFORE: lazy account_manager access in the template
users by id: calls/hour 38,000   mean 0.041 ms   total 1,558 ms/hour

-- AFTER: strict_loading flagged it; includes(customer: :account_manager) added
users by id = ANY(array): calls/hour 1,000   mean 0.61 ms   total 610 ms/hour

Why the batched query is cheaper even though each call is slower #

The mean execution time went up fifteen-fold, from 0.041 ms to 0.61 ms, and the total went down by more than half. The per-call figure rose because each execution now fetches about 40 users instead of one: the index scan descends the B-tree once per array element and visits the heap for each match. The total fell because the fixed costs of a statement — the network round trip, parsing or binding, executor startup, and the ORM’s own work to build a query and hydrate objects — are now paid once per page render instead of 38 times.

Those fixed costs do not appear in pg_stat_statements at all. total_exec_time covers only server-side execution, so the database view understates the real win. On the application side the saving is usually larger than on the database side: 37 fewer round trips at a typical intra-datacenter latency of a few tenths of a millisecond, plus 37 fewer query objects built and discarded by the ORM.

Where guards cannot help #

Strict loading only intercepts association access. It does not catch loops that issue explicit queries — Invoice.where(order_id: order.id).sum(:amount) inside a template loop is still one query per row, and it is not a lazy load in the ORM’s sense. It also cannot see raw SQL, database views queried per object, or calls to other services that query the database on your behalf. For those, the query-count budget approach remains useful across all three frameworks, and the statement-shape analysis in detecting N+1 in Django ORM generalises to any stack: group captured statements by normalised text and flag any shape executed more than a handful of times per request.

Finally, a guard is only as good as the code paths the tests exercise. Pages rendered with a single fixture row never trigger a loop. Seed collection-heavy tests with enough related rows that a lazy access would repeat, or the guard will pass code that still fans out in production.

Common Pitfalls #

Raising in production on day one. An unexercised path becomes an error page. Diagnostic signal: new exceptions immediately after deploy. Fix: log mode in production until violations are drained.

Satisfying the guard with a join that multiplies rows. Eager-loading a large has_many with a join duplicates parent rows. Diagnostic signal: rows= on the join far above the parent count and a large Unique or hash step. Fix: use batched loading (preload, selectinload, prefetch_related) for collections.

Guards that stop at one level. raiseload on Order.customer does not guard Customer.account_manager unless the wildcard or nested option is used. Diagnostic signal: lazy loads reappearing one association deeper. Fix: use raiseload("*") or strict loading by default.

Counting queries that are not N+1. A Django budget that fails on legitimate new features trains people to raise the limit. Diagnostic signal: budgets edited upward in most pull requests. Fix: assert on duplicated statement shapes rather than raw counts.

Frequently Asked Questions #

Does strict loading change the SQL my ORM generates? No. It changes what happens when code touches an unloaded association: an error or log instead of a query. Plans change only after you fix the flagged code.

Should strict loading raise in production? Usually raise in tests and development and log in production, so a missed path degrades instead of failing.

Does Django have a strict loading mode? Not built in. Use assertNumQueries, an execute_wrapper query budget, or a third-party package that seals querysets.

Up: Lazy Loading Plan Artifacts