N+1 detection & fixes

What Is N+1 and Why It Destroys Production

N+1 happens when: - One query loads parent entities - Then N additional queries load related entities Example: Load 100 users. For each user, load orders separately. Total queries = 101. Under load, this becomes: - Latency spike - DB CPU explosion - Thread pool exhaustion This does not show in small datasets. It explodes in production.

Incident Scenario: Latency Jump from 40ms to 900ms

Endpoint: GET /users Everything fine in staging (20 users). Production had 5,000 users. Root cause: Each user triggered lazy load of roles. 5,001 queries per request. Database survived. Application threads did not.

Anti-Pattern: Lazy Collection Access in Loop

List users = userRepository.findAll();

for (User u : users) {
    System.out.println(u.getOrders().size());
}

This triggers: 1 query for users N queries for orders Hidden cost. Silent disaster.

How to Detect N+1

1. Enable SQL logging in staging:

spring.jpa.show-sql=true
hibernate.format_sql=true

If you see repeating queries per row → N+1. 2. Enable Hibernate statistics:

hibernate.generate_statistics=true

Monitor: - Query count per request - Entity fetch count If query count scales with result size → problem. 3. Use APM tools: - New Relic - Datadog - OpenTelemetry traces Look for repetitive SQL spans.

Fix Strategy 1: Fetch Join

Explicitly load relations:

@Query("SELECT u FROM User u JOIN FETCH u.orders")
List findAllWithOrders();

Now: - Single SQL - Proper join - Predictable performance Be careful: Fetch join with pagination is dangerous. Database may multiply rows.

Fix Strategy 2: Batch Fetching

Instead of 1 + N queries, Hibernate can group loads. Set batch size:

hibernate.default_batch_fetch_size=50

Now N queries become: ceil(N / 50) This reduces load significantly.

Fix Strategy 3: Projection Instead of Entity

Often you do not need full entity. Use projection:

@Query("SELECT new com.example.UserSummary(u.id, u.email) FROM User u")
List findSummaries();

This: - Avoids relationship traversal - Avoids lazy loading entirely - Reduces memory footprint Best for read-heavy endpoints.

Why EAGER Is Not a Solution

Some developers switch to EAGER. This creates: - Massive joins - Huge result sets - Memory pressure - Slow queries EAGER hides N+1 but creates over-fetching. That is not optimization. That is shifting the problem.

Pagination and N+1

If you paginate 20 users, but each has 50 orders, you may load 1,000 rows. Understand your data shape. Always profile realistic dataset size.

Performance Impact

N+1 causes: - Connection pool exhaustion - Lock contention - Higher GC pressure - Increased latency variance Worst part: It scales linearly with data size.

Checklist

- Enable SQL logging in staging - Monitor query count per request - Use fetch joins intentionally - Use batch fetching for collections - Prefer projections for read APIs - Avoid EAGER as a shortcut - Load test with realistic dataset - Profile with APM before production release If your endpoint performance scales with row count linearly, you likely have N+1. Detect early. Fix intentionally. Measure always.