ORMs and the N+1 problem

What an ORM maps

An object-relational mapper connects an object model to a relational model. In SQLAlchemy 2.0, a declarative mapping defines both the Python classes and metadata for the SQL tables. A mapped class usually names a table, mapped attributes describe columns, and every mapped class has at least one primary-key column.

class Post(Base):
    __tablename__ = "post"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    comments: Mapped[list["Comment"]] = relationship(back_populates="post")

class Comment(Base):
    __tablename__ = "comment"

    id: Mapped[int] = mapped_column(primary_key=True)
    post_id: Mapped[int] = mapped_column(ForeignKey("post.id"))
    body: Mapped[str]
    post: Mapped["Post"] = relationship(back_populates="comments")

Post.title corresponds to a column. Post.comments describes a link between mapped classes through the foreign key. The object graph is convenient, but the database still executes SQL statements over rows.

Wrong "The ORM turns the relational database into an object database."

Right "The ORM keeps an object model and a relational model connected through explicit mappings. SQL tables, keys, and query shapes still matter."

How lazy loading creates N+1 queries

SQLAlchemy relationships are lazy by default. The first access to an unloaded relationship usually triggers a SQL query. That behavior becomes expensive when a loop touches the same relationship on every parent:

posts = session.scalars(select(Post)).all()  # one SELECT

for post in posts:
    print(len(post.comments))                # one SELECT per post

For 50 posts with unloaded comment collections, this shape emits one query for posts and 50 more for comments. This is the N+1 problem. The individual relationship queries can each be fast while the request still pays for 51 statements.

Wrong "N+1 means the database forgot an index."

Right "N+1 describes query fan-out: one statement loads N parent objects, then relationship access emits another statement for each parent."

The first diagnostic is the SQL log. SQLAlchemy's echo=True logs emitted SQL to standard output:

engine = create_engine(database_url, echo=True)

Repeated statements whose parameter changes once per parent are the visible signature.

Eager loading is planned loading

An eager loader tells the ORM which related data this query will need. It does not promise one SQL statement. SQLAlchemy's select-in loader emits one query for the parents and another query whose IN clause contains their keys:

stmt = select(Post).options(selectinload(Post.comments))
posts = session.scalars(stmt).all()

The SQL has this general shape:

SELECT post.id, post.title FROM post;
SELECT comment.id, comment.post_id, comment.body
FROM comment
WHERE comment.post_id IN (?, ?, ?);

Two bounded statements solve the fan-out. A joined eager loader is another option and puts related rows in the parent query with a join. The middle notes cover why a join is not automatically the better choice for collections.

Make the query declare its object graph

A loading strategy belongs to a query's use case. If an endpoint always renders post comments, request them with the posts. If a background job only needs post IDs, loading comments wastes work.

In SQLAlchemy 2.0, selectinload() batches a collection into one or more additional SELECT statements whose IN clauses contain the parent keys:

stmt = (
    select(Post)
    .where(Post.published.is_(True))
    .options(selectinload(Post.comments))
)
posts = session.scalars(stmt).all()

joinedload() uses a join. For one-to-many and many-to-many collections, the join multiplies result rows. SQLAlchemy requires Result.unique() so the ORM result is uniquified by primary key:

stmt = select(Post).options(joinedload(Post.comments))
posts = session.scalars(stmt).unique().all()

The two strategies can return the same mapped object graph while producing different SQL shapes. Select-in loading usually gives collections a predictable parent query plus batched relationship queries. Joined loading can suit a to-one association, where the relationship does not multiply one parent across many child rows.

Wrong "Eager loading means one query, so joining every relationship is the fastest setting."

Right "Eager means the required relationships are planned up front. A join can multiply rows, while select-in eager loading intentionally emits additional batched statements."

Hibernate 7.1 documents a sharper failure mode for global eager associations: a JPQL query that omits a required JOIN FETCH can cause Hibernate to issue secondary selects, which can produce N+1. Its guide recommends lazy associations by default and fetching the required graph before the persistence context closes. The exact defaults and APIs are framework-specific; the query-shaping problem is shared.

Turn hidden loads into failures

SQL logs reveal N+1 after the code path runs. SQLAlchemy also provides raiseload(), which replaces an unexpected relationship lazy load with an exception. A query can whitelist the relationship it needs and reject the rest:

stmt = select(Order).options(
    joinedload(Order.customer),
    raiseload("*"),
)
orders = session.scalars(stmt).all()

This is useful in tests and in code paths where an unplanned database call is a bug. It also exposes a common misconception about serialization. Walking a mapped object graph can access relationships and trigger SQL even when the repository method itself appears to contain one query.

raiseload() does not apply inside SQLAlchemy's unit-of-work flush. If a flush needs a relationship to finish its work, it can load that relationship despite the directive.

For regression coverage, count statements around the endpoint with the instrumentation supported by your stack. Hibernate's 7.1 guide explicitly recommends statement logging and describes using a data-source proxy to assert statement counts in integration tests.

// statementCountFor is an application test helper backed by SQL instrumentation.
assertThat(statementCountFor(() -> orderService.listOrders()))
    .isLessThanOrEqualTo(2);

The count should not grow once per parent. Account for documented loader batch limits when a fixture can cross them. A test with one parent cannot reveal a query count that scales with N.

Joins have limits

Joining multiple to-many associations in parallel creates a Cartesian product at the database level according to the Hibernate 7.1 guide. It also advises against fetch joins in limited or paged queries. This shape selects the page of parents first and loads the collection in a separate batch:

page_stmt = select(Order).order_by(Order.id).limit(50)
page_stmt = page_stmt.options(
    joinedload(Order.customer),
    selectinload(Order.lines),
)
orders = session.scalars(page_stmt).all()

Inspect the generated SQL and returned row volume for the actual cardinalities. Query count is one signal; a single statement with a huge multiplied result is a different performance problem.

The identity map has session scope

SQLAlchemy's Session maintains an identity map. Within one session, a database identity has one Python object instance. Two queries for the same primary key can execute two SQL statements and still return the same object:

u1 = session.scalars(select(User).where(User.id == 5)).one()
u2 = session.scalars(select(User).where(User.id == 5)).one()

assert u1 is u2

The identity guarantee prevents two conflicting in-memory representatives of the same row inside that session. It is not a guarantee that arbitrary queries skip the database. SQLAlchemy also documents that when a query finds an object already loaded, it normally skips repopulating that object's attributes. Refresh the object when the application must reload its state under the current transaction:

session.refresh(u1)  # emits a query immediately

Wrong "The identity map is a process-wide query cache. Once a row has loaded, later queries cannot reach the database."

Right "The identity map preserves object identity for a primary key inside one session. Query execution and object identity are separate concerns."

This nuance also explains why lazy many-to-one access does not always produce one query per parent. SQLAlchemy can satisfy a simple primary-key relationship from objects already present in the current identity map. You still measure the code path because the number of emitted statements depends on which related identities are already loaded.

Unit of work and flush timing

The unit of work records changes to managed objects and coordinates their database writes. SQLAlchemy instruments mapped attributes and collections, stores pending changes in the session, and flushes them before certain queries or commit. Hibernate describes its persistence context as a transactional write-behind cache: changes happen in memory first, then a flush translates entity changes into INSERT, UPDATE, or DELETE statements.

with Session(engine) as session:
    with session.begin():
        order = session.get(Order, order_id)
        order.status = "paid"  # pending in the unit of work
        session.flush()         # emits the required database write now

Flush and commit are different operations. A flush synchronizes pending state with the database inside the transaction. Commit completes the transaction. Exact automatic flush triggers depend on the ORM and its configured flush mode, so interview answers should name the framework before claiming a trigger.

The session boundary therefore carries object identity, pending writes, and transaction state together. Hibernate documents Session as a short-lived, single-threaded unit-of-work object. SQLAlchemy likewise says a Session is mutable and stateful and must not be shared among concurrent threads or asyncio tasks. Give each request or task its own session:

def handle_request():
    with Session(engine) as session:
        return run_request_queries(session)

When native SQL is the clearer boundary

Hibernate 7.1 exposes native SQL for database-specific features such as window functions, common table expressions, and Oracle CONNECT BY. SQLAlchemy lets Session.execute() run a parameterized textual statement in the session's transactional context.

stmt = text("""
    SELECT customer_id, SUM(total) AS spend
    FROM orders
    WHERE created_at >= :since
    GROUP BY customer_id
""")

rows = session.execute(stmt, {"since": cutoff}).all()

Native SQL is a reasonable boundary when a required database-specific feature or result shape is clearer in SQL than through the ORM query API. Keep values in bound parameters and decide what the result represents: scalars, a DTO, or managed entities.

Wrong "Once one query uses raw SQL, the repository must abandon the ORM and open a separate connection."

Right "Both SQLAlchemy and Hibernate provide native-query APIs. Raw SQL can stay inside the existing session and transaction boundary."

There is a cost. A database-dialect query gives up some portability, and mapping a native result to managed entities has stricter column requirements. Hibernate's 7.1 guide requires a native entity query over an inheritance mapping to include the properties of the base class and every subclass. A narrow scalar or DTO result avoids pretending that an incomplete report row is a fully managed entity.

Raw SQL does not repair a poorly chosen transaction or loading boundary. It is useful when the SQL itself is the part that needs direct control.