N+1 in SQLAlchemy Relationship Loading #

A FastAPI endpoint serialises 100 orders with their customer and their line items. It issues 201 queries. Adding joinedload for both relationships reduces it to one query — which now returns 4,000 rows because each order repeats once per line item, and the response takes longer than before. selectinload for the collection and joinedload for the scalar gives three queries and the fastest result.

Detection from the database is covered in detecting N+1 with pg_stat_statements. This page is about SQLAlchemy’s loader strategies and the SQL each produces.

The Condition #

SQLAlchemy relationships have a loading strategy, set on the mapping or per query with options():

The matching rule is simple: scalar relationships join, collections select-in. Nested paths are composed with chained options, and raiseload("*") on top ensures anything not declared fails loudly.

Four strategies, four SQL shapes A table of strategies. Lazy select issues one query per parent object. Joinedload issues one query with a LEFT OUTER JOIN, best for scalar relationships. Selectinload issues a second batched IN query, best for collections. Subqueryload repeats the parent query inside a subquery and is largely superseded. SQL best for lazy="select" one query per object nothing joinedload LEFT OUTER JOIN many-to-one selectinload IN query per batch collections subqueryload repeats parent query legacy mixing strategies per relationship is normal and usually optimal

Annotated Evidence #

The N+1, with SQL echo enabled:

SELECT orders.* FROM orders WHERE orders.account_id = %(account_id)s LIMIT %(param_1)s
SELECT customers.* FROM customers WHERE customers.id = %(pk_1)s
SELECT customers.* FROM customers WHERE customers.id = %(pk_1)s
… ×100
SELECT order_lines.* FROM order_lines WHERE order_lines.order_id = %(param_1)s
… ×100

With matched strategies:

stmt = (select(Order)
        .where(Order.account_id == account_id)
        .options(joinedload(Order.customer),
                 selectinload(Order.lines))
        .limit(100))
-- 1: parents with the scalar joined
SELECT orders.*, customers.* FROM orders
LEFT OUTER JOIN customers ON customers.id = orders.customer_id
WHERE orders.account_id = %(account_id)s LIMIT %(param_1)s

-- 2: the collection, batched
SELECT order_lines.* FROM order_lines WHERE order_lines.order_id IN (…100 ids…)
-- plan of the second statement
Index Scan using order_lines_order_id_idx on order_lines  (actual time=0.04..1.2 rows=4021 loops=1)
  Index Cond: (order_id = ANY ('{…}'::bigint[]))
  Index Searches: 100
  Buffers: shared hit=412

And the failure mode of joinedload on a collection:

SELECT orders.*, order_lines.* FROM orders
LEFT OUTER JOIN order_lines ON order_lines.order_id = orders.id
WHERE orders.account_id = %(account_id)s
-- 4,021 rows, each repeating every order column; and with LIMIT, SQLAlchemy must
-- wrap the parent query in a subquery so the limit counts orders, not joined rows
Identifying the strategy from the SQL Three items are annotated. Repeated single-parameter selects show lazy loading. A LEFT OUTER JOIN with both tables' columns shows joinedload. A second statement with an IN list of parent ids shows selectinload. SELECT customers … WHERE id = %(pk_1)s ×100 lazy: one per object LEFT OUTER JOIN customers … joinedload SELECT order_lines … WHERE order_id IN (…) selectinload, batched echo=True or the SQLAlchemy logger shows these directly

Step-by-Step Resolution #

  1. Enable SQL logging for a request (echo=True or the sqlalchemy.engine logger at INFO) and count statements.

  2. Classify each relationship in the query path as scalar or collection.

  3. Apply the matching strategy per relationship, chaining for nested paths:

    .options(
    joinedload(Order.customer).joinedload(Customer.account),
    selectinload(Order.lines).joinedload(OrderLine.product),
    raiseload("*"),
)

  4. Add raiseload("*") so any relationship not declared raises instead of silently querying.

  5. Index the batch key used by selectinload — the child’s foreign key column — or the batched query degrades into a scan.

  6. Tune the batch size when parent sets are large: selectin_polymorphic and loader options accept batch sizing; the default of 500 keys per statement is a reasonable balance between round trips and statement size.

  7. Check the plan of the batched query with a realistic array length, following IN lists vs VALUES vs ANY(array) plans.

100 orders with customer and lines Lazy loading takes 840 milliseconds with 201 queries. Joinedload for both relationships takes 210 milliseconds with one query but returns 4,021 rows. Selectinload for both takes 112 milliseconds with three queries. Joinedload for the scalar and selectinload for the collection takes 96 milliseconds with two queries. lazy (N+1) 840 ms, 201 queries joinedload both 210 ms, 4,021 rows selectinload both 112 ms, 3 queries joined + selectin 96 ms, 2 queries matching the strategy to the relationship shape wins

Before and After #

-- BEFORE: default lazy="select"
201 statements per request, 100 of them one-row customer lookups     840 ms

-- AFTER: joinedload(customer) + selectinload(lines) + raiseload("*")
2 statements per request                                              96 ms

Why selectinload usually beats subqueryload #

subqueryload re-runs the parent query as a subquery inside the child query. That means the parent’s joins, filters and sorting are executed twice, and any LIMIT must be handled carefully to avoid loading children for rows the parent query did not return. On complex parent queries that doubling is expensive and shows up clearly in pg_stat_statements as a second heavy statement resembling the first.

selectinload instead collects the parent primary keys already in memory and queries children by those keys. The child query is therefore simple and cheap regardless of how complicated the parent query was, and it batches automatically. The one case where subqueryload can still win is a very large parent set where sending tens of thousands of keys is worse than repeating a selective parent query — rare in request-response code, occasionally relevant in batch jobs.

For polymorphic relationships and inheritance hierarchies, the loader options have their own variants, but the same principle applies: prefer strategies that query children by parent keys over strategies that repeat the parent’s work.

Declaring defaults on the mapping #

Per-query options are precise but easy to forget. Setting a sensible default on the relationship mapping catches the paths nobody thought about:

class Order(Base):
    customer = relationship("Customer", lazy="joined")
    lines = relationship("OrderLine", lazy="selectin")
    audit_events = relationship("AuditEvent", lazy="raise")

Defaults of joined and selectin make the common paths correct automatically, while lazy="raise" on relationships that should never be loaded implicitly — large collections, audit trails — turns an accidental access into an error instead of a query storm. Per-query options still override the mapping where a particular endpoint needs something different, so the default is a floor rather than a constraint.

One caution: a lazy="joined" default applies to every query touching that entity, including ones that load thousands of rows and never use the relationship. For entities used in both list and detail contexts, leaving the mapping lazy and declaring options per query is often the better balance, with raiseload("*") making omissions visible during development.

Common Pitfalls #

joinedload on collections. Parent rows multiply. Diagnostic signal: result row counts far above the parent count. Fix: selectinload.

Forgetting raiseload("*"). New relationship access silently reintroduces the N+1. Diagnostic signal: query counts creeping up. Fix: add it in tests and development.

Unindexed child foreign key. The batched query scans. Diagnostic signal: sequential scan under an IN condition. Fix: index the column.

Loader options on the wrong entity. Chained options must follow the relationship path. Diagnostic signal: options silently ignored. Fix: chain from the root entity through each relationship.

Frequently Asked Questions #

What is the difference between joinedload and selectinload? #

Joinedload adds a LEFT OUTER JOIN to the same query, which suits many-to-one relationships. Selectinload issues a second query fetching children by parent ids, which suits collections because it avoids multiplying parent rows.

How do I make SQLAlchemy raise on lazy loads? #

Set lazy=“raise” on the relationship, or add raiseload(“*”) to the query options. Any access to an unloaded relationship then raises instead of issuing a query.

Should I still use subqueryload? #

Rarely. Selectinload achieves the same goal without re-executing the parent query, and it batches by primary key. Subqueryload remains useful only in narrow cases with very large parent sets.

Up: N+1 Query Detection