Class 8: The N+1 Problem & @BatchMapping

Duration: 55 minutes | Difficulty: Intermediate | Prerequisites: Class 7 completed

---

What You'll Learn

By the end of this class, you will:

• Understand the N+1 problem and why GraphQL is especially susceptible to it
• See the actual SQL queries - and feel the wall-clock cost - that reveal the problem
• Learn why parallelizing N+1 with virtual threads speeds it up but does not fix it
• Solve it properly with @BatchMapping, keeping the query in a service and the controller thin
• Collapse a nested to-one N+1 with a JOIN FETCH instead of a second batch

The N+1 Problem Explained

Let's say you query all 13 movies with their directors:

query MoviesWithDirectors {
  movies {
    title
    directors { name }
  }
}

With our current @SchemaMapping approach, here's what happens at the database level:

1. 1 query to load all movies: SELECT * FROM movie
2. For each movie, 1 query to load its directors: SELECT * FROM movie_directors JOIN person ... WHERE movie_id = 1
3. Same for movie 2: SELECT * FROM movie_directors JOIN person ... WHERE movie_id = 2
4. And movie 3, 4, 5... up to movie 13.

That's 1 + 13 = 14 queries for what should logically be 2. Load the movies, load the directors. This is the N+1 problem: 1 query for the parent (movies), plus N queries for the children (one per movie's directors).

With cast added too, it gets worse. If we also ask for cast { characterName person { name } }, that's another 13 queries - one per movie to load its cast. And each cast member triggers another query to load the Person. We're now looking at 40+ queries for a simple list page.

The N+1 problem exists in all data fetching systems, but GraphQL makes it especially dangerous because the client controls the query shape. A REST endpoint might always include directors in the response, so you'd optimize that particular endpoint. In GraphQL, any field combination is possible - and each nested field can trigger individual queries for each parent object.

Seeing the Problem

Before we fix it, let's make it impossible to miss. Two settings help.

First, log the SQL so you can count queries - set show-sql: true in application.yaml:

spring:
  jpa:
    show-sql: true

Second, so the cost shows up as time and not just log noise, add a small teaching seam that makes each per-movie load take a beat. LatencySimulator sleeps for a configurable demo.latency, standing in for a slow database or downstream call:

config/LatencySimulator.java

@Component
@RequiredArgsConstructor
public class LatencySimulator {

    private final DemoProperties demoProperties;

    /** Blocks the current thread for the configured latency; zero is a no-op. */
    public void pause() {
        Duration delay = demoProperties.latency();
        if (delay == null || delay.isZero() || delay.isNegative()) return;
        try {
            Thread.sleep(delay);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            throw new RuntimeException("Interrupted while simulating latency", e);
        }
    }
}

config/DemoProperties.java

@ConfigurationProperties(prefix = "demo")
public record DemoProperties(@DefaultValue("0") Duration latency) {}

application.yaml

demo:
  # Artificial per-load latency for the batching demo. 0 disables it.
  latency: 1s

(DemoProperties is a records-based config binding. Register it with @ConfigurationPropertiesScan on your application class, or with @EnableConfigurationProperties(DemoProperties.class).) The field resolvers from earlier classes resolve directors and cast one movie at a time, which is where the N+1 lives. Have each call pause() before it loads, and a single lookup now costs about a second:

person/PersonController.java (the N+1 version)

@SchemaMapping
List<Person> directors(Movie movie) {
    latencySimulator.pause();
    return movie.getDirectors();
}

(cast is the same shape.) Restart and run:

query MoviesWithDirectorsAndCast {
  movies {
    title
    directors { name }
    cast { characterName person { name } }
  }
}

Watch the terminal and the clock. You get a separate SELECT for every movie's directors and every movie's cast, and with 13 movies each paying its own second, the response crawls in at 20-plus seconds. The latency knob is only dramatizing what the SQL count already says: you are running far more queries than the data needs.

Detour: Can't We Just Parallelize It?

That 20-plus-second response is dominated by waiting - dozens of lookups, one after another, each blocked on its second of latency. So the tempting first instinct is not to reduce the queries but to run them at the same time. Virtual threads (Java 21+) make that nearly free, and turning them on is one property:

spring:
  threads:
    virtual:
      enabled: true

Spring for GraphQL runs a resolver on a separate thread when it returns a Callable, and with that property set Spring Boot wires a virtual-thread Executor for exactly this. So the obvious move is to wrap the lazy access in a Callable:

person/PersonController.java

@SchemaMapping
Callable<List<Person>> directors(Movie movie) {
    return () -> {
        latencySimulator.pause();
        return movie.getDirectors();   // this does NOT work
    };
}

Run the query and it throws a LazyInitializationException:

org.hibernate.LazyInitializationException: failed to lazily initialize a collection
  of role: com.graphqlguy.moviedb.movie.Movie.directors - no Session

This failure is worth understanding, because it is the whole reason the plain resolver worked at all. movie.getDirectors() is a lazy collection. The only thing that let the ordinary resolver touch it, the one without a Callable, was Open Session in View, which binds a Hibernate session to the request thread for the length of the request. A Callable runs elsewhere. Spring submits it to the virtual-thread executor, a different thread with no session bound to it, and the persistence context is not among the values Spring carries across that boundary. Off the request thread, the lazy proxy has no session to initialize from.

To parallelize for real, the resolver has to stop leaning on the request thread's session and instead run its own query, in its own transaction, on whatever thread it lands on. That is a service call - and PersonService is already @Transactional(readOnly = true), so a method on it opens a fresh session wherever it runs:

person/PersonController.java

@SchemaMapping
Callable<List<Person>> directors(Movie movie) {
    return () -> {
        latencySimulator.pause();
        return personService.directorsForMovie(movie.getId());
    };
}

person/PersonService.java

// a throwaway per-movie query, just for this experiment
public List<Person> directorsForMovie(Long movieId) {
    return movieRepository.findAllWithDirectorsByIdIn(List.of(movieId)).stream()
        .findFirst().map(Movie::getDirectors).orElse(List.of());
}

Now it runs. The service is transactional, so Spring opens a fresh session bound to the virtual thread the Callable runs on, runs a real by-id query inside it, and returns fully loaded data. Nothing lazy crosses a thread boundary. The lookups now overlap, so the wall clock drops from 20-plus seconds to roughly one. Problem solved?

Not even close.

Open the SQL log. You are still issuing the same 1 + N queries - one for the movies, then one directorsForMovie per movie - only now they run concurrently. You did not fix N+1. You parallelized it. The database does identical work, and Spring's own N+1 signal, /actuator/metrics/graphql.request.datafetch.count, still reports N data-fetch calls per request. Running them at once leaves that number where it was, and makes one thing worse. Each concurrent query holds a database connection for its lifetime. With 100 movies and HikariCP's default pool of 10, the eleventh query onward blocks waiting for a connection, and past the 30-second connectionTimeout it fails with SQLTransientConnectionException: Connection is not available. You traded latency for connection-pool pressure, and a new way to fail under load.

Virtual threads are not an N+1 fix

Reaching for virtual threads to make a slow GraphQL query faster hides the real problem. Parallelizing N+1 lowers latency only until the connection pool saturates or the database starts rate-limiting you. The fix is to stop issuing N+1 at all. Virtual threads stay useful - for genuinely independent work and for I/O-bound external calls (Class 13 uses them for exactly that) - just not here.

Revert the Callable wrapper and the throwaway directorsForMovie, and let's fix it properly.

The Solution: @BatchMapping

@BatchMapping replaces @SchemaMapping for fields that should be batch-loaded. Instead of Spring GraphQL calling your resolver once per parent object, it collects all parent objects and calls your resolver once with the entire list. Your resolver then loads all the data in a single query and returns a map of parent-to-children.

Here's the conceptual difference:

Step 1: Batch Queries in the Repositories

Both batched fields need a single query that loads everything for a set of movies. Add them to the repositories.

For directors, a JOIN FETCH pulls every movie's directors in one statement:

📁 movie/MovieRepository.java

@Query("select distinct m from Movie m left join fetch m.directors where m.id in :ids")
List<Movie> findAllWithDirectorsByIdIn(@Param("ids") List<Long> ids);

distinct collapses the duplicate movie rows a join-fetch produces when a movie has several directors. For cast, fetch the person in the same query - that one join fetch mc.person is what stops cast { person { name } } from becoming yet another N+1 later:

📁 movie/MovieCastRepository.java

@Query("select mc from MovieCast mc join fetch mc.person where mc.movie.id in :ids")
List<MovieCast> findWithPersonByMovieIdIn(@Param("ids") List<Long> ids);

Step 2: Load the Data in the Service

The batched query and the grouping belong in the service, so the controller stays free of repositories - the convention from earlier classes. The service takes a list of movie ids and returns the results in a map from movie id to its results, a plain shape with nothing GraphQL-specific about it:

📁 person/PersonService.java

@Service
@RequiredArgsConstructor
@Transactional(readOnly = true)
public class PersonService {

    private final MovieRepository movieRepository;
    private final MovieCastRepository movieCastRepository;
    // ... existing fields

    public Map<Long, List<Person>> findDirectorsByMovieIds(List<Long> movieIds) {
        return movieRepository.findAllWithDirectorsByIdIn(movieIds).stream()
            .collect(Collectors.toMap(Movie::getId, Movie::getDirectors));
    }

    public Map<Long, List<MovieCast>> findCastByMovieIds(List<Long> movieIds) {
        return movieCastRepository.findWithPersonByMovieIdIn(movieIds).stream()
            .collect(Collectors.groupingBy(cast -> cast.getMovie().getId()));
    }
}

Two things make this clean. The class is @Transactional(readOnly = true), so each call runs in its own session, and the join-fetched associations are fully loaded before the method returns. Nothing lazy escapes. The return type is Map<Long, ...>, so the service speaks in ids rather than GraphQL's batch shape. That keeps it reusable, since a REST endpoint could call it too, and keeps the one framework-specific detail in the controller, where it belongs.

Step 3: Batch the Fields in the Controller

Now the resolvers. @BatchMapping changes the call signature. Spring GraphQL collects all the movies and calls your method once with the whole list, expecting a Map from each parent object to its result. The controller delegates the loading to the service, then moves each result from its movie id onto the actual Movie instance Spring handed in:

📁 person/PersonController.java

@BatchMapping
Map<Movie, List<Person>> directors(List<Movie> movies) {
    latencySimulator.pause();
    log.info("Batch fetching directors for {} movies", movies.size());
    List<Long> movieIds = movies.stream().map(Movie::getId).toList();
    Map<Long, List<Person>> directorsByMovieId = personService.findDirectorsByMovieIds(movieIds);
    return movies.stream()
        .collect(Collectors.toMap(movie -> movie,
            movie -> directorsByMovieId.getOrDefault(movie.getId(), List.of())));
}

@BatchMapping
Map<Movie, List<MovieCast>> cast(List<Movie> movies) {
    latencySimulator.pause();
    log.info("Batch fetching cast for {} movies", movies.size());
    List<Long> movieIds = movies.stream().map(Movie::getId).toList();
    Map<Long, List<MovieCast>> castByMovieId = personService.findCastByMovieIds(movieIds);
    return movies.stream()
        .collect(Collectors.toMap(movie -> movie,
            movie -> castByMovieId.getOrDefault(movie.getId(), List.of())));
}

Inject PersonService and LatencySimulator, add @Slf4j, and delete the old @SchemaMapping versions. A few details earn a mention:

• The map's keys are the Movie objects, not their ids. That is a requirement of the @BatchMapping shortcut. Spring hands you the parent instances and uses them to distribute results back to the right response positions, so Movie must implement equals and hashCode, which Class 2 set up. It is a framework contract - which is exactly why it lives in the controller, not the service. The Spring GraphQL project lead describes the entity-as-key as a fallback the framework uses because it "cannot access the id of the entity"; keep the service speaking ids. The getOrDefault(id, List.of()) makes sure every movie appears in the map, even ones with no directors.

• The @BatchMapping method is deliberately not @Transactional. The transaction sits on the service method that runs the query. Wrapping the batch method itself would re-hydrate the parent Movie entities (an N+1 on the movie table) or throw computing hashCode on a lazy proxy - so keep the transactional boundary on the service call that works with ids.

• pause() now fires once per batch, not once per movie - the clearest sign the fix landed. It and the log line are demo scaffolding you would drop in real code.

• One resolver we did not write: MovieCast.person. Because the cast query already join fetched person, each MovieCast arrives with its person loaded, so cast { person { name } } resolves straight off the object with no extra query. That nested to-one N+1 was quietly closed by the fetch-join back in Step 1.

Step 4: Run and Compare

Restart the app (with show-sql: true still on) and run the same query:

query MoviesWithDirectorsAndCast {
  movies {
    title
    directors { name }
    cast { characterName person { name } }
  }
}

Now you'll see a handful of SQL statements instead of dozens:

1. One to load all movies
2. One to load all directors (... where m.id in (1,2,3,...))
3. One to load all cast with their people (... join fetch mc.person where mc.movie.id in (...))

Three queries where there were dozens - and because each batch pays the latency only once, the response comes back in about a second instead of twenty. That is the whole point: not just faster, but a fixed, bounded number of queries no matter how many movies you ask for.

Turn off the demo knobs

Set show-sql: false and demo.latency: 0 once you've seen the difference - both are teaching aids, and SQL logging is noisy in real use.

Understanding the Pattern

Both batched fields follow the same split, and once you've written one the rest feel mechanical:

1. The service takes a list of ids, runs one query, and groups the results into a Map<Long, ...> from id to results.
2. The controller's @BatchMapping collects the ids from the parent list, calls the service, and moves each result from its id onto its parent, in the Map<Parent, ...> Spring expects.

The specifics vary (different repositories, different grouping keys), but the structure is always the same: the loading logic stays in the service, framework-agnostic and based on ids, and the controller does the one GraphQL-shaped step of pairing results back to parents.

The key insight is that @BatchMapping changes the call signature: instead of receiving one parent and returning one result, it receives a list of parents and returns a map. Spring GraphQL handles the rest - it accumulates parents, calls your method once, and distributes the results back to the correct response positions.

Underneath, @BatchMapping is not magic. It registers a batch loading function in the BatchLoaderRegistry and binds a data fetcher to the schema field, so the actual accumulate-then-load-once behavior is powered by a graphql-java DataLoader. This is the same DataLoader machinery you would otherwise wire by hand; @BatchMapping is the shortcut that spares you the boilerplate. A useful secondary benefit falls out of that backing: each DataLoader keeps a per-request cache of what it has already loaded, so if two different parts of the same query resolve the same parent, the second resolution is served from that cache rather than re-run. The cache and its DataLoader live only for the duration of one GraphQL request and are discarded when it completes, so nothing bleeds across requests.

The Nested to-one: Fetch-Join vs. a Second Batch

It's worth being explicit about the N+1 we didn't have to fix separately. Batching cast loads all the MovieCast rows in one query, but cast { person { name } } reaches one level deeper, and MovieCast.person is a to-one reference. Left alone, each cast row would trigger its own SELECT on the person table - the to-many field batched, the to-one underneath it not.

There are two clean ways to close that gap, and we already took the simpler one.

Fetch-join it in the same query (what we did). Because findWithPersonByMovieIdIn says join fetch mc.person, every MovieCast comes back with its person already loaded, and the default resolver reads it straight off the object. One query covers the cast and their people. When the nested entity sits in the same table join as its parent, this is the tidiest fix: no extra resolver, no extra round trip.

Batch it with a second @BatchMapping (the alternative). If the to-one lived behind a boundary you couldn't join - a person served by a separate microservice or a REST call - you'd add a @BatchMapping on MovieCast instead:

@BatchMapping
Map<MovieCast, Person> person(List<MovieCast> castEntries) {
    List<Long> personIds = castEntries.stream().map(c -> c.getPerson().getId()).toList();
    Map<Long, Person> byId = personService.findByIds(personIds);
    return castEntries.stream()
        .collect(Collectors.toMap(cast -> cast, cast -> byId.get(cast.getPerson().getId())));
}

c.getPerson().getId() reads the foreign-key id off the proxy without loading the Person, so collecting the ids is free and the single batched lookup is the only query that touches person. Reach for this when a fetch-join isn't possible; when it is, the join is less code and one fewer resolver.

When @BatchMapping Is Not Enough

There is one place @BatchMapping cannot follow you: it does not receive an individual field's @Argument values. A @BatchMapping method binds the List of parents, and it can reach request-wide context through a GraphQLContext, @ContextValue, or Principal parameter; what it cannot declare is the field's own @Argument values. So if the schema field carries arguments - say reviews(minScore: Int) on Movie, where each movie in the batch could in principle be asked for a different minScore - no per-parent argument exists for the method to read. @BatchMapping batches the parents but cannot honor a filter that varies from one parent to the next.

The escape hatch is to drop back to a @SchemaMapping method and drive the DataLoader yourself. You register a batch loading function against the BatchLoaderRegistry, by injecting it into the controller constructor and calling registry.forTypePair(...).registerBatchLoader(...). Your @SchemaMapping method can then declare both the @Argument it needs and a DataLoader parameter, and pass a load key that includes the argument, so the argument becomes part of the cache key. You give up the terseness of @BatchMapping, but you get batching and access to the field arguments, which is exactly the combination @BatchMapping alone cannot offer.

A Different Lever: Hibernate's default_batch_fetch_size

@BatchMapping is the fix at the GraphQL layer. There is a second lever one layer down, in Hibernate itself, and the question of whether it can replace @BatchMapping comes up often enough to answer head-on.

hibernate.default_batch_fetch_size is a Hibernate setting, off by default, that changes how lazy associations load. Without it, the first time you touch movie.getDirectors() on an uninitialized collection, Hibernate issues one SELECT for that one movie. With it set to, say, 20, Hibernate instead looks at every other movie already in the same persistence context whose directors collection is still uninitialized and loads up to 20 of them in a single WHERE movie_id IN (...). You enable it globally:

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 20

With this set, the plain @SchemaMapping version of directors from Class 2 stops being a pure N+1. The data fetcher still runs once per movie, but the first call eagerly initializes a whole batch and the rest find their data already loaded. For 100 movies at batch size 20, you issue 5 SELECTs instead of 100. So can you delete your @BatchMapping code and set one property instead? No, and the four reasons why are the whole point of this section.

It turns N+1 into N/k, not into 1. A @BatchMapping written the way we did issues exactly one query for the field no matter how many parents arrive. default_batch_fetch_size issues ceil(N / batch_size) queries. Five round trips beats 100 by a mile, but it is not one, and the batch size is a number you have to guess and tune.

It batches lazy getters, and only lazy getters. default_batch_fetch_size only works while a Hibernate session is open. Inside a @Transactional method that is always true, so it batches fine. The fragile spot is the @SchemaMapping pattern: a resolver doing movie.getDirectors() touches a lazy proxy during GraphQL field resolution, outside any transaction you wrote. It works only because Open Session in View holds a session open for the whole request - turn OSIV off and it throws LazyInitializationException. @BatchMapping does not touch a lazy proxy at all; it runs an explicit query, so it does not depend on OSIV.

It only works for JPA entity associations. default_batch_fetch_size is a Hibernate feature, so it does not help a field backed by a REST call or another service. @BatchMapping is source-agnostic - the identical pattern batches a RestClient call to TMDB in Class 13 as cleanly as it batches a repository query here. The moment a field crosses a service boundary, Hibernate batching is not even in the room.

It is implicit. No method to log, no List<Movie> parameter, nothing in the GraphQL layer announcing that a field is batched. That is convenient when it works and invisible when it does not. @BatchMapping is explicit and observable: the log.info line from Step 2 fires once per batch, and the batch is something a test can assert on.

Side by side:

	@BatchMapping	default_batch_fetch_size
Layer	GraphQL / Spring controller	JPA / Hibernate
Queries for N parents	1	ceil(N / batch_size)
Configured	Per field, in Java	Once, as a global property
Works for non-JPA data (REST, gRPC)	Yes	No
Visible in the GraphQL layer	Yes (loggable, testable)	No
Effort	A resolver method per field	One line of YAML

The honest verdict is that default_batch_fetch_size is not a replacement for @BatchMapping, but it is an excellent safety net, and the two belong together. Set a modest default_batch_fetch_size (10 to 50 is typical) globally so that any association you forgot to optimize degrades to N/k instead of a full N+1. Then spend @BatchMapping on the fields that earn it: the hot paths, the fields that cross a service boundary, and the ones you want to see and test. The property is defense in depth; @BatchMapping is the design.

Why this tutorial leads with @BatchMapping

We teach @BatchMapping as the primary fix because it survives the two changes most likely to land on a real project: turning OSIV off in production, and moving a field's data behind an external API. default_batch_fetch_size quietly stops protecting those GraphQL fields in both cases. Add it to application.yaml as a backstop, but do not let it talk you out of writing the batch resolver.

Exercises

Exercise 1: Batch the reviews field

The reviews field on Movie is still a plain @SchemaMapping in ReviewController, resolving one movie at a time - the same N+1 you just fixed for directors. Convert it using the exact split from this class: add a findReviewsByMovieIds(List<Long>) method to ReviewService returning Map<Long, List<Review>> (one where movie_id in :ids query, grouped by movie id), then replace the @SchemaMapping with a thin @BatchMapping on the controller that delegates and moves each result onto its Movie parent. Confirm with show-sql: true that movies { title reviews { score } } now issues one reviews query instead of one per movie.

Exercise 2: Measure the Improvement

With show-sql: true, count the SQL queries before and after @BatchMapping. Try with different numbers of movies (create a few more via mutations). The improvement scales - with 100 movies, directors and cast stay at one query each instead of a hundred.

Exercise 3: Single Movie Query

What happens when you query a single movie by ID and ask for directors? Does the @BatchMapping still fire? Yes - it receives a list with one element. The batch overhead is negligible for single items, and the consistency means you don't need separate code paths.

Summary

In this class, you learned:

• The N+1 problem generates 1 + N database queries when loading related data for N parent objects - a performance killer that GraphQL makes worse because clients control the query shape
• Virtual threads parallelize N+1, they don't fix it. A Callable resolver runs off the request thread, so a naive movie.getDirectors() throws LazyInitializationException. Even done correctly through a transactional service, it only overlaps the same N queries, which helps until the connection pool saturates
• @BatchMapping collects all parent objects and calls your resolver once with the whole list, replacing N queries with one - the result map's keys are the parent objects, so the parent needs equals/hashCode
• Keep the loading logic in the service, working with ids (Map<Long, ...>), and keep the controller thin: it collects ids, delegates, and maps the results back onto the parents. The @BatchMapping method itself is not @Transactional - the transaction belongs on the service query
• JOIN FETCH loads relationships in one SQL query, and a fetch-join on a nested to-one (cast's person) closes that N+1 without a second @BatchMapping
• default_batch_fetch_size is a Hibernate-level safety net that softens N+1 to ceil(N / batch_size) queries, but it depends on an open session and only covers JPA associations - not a substitute for @BatchMapping

What's Next?

In Class 9: Testing, we'll write comprehensive tests for everything we've built:

• Integration tests with GraphQlTester
• Testing queries, mutations, and error handling
• Testing with authentication headers

---