Diagnosing and Fixing N+1 Queries Caused by SQLAlchemy Lazy Loading

Recognizable Condition

When iterating over a collection of parent objects and accessing a related attribute (e.g., order.items for each Order), the application shows a sudden increase in query count or latency. Logs reveal many similar SELECT statements against the related table, one per parent row.

Cause/Diagnostic Table

Symptom	Likely Cause	Diagnostic Indicator
Many identical SELECT statements for the same relationship	Default lazy='select' loading triggers a separate query each time the attribute is accessed	Engine echo or logs show a SELECT for the related table after each attribute access
High memory usage but few queries	Relationship configured with lazy='joined' causing a large result set	Single query returns many duplicate parent columns
Attribute returns a query object instead of a list	Relationship uses lazy='dynamic'	Accessing the attribute yields sqlalchemy.orm.query.Query instance

Ordered Checks

1. Enable SQL echo to see raw SQL:

   # In your script or interactive session
   from sqlalchemy import create_engine
   engine = create_engine('', echo=True)
   # Run the code that iterates over parents and accesses the relationship
   

   Run with sufficient DB read permissions. Observe the output.

2. Count queries using an event listener:

   from sqlalchemy import event
   query_count = 0
   @event.listens_for(engine, "before_cursor_execute")
   def receive_before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
       global query_count
       query_count += 1
   # Execute your workload
   print(f"Total queries: {query_count}")
   

   Compare the count to the expected number (e.g., 1 for parents + 1 for eager load).

3. Verify the loading strategy of the relationship:

   from sqlalchemy import inspect
   mapper = inspect(ParentClass)
   rel = mapper.relationships['items']
   print(rel.lazy)  # Expected: 'select', 'joined', 'dynamic', etc.
   

   No special permissions needed; just import the model.

Fixes Tied to Findings

• If diagnostic shows lazy='select' and many SELECTs (N+1):

  • Apply eager loading for the specific query:

  from sqlalchemy.orm import joinedload
  session.query(ParentClass).options(joinedload(ParentClass.items)).all()
  

  • Or set the relationship to lazy='joined' globally if the collection is small and always needed:

  # In model definition
  items = relationship('Item', lazy='joined')
  

  Risk: Joined loading can inflate result size; verify with EXPLAIN ANALYZE if the joined columns cause excessive duplication.

• If diagnostic shows lazy='joined' causing large rows:

  • Revert to lazy='select' and use joinedload only where needed.
  • Consider lazy='subquery' for collections that are moderately sized.

• If diagnostic shows lazy='dynamic' and you need the full list:

  • Replace with lazy='select' or eager load, then call all() on the dynamic attribute.

  parent.items.all()  # forces evaluation
  

Escalation Criteria

• Query count remains high after applying joinedload (possible cartesian product from multiple joined collections).
• Memory usage spikes (> expected row size * number of parents) after switching to lazy='joined'.
• Application latency does not improve after fixing the relationship; consider profiling other layers (e.g., serialization, caching).
• In such cases, collect a query plan (EXPLAIN ANALYZE) and consult a DBA or senior engineer for indexing or schema adjustments.

Practical Verification

After applying a fix, repeat the ordered checks. The query count should drop to the expected baseline (e.g., 1 query for parents + 1 for the relationship). Logs should no longer show repetitive SELECTs for the same relationship per parent row.

Example

Suppose we have:

class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer, primary_key=True)
    items = relationship('Item', lazy='select')  # default

class Item(Base):
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    order_id = Column(Integer, ForeignKey('orders.id'))
    name = Column(String)

Iterating without eager load:

for order in session.query(Order).all():
    print(order.items)  # triggers SELECT per order

With joinedload:

for order in session.query(Order).options(joinedload(Order.items)).all():
    print(order.items)  # no extra SELECTs

Run the script with echo=True before and after the change to confirm the difference.