30% Database Performance Gain: SQL Optimisation in Spring Boot

The Starting Point

At Decagon, I inherited a set of Spring Boot microservices that were noticeably slow under load. Profiling revealed the usual suspects: N+1 queries, missing indexes, and a few unbounded result sets being loaded into memory.

Here's what actually moved the needle.

1. Eliminating N+1 Queries with JOIN FETCH

The most impactful fix. An N+1 problem happens when you load a list of entities and then lazily fetch a related entity for each one — producing N+1 database round trips instead of 1.

Before:

List<Order> orders = orderRepository.findAll();
orders.forEach(o -> o.getItems().size()); // triggers N queries

After (JPQL JOIN FETCH):

@Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.status = :status")
List<Order> findWithItems(@Param("status") String status);

One query. For a table with 10,000 orders, this was the difference between 400ms and 12ms.

2. Projections Over Full Entity Loads

When you only need a subset of fields for a list view, loading full entities wastes memory and time. Spring Data JPA projections let you define exactly what you need:

public interface OrderSummary {
    Long getId();
    String getStatus();
    LocalDateTime getCreatedAt();
}

List<OrderSummary> findByCustomerId(Long customerId);

This reduced result set sizes and query times for list endpoints by roughly 40%.

3. Index-Aware Query Design

Checking EXPLAIN output on slow queries revealed that several common filters — order status, customer ID, created date — weren't indexed. Adding composite indexes on the most common filter combinations produced immediate improvements.

CREATE INDEX idx_orders_customer_status
  ON orders(customer_id, status, created_at DESC);

4. Pagination on All List Endpoints

Any endpoint returning a list without pagination is a ticking clock. Adding Pageable to all repository methods:

Page<Order> findByStatus(String status, Pageable pageable);

This also opened up cursor-based pagination for high-volume endpoints where offset pagination degrades.

Result

Combined, these changes produced the 30% database performance improvement measured in production, with the largest single gains coming from JOIN FETCH elimination and projection queries.

The lesson: profiling before optimising is non-negotiable. Each of these changes targeted a measured bottleneck, not an assumption.