Catch and remove an ORM N+1 query before it reaches production

A page is fast with three test users and painfully slow with 500. The SQL log shows one query for users followed by one address query per user. That growth pattern—not merely “many SQL statements”—is the ORM N+1 problem: one query loads N parents, and touching a lazily loaded relationship emits N additional selects.

Recognize the query shape

SELECT id, name FROM users ORDER BY id LIMIT 100;

SELECT id, user_id, email FROM addresses WHERE user_id = 1;
SELECT id, user_id, email FROM addresses WHERE user_id = 2;
-- repeated once for every loaded user

Each statement may look inexpensive in isolation. The cost accumulates through network round trips, parser and planner work, pool occupancy, ORM object construction and lock/snapshot duration. With 100 parents, the request issues 101 selects. With pagination increased to 500, it issues 501.

Prove it at the request boundary

from contextlib import contextmanager
from sqlalchemy import event

@contextmanager
def count_queries(engine):
    count = 0
    def before_cursor_execute(*_args):
        nonlocal count
        count += 1
    event.listen(engine, "before_cursor_execute", before_cursor_execute)
    try:
        yield lambda: count
    finally:
        event.remove(engine, "before_cursor_execute", before_cursor_execute)

with count_queries(engine) as queries:
    response = client.get("/users?limit=100")
    assert response.status_code == 200
    assert queries() <= 3

Count queries in a representative integration test using the same serializer or template as production. Do not enable unrestricted SQL logging with customer values in production. Pair count with total database time and response size; one enormous join can be worse than two bounded queries.

Reproduce the lazy load explicitly

from sqlalchemy import select

users = session.scalars(
    select(User).order_by(User.id).limit(100)
).all()

payload = [
    {
        "id": user.id,
        "name": user.name,
        "emails": [address.email for address in user.addresses],
    }
    for user in users
]

SQLAlchemy’s default relationship loading style is commonly lazy select. The initial statement returns users. Accessing user.addresses later asks the session for each unloaded collection. Serializers, template loops and property methods often hide the access that triggers SQL.

Fix a one-to-many page with select-in loading

from sqlalchemy import select
from sqlalchemy.orm import selectinload

statement = (
    select(User)
    .options(selectinload(User.addresses))
    .order_by(User.id)
    .limit(100)
)
users = session.scalars(statement).all()

selectinload first loads the parent rows, then issues another select whose IN clause contains the parent primary keys. SQLAlchemy documents it as generally the simplest and most efficient eager-loading strategy for collections. The query count becomes approximately two for this relationship rather than N+1.

SELECT id, name
FROM users
ORDER BY id
LIMIT 100;

SELECT user_id, id, email
FROM addresses
WHERE user_id IN (1, 2, 3, ...);

Use joined loading where the cardinality supports it

from sqlalchemy import select
from sqlalchemy.orm import joinedload

statement = (
    select(Order)
    .options(joinedload(Order.customer))
    .order_by(Order.id.desc())
    .limit(50)
)
orders = session.scalars(statement).unique().all()

A many-to-one relationship such as order-to-customer often fits joined eager loading because each parent contributes at most one related row. A one-to-many join duplicates parent columns once per child and can multiply rows dramatically. SQLAlchemy requires Result.unique() for joined eager loading of collections so row multiplication is made explicit.

Choose by relationship shape, not superstition

Situation	Likely strategy	Risk to measure
Many parents, collection per parent	selectinload	IN-list batches and composite-key support
Many-to-one, related row always needed	joinedload	Wide repeated columns
Relationship rarely used	Keep lazy or omit it	Accidental access later
Large collection not returned	Aggregate or explicit paged query	Loading thousands of objects
SQL already uses an explicit join	contains_eager when appropriate	Incorrect or partial collection semantics

Fail fast on accidental lazy loading

from sqlalchemy import select
from sqlalchemy.orm import raiseload, selectinload

statement = (
    select(User)
    .options(
        selectinload(User.addresses),
        raiseload("*"),
    )
)

raiseload replaces an unexpected relationship lazy load with an exception. It is useful in tests and carefully chosen read paths because it exposes hidden database access at the line that touches the relationship. SQLAlchemy notes that raiseload does not necessarily apply inside the unit-of-work flush process, so it is a guard for application reads, not a universal SQL firewall.

Do not “fix” N+1 by loading entire tables

# Wrong direction for a page of 100 users:
all_addresses = session.scalars(select(Address)).all()

# Ask for only the aggregate the response needs:
statement = (
    select(Address.user_id, func.count(Address.id))
    .where(Address.user_id.in_(user_ids))
    .group_by(Address.user_id)
)

Replacing N queries with one unbounded table scan can increase memory and data transfer. If the UI only needs a count, query a count. If it shows five recent children, use a database-supported top-N-per-parent design or a separate endpoint. Match the data shape to the response contract.

Watch pagination and ordering

Apply the parent page boundary before loading collections. Joining a collection into the main query and then applying LIMIT can limit joined rows rather than distinct parents unless the ORM rewrites it correctly. Test stable ordering with parents that have zero, one and many children.

statement = (
    select(User)
    .where(User.active.is_(True))
    .order_by(User.created_at.desc(), User.id.desc())
    .limit(100)
    .options(selectinload(User.addresses))
)

Compare plans and end-to-end behavior

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, id, email
FROM addresses
WHERE user_id IN (101, 102, 103);

Ensure the foreign key used by the relationship has an appropriate index. Measure with production-like cardinality and network latency. Validate query count, rows returned, database time, application memory, serialization time and response bytes. Cache warmth can hide the round-trip problem on a developer laptop.

Protect the repair with a regression budget

@pytest.mark.parametrize("parent_count", [1, 10, 100])
def test_user_list_query_count_does_not_scale(client, query_counter, parent_count):
    seed_users(parent_count, addresses_each=3)
    with query_counter() as count:
        response = client.get(f"/users?limit={parent_count}")
    assert response.status_code == 200
    assert count.value <= 3

The important assertion is that query count stays bounded as parent count grows. A fixed threshold should include known framework queries and remain narrow enough to catch regression. Also test the returned associations so an optimization cannot pass by silently omitting data.

Roll out with observable evidence

• Record p50/p95 request latency and database time before the change.
• Deploy to a small slice and compare query count by route.
• Watch connection-pool wait, rows transferred and application memory.
• Confirm response ordering and authorization remain unchanged.
• Retain a quick rollback to the previous query while investigating surprises.

Authorization filters belong in the query, not in a later Python loop. Eager loading must not broaden tenant or row visibility. Review generated SQL whenever relationship criteria carry security meaning.

N+1 is a growth bug. The durable fix is not “use joins everywhere”; it is to make the required data shape explicit, keep database round trips bounded, and test that the bound survives realistic cardinality.

Sources: the solved Stack Overflow explanation (question by Lars A. Brekken; accepted answer by Matt Solnit, CC BY-SA) and SQLAlchemy’s official relationship loading documentation, including lazy, select-in, joined and raise loading.