Lazy Loading vs Eager Loading in SQLAlchemy

When working with relational databases in SQLAlchemy, how you load related data can significantly impact application performance. Two primary strategies are lazy loading and eager loading.

1. Lazy loading
Definition:
Lazy loading means that related data is not loaded from the database until it is explicitly needed in Python code. This is the default behavior in SQLAlchemy.

How it works:
When you query a parent object, SQLAlchemy does not immediately fetch the related child objects. Instead, it creates a proxy object. When you first access that relationship, SQLAlchemy runs a separate SQL query to retrieve the data.

Example:

 from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, declarative_base, Session
from sqlalchemy import create_engine, select

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    posts = relationship("Post", back_populates="author", lazy="select")  # default lazy

class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    user_id = Column(Integer, ForeignKey("users.id"))
    author = relationship("User", back_populates="posts")

# Setup
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
session = Session(bind=engine)

# Example Query with Lazy Loading
user = session.execute(select(User).where(User.name == "Alice")).scalar_one()
# Only user is fetched here
# Accessing related posts triggers a new SQL query
for post in user.posts:
    print(post.title) 

Pros:

• Fetches data only when needed.
• Reduces initial query time for unused relationships.

Cons:

• Can lead to the ‘N+1 query problem’ if you iterate over multiple parents and access their relationships in a loop.
• More round-trip to the database.

2. Eager loading
Definition:
Eager loading means that related data is fetched along with the main query using SQL joins or additional SELECT queries.

How it works:
When you query a parent object, SQLAlchemy retrieves the child objects immediately. This reduces the number of separate queries but can increase the size of the query.

Types:

• Joined Eager Loading – Uses JOIN in the SQL query.
• Subquery Eager Loading – Uses a separate query for related data but loads it in bulk.

Example:

=
from sqlalchemy.orm import joinedload, subqueryload

# Joined Eager Loading: Load user and their posts in a single JOIN query
user_with_posts = session.execute(
    select(User).options(joinedload(User.posts)).where(User.name == "Alice")
).scalar_one()

for post in user_with_posts.posts:
    print(f"{user_with_posts.name} wrote: {post.title}")

# Subquery Eager Loading: Fetch posts in a second but batched query
user_with_posts_subq = session.execute(
    select(User).options(subqueryload(User.posts)).where(User.name == "Alice")
).scalar_one()

for post in user_with_posts_subq.posts:
    print(f"{user_with_posts_subq.name} wrote: {post.title}")

Pros:

• Avoids the N+1 query problem.
• Good for fetching all required data in one go.

Cons:

• Can increase the size of the result set unnecessarily if you don’t need the related data.
• Joins can become expensive for large datasets.

3. Choosing Between Lazy and Eager Loading
Use Lazy loading when:

• The related data is rarely accessed.
• You want to keep the initial query lightweight.

Use Eager loading when:

• You know you will need the data immediately.
• You want to optimize for fewer database round-trips.

Summary Table

Initial Query Size	Small	Large	Affects startup performance
Queries Count	Multiple	Fewer	Impacts DB round trips
Performance Risk	N+1 Problem	Large Joins	Affects scalability
When to Use	Rarely used data	Frequently used data	Depends on access pattern

Conclusion:
In SQLAlchemy, the choice between lazy loading and eager loading depends on your application’s data access patterns and performance requirements. Lazy loading can keep initial queries performance very fast and lightweight, but may lead to the N+1 query problem if related data is accessed frequently. Eager loading helps avoid excessive queries by preloading data, but can increase memory usage and query complexity.