Choosing SQLAlchemy Loading Strategies for Paginated Collections

The Problem: Pagination vs. Relationship Loading

When building a paginated API endpoint that returns a list of parent entities and their associated child collections, you face a conflict between query count and data integrity. Using default loading often leads to the "N+1 problem," where one query fetches the parents and N additional queries fetch children for each parent. Conversely, using a JOIN to fetch everything in one go often breaks pagination because the database returns one row per child, causing the LIMIT and OFFSET to apply to the total number of children rather than the number of parents.

The Useful Takeaway

For paginated one-to-many relationships, selectinload is the most reliable choice. It maintains correct pagination by fetching parents first and then fetching all related children in a single second query using an IN clause. This avoids N+1 latency and the row duplication associated with JOINs.

Decision Matrix: Loading Strategies

Strategy	Query Count	Pagination Safe	Memory/Row Risk	Best Use Case
Lazy Load	1 + N	Yes	Low initial, high latency	Single entity lookups
joinedload	1 (JOIN)	No (for collections)	Cartesian product blowup	Many-to-one relationships
selectinload	2	Yes	Proportional to page size	Paginated collections
subqueryload	2	Yes	High DB CPU on complex sets	Very large IN list limits

Trade-offs and Engineering Constraints

The joinedload Trap

joinedload uses a LEFT OUTER JOIN. While this reduces roundtrips to one, it multiplies the number of rows returned. If a parent has 10 children, the database returns 10 rows for that one parent. If you apply LIMIT 20, you might only get 2 parents if they have many children. To fix this, SQLAlchemy must use a subquery for the parent, which can be significantly slower.

The selectinload Advantage

selectinload executes two distinct queries. The first fetches the parent IDs for the current page. The second fetches all children whose parent_id is in the list of IDs retrieved from the first query. This keeps the parent pagination logic clean and the memory footprint predictable.

The subqueryload Alternative

subqueryload emits a second query that repeats the original parent query as a subquery. This is useful if the database has a strict limit on the number of parameters allowed in an IN clause, though selectinload is generally more performant on modern databases.

Implementation: SQLAlchemy 2.0 Style

This implementation assumes SQLAlchemy 2.0 declarative models. Run this within a session context with read permissions. The following pattern ensures that regardless of whether your page size is 10 or 100, only two queries are emitted.

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

# Define the query with selectinload to avoid N+1
# limit() and offset() apply strictly to the Parent entity
stmt = (
    select(Parent)
    .options(selectinload(Parent.children))
    .limit(20)
    .offset(0)
)

with Session(engine) as session:
    # execute() returns a Result object
    # scalars().all() extracts the Parent objects
    parents = session.execute(stmt).scalars().all()
    
    # Children are already loaded; accessing them does not trigger new queries
    for p in parents:
        print(f"Parent {p.id} has {len(p.children)} children")

Risks: If the limit is set to an extremely high value (e.g., 10,000), the resulting IN clause may exceed the database's maximum parameter limit. Keep page sizes bounded (e.g., 50–100).

Validation and Verification

To verify the implementation, follow these diagnostic steps:

1. SQL Logging: Initialize the engine with echo=True. Inspect the logs to confirm exactly two SELECT statements are emitted for the request.
2. Row Count Check: Verify that the number of Parent objects returned matches the limit exactly, regardless of how many children each parent has.
3. Identity Map Test: Access the children collection during serialization. If the logs show additional SELECT statements during this phase, the loading strategy was not applied correctly.

Limitations

Using selectinload requires the parent entities to be loaded into the identity map first. It is not suitable for scenarios where you need to filter the parent list based on a property of the child (for that, use join() or contains_eager()). For async engines, ensure you use AsyncSession and await the execution, though the loading options remain identical.