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.

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:
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:
session = Session()
authors = session.query(Author).all() # 1 query
for author in authors:
print(author.name, len(author.books)) # 1 query per authorWith 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:
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:
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 += 1Reset 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:
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:
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
| Aspect | joinedload | selectinload |
|---|---|---|
| Query count | 1 (single JOIN) | 2 (parent + IN query) |
| Row multiplication | Yes, for one-to-many | No |
| Best for | Many-to-one, small collections | One-to-many, large collections |
| Nested eager loads | Can produce complex JOINs | Separate 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:
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 toolsSmall utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related Python guides
All Python guidesPython iter(): How the Built-in Function Works
Explains Python's iter() built-in: how it creates iterators, the two-argument sentinel form, custom iterator classes, and common edge cases.
Python Pika: Queues, Exchanges, Routing Keys, and Acknowledgements
Learn how to use Python's Pika client with RabbitMQ: declare queues and exchanges, publish with routing keys, consume messages, and handle acknowledgements reliably.
Python Kafka: kafka-python vs Confluent Client
python kafka python vs confluent kafka: Compare kafka-python and Confluent Kafka Python clients: API differences, performance, feature coverage, and when to choose eac...
Python Kafka Offsets, Partitions, and Manual Commits
Learn how Kafka offsets and partitions work and how to manually commit offsets in Python consumers with kafka-python and confluent-kafka. Covers sync vs async commits, rebalances, and seeking.
More in Python
All Python guidesThe guides filed next to this one in Python, in library order.