Relationship Loading and Query Performance

ORM performance problems usually come from the SQL workload generated by application code: too many round trips, too many rows, large joins, missing indexes, or transactions that stay open too long.

The most important habit is:

Read ORM code and generated SQL together.

A short expression can create an expensive query, while a longer expression can reduce database work dramatically.

A simple relationship

Consider users and posts:

users
┌────┬────────┐
│ id │ name   │
├────┼────────┤
│  1 │ Alice  │
│  2 │ Bob    │
└────┴────────┘
       │
       │ one-to-many
       ▼
posts
┌────┬─────────┬──────────────┐
│ id │ user_id │ title        │
├────┼─────────┼──────────────┤
│ 10 │    1    │ ORM notes    │
│ 11 │    1    │ Indexes      │
│ 12 │    2    │ Transactions │
└────┴─────────┴──────────────┘

The ORM may expose:

user.posts
post.author

Those properties look like normal object access, but they can trigger SQL.

The N+1 problem

Suppose code loads five users:

users = session.scalars(select(User)).all()

for user in users:
    print(user.name, len(user.posts))

For the five-user demo, the parent query returns:

Each user has two matching post rows; the application output is:

With lazy loading, the SQL pattern can be:

SELECT users ...             # 1 query

SELECT posts WHERE user=1    # +1
SELECT posts WHERE user=2    # +1
SELECT posts WHERE user=3    # +1
SELECT posts WHERE user=4    # +1
SELECT posts WHERE user=5    # +1
---------------------------
6 queries total

For example, the first child query returns:

For 1,000 users, this can become 1,001 queries.

The database may spend more time on network latency, parsing, planning, and connection use than on the actual data.

Runnable N+1 demo

The repository includes:

scripts/orm/n_plus_one_demo.py

Run:

python -m pip install -r scripts/orm/requirements.txt
python scripts/orm/n_plus_one_demo.py

The script seeds five users with two posts each and counts executed SQL statements.

Expected shape:

lazy loading: 5 users, 10 posts
selectinload: 5 users, 10 posts

lazy-loading SQL statements: 6
selectinload SQL statements:  2

The exact statement count is intentionally visible so the performance difference is concrete.

Loading strategies

There is no universal "best" relationship loader.

Lazy loading

Load the relationship only when accessed.

SELECT users
      │
      ▼
User objects

later:
user.posts
      │
      ▼
SELECT posts WHERE user_id = ?

Useful when: - the relationship is rarely needed, - only a small number of parent objects are accessed, - additional round trips are acceptable.

Risk: - accidental N+1 queries, - database access from surprising places, - relationship access after the session closes.

Joined eager loading

Load parent and child rows with a join.

SELECT
    users.id,
    users.name,
    posts.id,
    posts.title
FROM users
LEFT JOIN posts
  ON posts.user_id = users.id;

Input tables are the two users and three posts shown in the relationship example near the top of this note. The joined result is:

One database query can retrieve everything.

But the relational result repeats parent columns:

Alice | post A
Alice | post B
Alice | post C
Bob   | post D

If one user has 10,000 posts, the parent data is repeated 10,000 times.

Joined loading is often useful for: - many-to-one relationships, - small related collections, - cases where one SQL round trip matters more than row duplication.

Select-in loading

A select-in strategy loads parents first, then related rows for all parent IDs.

SELECT id, name
FROM users;

SELECT id, user_id, title
FROM posts
WHERE user_id IN (1, 2, 3, 4, 5);

For the sample relationship tables above, the parent query returns:

The child query returns:

This usually gives:

1 parent query
1 child query
--------------
2 queries

It often works well for one-to-many collections.

In SQLAlchemy:

users = session.scalars(
    select(User)
    .options(selectinload(User.posts))
).all()

Different ORMs use different terms: - Django: select_related() and prefetch_related(), - EF Core: Include() plus single/split-query controls, - Hibernate: joins, fetch graphs, batch fetching, - Rails: includes, preload, and eager_load.

The underlying trade-off is still about round trips versus result size.

Cartesian explosion

Eager-loading multiple collections through one join can multiply rows.

Suppose:

1 user
10 posts
20 comments

A naive combined join can produce roughly:

10 × 20 = 200 result rows

even though there is only one user.

This can happen when multiple one-to-many relationships are joined together.

Use: - split/select-in queries, - projections, - separate queries, - pagination, when one large join would explode the result.

Projection

Do not load full objects when only a few fields are needed.

Instead of:

users = session.scalars(select(User)).all()

for a dropdown that only needs ID and name:

rows = session.execute(
    select(User.id, User.name)
    .order_by(User.name)
).all()

Using the two sample users shown above, the projection returns:

Conceptually:

full entity:
id, name, email, bio, avatar, settings, created_at, ...

projection:
id, name

Projection reduces: - bytes transferred, - object construction, - memory usage, - unnecessary column reads.

Pagination

Never assume a query returning "all rows" will stay small.

Offset pagination

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 2 OFFSET 1;

Input (posts, newest first):

Result for this small example page:

Simple, but large offsets can become expensive because the database still has to locate and skip earlier rows.

Keyset pagination

Use the last seen sort key:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 2;

With last_created_at = '2026-10-03 12:00:00' and last_id = 12, the same input rows return:

Keyset pagination is often better for deep scrolling over a stable ordered feed.

The sort must be deterministic. Include a unique tie-breaker such as id.

Counting

A common hidden cost is computing totals for pagination.

SELECT COUNT(*)
FROM posts
WHERE user_id = 1;

In the three-row sample posts table above, two rows belong to user 1, so the result is:

The count can be more expensive than fetching one page.

Consider whether the UI really needs an exact total.

Alternatives: - "has next page", - approximate counts, - cached counts, - pre-aggregated statistics.

Filtering in the database

Example users input rows:

Bad:

users = session.scalars(select(User)).all()
active = [u for u in users if u.active]

Better:

active = session.scalars(
    select(User).where(User.active.is_(True))
).all()

Both approaches return the active-user row, while the database-side query transfers only:

Move filtering, grouping, and aggregation into SQL when the database can do it efficiently.

Set-based updates

Bad bulk pattern:

orders = session.scalars(
    select(Order).where(Order.status == "expired")
).all()

for order in orders:
    order.status = "archived"

session.commit()

For many rows, this loads objects and sends many updates.

A set-based statement is often better:

UPDATE orders
SET status = 'archived'
WHERE status = 'expired';

Input (orders):

Resulting table:

ORMs usually expose bulk update APIs or raw SQL for this case.

Bulk inserts

Inserting one object and committing repeatedly is expensive.

Bad:

INSERT
COMMIT
INSERT
COMMIT
INSERT
COMMIT
...

Better: - batch objects in one transaction, - use executemany/bulk APIs, - use database bulk-load tools for very large imports.

For PostgreSQL, COPY can outperform row-by-row inserts substantially for large loads.

ORM convenience should not prevent using database-native bulk mechanisms when appropriate.

Inspect generated SQL

SQLAlchemy:

engine = create_engine(url, echo=True)

Django: - inspect QuerySet.query, - use Django Debug Toolbar in development, - enable database query logging.

EF Core: - configure command logging, - use ToQueryString() for inspection.

Hibernate: - SQL logging, - statistics, - datasource-proxy or observability tooling.

Rails: - SQL is shown in development logs, - query instrumentation can be attached through ActiveSupport.

Generated SQL should be part of code review for important data paths.

EXPLAIN

After identifying the SQL, inspect the database execution plan.

PostgreSQL example:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, title
FROM posts
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Example input rows:

The underlying SELECT returns:

Look for: - sequential scans on large tables, - unexpected row estimates, - large sorts, - nested loops over large row counts, - missing or unused indexes, - disk reads, - repeated scans.

The ORM does not replace database plan analysis.

Indexes and ORM queries

Suppose application code frequently runs:

SELECT *
FROM posts
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 20;

Using the same input rows, the result includes all selected columns:

A useful index might be:

CREATE INDEX idx_posts_user_created
ON posts(user_id, created_at DESC);

The correct index follows query patterns, not model class names.

Avoid indexing every foreign key or filter mechanically without measuring. Indexes speed reads but add write and storage cost.

Query count as a metric

For request-driven applications, count SQL statements per request.

Example:

GET /users/42
  4 SQL statements

GET /dashboard
  87 SQL statements  <-- investigate

A query-count regression test can catch accidental N+1 behavior.

Connection pool pressure

Many tiny queries consume pool time.

web workers
   │ │ │ │ │
   ▼ ▼ ▼ ▼ ▼
connection pool
   │ │ │
   ▼ ▼ ▼
database

If each request performs 100 sequential queries, connections stay checked out longer and the pool saturates sooner.

Reducing round trips can improve both latency and concurrency.

Async ORM does not make slow SQL fast

Async database APIs allow a worker to do other work while waiting for I/O.

They do not fix: - missing indexes, - N+1 queries, - huge joins, - bad cardinality estimates, - excessive rows.

slow SQL + async = slow SQL that does not block one thread

Use async when the application architecture benefits from asynchronous I/O, not as a database optimization substitute.

Caching

Caching can reduce repeated reads, but it creates invalidation and staleness concerns.

Possible layers:

request-local identity map
        │
        ▼
application cache / Redis
        │
        ▼
database buffer cache
        │
        ▼
storage

Before adding application caching: 1. fix query shape, 2. add appropriate indexes, 3. remove N+1 behavior, 4. confirm the workload is genuinely repetitive.

ORM identity map

A session may return the same in-memory object for repeated loads of one row.

a = session.get(User, 1)
b = session.get(User, 1)

# often a is b inside the same session

Input (users):

Both lookups resolve to the same row, and usually to the same in-memory object within one session:

This can reduce duplicate object loading inside one unit of work.

It is not a distributed cache and does not mean the object is always current relative to other transactions.

Avoid long-lived sessions

Keeping ORM sessions alive for too long can cause: - stale entities, - large identity maps, - unclear transaction boundaries, - unexpected lazy loads, - connection retention.

A common server pattern is:

request starts
     │
     ▼
open session
     │
     ▼
perform unit of work
     │
     ▼
commit / rollback
     │
     ▼
close session

Performance review checklist

For an important ORM query:

1. How many SQL statements run?
2. How many rows come back?
3. Which columns are selected?
4. Are relationships lazy, joined, or select-in loaded?
5. Is pagination bounded and deterministic?
6. Does the SQL have appropriate indexes?
7. What does EXPLAIN show?
8. How long is the transaction open?
9. Is the connection pool waiting?
10. Would a set-based statement be simpler?

Common mistakes

Fixing N+1 with one enormous join

This can create row multiplication and high memory use.

Loading entities for analytics

Use projections or SQL aggregates instead.

Paging without deterministic order

Rows can move between pages or appear twice.

Calling .all() on an unbounded query

Data grows even if the code does not change.

Adding caching before fixing SQL

Caching can hide the symptom while preserving the root cause.

Benchmarking only application CPU

Most ORM bottlenecks are database/network behavior.

Related notes

• Introduction to ORM
• ORM tools and their trade-offs
• Schema migrations
• Transactions, concurrency, and unit of work
• Database performance