Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Fixing the Python SQLAlchemy N+1 Problem

Learn how the N+1 query problem appears in SQLAlchemy, how to detect it, and how joinedload and selectinload fix it.

~5 min readPython
#SQLAlchemy#ORM#Performance#Database Queries
Illustration showing one parent query branching into many child queries, representing the SQLAlchemy N+1 problem.

The N+1 query problem appears in SQLAlchemy when you load a collection of parent objects and then access a relationship on each one, causing a separate SELECT for each parent's related rows. The name describes the pattern: one query for the parent rows, then N additional queries for the related rows, where N is the number of parents loaded. The result is a query count that grows linearly with your data, and the cost becomes visible as soon as the collection exceeds a handful of rows.

How Lazy Loading Produces the N+1 Problem

In SQLAlchemy, relationships default to lazy loading. When you access a relationship attribute on an ORM instance, SQLAlchemy issues a new SELECT to fetch the related objects if they are not already loaded. This behavior is controlled by the lazy="select" default on the relationship.

Consider two models:

python
from sqlalchemy import Column, ForeignKey, Integer, String from sqlalchemy.orm import declarative_base, relationship Base = declarative_base() class Author(Base): __tablename__ = "authors" id = Column(Integer, primary_key=True) name = Column(String, nullable=False) books = relationship("Book", back_populates="author") class Book(Base): __tablename__ = "books" id = Column(Integer, primary_key=True) title = Column(String, nullable=False) author_id = Column(Integer, ForeignKey("authors.id"), nullable=False) author = relationship("Author", back_populates="books")

The default lazy behavior means that author.books is not fetched when the author row is loaded. Accessing the attribute triggers a query at that moment.

A Minimal Reproduction of the Problem

Fetching all authors and then reading their books produces the N+1 pattern directly:

python
session = Session() authors = session.query(Author).all() # 1 query for author in authors: print(author.name, len(author.books)) # 1 query per author

With 100 authors, this executes 101 queries. The number of queries scales with the number of parent rows, which is why the problem passes unnoticed with a small dataset and becomes obvious under real traffic.

The root cause is that the relationship attribute uses lazy loading by default, and the ORM has no way to know in advance that you will access books for every author. Each access is treated as an independent lookup.

Detecting the N+1 Problem in Your Application

The most reliable way to detect the N+1 problem is to observe the SQL statements your application emits. SQLAlchemy's echo configuration prints every query to the console:

python
engine = create_engine("postgresql://user:pass@localhost/db", echo=True)

In production, enable query logging at the database level. On PostgreSQL, pg_stat_statements is useful for spotting a single SELECT statement with a very high call count against the relationship's foreign key table. That pattern is a strong signal.

You can also count queries in tests by attaching an event listener to the engine:

python
from sqlalchemy import event query_count = 0 @event.listens_for(engine, "before_cursor_execute") def count_queries(conn, cursor, statement, parameters, context, executemany): global query_count query_count += 1

Reset query_count before each scenario, then run the code path under test and assert the count is within your expected bound. This turns the N+1 problem into a test failure rather than a production surprise.

Fixing the Problem with joinedload

The joinedload option tells SQLAlchemy to fetch the related rows in the same query using a LEFT OUTER JOIN:

python
from sqlalchemy.orm import joinedload authors = session.query(Author).options(joinedload(Author.books)).all() for author in authors: print(author.name, len(author.books))

This produces a single query that joins authors with books. The ORM assembles the object graph from the joined result set, and accessing author.books no longer triggers additional queries.

joinedload works well for many-to-one relationships such as book.author, or for one-to-many relationships where each parent has only a few children. For a one-to-many relationship with many children per parent, the JOIN multiplies the number of rows in the result set, which increases memory usage and transfer time.

Fixing the Problem with selectinload

The selectinload option uses a separate SELECT with an IN clause to load the related objects after the parents are fetched:

python
from sqlalchemy.orm import selectinload authors = session.query(Author).options(selectinload(Author.books)).all() for author in authors: print(author.name, len(author.books))

This executes two queries when at least one author is loaded: one for the authors, and one for all books whose author_id is in the set of loaded author IDs. The second query is issued once for the whole set, not once per author.

selectinload is often the better choice for one-to-many relationships because it avoids the row multiplication of a JOIN. It also handles nested eager loads more predictably when you chain multiple relationships.

Comparing joinedload and selectinload

Aspectjoinedloadselectinload
Query count1 (single JOIN)2 (parent + IN query)
Row multiplicationYes, for one-to-manyNo
Best forMany-to-one, small collectionsOne-to-many, large collections
Nested eager loadsCan produce complex JOINsSeparate query per level

The choice depends on the relationship shape and the expected data volume. For a many-to-one relationship like book.author, joinedload is straightforward and efficient. For a one-to-many relationship where each author has dozens of books, selectinload avoids transferring a large joined result set.

When Lazy Loading Is the Right Choice

Lazy loading is not inherently wrong. It is appropriate when the relationship is accessed rarely, or when the number of parent rows is small and the related data is only needed for a subset of them.

The problem is not lazy loading itself but using it without awareness of the query pattern it produces. If a code path loads parents and then accesses a relationship inside a loop, that is a clear signal that eager loading is needed.

You can also change the default loading strategy on the relationship definition:

python
class Author(Base): __tablename__ = "authors" id = Column(Integer, primary_key=True) name = Column(String, nullable=False) books = relationship("Book", back_populates="author", lazy="selectin")

This makes every query that loads authors also load their books. The tradeoff is that even queries that only need the author name will now issue the extra SELECT. Prefer per-query options like joinedload or selectinload unless the relationship is almost always needed.

Production Considerations

The N+1 problem is a runtime cost issue. Each additional query adds a database round trip, and the cost compounds under concurrent load. A single code path that issues 101 queries instead of 2 can saturate a database connection pool when the endpoint receives traffic.

Eager loading reduces the number of round trips but can increase the amount of data transferred per query. For large collections, selectinload keeps each query simple and lets the database use an index on the foreign key column.

Pagination is another area where the two strategies differ. A joinedload on a collection duplicates parent rows in the joined result, so a naive limit/offset would not paginate parents cleanly. SQLAlchemy can wrap the query in a subquery to compensate, but selectinload keeps the parent query separate and avoids that complexity.

Caching is not a substitute for fixing the N+1 problem. Even with a cache in front of the application, the database still receives the repeated queries on a cache miss. The fix belongs in the query construction, not in an external layer.

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.