n-plus-one

Team list API slowed down: diagnosing an N+1 query explosion

A single GET /teams endpoint can silently issue one member query per team. This module uses PostgreSQL statistics and Hibernate query counts to compare entity loading and projection strategies.

The Bug

This scenario models a failure that's easy to miss: an API that passes every test at small scale and silently degrades as data grows. The N+1 problem hides behind correct behavior. The code returns the right data, but the ORM silently multiplies database round trips. Using pg_stat_statements and Hibernate's query counter surfaces exactly what the ORM abstracts away. Each fix strategy is then measured against the same load to compare how it changes the database access pattern.

The team list API worked fine during development with a small dataset. After launch, the organization grew to many teams with members attached to each team. Response times degraded with data volume, and the endpoint became a top latency contributor.

Symptoms:

• GET /teams p95 latency exceeded the target SLA
• Database connection pool usage spiked during traffic bursts
• PostgreSQL pg_stat_statements showed a single SELECT pattern called 100 times per request
• No code changes had been deployed. The degradation correlated with data growth, not releases

Diagnosis

Production discovery followed this sequence:

1. Response-time correlation with data growth

The team list API degraded proportionally to data volume. No code changes had been deployed; the slowdown tracked data growth, not releases.

2. Hibernate SQL logging

Enabling spring.jpa.show-sql=true revealed the same SELECT * FROM members WHERE team_id = ? repeating once per team. With 100 teams, 100 identical queries appeared in the log.

3. pg_stat_statements confirmation

Server-side query statistics confirmed the pattern:

SELECT left(query, 80) AS query, calls, round(mean_exec_time::numeric, 3) AS mean_ms
FROM   pg_stat_statements
WHERE  query ILIKE 'select%'
ORDER  BY calls DESC;

The member-fetch query showed calls = 100 per request, PostgreSQL's own view of the N+1 explosion.

4. Code trace

team.getMembers().size() inside a loop triggered Hibernate's lazy proxy initialization, issuing one SELECT per team. The N+1 pattern was confirmed in the iteration path.

5. QueryCounter measurement

Using Hibernate Statistics (getPrepareStatementCount()), the exact count was measured: 101 queries per request (1 for teams + 100 for members).

Root cause

The Team entity uses JPA's default FetchType.LAZY for its members collection. When the service serializes teams to JSON, accessing team.getMembers() triggers a proxy initialization: Hibernate silently issues a separate SELECT for each team's members:

@OneToMany(mappedBy = "team", fetch = FetchType.LAZY)
private List<Member> members;  // Hibernate injects a proxy, not a real list

1.  SELECT * FROM teams                           -- 1 query: load all teams
2.  SELECT * FROM members WHERE team_id = 1       -- proxy fires for team 1
3.  SELECT * FROM members WHERE team_id = 2       -- proxy fires for team 2
    ...
101. SELECT * FROM members WHERE team_id = 100     -- proxy fires for team 100

sequenceDiagram
    participant App as Application
    participant Hibernate as Hibernate
    participant DB as PostgreSQL
    
    App->>Hibernate: findAll()
    Hibernate->>DB: SELECT * FROM teams
    DB-->>Hibernate: 100 teams (members = proxy)
    
    loop For each team (×100)
        App->>Hibernate: team.getMembers()
        Hibernate->>DB: SELECT * FROM members WHERE team_id = ?
        DB-->>Hibernate: ~10 members
    end
    
    Note over App,DB: Total: 1 + 100 = 101 queries

Loading

100 teams × 1 extra query each = 101 total queries. With 1,000 teams, it would be 1,001.

Why it was invisible in dev

The code is correct. It returns the right data. With a small local dataset, the overhead is easy to miss. The problem only manifests as data grows because each parent entity triggers an independent query. JPA abstracts the SQL away, so standard code review and unit tests don't catch it.

The Fix

Strategy 1: Fetch Join — One SQL JOIN per request. When to use: default fix when entities are needed downstream and only one @OneToMany is involved.

Add JOIN FETCH to the JPQL query. Hibernate loads teams and members in a single SQL JOIN.

@Query("SELECT DISTINCT t FROM Team t JOIN FETCH t.members")
List<Team> findAllWithFetchJoin();

Generated SQL:

SELECT DISTINCT t.id, t.name, m.id, m.name, m.team_id
FROM teams t INNER JOIN members m ON t.id = m.team_id

This reduces the measured query count from 101 to 1 for this endpoint.

sequenceDiagram
    participant App as Application
    participant Hibernate as Hibernate
    participant DB as PostgreSQL
    
    App->>Hibernate: findAllWithFetchJoin()
    Hibernate->>DB: SELECT t.*, m.* FROM teams t JOIN members m ON t.id = m.team_id
    DB-->>Hibernate: 1000 rows (100 teams × 10 members)
    Hibernate-->>App: 100 Team entities (members populated)
    
    Note over App,DB: Total: 1 query

Loading

JPQL DISTINCT is required to de-duplicate root Team entities in Hibernate's result list — the JOIN itself still returns one SQL row per joined member, so without DISTINCT a team with 10 members would appear 10 times in the returned List<Team>.

Limitations

• Multiple bag fetches: Hibernate cannot fetch-join two bag-style List collections in one query and throws MultipleBagFetchException. Switching the collection type to Set sidesteps the exception, but fetch-joining multiple collections still risks Cartesian row growth.
• Pagination incompatibility: Pageable with JOIN FETCH forces Hibernate to load the entire result set into memory before paginating (HHH000104 warning). With large datasets, this causes OOM risk.

Strategy 2: EntityGraph — Same as Fetch Join, declared via annotation. When to use: same query, different loading strategy per call site.

Same result as Fetch Join, but the loading strategy is declared via annotation instead of embedded in the query.

@EntityGraph(attributePaths = "members")
@Query("SELECT t FROM Team t")
List<Team> findAllWithEntityGraph();

Produces 1 query with throughput comparable to Fetch Join. Generates a LEFT OUTER JOIN under the hood.

Limitations

• Same MultipleBagFetchException risk: See Fetch Join limitations above.
• No compile-time safety for ad-hoc graphs: Attribute name typos in attributePaths are not caught at compile time. They surface as runtime errors.
• @Subgraph complexity: Nested loading is possible via @Subgraph, but graph definitions quickly become complex and hard to maintain.

Both Fetch Join and EntityGraph share the MultipleBagFetchException limitation. See @BatchSize or Projection for alternatives.

Strategy 3: DTO Projection — Flat columns into a record. When to use: read-only endpoints (lists, reports, exports).

Skip entity loading. Query only the columns you need as flat rows, then group in application code.

@Query("SELECT new com.forge.patterns.nplusone.dto.TeamMemberProjection("
     + "t.id, t.name, m.id, m.name) "
     + "FROM Team t JOIN t.members m ORDER BY t.id, m.id")
List<TeamMemberProjection> findAllAsProjection();

public record TeamMemberProjection(Long teamId, String teamName,
                                   Long memberId, String memberName) {}

Produces 1 query and avoids entity materialization, making it the leanest read path for this endpoint.

The improvement over Fetch Join / EntityGraph comes from eliminating Hibernate's persistence context: no managed entities, no dirty checking, no proxy overhead. For a read-only list endpoint that immediately serializes to JSON, none of that machinery adds value.

Limitations

• No persistence context: DTOs are not managed entities. No dirty checking, no lazy loading after projection. If the use case requires modification and save, this strategy cannot be used.
• FQCN (Fully-Qualified Class Name) in JPQL: The constructor expression requires a fully-qualified class name hard-coded in the query string, creating a coupling between the query and the DTO package path.
• Cache behaviour: DTO projections do not enter the persistence context, so repeated queries within the same transaction re-execute SQL. The Hibernate second-level entity cache cannot serve projection rows either, since the result is not an entity keyed by primary key. Hibernate's query cache could in theory cache the rows, but it is opt-in and invalidated on any write to the involved tables — an application-level @Cacheable is the usual complement for read-heavy paths. PostgreSQL itself has no built-in result cache; only the page cache and plan cache, which apply equally to entity queries.

Strategy 4: @BatchSize — One annotation, no query rewrite, drops 101 queries to 2 via IN(...). When to use: emergency fix, or multiple @OneToMany collections where Fetch Join can't apply.

The previous strategies require rewriting queries or adding annotations to repository methods. @BatchSize takes a different approach: keep the existing code as-is, and let Hibernate batch-fetch lazy collections automatically.

// The only change: add @BatchSize to the collection field
@OneToMany(mappedBy = "team", fetch = FetchType.LAZY)
@BatchSize(size = 100)
private List<Member> members;

When team.getMembers() triggers lazy loading for the first team, Hibernate doesn't fetch members for just that team. It batches up to 100 uninitialized collections and loads them all in a single SELECT ... WHERE team_id IN (?, ?, ..., ?).

Generated SQL (with 100 teams, batch size 100):

-- Query 1: Load all teams
SELECT t.id, t.name FROM teams t

-- Query 2: Batch-load all member collections in one IN-clause
SELECT m.id, m.name, m.team_id FROM members m WHERE m.team_id IN (?, ?, ..., ?)

sequenceDiagram
    participant App as Application
    participant Hibernate as Hibernate
    participant DB as PostgreSQL
    
    App->>Hibernate: findAll()
    Hibernate->>DB: SELECT * FROM teams
    DB-->>Hibernate: 100 teams (members = proxy)
    
    App->>Hibernate: team[0].getMembers()
    Note over Hibernate: Batch-fetch up to 100 collections at once
    Hibernate->>DB: SELECT * FROM members WHERE team_id IN (1,2,...,100)
    DB-->>Hibernate: 1000 members (all at once)
    
    Note over App,DB: Total: 2 queries (instead of 101)

Loading

101 queries drop to 2. No query rewriting or DTO creation is needed for this strategy; the behavior comes from one entity annotation.

In this demo, a separate read-only entity (BatchSizeTeam) is used so the naive N+1 benchmark remains demonstrable. In production, @BatchSize can be added to the existing entity, or hibernate.default_batch_fetch_size=100 can be set in application.yml.

Limitations

• Batch size tuning: Too large and the IN clause may exceed database-specific length limits. Too small and you still fire many queries. Requires empirical tuning.
• Full entity loading: Like Fetch Join/EntityGraph, loads complete entities into the persistence context. Higher memory usage compared to DTO Projection.
• Hibernate-specific: @BatchSize is a Hibernate annotation, not part of the JPA standard. Not portable to other JPA providers (EclipseLink, OpenJPA).

Recommendation

Scenario	Strategy	Why
Default choice, entities needed downstream	Fetch Join	1 query, dirty checking preserved, most common
Same query, different loading per context	EntityGraph	Separates query from loading strategy
Read-only endpoints (lists, reports, exports)	DTO Projection	Fastest, eliminates all ORM overhead
Emergency fix, minimal code change	@BatchSize	One annotation, no query rewrite needed
Multiple @OneToMany collections	@BatchSize or EntityGraph + separate queries	Avoids Cartesian product

Cross-database notes

The N+1 problem is ORM-level, not database-specific. The same pattern occurs regardless of the underlying database. The diagnostic tools differ, though:

Feature	PostgreSQL	MySQL	Oracle
Query statistics	pg_stat_statements extension	Performance Schema events_statements_summary_by_digest	V$SQL / V$SQLAREA
Query plan	EXPLAIN (ANALYZE, BUFFERS)	EXPLAIN ANALYZE (8.0+)	DBMS_XPLAN.DISPLAY_CURSOR
Hibernate Statistics	DB-independent, JVM level	Same	Same

pg_stat_statements is a PostgreSQL extension. It requires CREATE EXTENSION pg_stat_statements; and must be added to shared_preload_libraries at server startup. MySQL's Performance Schema is enabled by default.

Evidence

• This module uses two proof layers: query-count diagnostics are the primary proof that the N+1 pattern exists, and k6 is secondary/manual evidence for how that pattern changes user-facing latency.
• pg_stat_statements and Hibernate Statistics are both part of the evidence because ORM-level fixes should still be visible from the database side.
• pg_stat_statements proof requires a PostgreSQL server started with shared_preload_libraries=pg_stat_statements; the dedicated integration test container provides that setup.
• Team and BatchSizeTeam intentionally map the same table to keep the naive endpoint demonstrable. Do not mix those entity types in the same transaction when adding future behavior; first-level cache state can otherwise make evidence ambiguous.
• Strategy comparisons are interpreted by use case, not by raw RPS alone. Projection is optimized for read-only APIs, while entity-loading strategies preserve different ORM behaviors downstream.
• Canonical run IDs, measured numbers, and reproducibility notes live in BENCHMARK_RESULTS.md.

Quick start

# Integration tests (Testcontainers, no external setup)
./gradlew :n-plus-one:testIntegration

# Secondary/manual load benchmark (Docker required; writes local summary artifacts)
./n-plus-one/run_benchmark.sh

Architecture

Layer	Class	Responsibility
Domain	Team, Member	Parent (@OneToMany LAZY) / Child (@ManyToOne)
Domain	BatchSizeTeam, BatchSizeMember	Read-only view with @BatchSize(100)
DTO	TeamMemberProjection	Flat projection record
DTO	TeamResponse	Team + members response mapping
Repository	TeamRepository	Naive / Fetch Join / EntityGraph / Projection queries
Repository	BatchSizeTeamRepository	Batch-size strategy query
Service	TeamService	Strategy methods + projection grouping
Controller	TeamController	/naive, /fetch-join, /entity-graph, /projection, /batch-size
Test Utility	QueryCounter (shared)	Hibernate Statistics-based query counter

Related scenarios

• Pagination issues on large result sets? → Pagination Module
• Lock contention under writes? → Locking Module or Deadlock Module