Spring Boot Course · Lesson 9

The N+1 Query Problem

Your paginated endpoints look fast and correct. But turn on the SQL log and GET /api/books fires seven queries for one page of six books — one for the page, then one more for every single book to load its authors. That's the N+1 problem: the most common performance trap in JPA. This lesson makes it visible, makes your app refuse to hide it, then kills it with one annotation.

The win

Three moves. See it — read the SQL log and watch one request explode into 1 + N queries. Fail loud — turn off Open Session in View so a hidden lazy load throws instead of silently querying. Fix it — add @EntityGraph so the association loads in the same query: one JOIN instead of N+1 round-trips. All verified against real Hibernate output.

What N+1 actually is

You run 1 query to fetch a list of parents. Then, for each of the N parents, the ORM quietly fires one more query to fetch the related data. One page of 20 books that each touch their authors becomes 21 database round-trips — and it scales with your data, not your code. The query count is invisible in the JSON response; the only way to see it is to watch the SQL.

Step 1 — Turn on the SQL log and watch it happen

Hibernate can print every statement it runs. Switch it on (this is the logging-based alternative we flagged back in Lesson 5 — no show-sql needed):

src/main/resources/application.properties
# Hibernate never alters the schema - Liquibase owns it. Just verify entities match the tables.
spring.jpa.hibernate.ddl-auto=validate


# Render Page results in the stable { content, page } shape (PagedModel) instead of raw PageImpl
spring.data.web.pageable.serialization-mode=via_dto


# Cap how many rows one page request can pull
spring.data.web.pageable.max-page-size=100
# (optional) change the default page size for unparameterized requests
spring.data.web.pageable.default-page-size=20


# Log every SQL statement Hibernate executes
logging.level.org.hibernate.SQL=DEBUG

Restart, then hit the books list and count the SQL lines:

Terminal window
curl -s "http://localhost:8080/api/books?size=20"    # 6 books (3 seeded, 2 added in Lesson 6, 1 in Lesson 8), 6 authors
-- 1: the page of books
select b1_0.id,b1_0.title,b1_0.year from book b1_0 order by b1_0.title,b1_0.id offset ? rows fetch first ? rows only
-- then ONE of these per book, to load its authors:
select a1_0.book_id,a1_1.id,a1_1.name from book_author a1_0 join author a1_1 on a1_1.id=a1_0.author_id where a1_0.book_id=?
select a1_0.book_id,a1_1.id,a1_1.name from book_author a1_0 join author a1_1 on a1_1.id=a1_0.author_id where a1_0.book_id=?
select a1_0.book_id,a1_1.id,a1_1.name from book_author a1_0 join author a1_1 on a1_1.id=a1_0.author_id where a1_0.book_id=?
select a1_0.book_id,a1_1.id,a1_1.name from book_author a1_0 join author a1_1 on a1_1.id=a1_0.author_id where a1_0.book_id=?
select a1_0.book_id,a1_1.id,a1_1.name from book_author a1_0 join author a1_1 on a1_1.id=a1_0.author_id where a1_0.book_id=?
select a1_0.book_id,a1_1.id,a1_1.name from book_author a1_0 join author a1_1 on a1_1.id=a1_0.author_id where a1_0.book_id=?

One query for the page, six for the authors — 1 + 6 = 7. Notice there's no count(*) in that log, even though Lesson 8 said a Page costs the rows plus a count query: Spring Data skips the count when the first page comes back short — six rows is under the page size of 20, so it already knows there's no next page. Ask for more rows than fit on a page and the count query shows up. The authors endpoints don't have this problem: AuthorResponse is just id and name, so mapping an author touches nothing lazy — a flat DTO is the cheapest N+1 fix there is, and you made it in Lesson 6 without noticing.

-- GET /api/authors?size=20  ->  one statement, nothing lazy to chase
select a1_0.id,a1_0.name from author a1_0 order by a1_0.name,a1_0.id offset ? rows fetch first ? rows only
Why one page = many queries

In Lesson 6 the association was mapped @ManyToMany(fetch = FetchType.LAZY) — the authors aren't loaded with the book; authors is a placeholder collection until something touches it. And something does: mapping each Book to a BookResponse calls book.getAuthors() to build the nested author responses. That first touch fires a fresh select against the book_author join table, joined to author. Six books, six touches, six queries. Pagination (Lesson 8) caps the damage at page size rather than table size — but 1+20 per request is still a bug.

Step 2 — Why you never noticed

Those lazy loads happen during DTO mapping, in the controller — after the service returned. That should be impossible: a lazy association needs an open persistence session, and the transaction that loaded the books is long over. It works because of a Spring Boot default called Open Session in View (OSIV): it holds the Hibernate session open for the entire web request, straight through JSON rendering. Convenient — it's why entity-to-DTO mapping "just works" — but it's exactly what lets N+1 hide. The queries fire; you just never feel them.

Step 3 — Make it fail loud

Turn OSIV off. Now the session closes when the transaction that loaded the data ends — in our app, the repository call itself — and any lazy access during mapping has no session to run in:

src/main/resources/application.properties
13 collapsed lines
# Hibernate never alters the schema - Liquibase owns it. Just verify entities match the tables.
spring.jpa.hibernate.ddl-auto=validate


# Render Page results in the stable { content, page } shape (PagedModel) instead of raw PageImpl
spring.data.web.pageable.serialization-mode=via_dto


# Cap how many rows one page request can pull
spring.data.web.pageable.max-page-size=100
# (optional) change the default page size for unparameterized requests
spring.data.web.pageable.default-page-size=20


# Log every SQL statement Hibernate executes
logging.level.org.hibernate.SQL=DEBUG


# Close the session at the end of the service layer, not the end of the request
spring.jpa.open-in-view=false

Restart and hit the books list again. The hidden N+1 becomes a loud, immediate failure:

HTTP/1.1 500
{"detail":"An unexpected error occurred.","instance":"/api/books","status":500,"title":"Internal Server Error"}

The generic problem body is the Lesson 7 handler doing its job — hiding internals from the client. The real cause is in the log:

org.hibernate.LazyInitializationException: Cannot lazily initialize collection of role 'com.example.library.book.Book.authors' with key '01a0d23f-3f2d-7f3c-83a1-d3780b048fd0' (no session)
Transaction, session, detached — the vocabulary

A database transaction is a unit of work that runs all-or-nothing (commit or roll back). Hibernate binds a persistence session — the context that tracks your entities and can lazily load their associations — to that transaction. When the transaction ends, the session closes and every entity it loaded becomes detached: a plain object with no live link to the database. Touch a lazy association on a detached entity and there's no session to run the query in — that's the LazyInitializationException above. (In our app there's no @Transactional on the service, so the loading transaction is the repository call itself — the session closes the moment findAll returns, well before the controller maps.)

A failure is better than a silent tax

This looks like a regression — you just broke the books endpoints. It's the opposite: OSIV was propping up a latent problem, paying a hidden per-row query tax on every request. Turning it off converts "silently slow" into "loudly wrong," so you fix the fetch deliberately instead of shipping the N+1. The same instinct as the pagination tiebreaker: make the guarantee un-bypassable. From here, every lazy load you actually need must be fetched on purpose — which is the fix.

The switch comes down to who owns the session and how long it lives:

OSIV on (Spring Boot default)OSIV off
Who owns the sessionthe request — opened at request starteach repository call
Session lifespanthe whole request, through JSON renderingjust the repository method
Entities after the service returnsmanaged (session still open)detached (session closed)
book.getAuthors() during mappinginitializes → fires a query silentlythrows LazyInitializationException
Net resultworks, but a hidden N+1a loud, immediate failure

OSIV-on isn't "no boundary" — it's a much wider one (the whole request) that makes lazy loading always succeed. That convenience is exactly what hides the bug; narrowing the session back to the query that loaded the data is what surfaces it.

Step 4 — Fix it with @EntityGraph

The book needs its authors, so fetch them together. @EntityGraph tells Spring Data which associations to pull in the same query — redeclare the repository methods that feed the DTO mapping and annotate them:

src/main/java/com/example/library/book/BookRepository.java
package com.example.library.book;


import java.util.Optional;                               // new: findById's return type
import java.util.UUID;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.EntityGraph;   // new
import org.springframework.data.jpa.repository.JpaRepository;


public interface BookRepository extends JpaRepository<Book, UUID> {


    @Override
    @EntityGraph(attributePaths = "authors")
    Page<Book> findAll(Pageable pageable);


    @Override
    @EntityGraph(attributePaths = "authors")
    Optional<Book> findById(UUID id);


    @EntityGraph(attributePaths = "authors")
    Page<Book> findByAuthorsId(UUID authorId, Pageable pageable);


    // Is any book still linked to this author? (Spring Data derives an EXISTS query.)
    boolean existsByAuthorsId(UUID authorId);
}

That's the whole fix. Restart, hit the list, and the seven queries collapse to one — a single statement that carries the authors along:

select b1_0.id,a1_0.book_id,a1_1.id,a1_1.name,b1_0.title,b1_0.year
from (select b1_0.id,b1_0.title,b1_0.year from book b1_0 order by b1_0.title,b1_0.id offset ? rows fetch first ? rows only) b1_0(id,title,year)
left join book_author a1_0 on b1_0.id=a1_0.book_id
left join author a1_1 on a1_1.id=a1_0.author_id
order by b1_0.title,b1_0.id

The single-book endpoint gets the same treatment, minus the paging wrapper — there's only one book to fetch, so Hibernate joins straight through:

select b1_0.id,a1_0.book_id,a1_1.id,a1_1.name,b1_0.title,b1_0.year
from book b1_0
left join book_author a1_0 on b1_0.id=a1_0.book_id
left join author a1_1 on a1_1.id=a1_0.author_id
where b1_0.id=?
Why this is paging-safe even for a collection

Joining a to-many multiplies rows: a book with two authors becomes two rows, so a naive LIMIT would count author-rows and could cut a book in half. Hibernate 7 avoids that. Read the captured SQL from the inside out: it pages the books in a subquery first (offset ? rows fetch first ? rows only inside the parentheses), then joins the authors onto that fixed set of books. The Lesson 8 id tiebreaker is what makes that inner page deterministic. Older Hibernate versions couldn't do this — they logged HHH000104 ("firstResult/maxResults specified with collection fetch; applying in memory") and paged the whole result set in memory. That warning is absent here.

Every books endpoint is green again with one query each, and LazyInitializationException is gone — because every association a controller touches is fetched on purpose. (The write paths stay green too: POST, PUT and DELETE /api/books still return 201/200/204 with OSIV off. They were never at risk: the service builds the book's authors set in memory from findAllById (Lesson 6) before saving, so it's a plain populated collection, not a lazy proxy, and mapping it needs no open session. DELETE maps nothing at all.)

Other tools in the fetching box

@EntityGraph is the everyday fix, but know the neighbours:

  • JOIN FETCH — the same idea written explicitly in JPQL (@Query("select b from Book b join fetch b.authors")); use it when you need a custom query, not just a fetch hint.
  • @BatchSize / hibernate.default_batch_fetch_size — when you keep associations lazy, this batches the N loads into a few IN (?, ?, …) queries instead of N singles. Turns 1 + N into roughly 1 + (N / batchSize).
  • DTO projections — select exactly the columns you need straight into a DTO (JPQL select new, or a Spring Data interface projection), skipping entity loading altogether. The lightest option for read-heavy endpoints.
  • FetchType.EAGER on the mapping — looks like the same fix with less typing, and isn't. Tried on this project with OSIV off and no entity graph: GET /api/books?size=20 returns 200 again, but the log still shows the page query followed by six book_author selects — for a query, Hibernate satisfies EAGER with extra selects, not a join, so the N+1 is merely hidden inside the repository call where OSIV-off can no longer expose it. Only the by-id lookup gets a real join. And because it's a property of the mapping, every load of a Book pays for the authors — DELETE now fetches them before deleting — with no way to say "not this time". An entity graph is per query; EAGER is forever.

Before and after

Same endpoints, same data, one annotation's difference:

EndpointOSIV on (hidden)OSIV off, no fixOSIV off + @EntityGraph
GET /api/books?size=201 + 6 = 7 queries500 — fails loud1 query (paginate, then join)
GET /api/books/{id}1 + 1 = 2 queries500 — fails loud1 query (LEFT JOIN)
GET /api/authors?size=201 query1 query — still 2001 query (flat DTO, nothing lazy)

Step 5 — Turn the SQL log off again

The SQL log did its job: it showed the N+1 problem and then showed it gone. Left in application.properties, it would keep logging every statement of every request wherever the app runs, production included — a flood of lines nobody reads, written on every query. Take it out:

src/main/resources/application.properties
# Hibernate never alters the schema - Liquibase owns it. Just verify entities match the tables.
spring.jpa.hibernate.ddl-auto=validate


# Render Page results in the stable { content, page } shape (PagedModel) instead of raw PageImpl
spring.data.web.pageable.serialization-mode=via_dto


# Cap how many rows one page request can pull
spring.data.web.pageable.max-page-size=100
# (optional) change the default page size for unparameterized requests
spring.data.web.pageable.default-page-size=20


# Close the session at the end of the service layer, not the end of the request
spring.jpa.open-in-view=false

Restart and hit the books list: the response is the same, and the log no longer shows any SQL. The SQL log is a tool you switch on to investigate and off when you're done — add the line back whenever you need to see what Hibernate runs. open-in-view=false stays: that one is a rule for the app, not a look inside it.

If it doesn't behave
  • Still seeing N+1 in the log? The @EntityGraph is on a method the request doesn't call. The list uses findAll(Pageable) / findByAuthorsId; make sure the graph is on the exact overload being invoked.
  • LazyInitializationException after the fix? Some association a DTO touches still isn't fetched. Trace what the mapping reads and add it to attributePaths, or trim the DTO so it doesn't reach for it.
  • Overriding findAll(Pageable) won't compile? Match the inherited signature exactly and add @Override; import org.springframework.data.jpa.repository.EntityGraph.
  • Want to confirm the count without eyeballing logs? That's what a test asserting the statement count is for — Lesson 12 writes one for each entity graph.

Check yourself

Answer from memory before revealing.

A page of 20 books logs 21 SQL statements. What is happening?
Why did the N+1 run silently until you turned open-in-view off?
Why is @EntityGraph(attributePaths = "authors") safe on a paginated books query?
Why don't the author endpoints show N+1 at all?

Read this next

Primary source: Vlad Mihalcea's N+1 query problem with JPA and Hibernate — the definitive explanation, from the author of High-Performance Java Persistence. Follow it with The Open Session in View anti-pattern for the OSIV argument in full, and Baeldung's JPA Entity Graph for more @EntityGraph patterns.

Glossary: the terms this lesson introduces are in the Glossary, under Lesson 9 — your quick-reference for every term we use.

Architecture decisions: the 3 choices this lesson makes, each with its reason, are in Architecture Decisions, under Lesson 9.

Code: the project at the end of this lesson · what changed in this lesson, exercises included.

💬 Discuss