Book a Demo
Book a Demo
CubeAPM
CubeAPM CubeAPM

How to Monitor Spring Data JPA: Detecting N+1 and Slow Query Issues

How to Monitor Spring Data JPA: Detecting N+1 and Slow Query Issues

Table of Contents

A single unnoticed N+1 query pattern in a Spring Data JPA application can turn a 5ms database call into 500ms at scale not because the query is slow, but because Hibernate fires one query per row fetched in a loop. Teams running 10,000 daily active users often don’t see this until the production database starts throwing timeout alerts at 50,000.

This guide walks through the exact steps to detect N+1 problems and slow queries in Spring Data JPA: from enabling SQL logging and Hibernate statistics to using p6spy and an APM tool to catch what logs alone miss. By the end, you will have a monitoring setup that surfaces these issues before your users do.

Prerequisites

  • Spring Boot 2.x or 3.x application with Spring Data JPA configured
  • Hibernate as the JPA provider (default in Spring Boot)
  • A relational database (PostgreSQL, MySQL, or similar)
  • Access to application.properties or application.yml
  • Java 11 or higher
  • Basic familiarity with JPA entity relationships (@OneToMany, @ManyToOne)
  • Maven or Gradle build system

Step 1: Enable SQL Logging to See What Hibernate Is Generating

The fastest way to start detecting N+1 and slow queries is to make Hibernate print every SQL statement it executes. This gives you direct visibility into whether a single service method is firing 1 query or 150.

Add these properties to your application.properties:

# Show all SQL statements in logs
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
# Log SQL with parameter bindings
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.orm.jdbc.bind=TRACE

Once enabled, a method that loads 50 authors and then accesses their posts in a loop will produce output like this in your console:

select a1_0.id, a1_0.name from author a1_0
select p1_0.author_id, p1_0.id, p1_0.title from post p1_0 where p1_0.author_id=1
select p1_0.author_id, p1_0.id, p1_0.title from post p1_0 where p1_0.author_id=2
select p1_0.author_id, p1_0.id, p1_0.title from post p1_0 where p1_0.author_id=3
-- ...47 more queries

That pattern: one query followed by N repeated queries differing only by an ID, is the N+1 problem. The number of additional queries equals the number of parent entities returned.

What to look for in the logs:

  • Repeated SELECT statements with the same structure but different WHERE clause values
  • The same table being queried inside a loop
  • Queries with no WHERE clause fetching full tables (missing pagination)

One limitation: SQL logs show you that a problem exists but not which service method caused it. For that, you need the steps that follow.

Step 2: Enable Hibernate Statistics to Quantify the Problem

SQL logs are noisy at high volume and hard to analyze programmatically. Hibernate Statistics gives you aggregated numbers: total queries executed, slow queries, second-level cache hit rates, and entity load counts, all measurable per request.

Add this property:

spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.stat=DEBUG

Then expose statistics programmatically so you can inspect them in tests or service code:

import org.hibernate.SessionFactory;
import org.hibernate.stat.Statistics;
import jakarta.persistence.EntityManagerFactory;
@Service
public class HibernateStatsService {
    private final Statistics statistics;
    public HibernateStatsService(EntityManagerFactory entityManagerFactory) {
        SessionFactory sessionFactory = entityManagerFactory.unwrap(SessionFactory.class);
        this.statistics = sessionFactory.getStatistics();
    }
    public void printStats() {
        System.out.println("Total queries: " + statistics.getQueryExecutionCount());
        System.out.println("Slowest query (ms): " + statistics.getQueryExecutionMaxTime());
        System.out.println("Slowest query string: " + statistics.getQueryExecutionMaxTimeQueryString());
        System.out.println("Entity loads: " + statistics.getEntityLoadCount());
        System.out.println("Collections initialized: " + statistics.getCollectionFetchCount());
    }
}

The getCollectionFetchCount() metric is particularly telling. If you load 100 Author entities and this counter shows 100, you have an N+1 problem on the posts collection.

You can also write a JUnit test that asserts query count stays within an acceptable threshold, catching N+1 regressions before they reach production:

@SpringBootTest
@Transactional
class AuthorServiceTest {
    @Autowired
    private AuthorService authorService;
    @Autowired
    private EntityManagerFactory entityManagerFactory;
    @Test
    void fetchingAuthorsShouldNotExceedTwoQueries() {
        Statistics stats = entityManagerFactory.unwrap(SessionFactory.class).getStatistics();
        stats.setStatisticsEnabled(true);
        stats.clear();
        authorService.printAuthorsAndPosts();
        long queryCount = stats.getQueryExecutionCount();
        assertThat(queryCount).isLessThanOrEqualTo(2);
    }
}

This test fails the moment a developer introduces an N+1 regression, giving you a CI gate before the code ships.

Step 3: Add p6spy to Capture Slow Queries With Timing

Hibernate statistics give you aggregate numbers. p6spy gives you timing data for every individual SQL statement, including the actual parameter values — which is what you need to identify the slow outliers in a realistic request mix.

Add the dependency to pom.xml:

<dependency>
    <groupId>com.github.gavlyukovskiy</groupId>
    <artifactId>p6spy-spring-boot-starter</artifactId>
    <version>1.9.2</version>
</dependency>

For Gradle:

implementation 'com.github.gavlyukovskiy:p6spy-spring-boot-starter:1.9.2'

Create spy.properties in src/main/resources:

# Log format: execution time | category | SQL with parameters
logMessageFormat=com.p6spy.engine.spy.appender.MultiLineFormat
appender=com.p6spy.engine.spy.appender.Slf4JLogger
slf4jLogLevel=DEBUG
# Only log statements taking longer than 100ms
executionThreshold=100
# Log slow query details
outagedetection=true
outagedetectioninterval=2

With executionThreshold=100, p6spy will only log queries that take over 100 milliseconds, which cuts noise dramatically while surfacing the queries that actually hurt user-facing latency.

The output looks like this:

#1717234512345 | took 347ms | statement |
    select o1_0.id, o1_0.product_id, o1_0.user_id, o1_0.amount
    from orders o1_0
    where o1_0.user_id = 4521

This is significantly more useful than raw SQL logs because you see which queries are slow, not just which queries ran.

Important: Disable p6spy in production or set a high executionThreshold value. The proxying overhead can add 2-5ms per query at high throughput, acceptable for staging environments but worth measuring before enabling broadly in production.

Step 4: Fix the N+1 Problem With JOIN FETCH, Entity Graphs, and Batch Fetching

Once you have confirmed an N+1 problem through logs or statistics, you have three main tools to fix it. The right choice depends on your access pattern.

Option 1: JOIN FETCH in JPQL

Use this when you always need the related collection when loading the parent.

public interface AuthorRepository extends JpaRepository<Author, Long> {
    @Query("SELECT a FROM Author a JOIN FETCH a.posts")
    List<Author> findAllWithPosts();
}

This generates a single SQL JOIN instead of N separate selects:

select a1_0.id, a1_0.name, p1_0.author_id, p1_0.id, p1_0.title
from author a1_0
join post p1_0 on a1_0.id = p1_0.author_id

One query. Done.

Watch out: JOIN FETCH with pagination (Pageable) triggers a HHH90003004 warning in Hibernate — it loads all rows into memory and paginates in Java, not the database. For paginated endpoints, use @EntityGraph or batch fetching instead.

Option 2: @EntityGraph

Use this when you need the collection sometimes, not always. Entity graphs let you override fetch strategy per query method without writing JPQL.

@EntityGraph(attributePaths = {"posts"})
@Query("SELECT a FROM Author a")
List<Author> findAllWithPosts();

Or using Spring Data’s named method syntax:

@EntityGraph(attributePaths = {"posts"})
Optional<Author> findById(Long id);

Entity graphs work well alongside pagination because they use a subselect strategy that avoids the in-memory pagination problem.

Option 3: Batch Fetching

Use this when you want to keep lazy loading but reduce the number of round trips.

spring.jpa.properties.hibernate.default_batch_fetch_size=25

With a batch size of 25, instead of 100 individual queries for 100 authors’ posts, Hibernate generates:

SELECT * FROM post WHERE author_id IN (1,2,3,...,25)
SELECT * FROM post WHERE author_id IN (26,27,...,50)
-- and so on

Four queries instead of 100. The queries are still lazy, but grouped. This is a useful default setting even if you also use JOIN FETCH for specific hot paths — it acts as a safety net for relationships you missed.

Comparison of fix options

ScenarioRecommended fix
Always need the associationJOIN FETCH
Sometimes need the association@EntityGraph
Paginated queries with lazy collections@EntityGraph with subselect
General safety net across the appdefault_batch_fetch_size
Large collections with heavy filteringDTO projection

Step 5: Configure Slow Query Logging at the Database Driver Level

For slow queries that are not caused by N+1 — such as a missing index or a full table scan — configure your database driver and JPA to log them directly.

Slow query log via Hibernate

# Log any query taking longer than 500ms
spring.jpa.properties.hibernate.session.events.log.LOG_QUERIES_SLOWER_THAN_MS=500

This is a Hibernate 5.4.5+ feature that writes slow query entries to a dedicated logger. Configure the logger level:

logging.level.org.hibernate.SQL_SLOW=INFO

Slow query log via datasource (HikariCP + PostgreSQL example)

For PostgreSQL, enable log_min_duration_statement on the database server side. In postgresql.conf:

log_min_duration_statement = 200

This logs any query exceeding 200ms directly in PostgreSQL logs, completely independent of the application layer — which catches slow queries from database migration scripts, cron jobs, and other non-JPA access patterns.

Named query tracking

# Track query execution stats per named query
spring.jpa.properties.hibernate.cache.use_query_cache=false
spring.jpa.properties.hibernate.generate_statistics=true

With statistics enabled, you can retrieve per-query timing breakdowns:

QueryStatistics qs = statistics.getQueryStatistics(
    "SELECT a FROM Author a JOIN FETCH a.posts"
);
System.out.println("Avg time: " + qs.getExecutionAvgTime() + "ms");
System.out.println("Max time: " + qs.getExecutionMaxTime() + "ms");
System.out.println("Execution count: " + qs.getExecutionCount());

Step 6: Instrument With OpenTelemetry for Production-Grade Observability

SQL logs and Hibernate statistics work well in development and staging. In production, you need trace-level visibility that ties database query timing to the specific API endpoint, user session, and service that triggered it. That requires distributed tracing with OpenTelemetry.

Add the OpenTelemetry Java agent to your Spring Boot application. With Maven, add the JDBC instrumentation:

<dependency>
    <groupId>io.opentelemetry.instrumentation</groupId>
    <artifactId>opentelemetry-jdbc</artifactId>
    <version>2.4.0-alpha</version>
</dependency>

Run your application with the OpenTelemetry Java agent:

java -javaagent:/path/to/opentelemetry-javaagent.jar \
  -Dotel.service.name=order-service \
  -Dotel.exporter.otlp.endpoint=http://your-apm-backend:4317 \
  -Dotel.traces.exporter=otlp \
  -jar your-app.jar

The OpenTelemetry JDBC instrumentation automatically creates spans for every database call, including:

  • The SQL statement (sanitized by default to remove parameter values)
  • Execution time in milliseconds
  • The database system and target table
  • The trace context linking this DB call to the parent HTTP request span

This is where the unique insight shows up in production: you will see not just that a query took 400ms, but that it was called 87 times in the span of a single HTTP request to GET /api/orders, and that request itself took 2.1 seconds. Without distributed tracing, you would see a slow response time on the endpoint but have no way to attribute it to Hibernate’s lazy loading triggering repeated queries inside a serializer loop.

Understanding how this fits into your broader infrastructure monitoring practice helps — slow queries rarely exist in isolation; they usually compound with connection pool exhaustion and CPU pressure on the database host.

What an APM tool surfaces that logs miss

SignalSQL logsHibernate statsOpenTelemetry APM
Which queries are slowYesAggregate onlyYes, per request
Which endpoint caused itNoNoYes
How many times called per requestManual countingCollection fetch countYes, automatic
Database connection wait timeNoNoYes
Correlation with upstream errorsNoNoYes
Historical trendsNoNoYes

Step 7: Monitor JPA Query Performance With CubeAPM

CubeAPM is an OpenTelemetry-native APM platform that surfaces Spring Data JPA query performance in production without requiring any custom instrumentation beyond the standard OTel Java agent. It deploys inside your own cloud or on-premises infrastructure, so database query traces and slow query data never leave your environment.

Once your Spring Boot application is instrumented with the OpenTelemetry agent pointing to CubeAPM, you get:

  • Span-level query breakdown: Every SQL statement appears as a child span under the parent HTTP request trace, with execution time, affected rows, and database target visible at a glance.
  • N+1 detection by query pattern: CubeAPM groups repeated queries with the same structure (differing only by parameter values) and surfaces them as a single pattern with a count. If SELECT * FROM post WHERE author_id = ? fires 150 times in one trace, it appears as one entry labeled “×150” — not 150 separate spans buried in a waterfall.
  • Slow query percentiles: p50, p90, and p99 query times per endpoint, so you can distinguish a query that is always slow from one that occasionally spikes.
  • RED metrics at service and endpoint level: Rate, Error, and Duration metrics per API endpoint make it easy to correlate a latency increase on GET /orders with a new N+1 pattern introduced in the last deployment.
  • Log and trace correlation: Application logs are linked to trace IDs, so you can jump from a slow trace directly to the Hibernate statistics log entry from the same request.

CubeAPM pricing is $0.15/GB of ingested data with no per-seat fees. For a team generating 5GB of trace data per day (roughly 150GB/month), the APM cost is $22.50/month — compared to a Datadog APM setup at $42/host/month that would cost $420/month for a 10-host Spring Boot deployment before log or custom metric charges.

Since CubeAPM runs self hosted inside your VPC, there are no data egress charges when sending traces from your application servers to the APM backend — a cost that adds roughly $0.10/GB in egress fees with cloud-only SaaS APM tools.

Pricing based on publicly available information as of June 2026. Verify current rates at [cubeapm.com/pricing](https://cubeapm.com/pricing/).

Troubleshooting Common Issues

Hibernate statistics show 0 queries even though SQL logging shows activity

Statistics must be enabled before the session opens. If you call stats.clear() after the session has already started, you may miss queries from that session. Call clear() before invoking the service method, and ensure generate_statistics=true is set in application.properties.

p6spy is not logging anything

Check that the spy.properties file is in src/main/resources and that the executionThreshold is not set too high for your query times. In development where queries typically run in under 10ms, set executionThreshold=0 to log everything, then raise it for staging environments.

JOIN FETCH with Pageable produces the warning HHH90003004

This is a known Hibernate behavior: JOIN FETCH cannot be combined with database-level pagination because the JOIN multiplies rows. Replace JOIN FETCH with @EntityGraph using the SUBSELECT fetch mode, or use a two-query approach: first fetch IDs with pagination, then fetch full entities by those IDs with JOIN FETCH.

// Two-query approach for paginated N+1 fix
@Query("SELECT a.id FROM Author a")
Page<Long> findAuthorIds(Pageable pageable);
@Query("SELECT a FROM Author a JOIN FETCH a.posts WHERE a.id IN :ids")
List<Author> findByIdsWithPosts(@Param("ids") List<Long> ids);

N+1 persists even after adding JOIN FETCH

Check whether the entity is being serialized to JSON after the transaction closes. If Jackson (or another serializer) accesses lazy collections outside the @Transactional boundary, Hibernate will re-trigger lazy loading — and your JOIN FETCH inside the repository never ran in that context. Annotate the service method with @Transactional(readOnly = true) to keep the session open through serialization, or use DTO projections that copy data out before the session closes.

Slow queries identified in development do not reproduce in production

Execution plans differ between environments. A query that uses an index in development may do a full table scan in production if the table has 500x more rows. Use EXPLAIN ANALYZE on the slow query directly against the production database (on a read replica if available) to confirm the plan. Production-scale query analysis often requires database monitoring tools that track execution plans over time, not just query text.

OpenTelemetry spans show database time but no SQL text

By default, the OTel JDBC instrumentation sanitizes query text (replaces literals with ?) for security. To enable full SQL capture in non-production environments:

-Dotel.instrumentation.jdbc.statement-sanitizer.enabled=false

Never enable this in production environments where query parameters may contain PII.

—

Disclaimer: The information in this article reflects the latest details available at the time of publication and may change as technologies and products evolve. Features, pricing, and plan limits can change over time. Always verify the latest information directly with the vendor before making purchasing or deployment decisions.

—

Frequently Asked Questions

What causes the N+1 query problem in Spring Data JPA?

The N+1 problem is caused by Hibernate’s default lazy loading behavior on `@OneToMany` and `@ManyToOne` relationships. When you load a list of parent entities and then access a lazy collection on each one inside a loop, Hibernate fires one query to load the parents and then one additional query per parent to load the related collection. With 100 parent entities, that is 101 queries instead of 1.

How do I detect N+1 queries without enabling verbose SQL logging in production?

Enable Hibernate statistics with `spring.jpa.properties.hibernate.generate_statistics=true` and monitor `getCollectionFetchCount()`. If this counter equals or exceeds the number of parent entities loaded, an N+1 pattern is present. For production-grade detection without log noise, use an OpenTelemetry-compatible APM tool that groups repeated query patterns by structure and surfaces them with execution counts per trace.

Is JOIN FETCH always the right fix for N+1 in Spring Data JPA?

Not always. JOIN FETCH is the right fix when you always need the associated collection. If you only sometimes need it, use `@EntityGraph` to avoid loading data you do not need. If you use pagination, avoid JOIN FETCH entirely because it causes in-memory pagination rather than database-level pagination. For general protection across the application, set `hibernate.default_batch_fetch_size` to group lazy loads into IN clause queries.

What is the difference between p6spy and Hibernate slow query logging?

Hibernate slow query logging (`LOG_QUERIES_SLOWER_THAN_MS`) works at the JDBC abstraction layer and logs only queries that exceed your threshold. It does not log parameter values by default. p6spy works as a JDBC proxy and captures every statement with full parameter values and execution time, giving you richer data but with a small per-query overhead. p6spy is better suited to development and staging; Hibernate slow query logging is lower-overhead for production.

Can I catch N+1 regressions automatically in CI?

Yes. Enable Hibernate statistics in your Spring Boot test context, call `stats.clear()` before your service method, then assert that the query count after the call is within an expected bound. If a developer introduces a new lazy association that creates N+1 behavior, the test fails with a query count assertion error. This is the most reliable way to prevent N+1 regressions from reaching production.

Why does my Spring Data JPA application run fast locally but slow in production?

The most common causes are query execution plan differences due to data volume, missing indexes on foreign key columns, and lazy loading triggered outside the transaction boundary during JSON serialization. Locally, your table has hundreds of rows and full table scans are fast. In production with millions of rows, the same query without an index takes seconds. Use `EXPLAIN ANALYZE` on the production database and add indexes on columns used in JPA relationship joins and WHERE clauses.

What OpenTelemetry instrumentation covers Spring Data JPA automatically?

The OpenTelemetry Java agent with JDBC instrumentation automatically captures spans for database calls made through Hibernate and Spring Data JPA without requiring code changes. It records query text, execution time, database type, and trace context. For Spring MVC, the same agent captures HTTP request spans automatically, giving you the full trace from HTTP request to database query in a single waterfall view.

Book a Demo

Related Posts

×
×