-- PostgreSQL
EXPLAIN ANALYZE
SELECT u.name, o.order_date, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date > '2025-01-01'
ORDER BY o.total DESC;

-- MySQL
EXPLAIN
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Covering index (includes all needed columns)
CREATE INDEX idx_orders_covering ON orders(user_id, order_date)
INCLUDE (total, status);

-- Partial index for specific conditions
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Bad: Using SELECT *
SELECT * FROM orders WHERE user_id = 123;

-- Good: Select only needed columns
SELECT id, order_date, total FROM orders WHERE user_id = 123;

-- Bad: Function on indexed column prevents index use
SELECT * FROM users WHERE UPPER(email) = 'USER@EXAMPLE.COM';

-- Good: Use functional index or handle in application
CREATE INDEX idx_users_email_upper ON users(UPPER(email));
SELECT * FROM users WHERE UPPER(email) = 'USER@EXAMPLE.COM';

-- Bad: OR conditions can prevent index use
SELECT * FROM orders WHERE user_id = 123 OR status = 'pending';

-- Good: Use UNION for better performance
SELECT * FROM orders WHERE user_id = 123
UNION
SELECT * FROM orders WHERE status = 'pending';

-- Batch operations instead of N+1 queries
-- Bad: Multiple single queries
SELECT * FROM orders WHERE id = 1;
SELECT * FROM orders WHERE id = 2;
SELECT * FROM orders WHERE id = 3;

-- Good: Single batch query
SELECT * FROM orders WHERE id IN (1, 2, 3);

import javax.persistence.*;
import org.hibernate.annotations.*;
import java.util.*;

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_email", columnList = "email"),
    @Index(name = "idx_created", columnList = "created_at")
})
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(unique = true, nullable = false)
    private String email;

    @Column(name = "created_at")
    private LocalDateTime createdAt;

    // Use LAZY loading by default
    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    @BatchSize(size = 25) // Batch fetch to avoid N+1
    private Set orders = new HashSet<>();

    // Bidirectional mapping
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "department_id")
    private Department department;
}

import org.springframework.data.jpa.repository.*;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface UserRepository extends JpaRepository {

    // Bad: Will cause N+1 queries
    List findAll();

    // Good: Use JOIN FETCH to eagerly load associations
    @Query("SELECT DISTINCT u FROM User u LEFT JOIN FETCH u.orders")
    List findAllWithOrders();

    // Use EntityGraph for dynamic fetching
    @EntityGraph(attributePaths = {"orders", "department"})
    @Query("SELECT u FROM User u WHERE u.status = :status")
    List findByStatusWithAssociations(@Param("status") String status);

    // Fetch only IDs when you don't need full entities
    @Query("SELECT u.id FROM User u WHERE u.createdAt > :date")
    List findUserIdsSince(@Param("date") LocalDateTime date);
}

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.EntityManager;
import java.util.List;

@Service
public class OrderService {

    private final EntityManager entityManager;
    private static final int BATCH_SIZE = 50;

    public OrderService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Transactional
    public void batchInsertOrders(List orders) {
        for (int i = 0; i < orders.size(); i++) {
            entityManager.persist(orders.get(i));

            // Flush and clear every BATCH_SIZE entities
            if (i > 0 && i % BATCH_SIZE == 0) {
                entityManager.flush();
                entityManager.clear();
            }
        }
        entityManager.flush();
        entityManager.clear();
    }

    @Transactional
    public void batchUpdateOrders(List orderIds, String newStatus) {
        // Use bulk update instead of individual updates
        entityManager.createQuery(
            "UPDATE Order o SET o.status = :status WHERE o.id IN :ids"
        )
        .setParameter("status", newStatus)
        .setParameter("ids", orderIds)
        .executeUpdate();
    }
}

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;

// DTO projection interface
public interface UserSummary {
    Long getId();
    String getEmail();
    Integer getOrderCount();
}

@Repository
public interface UserRepository extends JpaRepository {

    // Projection query - fetches only needed fields
    @Query("SELECT u.id as id, u.email as email, COUNT(o) as orderCount " +
           "FROM User u LEFT JOIN u.orders o " +
           "GROUP BY u.id, u.email")
    List findUserSummaries();

    // Class-based projection
    @Query("SELECT new com.example.dto.UserDTO(u.id, u.email, u.name) " +
           "FROM User u WHERE u.status = :status")
    List findUserDTOs(@Param("status") String status);
}

// DTO class
public class UserDTO {
    private Long id;
    private String email;
    private String name;

    public UserDTO(Long id, String email, String name) {
        this.id = id;
        this.email = email;
        this.name = name;
    }

    // Getters and setters
}

import org.springframework.boot.actuate.health.Health;
import org.springframework.boot.actuate.health.HealthIndicator;
import org.springframework.stereotype.Component;
import com.zaxxer.hikari.HikariDataSource;

@Component
public class DatabaseHealthIndicator implements HealthIndicator {

    private final HikariDataSource dataSource;

    public DatabaseHealthIndicator(HikariDataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public Health health() {
        var pool = dataSource.getHikariPoolMXBean();

        return Health.up()
            .withDetail("active_connections", pool.getActiveConnections())
            .withDetail("idle_connections", pool.getIdleConnections())
            .withDetail("total_connections", pool.getTotalConnections())
            .withDetail("threads_awaiting", pool.getThreadsAwaitingConnection())
            .build();
    }
}

-- Flyway migration example: V1__create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);

-- V2__add_user_preferences.sql
CREATE TABLE user_preferences (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    theme VARCHAR(50) DEFAULT 'light',
    language VARCHAR(10) DEFAULT 'en',
    UNIQUE(user_id)
);

CREATE INDEX idx_user_preferences_user_id ON user_preferences(user_id);