Indexing, Caching & Database Optimization

-- Create a table
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status      VARCHAR(20) NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Basic index on frequently filtered column
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Index on status (low cardinality — but often used in WHERE)
CREATE INDEX idx_orders_status ON orders(status);

-- Composite index: queries filtering by customer AND status
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- Covering index: serve SELECT id, status, total_amount WHERE customer_id=? from index alone
CREATE INDEX idx_orders_covering
    ON orders(customer_id, status)
    INCLUDE (total_amount);

-- Partial index: only index rows matching a condition (smaller, faster)
CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'PENDING'; -- only indexes PENDING orders
-- Used when: WHERE status = 'PENDING' ORDER BY created_at

-- Expression index: index on a function result
CREATE INDEX idx_orders_email_lower
    ON users(LOWER(email)); -- for case-insensitive email lookup
-- Used when: WHERE LOWER(email) = 'user@example.com'

-- Unique index (also enforces uniqueness constraint)
CREATE UNIQUE INDEX uk_users_email ON users(email);

-- Drop index
DROP INDEX IF EXISTS idx_orders_customer;

-- EXPLAIN: shows the query plan WITHOUT executing
-- EXPLAIN ANALYZE: EXECUTES the query and shows actual vs estimated stats
-- Always use EXPLAIN ANALYZE BUFFERS for real diagnosis

EXPLAIN ANALYZE BUFFERS
SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.customer_id = u.id
WHERE o.status = 'SHIPPED'
  AND o.created_at > '2026-01-01';

-- Sample output:
-- Nested Loop  (cost=0.84..15.89 rows=2 width=52)
--              (actual time=0.025..0.032 rows=2 loops=1)
--   ->  Index Scan using idx_orders_status on orders o
--         (cost=0.42..8.00 rows=2 width=24)
--         (actual time=0.017..0.020 rows=2 loops=1)
--       Index Cond: (status = 'SHIPPED')
--       Filter: (created_at > '2026-01-01')
--   ->  Index Scan using users_pkey on users u
--         (cost=0.42..3.94 rows=1 width=28)
--         (actual time=0.005..0.006 rows=1 loops=1)
--       Index Cond: (id = o.customer_id)
-- Planning Time: 0.215 ms
-- Execution Time: 0.058 ms

-- KEY THINGS TO LOOK FOR:
-- 'Seq Scan' on a large table = MISSING INDEX (add one!)
-- 'Index Scan' = good, using an index
-- 'Index Only Scan' = best, covering index in use
-- 'Hash Join' / 'Merge Join' = efficient joins
-- 'Nested Loop' = ok for small result sets, bad for large ones
-- rows=10000 actual rows=1 = bad row estimate (UPDATE statistics: ANALYZE table_name)

-- After adding an index, re-run EXPLAIN ANALYZE to verify it's being used

import jakarta.persistence.*;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.Query;
import java.util.List;

// =========================================================
// PROBLEM 1: N+1 Query Problem
// =========================================================

@Entity
class Post {
    @Id @GeneratedValue Long id;
    String title;

    // FetchType.LAZY: comments not loaded until accessed
    @OneToMany(mappedBy = "post", fetch = FetchType.LAZY)
    List<Comment> comments;
}

@Entity
class Comment {
    @Id @GeneratedValue Long id;
    String text;
    @ManyToOne Post post;
}

// BAD: N+1 queries
interface PostRepository extends org.springframework.data.jpa.repository.JpaRepository<Post, Long> {
    // Finding all posts: 1 query
    // Then for each post.getComments(): 1 more query!
    // 100 posts = 101 queries. Classic N+1.

    // FIX 1: JOIN FETCH in JPQL
    @Query("SELECT DISTINCT p FROM Post p LEFT JOIN FETCH p.comments")
    List<Post> findAllWithComments();
    // 1 single SQL query with JOIN — all posts AND comments loaded

    // FIX 2: @EntityGraph — declarative eager loading
    @EntityGraph(attributePaths = {"comments"})
    List<Post> findAll(); // overrides lazy fetch just for this method

    // FIX 3: @BatchSize — load in batches instead of one-by-one
    // Add to the entity:
    // @OneToMany @BatchSize(size = 25)
    // Loads comments 25 at a time instead of 1 at a time
}

// =========================================================
// PROBLEM 2: Loading Entire Entity When Only Some Columns Needed
// =========================================================
interface UserSummary {
    Long getId();
    String getName();
    String getEmail();
    // Spring Data Projection interface — only SELECT id, name, email
}

interface UserRepository extends org.springframework.data.jpa.repository.JpaRepository<User, Long> {
    // BAD: loads ALL columns (including large blobs, passwords, etc.)
    List<User> findByStatus(UserStatus status);

    // GOOD: projection — only loads needed columns
    List<UserSummary> findByStatus(UserStatus status, Class<UserSummary> type);
    // Or via JPQL:
    @Query("SELECT u.id as id, u.name as name, u.email as email FROM User u WHERE u.status = :status")
    List<UserSummary> findSummariesByStatus(@org.springframework.data.repository.query.Param("status") UserStatus status);
}

// =========================================================
// PROBLEM 3: @Transactional on read queries without readOnly
// =========================================================
// Always mark read-only service methods:
// @Transactional(readOnly = true)
// This tells Hibernate: don't track dirty state (no need to flush),
// and hints the DB to use a read replica if configured.

// =========================================================
// PROBLEM 4: Fetch too much data
// =========================================================
interface PagedUserRepository extends org.springframework.data.jpa.repository.JpaRepository<User, Long> {
    // Use Pageable for large datasets — NEVER load all rows!
    org.springframework.data.domain.Page<User> findByStatus(
        UserStatus status,
        org.springframework.data.domain.Pageable pageable
    );
}
// Usage:
// Pageable page = PageRequest.of(0, 20, Sort.by("createdAt").descending());
// Page<User> result = userRepo.findByStatus(ACTIVE, page);

<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-jcache</artifactId>
</dependency>
<dependency>
    <groupId>com.github.ben-manes.caffeine</groupId>
    <artifactId>caffeine</artifactId>
</dependency>
<dependency>
    <groupId>com.github.ben-manes.caffeine</groupId>
    <artifactId>jcache</artifactId>
</dependency>

spring:
  jpa:
    properties:
      hibernate:
        cache:
          use_second_level_cache: true
          use_query_cache: true
          region:
            factory_class: org.hibernate.cache.jcache.internal.JCacheRegionFactory
        javax:
          cache:
            provider: com.github.benmanes.caffeine.jcache.spi.CaffeineCachingProvider
            missing_cache_strategy: create-warn

import jakarta.persistence.*;
import org.hibernate.annotations.Cache;
import org.hibernate.annotations.CacheConcurrencyStrategy;

@Entity
@Table(name = "countries")
@Cache(usage = CacheConcurrencyStrategy.READ_ONLY) // for data that never changes
// CacheConcurrencyStrategy options:
// READ_ONLY — best performance, for immutable data (countries, currencies, enums)
// NONSTRICT_READ_WRITE — for rarely-modified data, slight staleness acceptable
// READ_WRITE — transactionally correct, more overhead
public class Country {
    @Id private String code;   // "IN", "US", "JP"
    private String name;        // "India", "United States", "Japan"
    private String currency;
    // Getters...
}

// Collections can also be cached:
@Entity
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
class Product {
    @Id Long id;
    String name;

    @OneToMany
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE) // cache the collection too!
    List<ProductImage> images;
}

// Query cache: cache query RESULTS (not entities)
// Add to repository:
// @QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "true"))
// List<Country> findAll();

// L2 cache hits vs misses in logs:
// Enable: logging.level.org.hibernate.stat=DEBUG
// spring.jpa.properties.hibernate.generate_statistics=true
// Then check: SecondLevelCacheHitCount, SecondLevelCacheMissCount

import org.springframework.cache.annotation.EnableCaching;
import org.springframework.context.annotation.*;
import org.springframework.data.redis.cache.*;
import org.springframework.data.redis.connection.RedisConnectionFactory;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.serializer.*;
import java.time.Duration;

@Configuration
@EnableCaching
public class RedisConfig {

    // Configure RedisTemplate for manual Redis operations
    @Bean
    public RedisTemplate<String, Object> redisTemplate(
            RedisConnectionFactory factory) {
        RedisTemplate<String, Object> template = new RedisTemplate<>();
        template.setConnectionFactory(factory);
        template.setKeySerializer(new StringRedisSerializer());
        template.setValueSerializer(new GenericJackson2JsonRedisSerializer()); // JSON
        template.setHashKeySerializer(new StringRedisSerializer());
        template.setHashValueSerializer(new GenericJackson2JsonRedisSerializer());
        template.afterPropertiesSet();
        return template;
    }

    // Configure Spring Cache with different TTL per cache name
    @Bean
    public org.springframework.cache.CacheManager cacheManager(
            RedisConnectionFactory factory) {
        RedisCacheConfiguration defaultConfig = RedisCacheConfiguration.defaultCacheConfig()
            .entryTtl(Duration.ofMinutes(10))        // default 10min TTL
            .serializeValuesWith(RedisSerializationContext.SerializationPair
                .fromSerializer(new GenericJackson2JsonRedisSerializer()))
            .disableCachingNullValues();              // don't cache nulls

        return RedisCacheManager.builder(factory)
            .cacheDefaults(defaultConfig)
            .withCacheConfiguration("users",
                defaultConfig.entryTtl(Duration.ofMinutes(5)))  // users cache: 5min
            .withCacheConfiguration("countries",
                defaultConfig.entryTtl(Duration.ofHours(24)))   // countries: 24h
            .withCacheConfiguration("sessions",
                defaultConfig.entryTtl(Duration.ofDays(7)))     // sessions: 1 week
            .build();
    }
}

import org.springframework.data.redis.core.*;
import org.springframework.stereotype.Service;
import java.time.Duration;
import java.util.*;

@Service
public class RedisService {
    private final RedisTemplate<String, Object> redis;

    public RedisService(RedisTemplate<String, Object> redis) {
        this.redis = redis;
    }

    // --- String/Value operations (most common) ---
    public void set(String key, Object value, Duration ttl) {
        redis.opsForValue().set(key, value, ttl);
    }

    public Object get(String key) {
        return redis.opsForValue().get(key);
    }

    // Atomic increment (for counters, rate limiting)
    public Long increment(String key) {
        return redis.opsForValue().increment(key);
    }

    // --- Hash operations (store objects as key-field-value) ---
    public void setUserFields(Long userId, String field, Object value) {
        redis.opsForHash().put("user:" + userId, field, value);
    }

    public Map<Object, Object> getUserAll(Long userId) {
        return redis.opsForHash().entries("user:" + userId);
    }

    // --- List (queue/stack) ---
    public void pushToQueue(String queue, Object value) {
        redis.opsForList().rightPush(queue, value); // enqueue
    }
    public Object popFromQueue(String queue) {
        return redis.opsForList().leftPop(queue, Duration.ofSeconds(5)); // blocking dequeue
    }

    // --- Set (unique members) ---
    public void addToSet(String key, Object... values) {
        redis.opsForSet().add(key, values);
    }
    public boolean isMember(String key, Object value) {
        return Boolean.TRUE.equals(redis.opsForSet().isMember(key, value));
    }

    // --- Sorted Set (leaderboard, scheduling) ---
    public void addToLeaderboard(String board, String player, double score) {
        redis.opsForZSet().add(board, player, score);
    }
    public Set<Object> getTopN(String board, int n) {
        return redis.opsForZSet().reverseRange(board, 0, n - 1);
    }

    // --- Rate Limiter using Redis ---
    public boolean isRateLimited(String identifier, int maxRequests, long windowSeconds) {
        String key = "rate:" + identifier;
        Long count = redis.opsForValue().increment(key);
        if (count != null && count == 1) {
            redis.expire(key, Duration.ofSeconds(windowSeconds));
        }
        return count != null && count > maxRequests;
    }

    // --- Cache Aside Pattern ---
    public UserResponse getUser(Long id) {
        String key = "user:" + id;
        UserResponse cached = (UserResponse) redis.opsForValue().get(key);
        if (cached != null) return cached; // cache hit

        // cache miss — load from DB
        UserResponse user = userRepository.findById(id).map(this::toResponse).orElseThrow();
        redis.opsForValue().set(key, user, Duration.ofMinutes(10)); // populate cache
        return user;
    }
}

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/myapp
    username: appuser
    password: secret
    driver-class-name: org.postgresql.Driver

    hikari:
      # Pool size formula: connections = (core_count * 2) + effective_spindle_count
      # For most web apps on a 4-core machine: 4*2 + 1 = 9
      # HikariCP docs: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
      maximum-pool-size: 10
      minimum-idle: 5             # keep at least 5 idle (reduces cold-start latency)

      # Connection timeout: how long to WAIT for a connection from the pool
      # If pool is exhausted and no connection available after this time: throw exception
      connection-timeout: 30000   # 30 seconds (reduce for faster failure: 3000-5000ms)

      # How long a connection can sit IDLE before being removed from pool
      idle-timeout: 600000        # 10 minutes

      # Max lifetime of a connection (recycle to prevent stale connections)
      # Must be < DB server's wait_timeout (MySQL) or tcp_keepalives_idle (Postgres)
      max-lifetime: 1800000       # 30 minutes

      # Query to test connection health
      connection-test-query: SELECT 1  # for MySQL. For PostgreSQL: omit (uses ping)

      pool-name: MainHikariPool   # visible in JMX/Actuator metrics

      # Leak detection: log a warning if a connection is held for more than this time
      leak-detection-threshold: 5000  # 5 seconds — catches forgotten connection.close()

      # For read replicas: create a second DataSource
      # Inject specific one with @Qualifier("readDataSource")

import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariPoolMXBean;
import org.springframework.stereotype.Component;

@Component
public class HikariPoolMonitor {

    private final HikariDataSource dataSource;

    public HikariPoolMonitor(javax.sql.DataSource dataSource) {
        this.dataSource = (HikariDataSource) dataSource;
    }

    public void printStats() {
        HikariPoolMXBean pool = dataSource.getHikariPoolMXBean();
        System.out.println("=== HikariCP Pool Stats ===");
        System.out.println("Total connections:  " + pool.getTotalConnections());
        System.out.println("Active connections: " + pool.getActiveConnections());
        System.out.println("Idle connections:   " + pool.getIdleConnections());
        System.out.println("Threads waiting:    " + pool.getThreadsAwaitingConnection());
        // If 'Threads waiting' > 0 frequently: increase maximum-pool-size
        // If 'Active connections' always = total: pool is saturated!
    }
}

// Actuator exposes HikariCP metrics at:
// GET /actuator/metrics/hikaricp.connections
// GET /actuator/metrics/hikaricp.connections.active
// GET /actuator/metrics/hikaricp.connections.idle
// GET /actuator/metrics/hikaricp.connections.pending
// Integrate with Prometheus + Grafana for dashboards

# PostgreSQL: log queries slower than 100ms
# In postgresql.conf:
# log_min_duration_statement = 100  # milliseconds
# log_statement = 'all'             # log ALL queries (dev only)
# log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# In Spring Boot: log slow Hibernate queries
spring:
  jpa:
    properties:
      hibernate:
        session:
          events:
            log:
              LOG_QUERIES_SLOWER_THAN_MS: 100  # log queries > 100ms

# P6Spy: intercept and log ALL JDBC calls with timing
# Add dependency: com.github.gavlyukovskiy:p6spy-spring-boot-starter:1.9.1
# application.yml:
# decorator:
#   datasource:
#     p6spy:
#       enable-logging: true
#       log-format: 'p6spy - time:%(executionTime)ms | sql:%(sql)'
#       slow-query-threshold-ms: 100