What this usually means
SQLAlchemy's default lazy loading causes a separate database query each time you access a relationship attribute on an ORM instance. When you iterate over a collection of parent objects and access a related attribute (like `post.author`), each access triggers a new SELECT. This turns O(1) into O(N+1) database round-trips, where N is the number of parent objects. The symptom is hundreds or thousands of tiny queries instead of a single JOIN.
The first ten minutes — establish facts before touching code.
- 1Run `echo=True` on your engine to print all SQL queries to stdout: `engine = create_engine('postgresql://...', echo=True)`
- 2Count queries manually: `len(connection.execute('SELECT * FROM pg_stat_activity WHERE query LIKE ...').fetchall())`
- 3Use SQLAlchemy event listener to log query counts: `@event.listens_for(engine, 'before_cursor_execute')` and increment a counter
- 4Profile with `cProfile`: `python -m cProfile -o output.prof my_script.py && snakeviz output.prof`
- 5Use `flask-sqlalchemy` or `SQLAlchemy-Continuum` query counter middleware if available
- 6Check application logs for repeated SQL statements with same structure but different parameters
The specific files, logs, configs, and dashboards that usually own this bug.
- searchSQLAlchemy engine echo output (stderr or log file) — look for repeated SELECTs on the same table
- searchDatabase slow query log (e.g., `pg_stat_statements` in PostgreSQL)
- searchApplication performance monitoring (APM) traces showing number of DB calls per request
- searchModel relationship definitions in your ORM models — check `lazy` parameter
- searchView/endpoint code where you iterate over collections and access relationships
- searchSQLAlchemy `before_execute` event handler logs if you've instrumented
- searchNetwork round-trip time graphs — N+1 often correlates with many small network calls
Practical causes, not theory. These are the things you will actually find.
- warningDefault `lazy='select'` on relationships — the classic N+1 trigger
- warningSerializing models with relationships (e.g., using Marshmallow or Pydantic) triggers lazy loads
- warningNested loops in business logic that access relationships inside loops
- warningUsing `db.session.query(Parent).all()` and then accessing child attributes in templates
- warningNot using `joinedload()` or `subqueryload()` when fetching parent objects
- warningCaching only parent objects but not preloading children
- warningUsing `lazy='dynamic'` which still queries per access if not careful
Concrete fix directions. Pick the one that matches your root cause.
- buildAdd `joinedload()` to the query: `session.query(Parent).options(joinedload(Parent.children)).all()`
- buildSet `lazy='joined'` or `lazy='subquery'` on the relationship definition if always needed
- buildUse `selectinload()` for many-to-many or when joinedload would cartesian product
- buildRefactor code to fetch all related data in one query using explicit joins
- buildUse `contains_eager()` if you already have a join and want to populate the relationship
- buildBatch load relationships with `lazy='noload'` and explicit batch queries using `in_()`
A fix you cannot prove is a guess. Close the loop.
- verifiedCount queries before and after: `len(connection.execute('SELECT * FROM pg_stat_activity WHERE query LIKE ...'))` should drop dramatically
- verifiedEnable echo on engine and confirm only 1 query (or a few queries) for the entire request
- verifiedUse SQLAlchemy `get_history` or `session.object_session` to check if relationships are loaded
- verifiedRun the same request in a staging environment with query counting middleware
- verifiedCheck APM dashboard — number of SQL calls per request should reduce to O(1)
- verifiedPerformance test: measure response time for worst-case N (e.g., 1000 parents) — should be constant
Things that make this bug worse or harder to find.
- warningApplying `joinedload()` indiscriminately — can cause Cartesian products with multiple joins
- warningForgetting that `joinedload()` uses LEFT OUTER JOIN which may return duplicate parents; use `distinct()` if needed
- warningSetting `lazy='joined'` globally when the relationship is rarely accessed — wastes memory and performance
- warningUsing `lazy='subquery'` without considering that it adds a subquery per relationship
- warningNot understanding that `lazy='dynamic'` still issues a query every time you call `.all()` or iterate
- warningAssuming `contains_eager()` works without an explicit join in the query
Blog API Slows to a Crawl: 1000 Posts, 1001 Queries
Timeline
- 09:15Alert: API response time >10s for /api/posts endpoint
- 09:20Checked New Relic: 1001 database calls per request, average 5ms each
- 09:25Identified pattern: one query for posts, then 1000 queries for post.author
- 09:30Checked model: `author = relationship('User', backref='posts')` — lazy='select' default
- 09:35Test fix: added `joinedload(Post.author)` to the query
- 09:40Deployed to staging, confirmed 1 query with JOIN
- 09:50Deployed to production, response time dropped to 200ms
- 10:00Monitored for 30 minutes — no issues, pager duty closed
The alert came in at 9:15 AM: the /api/posts endpoint was timing out. Our blog platform listed 1000 posts per page, and users were seeing 10-second load times. New Relic showed a textbook N+1: 1 query for posts, 1000 for author names. Each took only 5ms, but 1001 round trips plus network latency killed us.
I traced it to the serialization code. We used Marshmallow to serialize posts, and it accessed `post.author.name` for each post. SQLAlchemy's default lazy loading fired a SELECT for each one. The fix was simple: add `.options(joinedload(Post.author))` to the query. But I also checked if any other relationships were accessed — `post.comments` and `post.tags` were not in this endpoint, so no need for more joins.
After deploying, response time dropped from 10s to 200ms. The lesson: always profile query counts before assuming performance is from heavy queries. We added a query counter middleware to catch regressions in CI.
Root cause
Default lazy='select' on the `author` relationship caused 1000 additional queries when accessing `post.author` in serialization.
The fix
Added `joinedload(Post.author)` to the query in the endpoint. Also set `lazy='joined'` on the relationship because authors are always needed.
The lesson
Never trust ORM defaults for performance. Always eagerly load relationships that are accessed in the same request. Use query counting in tests.
Instead of relying on echo output, instrument your engine with a query counter. This code uses `before_cursor_execute` to increment a counter per request:
```python from sqlalchemy import event from flask import g @event.listens_for(engine, 'before_cursor_execute') def count_queries(conn, cursor, statement, parameters, context, executemany): if not hasattr(g, 'query_count'): g.query_count = 0 g.query_count += 1 ``` Then in your endpoint, log `g.query_count` after the response. A single endpoint should have <10 queries typically. This catches N+1 before it hits production.
You can also log the actual SQL statements to see patterns. For Flask, use `app.after_request` to log the count. For Django, middleware works similarly.
`joinedload()` emits a LEFT OUTER JOIN and populates the relationship in one query. It's great for to-one relationships but can cause Cartesian products for many-to-many if you join multiple collections. Use `subqueryload()` for collections: it issues a separate subquery but still loads all children in one go.
`selectinload()` is often the best for many-to-many: it issues a second query with an IN clause on parent IDs. This avoids the Cartesian product and is efficient for large datasets. Example: `session.query(Parent).options(selectinload(Parent.children)).all()`.
`lazy='dynamic'` is not a loading strategy; it returns a query object. It still queries when you access it. Only use it when you need to add filters to the relationship query.
Serialization libraries like Marshmallow, Pydantic, or GraphQL resolvers often trigger lazy loads. If you use Marshmallow with `Nested` fields, every parent triggers a query for children. Solution: preload relationships before passing to the schema.
Jinja2 templates that loop over `post.comments` also trigger lazy loads. Ensure you eager-load in the view. Use `contains_eager()` if you've already joined the table in the query and want SQLAlchemy to populate the relationship without another query.
Example: `session.query(Post).outerjoin(Post.comments).options(contains_eager(Post.comments)).all()` — this avoids N+1 when iterating `post.comments`.
Frequently asked questions
How do I find which relationship is causing N+1?
Enable SQLAlchemy echo and look for repeated SELECT statements with different WHERE parameters. The table name in the SELECT tells you which relationship. Alternatively, add a custom event listener that logs the stack trace of the first query of a repeated pattern using `traceback.extract_stack()`.
Can I set lazy loading globally to 'joined'?
You can set `lazy='joined'` on all relationships via `relationship(..., lazy='joined')`, but this can cause performance issues if you join many tables unnecessarily. It's better to be explicit per query using `options()`. For relationships that are always needed, set `lazy='joined'` on the model definition.
What's the difference between `joinedload` and `selectinload`?
`joinedload` uses a LEFT OUTER JOIN to load children in the same query. `selectinload` issues a second query with an IN clause on parent primary keys. `joinedload` is good for to-one relationships; `selectinload` is better for collections because it avoids Cartesian products when multiple collections are loaded.
Does `lazy='dynamic'` prevent N+1?
No. `lazy='dynamic'` returns a query object that still executes a SQL query when you iterate, call `.all()`, or call `.count()`. It can even cause N+1 if used in a loop. Only use it when you need to add filters to the relationship query before execution.
How do I test for N+1 in CI?
Write a test that fetches a representative number of parents (e.g., 10) and asserts the total number of queries is below a threshold. Use a test database and a query counter fixture. For example, with pytest and SQLAlchemy, you can use the `pytest-sqlalchemy` plugin or manually count queries with event listeners.