The Silent Database Killer: Cracking the Hibernate N+1 Problem (And the Pagination Trap You Didn’t See Coming)

It’s a classic Monday morning scenario. Your application passed every local JUnit integration test, sailed through QA with flying colors, and was deployed to production. For the first few weeks, everything ran seamlessly.

Then, traffic spiked. Suddenly, your APM dashboard is lighting up red. CPU utilization on your database is hitting 98%, API response times are degrading from 50ms to 4 seconds, and your application is teetering on the edge of an OutOfMemoryError.

You open up your service logs, and you’re greeted by a never-ending waterfall of identical SQL statements flooding the console for a single API request.

Welcome to the Hibernate N+1 Query Problem — the single most common performance bottleneck in Spring Data JPA applications.

In this article, we aren’t going to look at textbook definitions. Instead, we will walk through a real-world enterprise scenario, look at exactly how this happens in code, and break down a hidden pagination trap that forces Hibernate to secretly pull millions of rows into your JVM memory.

The Real-World Setup: The Customer Order Engine

Imagine we are building a backend module for a banking or e-commerce platform. We have a simple relationship: a Customer can have multiple Order records.

To keep performance fast by default, we configure the relationship to load lazily using FetchType.LAZY.

@Entity
public class Customer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @OneToMany(mappedBy = "customer", fetch = FetchType.LAZY)
    private List<Order> orders;
}

Now, let’s look at a straightforward business requirement. We need an admin dashboard endpoint that lists customers alongside their recent orders. A developer might write a service method like this:

@Service
@Transactional(readOnly = true)
public class CustomerDashboardService {

    @Autowired
    private CustomerRepository customerRepository;

    public List<CustomerDTO> getDashboardData() {
        // 1. Fetch all customers from the database
        List<Customer> customers = customerRepository.findAll();

        // 2. Loop through them and map them to DTOs
        return customers.stream().map(customer -> {
            List<OrderDTO> orderDTOs = customer.getOrders().stream() // Triggers a database query!
                .map(order -> new OrderDTO(order.getId(), order.getAmount()))
                .toList();
            return new CustomerDTO(customer.getId(), customer.getName(), orderDTOs);
        }).toList();
    }
}

This code looks clean, readable, and perfectly logical. But let’s look at what Hibernate executes under the hood.

Deconstructing the N+1 Execution

When customerRepository.findAll() runs, Hibernate executes exactly 1 query to fetch the rows from the customer table:

SELECT * FROM customer;

If that query returns 100 customers, the list contains 100 managed entities. However, because the orders collection is marked as LAZY, those collections are just empty Hibernate proxies.

The real damage happens when the code loops through the stream. The moment customer.getOrders() is accessed to read the data, Hibernate is forced to initialize the proxy. It immediately fires an individual query to find the orders for that specific customer id.

Because there are 100 customers, Hibernate loops and fires 100 individual queries:

SELECT * FROM orders WHERE customer_id = 1;
SELECT * FROM orders WHERE customer_id = 2;
...
SELECT * FROM orders WHERE customer_id = 100;

Total queries executed = 1 (initial query) + N (where N is the number of customers returned).

If you have 10,000 customers in production, your application fires 10,001 database calls for a single page render. Your database connection pool is immediately exhausted, and your system grinds to a halt.

The Traps and the False Fixes

When developers first discover this issue, they usually reach for one of two common “fixes.” Let’s look at why the first one is a major trap.

The Rookie Mistake: Changing to FetchType.EAGER

You might think, “If loading the data lazily in a loop causes extra queries, let’s just change the mapping to FetchType.EAGER!"

@OneToMany(mappedBy = "customer", fetch = FetchType.EAGER)
private List<Order> orders;

Why this fails: If you use a basic JPQL query or a Spring Data method like findAll(), **FetchType.EAGER does absolutely nothing to prevent the N+1 problem.**

Hibernate still parses the instruction as “Fetch all customers first.” It runs SELECT * FROM customer. Once it populates the entities, it looks at the EAGER declaration and says, "Oh, I need to populate these collections immediately." It then loops through the customers and fires the remaining $N$ queries anyway. The only difference is that the queries happen during the repository call instead of the service layer loop.

🛑 The Hidden Trap: Eager Fetching vs. Pagination

This brings us to the most dangerous, counter-intuitive behavior in all of Spring Data JPA — one that trips up many experienced developers.

Let’s say you decide to fix your N+1 problem properly by using a JOIN FETCH query in your repository. This forces the database to perform a clean SQL join and retrieve the parents and children in a single, unified database trip.

public interface CustomerRepository extends JpaRepository<Customer, Long> {

    @Query("SELECT c FROM Customer c LEFT JOIN FETCH c.orders")
    Page<Customer> findAllWithOrders(Pageable pageable);
}

You pass a Pageable requesting page 0 with a size of 20. You run your application, check the UI, and it works perfectly! You only see one query in the console. You think you've won.

But look closer at your console logs. Buried in the startup logs or execution trace, you will find this terrifying warning from Hibernate:

WARN [org.hibernate.orm.query] HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory!

What does “Applying in Memory” actually mean?

Take a look at the SQL that Hibernate actually sent to your database. You will notice something shockingly missing: There is no LIMIT or OFFSET clause in the generated SQL query.

Because a single Customer can have multiple Orders, joining those two tables creates a relational Cartesian product. If a customer has 5 orders, the database returns 5 duplicate rows for that single customer.

If Hibernate applied a database-level LIMIT 20 to that joined result set, it would cut off the rows mid-order, resulting in truncated data and broken entities.

To protect data integrity, Hibernate makes a drastic choice: It deliberately ignores your pagination parameters at the database level.

Instead, it sends a massive SELECT * query, pulls every single row from your database table directly into your JVM’s memory (heap space), builds the object graph in Java, and then manually truncates the list to give you the 20 records you asked for.

If your database table has millions of rows, your application will allocate gigabytes of memory for a simple paginated query, trigger severe Garbage Collection pauses, and eventually crash with an OutOfMemoryError under production loads.

The Clean, Production-Ready Solution

How do we achieve both true database-level pagination and eliminate the N+1 problem simultaneously?

Solution 1: Using global batch configuration property

Hibernate provides an elegant global configuration flag designed precisely for this scenario: default_batch_fetch_size.

Add this single property to your application.yml file:

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 30

How this changes the game:

1. You keep your entities configured as FetchType.LAZY.
2. In your repository, you drop the problematic JOIN FETCH from your paginated queries and use a standard, clean query. Your database can now safely execute a real LIMIT and OFFSET query, returning exactly 20 parent rows.
3. When your service layer loops through those 20 customers to map their orders, Hibernate doesn’t fire 20 individual queries. It looks at your batch fetch size, groups the IDs together, and executes a single, highly optimized IN clause query:

SELECT * FROM orders WHERE customer_id IN (1, 2, 3, 4, 5, ... 20);

Your total query count drops from $1 + N$ down to just 2 queries-completely eliminating the N+1 problem while preserving lightning-fast, database-level pagination.

But you might have different batch size for different queries, your pagination limit might be passed from user, to provide flexibility of page size. In that case global configuration will not be the best solution.

Solution 2: The Two-Step @EntityGraph Pattern

You can use an Entity Graph safely without risking memory exhaustion, you must decouple the pagination logic from the collection fetching. We break this down into a quick two-step pattern:

1. Use a lightweight paginated query to fetch only the Parent IDs we need (sending a real SQL LIMIT/OFFSET to the database).
2. Pass those specific IDs into a secondary query decorated with @EntityGraph to fetch the complete object graph.

Here is what your repository looks like:

public interface CustomerRepository extends JpaRepository<Customer, Long> {

    // Step 1: Safe, database-level pagination (Returns just the active page slice)
    @Query("SELECT c FROM Customer c")
    Page<Customer> findPageOfCustomers(Pageable pageable);

    // Step 2: Fetch the object graph strictly for the targeted page IDs
    @EntityGraph(attributePaths = {"orders"})
    @Query("SELECT c FROM Customer c WHERE c.id IN :ids")
    List<Customer> findCustomersWithOrdersByIds(@Param("ids") List<Long> ids);
}

Next, coordinate these two calls within your service layer logic:

public Page<CustomerDTO> getPaginatedDashboard(Pageable pageable) {
    // 1. Database handles LIMIT/OFFSET perfectly on the slim entities
    Page<Customer> customerPage = customerRepository.findPageOfCustomers(pageable);

    List<Long> customerIds = customerPage.getContent().stream()
            .map(Customer::getId)
            .toList();

    // 2. Fetch full object graphs ONLY for these 20 specific records
    List<Customer> customersWithOrders = customerRepository
            .findCustomersWithOrdersByIds(customerIds);

    // 3. Map populated entities to DTOs
    List<CustomerDTO> dtos = customersWithOrders.stream()
            .map(this::convertToDto)
            .toList();

    return new PageImpl<>(dtos, pageable, customerPage.getTotalElements());
}

This pattern ensures your logs stay 100% warning-free. The database filters your dataset down first, and your application only loads the matching rows into memory.

Solution 3: The One-Query Holy Grail (Correlated Subqueries)

What if you absolutely refuse to write two separate method calls in your service layer? Can we achieve true database-level pagination and eliminate the N+1 problem inside a single repository query?

Yes, by bypassing how Hibernate structures its default table joins. Instead of a broad join fetch, you rewrite your JPQL query to use a correlated subquery combined with an explicit count query.

public interface CustomerRepository extends JpaRepository<Customer, Long> {

    @Query(value = """
           SELECT c FROM Customer c 
           LEFT JOIN FETCH c.orders 
           WHERE c.id IN (
               SELECT innerC.id FROM Customer innerC
           )
           """,
           countQuery = "SELECT COUNT(c) FROM Customer c")
    Page<Customer> findDashboardData(Pageable pageable);
}

Why This One-Query Approach Works:

1. Explicit Count Query: Providing a separate countQuery stops Spring Data from attempting to parse your heavy fetch logic into a broken counting mechanism.
2. True Database Pagination: Because the pagination conditions get bound tightly to the nested parent subquery footprint, your underlying database engine manages row limitations before resolving the structural collection mappings.
3. Zero In-Memory Warnings: Hibernate receives a distinct, pre-filtered set of IDs to map against the child rows. The HHH90003004 memory leak warning vanishes completely.

Your service layer drops down to one single, elegant line of code:

public Page<CustomerDTO> getDashboardData(Pageable pageable) {
    // Single database trip, true SQL pagination, and 0 memory leaks!
    Page<Customer> customerPage = customerRepository.findDashboardData(pageable);
    return customerPage.map(this::convertToDto);
}

Summary Takeaways for Your Code Reviews

• Never use FetchType.EAGER as a blanket fix for N+1 issues; it merely changes where the extra queries are executed.
• Never combine JOIN FETCH with collection associations when using a Pageable parameters. Always keep an eye out for the HHH90003004 memory warning in your log files.
• If you want an elegant, global safety net, leverage batch fetching via default_batch_fetch_size in your properties configuration to automatically collapse N subsequent queries into clean, highly optimized IN batches.
• If you prefer explicit control over specific queries, use the Two-Step Fetching Pattern via your service layer, or rewrite your JPQL to use a Correlated Subquery alongside an explicit countQuery to force the database to pagination-filter rows before joining.

To see a deep dive into configuring and debugging these queries directly inside IntelliJ IDEA, you can check out this Spring Data JPA Pagination Tutorial. This step-by-step video demonstrates how to safely manage child collections and check your query performance indicators.