Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

SQLAlchemy Lazy vs Eager Loading: joinedload and selectinload

Understand why SQLAlchemy's default lazy loading causes N+1 queries, and how eager loading with joinedload and selectinload avoids the problem.

~6 min readPython
#SQLAlchemy#ORM#Performance#Python
Diagram showing SQLAlchemy lazy loading with multiple queries versus eager loading with joinedload and selectinload strategies.

SQLAlchemy's default lazy loading issues a separate SELECT when you access a relationship that has not already been loaded. That is convenient for single-object access, but in bulk operations it creates the N+1 query problem. Switching to eager loading with joinedload or selectinload is the usual fix. This article explains how both strategies work, where they differ, and how to choose between them.

The N+1 Problem and Why Lazy Loading Is the Default

Consider two models: User and Address, where a user has many addresses.

python
from sqlalchemy.orm import declarative_base, relationship from sqlalchemy import Column, Integer, ForeignKey, String Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) name = Column(String) addresses = relationship("Address", back_populates="user") class Address(Base): __tablename__ = "addresses" id = Column(Integer, primary_key=True) email = Column(String) user_id = Column(Integer, ForeignKey("users.id")) user = relationship("User", back_populates="addresses")

If you load all users and then iterate over their addresses, lazy loading triggers a new query for each user:

python
users = session.query(User).all() for user in users: print(user.addresses) # one SELECT per user

With 100 users, you get 1 query for the users and 100 queries for addresses. This is the classic N+1 pattern. Lazy loading is the default because it avoids loading related data until you actually need it, which can be efficient for single-object access. But in bulk operations, it becomes a performance problem.

Eager Loading with joinedload: How It Works and When It Fits

joinedload tells SQLAlchemy to fetch the related objects in the same SQL query using a JOIN. For a many-to-one relationship, this is often a single LEFT OUTER JOIN. For a one-to-many collection, it produces a cartesian product of parent and child rows, which can become large if the collection is big.

python
from sqlalchemy.orm import joinedload users = session.query(User).options(joinedload(User.addresses)).all()

This generates a single SQL query that joins users with addresses. SQLAlchemy then deduplicates the parent rows in Python, so you get a list of User objects with populated addresses collections without extra queries.

joinedload is most effective for many-to-one references where each parent has exactly one related object. For example, loading an Address with its User:

python
addresses = session.query(Address).options(joinedload(Address.user)).all()

This avoids the N+1 for the reverse direction. However, when the collection is large, the JOIN can produce a huge result set, increasing memory and network transfer. The deduplication step also adds CPU overhead.

Eager Loading with selectinload: A Second Query Strategy

selectinload takes a different approach. Instead of joining in the original query, it issues a second SELECT statement that loads all related objects for the parent set at once, using a WHERE ... IN clause on the foreign key.

python
from sqlalchemy.orm import selectinload users = session.query(User).options(selectinload(User.addresses)).all()

This results in two queries total: one for the users, and one for all addresses whose user_id is in the list of loaded user IDs. The second query is efficient because it uses a single IN clause, and the result is processed into collections in Python.

selectinload avoids the large cartesian product that joinedload can create for collections. It also works well for nested eager loading, because each level adds one extra query, not a massive join.

Comparing joinedload and selectinload in Practice

The choice between the two often comes down to the cardinality of the relationship and the shape of your data.

Criterionjoinedloadselectinload
SQL queriesOne query with JOINTwo queries (parent + IN subquery)
Result set sizeCan blow up with large collectionsLinear with number of related rows
Best forMany-to-one referencesOne-to-many collections
Nested eager loadingCan become unwieldy with deep joinsEach level adds a separate query
Database compatibilityUses standard SQL joinsUses standard SQL IN clauses
Memory on clientHigher due to duplicate parent rowsLower, since no duplication

For a many-to-one relationship like Address.user, joinedload is usually the better choice because the JOIN adds only one extra column set and does not multiply rows. For a one-to-many relationship like User.addresses, selectinload is generally safer because it avoids the row multiplication.

There is also a subtle difference in how filtering interacts with each strategy. Because joinedload loads related rows through a JOIN, filters that reference the joined table can affect the query shape. selectinload runs separately, so it does not interfere with the primary query's WHERE clause.

Common Pitfalls: Filtering, Collection Size, and Query Complexity

A frequent mistake is using joinedload on a collection and expecting a filter on the relationship to limit the loaded collection. For example:

python
# This filters the parent rows, not the collection users = session.query(User).options(joinedload(User.addresses)).filter(User.addresses.any(email="a@b.com")).all()

This query returns only users that have at least one matching address, but it still loads all addresses for each returned user. If you need to filter the collection itself, you need a separate relationship or use contains_eager with an explicit join, which is more complex.

selectinload avoids this pitfall because the collection loading is a separate query, so you can safely filter the parent query without affecting the related objects.

Another consideration is query complexity. If you have multiple collections on the same parent, joinedload can create a massive join with multiple tables, leading to a combinatorial explosion. selectinload keeps each collection loading separate, so the query count increases linearly with the number of collections, but each query stays simple.

Choosing the Right Strategy for Your Relationship

The decision should be based on the relationship type and the typical size of the related data.

  • Use joinedload for many-to-one references where you always need the related object. The extra JOIN is cheap and avoids a second round trip.
  • Use selectinload for one-to-many collections where the collection can be large. The separate query is more predictable and avoids row multiplication.
  • If you need to eager load multiple collections, prefer selectinload to keep the query plan manageable.
  • If you are loading a single parent object and its small collection, either works; joinedload might be marginally faster because it is one query, but selectinload is often clearer.

A practical pattern is to define the loading strategy at the query level, not on the relationship itself, so you can vary it per use case. This keeps the default lazy but allows you to opt into eager loading where it matters.

For example, in a REST API endpoint that returns a user with their addresses, you would write:

python
user = session.query(User).options(selectinload(User.addresses)).filter(User.id == user_id).first()

This gives you a single user and all addresses in two queries, which is predictable and efficient.

When you have deeply nested relationships, such as User.addresses.city, you can chain the loading options. Assuming Address has a city relationship:

python
from sqlalchemy.orm import selectinload users = session.query(User).options( selectinload(User.addresses).selectinload(Address.city) ).all()

This will produce three queries: users, addresses, and cities. Each level is loaded independently, which is often more efficient than a deep join.

Remember that eager loading is not a substitute for proper indexing. The second query in selectinload uses a foreign key, so ensure that column is indexed. For joinedload, the join condition benefits from indexes on both sides. Without indexes, even eager loading will be slow.

Finally, be aware of the difference between eager loading and explicit joins. joinedload and selectinload are purely for loading related objects; they do not let you filter or modify the query based on the related table. If you need to filter on the related table, use join and contains_eager instead.

In summary, the default lazy loading is fine for single-object access but breaks down in bulk operations. joinedload and selectinload give you control over when related data is fetched. Choose joinedload for many-to-one and selectinload for collections, and always consider the size of the result set and the number of queries you are willing to execute.

Try matching browser tools

All tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.