Optimizing PostgreSQL Queries in Spring Boot Applications for 60% Faster API Response Times

In high-traffic enterprise backend systems, database query bottlenecks are often the primary cause of high latency and degraded application responsiveness. During my work at iQSys Technologies Pvt. Ltd., optimizing database queries resulted in a 60% reduction in average API response time.

In this technical deep dive, I share the exact strategies and Spring Data JPA techniques we used to achieve these performance gains.

1. The Hidden Cost of N+1 Query Problems

One of the most common issues when using Object-Relational Mapping (ORM) frameworks like Hibernate/JPA is the N+1 Select Problem.

The Problem Scenario

Imagine an Employee entity that has a @ManyToOne relationship with a Department entity. When fetching a page of 50 employees, JPA might issue:

1. 1 query to fetch the 50 employees.
2. 50 individual queries to fetch the department details for each employee!

// Bad Approach: Lazy loading triggers N individual DB round-trips inside loop or JSON serializer
List<Employee> employees = employeeRepository.findAll();

The Solution: Entity Graphs & FETCH JOINs

We solved this by leveraging JPA Entity Graphs or explicit JOIN FETCH queries:

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @EntityGraph(attributePaths = {"department", "roles"})
    @Query("SELECT e FROM Employee e WHERE e.active = true")
    List<Employee> findAllActiveWithDetails();
}

This collapses 51 query round-trips into a single optimized SQL join query, reducing database CPU load and network overhead drastically.

2. Multi-Column Composite Indexes in PostgreSQL

Indexes are critical, but blindly creating single-column indexes on every column can actually slow down INSERT and UPDATE operations while failing to speed up multi-condition queries.

Query Analysis with EXPLAIN ANALYZE

We identified slow queries by logging execution times above 200ms and analyzing them with PostgreSQL's EXPLAIN ANALYZE:

EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE status = 'PROCESSING' AND created_at >= '2026-01-01' 
ORDER BY created_at DESC;

Creating Partial Composite Index

Instead of indexing all status rows, we created a partial composite index targeting active orders:

CREATE INDEX idx_orders_processing_created 
ON orders (status, created_at DESC) 
WHERE status = 'PROCESSING';

Result: Query execution time dropped from 340ms to 8ms!

3. Tuning HikariCP Connection Pool

Spring Boot uses HikariCP as its default JDBC connection pool. Default settings may not be optimal for workloads with concurrent microservice requests.

Optimized application.yml Configuration

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 10
      idle-timeout: 300000
      max-lifetime: 1800000
      connection-timeout: 20000
      pool-name: HikariCP-PrimaryDB

Summary & Takeaways

By systematically tackling ORM query generation, configuring composite PostgreSQL indexes, and tuning HikariCP connection parameters, we achieved:

• ⚡ -60% decrease in overall API response latency.
• 📉 -40% reduction in database server CPU utilization under peak traffic.
• 🚀 Zero breaking changes to frontend API contracts.