Database Optimization trong Java: Hướng dẫn về Spring Boot, Quarkus, Hibernate, JPA và SQL Performance

100 round-trips × 1ms = 100ms thêm vào mỗi request

Ứng dụng              PostgreSQL
    │                      │
    │──── SELECT orders ───>│    1 round-trip
    │<──── 50 rows ─────────│
    │──── SELECT status ───>│
    │<─────────────────────│    50 round-trips
    │──── SELECT user ─────>│
    │<─────────────────────│    50 round-trips
    │──── SELECT items ────>│
    │<─────────────────────│    50 round-trips
    │                      │
    Total: 151 round-trips

-- Tỷ lệ cache hit của buffer pool
SELECT
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;
-- Mục tiêu: > 0.99 cho OLTP workload

-- Chạy thủ công sau bulk operation lớn
ANALYZE orders;

-- Xem statistics hiện tại
SELECT tablename, n_live_tup, n_dead_tup, last_analyze
FROM pg_stat_user_tables
WHERE tablename = 'orders';

B-Tree cho column "price":
                    [50]
                 /        \
           [20, 35]       [65, 80]
          /   |   \       /   |   \
       [10] [25] [40] [55] [70] [90,95]
        ↓    ↓    ↓    ↓    ↓    ↓
      Row  Row  Row  Row  Row  Row
      ptrs ptrs ptrs ptrs ptrs ptrs

-- PostgreSQL: query này luôn cần 2 lookups nếu không là covering index
-- 1. Index lookup để tìm row pointer
-- 2. Heap fetch để đọc actual data
SELECT * FROM orders WHERE customer_id = 123;

-- MySQL InnoDB: query này với secondary index trên customer_id
-- cần lookup secondary index, rồi primary key lookup (clustered)
-- = 2 B-Tree traversals
SELECT * FROM orders WHERE customer_id = 123;

-- Query thường gặp trong order management system:
SELECT * FROM orders
WHERE status = 'PENDING'
  AND created_at > NOW() - INTERVAL '24 hours'
  AND merchant_id = 456;

-- Index option 1: (status, merchant_id, created_at)
-- Index option 2: (merchant_id, status, created_at)
-- Index option 3: (created_at, status, merchant_id)

CREATE INDEX idx_orders_merchant_status_created
ON orders (merchant_id, status, created_at DESC);

-- Query:
SELECT order_id, status, total_amount
FROM orders
WHERE merchant_id = 456 AND status = 'PENDING';

-- Index (merchant_id, status) — không covering, phải heap fetch để lấy total_amount
-- Index (merchant_id, status, total_amount, order_id) — covering index
-- PostgreSQL EXPLAIN: "Index Only Scan" thay vì "Index Scan"

EXPLAIN ANALYZE
SELECT order_id, status, total_amount
FROM orders
WHERE merchant_id = 456 AND status = 'PENDING';

-- Với covering index:
-- Index Only Scan using idx_orders_covering on orders
--   Index Cond: ((merchant_id = 456) AND (status = 'PENDING'))
--   Heap Fetches: 0    ← không cần đọc heap
--   Rows Removed by Filter: 0

-- Hệ thống payment: 95% transaction là COMPLETED, 5% là PENDING/PROCESSING
-- Chỉ operations cần xử lý PENDING/PROCESSING mới cần index

CREATE INDEX idx_transactions_pending
ON transactions (created_at, merchant_id)
WHERE status IN ('PENDING', 'PROCESSING');

-- Index này chỉ chứa ~5% rows, nhỏ hơn 20× so với full index
-- Query với WHERE status = 'PENDING' AND merchant_id = 123 sẽ dùng index này

-- Kiểm tra cardinality
SELECT
    attname AS column_name,
    n_distinct,
    correlation  -- 1.0 = perfectly sorted, -1.0 = reverse sorted, 0 = random
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('status', 'merchant_id', 'created_at');

-- Một table "orders" có 15 index trong production thực tế:
-- Mỗi INSERT/UPDATE/DELETE phải update tất cả 15 B-Trees

-- Đo write throughput impact:
-- 5 indexes: ~12,000 inserts/sec
-- 15 indexes: ~7,000 inserts/sec
-- 25 indexes: ~4,000 inserts/sec

-- Các index không được dùng trong 30 ngày
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY pg_relation_size(indexrelid) DESC;

// Hibernate - JPA thường mặc định SELECT *
@Entity
public class Order {
    @Id private Long id;
    private String status;
    private BigDecimal totalAmount;
    @Lob private byte[] receiptPdf; // 5MB mỗi order
    // 20 fields khác
}

// Code này load 5MB receipt PDF cho mỗi order dù không cần
List<Order> orders = orderRepository.findByMerchantId(merchantId);

// Fix: dùng projection
public interface OrderSummary {
    Long getId();
    String getStatus();
    BigDecimal getTotalAmount();
}

List<OrderSummary> orders = orderRepository.findByMerchantId(merchantId, OrderSummary.class);

// Code nhìn vô hại:
List<Order> orders = orderRepository.findAll(); // 1 query
for (Order order : orders) {
    // Mỗi access này trigger 1 query riêng nếu items là LAZY
    System.out.println(order.getItems().size()); // N queries
}
// Tổng: 1 + N queries

// application.properties - bật Hibernate statistics
spring.jpa.properties.hibernate.generate_statistics=true
spring.jpa.properties.hibernate.session.events.log.LOG_QUERIES_SLOWER_THAN_MS=100

// Log output:
// Session Metrics {
//   1000001 nanoseconds spent acquiring 1 JDBC connections;
//   2502341 nanoseconds spent on 201 JDBC statements; ← 201 queries!
//   ...
// }

@Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.merchantId = :merchantId")
List<Order> findWithItems(@Param("merchantId") Long merchantId);
// 1 query với JOIN, Hibernate hydrate cả Order và Items

@EntityGraph(attributePaths = {"items", "items.product"})
List<Order> findByMerchantId(Long merchantId);
// Hibernate tự tạo JOIN FETCH query

@Entity
public class Order {
    @OneToMany
    @BatchSize(size = 50) // Load 50 order's items trong 1 query
    private List<OrderItem> items;
}

// Với 200 orders: 1 query + ceil(200/50) = 5 queries = 6 tổng
// Thay vì 201 queries

-- Hash Join: hiệu quả khi cả hai table lớn, không có index dùng được
-- Merge Join: hiệu quả khi cả hai input đã sorted (index scan)
-- Nested Loop: hiệu quả khi outer table nhỏ, inner có index

-- Ví dụ: join orders với merchants
EXPLAIN ANALYZE
SELECT o.id, o.total_amount, m.name
FROM orders o
JOIN merchants m ON o.merchant_id = m.id
WHERE o.status = 'PENDING'
  AND o.created_at > NOW() - INTERVAL '1 day';

-- Nếu thấy "Hash Join" với "Seq Scan" trên orders → thiếu index
-- Nếu thấy "Nested Loop" với orders là outer và merchants là inner → OK

-- Bad: JOIN merchants chỉ để check existence
SELECT o.*
FROM orders o
JOIN merchants m ON o.merchant_id = m.id
WHERE m.status = 'ACTIVE';

-- Better: Semi-join với EXISTS, không cần fetch merchant data
SELECT o.*
FROM orders o
WHERE EXISTS (
    SELECT 1 FROM merchants m
    WHERE m.id = o.merchant_id AND m.status = 'ACTIVE'
);

-- IN với subquery: PostgreSQL materialize subquery một lần
SELECT * FROM orders
WHERE merchant_id IN (SELECT id FROM merchants WHERE status = 'ACTIVE');

-- EXISTS: short-circuit ngay khi tìm thấy match đầu tiên
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM merchants m
    WHERE m.id = o.merchant_id AND m.status = 'ACTIVE'
);

-- NOT IN với NULL — trap phổ biến
-- Nếu subquery trả về bất kỳ NULL nào, toàn bộ NOT IN trả về empty set!
SELECT * FROM orders
WHERE merchant_id NOT IN (SELECT id FROM inactive_merchants);
-- Nếu có 1 merchant NULL trong inactive_merchants → 0 rows trả về

-- NOT EXISTS — an toàn với NULL
SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM inactive_merchants im
    WHERE im.id = o.merchant_id
);

-- Page 1: OFFSET 0 LIMIT 20 — nhanh
SELECT * FROM orders ORDER BY created_at DESC OFFSET 0 LIMIT 20;

-- Page 500: OFFSET 9980 LIMIT 20 — PostgreSQL vẫn phải:
-- 1. Scan index từ đầu đến row 10000
-- 2. Discard 9980 rows
-- 3. Return 20 rows
SELECT * FROM orders ORDER BY created_at DESC OFFSET 9980 LIMIT 20;

OFFSET 0     OFFSET 9980
[READ] ──────────────────────────[DISCARD]──[RETURN]
^                                           ^
Start                                     Page 500

-- Lần đầu tiên — không cần cursor
SELECT id, created_at, total_amount
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Lần sau: dùng last row từ response trước làm cursor
-- last_created_at = '2026-06-01 10:30:00', last_id = 9876
SELECT id, created_at, total_amount
FROM orders
WHERE (created_at, id) < ('2026-06-01 10:30:00', 9876)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Index (created_at DESC, id DESC) làm cho query này O(log n) bất kể trang nào

// Spring Data implementation
public interface OrderRepository extends JpaRepository<Order, Long> {
    @Query("""
        SELECT o FROM Order o
        WHERE (o.createdAt < :cursorDate)
           OR (o.createdAt = :cursorDate AND o.id < :cursorId)
        ORDER BY o.createdAt DESC, o.id DESC
        """)
    List<Order> findNextPage(
        @Param("cursorDate") Instant cursorDate,
        @Param("cursorId") Long cursorId,
        Pageable pageable
    );
}

-- Anti-pattern: load tất cả rows vào application, aggregate trong Java
List<Order> allOrders = orderRepository.findByMerchantId(merchantId);
Map<String, Long> countByStatus = allOrders.stream()
    .collect(Collectors.groupingBy(Order::getStatus, Collectors.counting()));
// Load hàng triệu rows vào heap để count!

-- Better: Aggregate trong database
@Query("""
    SELECT o.status AS status, COUNT(o) AS count
    FROM Order o
    WHERE o.merchantId = :merchantId
    GROUP BY o.status
    """)
List<StatusCount> countByStatus(@Param("merchantId") Long merchantId);

public interface StatusCount {
    String getStatus();
    Long getCount();
}

-- Xếp hạng merchant theo revenue trong từng tháng
SELECT
    merchant_id,
    month,
    revenue,
    RANK() OVER (PARTITION BY month ORDER BY revenue DESC) AS rank_in_month,
    SUM(revenue) OVER (PARTITION BY merchant_id) AS total_revenue
FROM monthly_revenue;
-- Một query, không cần self-join hay subquery

Transaction bắt đầu
       │
       ▼
Persistence Context (Map<EntityKey, Object>)
       │
  ┌────┴─────┐
  │ Load     │──→ Check cache → Miss → Issue SELECT → Store in cache
  │ entity   │
  └────┬─────┘
       │
  ┌────┴─────┐
  │ Modify   │──→ Entity tracked, change recorded in "dirty checking"
  │ entity   │
  └────┬─────┘
       │
  ┌────┴─────┐
  │ Flush    │──→ Hibernate compares current state vs snapshot
  │ (commit) │    Issues UPDATE only for changed fields
  └──────────┘

// Problematic pattern: batch processing trong một transaction
@Transactional
public void processAllOrders() {
    List<Order> orders = orderRepository.findAll(); // load 100,000 orders
    for (Order order : orders) {
        order.setProcessed(true);
        // Sau 100,000 iterations, persistence context chứa 100,000 entities
        // Flush sẽ so sánh 100,000 states
    }
}

// Fix: clear persistence context định kỳ
@Transactional
public void processAllOrders() {
    int batchSize = 100;
    int offset = 0;
    while (true) {
        List<Order> batch = orderRepository.findAll(
            PageRequest.of(offset / batchSize, batchSize)
        ).getContent();
        if (batch.isEmpty()) break;

        for (Order order : batch) {
            order.setProcessed(true);
        }
        orderRepository.saveAll(batch);
        entityManager.flush();
        entityManager.clear(); // ← clear persistence context
        offset += batchSize;
    }
}

// Service A và Service B trong cùng request nhưng transaction khác nhau
@Service
public class ServiceA {
    @Transactional
    public Order getOrder(Long id) {
        return orderRepository.findById(id).get(); // Query 1
    }
}

@Service
public class ServiceB {
    @Transactional
    public Order getOrder(Long id) {
        return orderRepository.findById(id).get(); // Query 2 — không share cache với ServiceA!
    }
}

// Nếu cùng được gọi trong một flow: 2 SELECT queries

@Entity
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class ProductCategory {
    @Id private Long id;
    private String name;
    // Data thay đổi ít (vài lần/tuần), đọc nhiều (mọi product page)
}

// application.properties
spring.jpa.properties.hibernate.cache.use_second_level_cache=true
spring.jpa.properties.hibernate.cache.region.factory_class=
    org.hibernate.cache.jcache.JCacheRegionFactory
spring.jpa.properties.javax.cache.provider=
    org.ehcache.jsr107.EhcacheCachingProvider

@Entity
public class Order {
    @ManyToOne(fetch = FetchType.LAZY) // ✓ Default nên là LAZY
    private Merchant merchant;

    @OneToMany(fetch = FetchType.LAZY) // ✓
    private List<OrderItem> items;

    @ManyToOne(fetch = FetchType.EAGER) // ✗ Avoid EAGER trong @xToMany
    private OrderStatus currentStatus; // EAGER trên @ManyToOne ít nguy hiểm hơn
}

// Với EAGER:
Order order = orderRepository.findById(1L).get();
// Hibernate tự động join fetch merchant, items, subItems, v.v.
// Có thể tạo Cartesian product nếu nhiều EAGER collections

// Ví dụ worst case:
// Order với 50 items, mỗi item có 10 sub-items
// EAGER load cả hai collections:
// Result set: 50 × 10 = 500 rows để represent 1 order!

// Không có batching: 1000 inserts = 1000 round-trips
for (int i = 0; i < 1000; i++) {
    entityManager.persist(new Order(...));
}

// Với batching: 1000 inserts = 10 round-trips (batch size 100)

# application.properties
spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
# Bắt buộc nếu dùng IDENTITY generation strategy
# IDENTITY strategy disable batching! Dùng SEQUENCE thay thế
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true

// IDENTITY — PostgreSQL SERIAL, MySQL AUTO_INCREMENT
// Hibernate phải flush ngay sau persist để lấy generated ID
// → Không batch được!
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

// SEQUENCE — PostgreSQL sequences, Oracle sequences
// Hibernate có thể pre-allocate IDs, persist nhiều entity, batch flush
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "order_seq")
@SequenceGenerator(name = "order_seq", sequenceName = "order_id_seq",
                   allocationSize = 50) // Pre-allocate 50 IDs
private Long id;

// Fetch Join — explicit trong JPQL, bạn kiểm soát hoàn toàn
@Query("""
    SELECT DISTINCT o FROM Order o
    JOIN FETCH o.merchant m
    JOIN FETCH o.items i
    JOIN FETCH i.product p
    WHERE o.createdAt > :since
    """)
List<Order> findRecentOrdersWithDetails(@Param("since") Instant since);

// Entity Graph — declarative, reusable, repository-level
@NamedEntityGraph(
    name = "Order.withMerchantAndItems",
    attributeNodes = {
        @NamedAttributeNode("merchant"),
        @NamedAttributeNode(value = "items",
                           subgraph = "items.product")
    },
    subgraphs = @NamedSubgraph(
        name = "items.product",
        attributeNodes = @NamedAttributeNode("product")
    )
)
@Entity
public class Order { ... }

// Repository
@EntityGraph("Order.withMerchantAndItems")
List<Order> findByCreatedAtAfter(Instant since);

// Lỗi khi fetch > 1 collection bằng JOIN FETCH:
@Query("SELECT o FROM Order o JOIN FETCH o.items JOIN FETCH o.tags")
// → MultipleBagFetchException: cannot simultaneously fetch multiple bags

// Fix 1: Đổi List thành Set (order không quan trọng)
@OneToMany
private Set<OrderTag> tags; // Thay vì List

// Fix 2: Dùng 2 query riêng biệt với Hibernate batch loading

pool_size = (core_count * 2) + effective_spindle_count

pool_size = (4 * 2) + 1 = 9

# application.yml
spring:
  datasource:
    hikari:
      maximum-pool-size: 10        # Tối đa connections trong pool
      minimum-idle: 5              # Minimum connections duy trì khi idle
      connection-timeout: 30000    # 30s timeout khi acquire connection
      idle-timeout: 600000         # 10 phút — close idle connection
      max-lifetime: 1800000        # 30 phút — recycle connection (tránh firewall timeout)
      keepalive-time: 60000        # 1 phút ping để giữ connection alive
      validation-timeout: 5000     # 5s timeout khi validate connection
      leak-detection-threshold: 60000  # 60s — log warning nếu connection không release

// Leak detection phát hiện code quên close connection
// Log output khi threshold vượt:
// WARN  c.z.h.pool.ProxyLeakTask - Connection leak detection triggered for
//       com.zaxxer.hikari.pool.ProxyConnection@1a2b3c4d on thread http-nio-8080-exec-1,
//       stack trace follows
// java.lang.Exception: Apparent connection leak detected
//         at com.example.OrderService.processOrder(OrderService.java:45)

// Expose HikariCP metrics với Micrometer
@Bean
MeterRegistryCustomizer<MeterRegistry> hikariMetrics(DataSource dataSource) {
    return registry -> {
        if (dataSource instanceof HikariDataSource hikariDS) {
            hikariDS.setMetricRegistry(registry);
        }
    };
}
// Metrics:
// hikaricp.connections.active  — connections đang được dùng
// hikaricp.connections.pending — requests đang chờ connection
// hikaricp.connections.timeout — connection acquisition timeouts

// Anti-pattern: transaction quá rộng
@Transactional
public OrderResponse processOrder(OrderRequest request) {
    validatePayment(request); // Gọi external payment API — 200ms
    Order order = createOrder(request); // DB operation
    sendNotificationEmail(request); // Gọi email service — 100ms
    updateInventory(request); // DB operation
    return buildResponse(order);
}
// Transaction hold connection trong suốt 300ms+ gọi external services
// Database lock held trong 300ms

// Better: tách external calls ra ngoài transaction
public OrderResponse processOrder(OrderRequest request) {
    PaymentResult payment = validatePayment(request); // Không cần transaction
    OrderResponse response = createAndUpdateTransactionally(request, payment); // Transaction nhỏ
    sendNotificationEmail(request); // Không cần transaction
    return response;
}

@Transactional
private OrderResponse createAndUpdateTransactionally(OrderRequest request,
                                                      PaymentResult payment) {
    Order order = createOrder(request);
    updateInventory(request);
    return buildResponse(order);
}

// @Transactional(readOnly = true) làm gì?
// 1. Hibernate disable dirty checking — tiết kiệm CPU
// 2. Hibernate không snapshot entity state — tiết kiệm memory
// 3. JDBC driver có thể optimize (routing tới read replica)
// 4. Database có thể optimize (skip write lock overhead)

@Transactional(readOnly = true)
public List<OrderSummary> getOrderSummaries(Long merchantId) {
    return orderRepository.findSummariesByMerchantId(merchantId);
}

// Spring Boot — custom routing datasource
@Configuration
public class DataSourceConfig {
    @Bean
    @Primary
    public DataSource routingDataSource(
            @Qualifier("primary") DataSource primary,
            @Qualifier("replica") DataSource replica) {
        TransactionRoutingDataSource routing = new TransactionRoutingDataSource();
        routing.setTargetDataSources(Map.of(
            "primary", primary,
            "replica", replica
        ));
        routing.setDefaultTargetDataSource(primary);
        return routing;
    }
}

public class TransactionRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
            ? "replica"
            : "primary";
    }
}

// Quarkus — Reactive với multiple datasources
@ApplicationScoped
public class OrderService {
    @Inject
    @ReactiveDataSource("primary")
    PgPool primaryPool;

    @Inject
    @ReactiveDataSource("replica")
    PgPool replicaPool;

    public Uni<List<Order>> findOrders(Long merchantId) {
        return replicaPool.preparedQuery(
            "SELECT * FROM orders WHERE merchant_id = $1"
        ).execute(Tuple.of(merchantId))
         .map(rows -> mapToOrders(rows));
    }
}

Request vào
     │
     ▼
[Open Session]
     │
     ▼
Service Layer (Transaction mở và đóng)
     │
     ▼
[Transaction closed, nhưng Session vẫn mở]
     │
     ▼
View/Controller (Serialize response)
     │ ← Lazy loading vẫn hoạt động ở đây (session còn mở)
     │ ← Nhưng: không có transaction → mỗi lazy load = new DB call
     ▼
[Close Session]

# Disable OSIV (Spring Boot 2+ default: true, nên set false)
spring.jpa.open-in-view=false

// Active Record (Panache Entity) — tốt cho simple CRUD
@Entity
public class Order extends PanacheEntity {
    public String status;
    public BigDecimal totalAmount;
    public Long merchantId;

    // Queries là static methods trên entity
    public static List<Order> findByMerchant(Long merchantId) {
        return find("merchantId = ?1 and status = 'PENDING'", merchantId).list();
    }

    public static PanacheQuery<Order> streamAll() {
        return findAll();
    }
}

// Usage
List<Order> orders = Order.findByMerchant(merchantId);

// Repository pattern — tốt hơn cho complex domain logic, testability
@ApplicationScoped
public class OrderRepository implements PanacheRepository<Order> {
    public List<Order> findPendingByMerchant(Long merchantId, int limit) {
        return find("merchantId = ?1 and status = 'PENDING' order by createdAt",
                    merchantId)
               .page(0, limit)
               .list();
    }
}

Primary DB ──WAL stream──→ Replica 1
           ──WAL stream──→ Replica 2

Application:
- Writes → Primary
- Reads  → Replica 1 hoặc 2 (load balanced)

// Scenario: User update profile → ngay lập tức redirect to profile page
// Nếu read từ replica với 100ms lag → user thấy data cũ

// Pattern: "Read your own writes" — sau write, đọc từ primary
@Service
public class UserService {
    @Transactional
    public User updateProfile(Long userId, UpdateRequest request) {
        User user = userRepository.findById(userId).get();
        user.apply(request);
        return userRepository.save(user);
        // Return từ primary context — data mới nhất
        // Không redirect sang endpoint khác ngay lập tức
    }
}

// Hoặc: session-level primary routing sau write
public class PrimaryAfterWriteFilter implements Filter {
    @Override
    public void doFilter(ServletRequest request, ...) {
        if (userJustWrote(request)) {
            routingContext.setForceWriteDataSource(true);
        }
        chain.doFilter(request, response);
    }
}

┌─────────────────┐         ┌─────────────────┐
│  Command Model  │         │   Query Model   │
│  (Write Side)   │         │   (Read Side)   │
│                 │         │                 │
│  - Order        │──event──│  - OrderView    │
│  - OrderItem    │──────→  │  - MerchantStat │
│  - Payment      │         │  - DailyReport  │
│  (normalized)   │         │  (denormalized) │
└─────────────────┘         └─────────────────┘
        │                           │
   PostgreSQL                 PostgreSQL (read replica)
   (primary)                  hoặc Elasticsearch
                              hoặc Redis

// Command side — domain-driven, normalized
@Service
public class OrderCommandService {
    @Transactional
    public OrderId placeOrder(PlaceOrderCommand command) {
        Order order = Order.place(command); // Domain logic
        orderRepository.save(order);
        eventPublisher.publish(new OrderPlacedEvent(order));
        return order.getId();
    }
}

// Query side — optimized read model, flat denormalized structure
@Service
public class OrderQueryService {
    public OrderView getOrderView(Long orderId) {
        return orderViewRepository.findById(orderId)
               .orElseThrow(); // Pre-built, denormalized view
    }
}

// Event handler updates read model
@Component
public class OrderViewUpdater {
    @EventListener
    @Transactional
    public void handle(OrderPlacedEvent event) {
        OrderView view = OrderView.builder()
            .orderId(event.getOrderId())
            .merchantName(merchantRepository.findNameById(event.getMerchantId()))
            .totalAmount(event.getTotalAmount())
            // ... denormalized fields
            .build();
        orderViewRepository.save(view);
    }
}

-- Range partitioning theo created_at
CREATE TABLE orders (
    id BIGINT,
    merchant_id BIGINT,
    status VARCHAR(20),
    created_at TIMESTAMP WITH TIME ZONE,
    total_amount DECIMAL(10,2)
) PARTITION BY RANGE (created_at);

-- Tạo partition mỗi tháng
CREATE TABLE orders_2026_01 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE orders_2026_02 PARTITION OF orders
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Index tự động apply cho tất cả partitions
CREATE INDEX ON orders (merchant_id, created_at DESC);

-- Query chỉ scan partition liên quan (partition pruning)
EXPLAIN SELECT * FROM orders
WHERE created_at BETWEEN '2026-06-01' AND '2026-06-30';
-- → Chỉ scan orders_2026_06, không phải toàn bộ table

-- Tạo partition tự động với pg_partman extension
SELECT partman.create_parent(
    p_parent_table => 'public.orders',
    p_control => 'created_at',
    p_type => 'range',
    p_interval => 'monthly',
    p_premake => 3 -- Tạo sẵn 3 tháng tới
);

-- View tính toán daily merchant stats — query nặng nếu compute realtime
CREATE MATERIALIZED VIEW merchant_daily_stats AS
SELECT
    merchant_id,
    DATE(created_at) AS date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE status = 'COMPLETED'
GROUP BY merchant_id, DATE(created_at);

CREATE UNIQUE INDEX ON merchant_daily_stats (merchant_id, date);

-- Refresh: concurrent refresh không block reads
REFRESH MATERIALIZED VIEW CONCURRENTLY merchant_daily_stats;

-- Schedule refresh mỗi giờ (pg_cron extension)
SELECT cron.schedule('0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY merchant_daily_stats');

// Đọc từ materialized view trong Spring Data
@Repository
public interface MerchantDailyStatsRepository extends JpaRepository<MerchantDailyStats, MerchantDateKey> {
    List<MerchantDailyStats> findByMerchantIdAndDateBetween(
        Long merchantId, LocalDate start, LocalDate end);
}

@Entity
@Table(name = "merchant_daily_stats")
@Immutable // Hibernate không track dirty changes
public class MerchantDailyStats { ... }

@Configuration
@EnableCaching
public class CacheConfig {
    @Bean
    public RedisCacheManager cacheManager(RedisConnectionFactory factory) {
        RedisCacheConfiguration defaultConfig = RedisCacheConfiguration
            .defaultCacheConfig()
            .entryTtl(Duration.ofMinutes(10))
            .serializeValuesWith(
                RedisSerializationContext.SerializationPair.fromSerializer(
                    new GenericJackson2JsonRedisSerializer()
                )
            );

        Map<String, RedisCacheConfiguration> cacheConfigs = Map.of(
            "merchants", defaultConfig.entryTtl(Duration.ofHours(1)),
            "categories", defaultConfig.entryTtl(Duration.ofDays(1)),
            "orderViews", defaultConfig.entryTtl(Duration.ofMinutes(5))
        );

        return RedisCacheManager.builder(factory)
            .cacheDefaults(defaultConfig)
            .withInitialCacheConfigurations(cacheConfigs)
            .build();
    }
}

@Service
public class MerchantService {
    @Cacheable(value = "merchants", key = "#merchantId")
    public Merchant getMerchant(Long merchantId) {
        return merchantRepository.findById(merchantId).orElseThrow();
    }

    @CacheEvict(value = "merchants", key = "#merchant.id")
    @Transactional
    public Merchant updateMerchant(Merchant merchant) {
        return merchantRepository.save(merchant);
    }
}

// Probabilistic early expiration (PER) / jittered TTL
public Merchant getMerchantWithJitter(Long merchantId) {
    // TTL = base + random(0, jitter)
    Duration jitter = Duration.ofSeconds(ThreadLocalRandom.current().nextInt(0, 300));
    Duration ttl = Duration.ofMinutes(10).plus(jitter);
    // ...
}

-- PostgreSQL: bật slow query log
-- postgresql.conf
log_min_duration_statement = 100  -- Log query > 100ms
log_statement = 'none'            -- Chỉ log slow queries, không phải tất cả
log_duration = off
auto_explain.log_min_duration = 1000  -- Auto-explain query > 1s
auto_explain.log_analyze = true
auto_explain.log_buffers = true

# Phân tích slow query log với pgBadger
pgbadger /var/log/postgresql/postgresql.log -o report.html
# Report: top slow queries, most frequent queries, queries by wait time

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, m.name
FROM orders o
JOIN merchants m ON o.merchant_id = m.id
WHERE o.status = 'PENDING'
  AND o.created_at > NOW() - INTERVAL '24 hours'
ORDER BY o.created_at DESC
LIMIT 100;

Limit  (cost=0.56..487.23 rows=100 width=32) (actual time=0.823..12.456 rows=100 loops=1)
  ->  Nested Loop  (cost=0.56..48723.12 rows=10000 width=32) (actual time=0.821..12.201 rows=100 loops=1)
        ->  Index Scan Backward using idx_orders_status_created on orders o
              (cost=0.43..38520.10 rows=10000 width=24)
              (actual time=0.456..8.123 rows=100 loops=1)
              Index Cond: ((status = 'PENDING') AND (created_at > (now() - '1 day'::interval)))
              Buffers: shared hit=203 read=12  ← 12 blocks từ disk, 203 từ cache
        ->  Index Scan using merchants_pkey on merchants m
              (cost=0.13..1.00 rows=1 width=8)
              (actual time=0.036..0.036 rows=1 loops=100)
              Buffers: shared hit=300
Planning Time: 2.567 ms
Execution Time: 12.890 ms

// application.properties
spring.jpa.properties.hibernate.generate_statistics=true

// Programmatic access
@Autowired
private EntityManagerFactory emf;

public void printStats() {
    Statistics stats = emf.unwrap(SessionFactory.class).getStatistics();
    log.info("Query count: {}", stats.getQueryExecutionCount());
    log.info("Entity loads: {}", stats.getEntityLoadCount());
    log.info("Collections loaded: {}", stats.getCollectionLoadCount());
    log.info("Second-level cache hits: {}", stats.getSecondLevelCacheHitCount());
    log.info("Second-level cache misses: {}", stats.getSecondLevelCacheMissCount());
}

// Detect N+1 với DataSource proxy
@Bean
public DataSource dataSource(DataSourceProperties properties) {
    HikariDataSource hikariDS = properties.initializeDataSourceBuilder()
        .type(HikariDataSource.class).build();

    return ProxyDataSourceBuilder.create(hikariDS)
        .logSlowQueryBySlf4j(100, TimeUnit.MILLISECONDS)
        .countQuery()
        .build();
}

// Prometheus metrics với Micrometer
// Thêm dependency: micrometer-registry-prometheus

// application.properties
management.endpoints.web.exposure.include=health,info,metrics,prometheus
management.metrics.tags.application=${spring.application.name}

// Metrics tự động expose:
// hikaricp_connections_active  — gauge
// hikaricp_connections_idle    — gauge
// hikaricp_connections_pending — gauge
// hikaricp_connections_timeout_total — counter (acquisition timeout)
// hikaricp_connections_creation_seconds — histogram (time to create connection)

# Alert: Connection pool gần đầy
- alert: HikariConnectionPoolSaturation
  expr: hikaricp_connections_pending > 2
  for: 30s
  labels:
    severity: warning
  annotations:
    summary: "HikariCP pool has {{ $value }} pending connections"

# Alert: Connection acquisition timeout tăng
- alert: HikariConnectionTimeout
  expr: rate(hikaricp_connections_timeout_total[5m]) > 0
  for: 1m
  labels:
    severity: critical

-- Xem locks hiện tại
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY duration DESC;

-- Xem lock dependencies (ai đang block ai)
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocked.wait_event
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

2026-06-07 10:30:15 UTC ERROR: deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678;
         blocked by process 5678.
         Process 5678 waits for ShareLock on transaction 1234;
         blocked by process 1234.
HINT:  See server log for query details.

// Consistent lock ordering — luôn lock theo cùng thứ tự
// Bad: Transaction A lock order_1 rồi order_2
//       Transaction B lock order_2 rồi order_1 → deadlock potential
@Transactional
public void transferBalance(Long fromId, Long toId, BigDecimal amount) {
    // Sort IDs để đảm bảo consistent lock order
    Long firstId = Math.min(fromId, toId);
    Long secondId = Math.max(fromId, toId);

    Account first = accountRepository.findByIdWithLock(firstId);  // SELECT FOR UPDATE
    Account second = accountRepository.findByIdWithLock(secondId);

    // Tiếp tục với logic...
}

// SELECT FOR UPDATE trong Spring Data JPA
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("SELECT a FROM Account a WHERE a.id = :id")
Account findByIdWithLock(@Param("id") Long id);

-- 1. Tìm query tốn CPU nhiều nhất (cần pg_stat_statements extension)
SELECT
    query,
    calls,
    total_exec_time / calls AS avg_time_ms,
    rows / calls AS avg_rows,
    100.0 * total_exec_time / sum(total_exec_time) OVER () AS cpu_percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 2. Tìm sequential scans lớn
SELECT
    relname AS table,
    seq_scan AS sequential_scans,
    seq_tup_read AS rows_read_sequentially,
    idx_scan AS index_scans,
    n_live_tup AS live_rows
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC;

-- 3. Reset statistics sau fix để verify improvement
SELECT pg_stat_reset();
SELECT pg_stat_statements_reset();

// Track query performance metrics với custom Hibernate interceptor
public class SlowQueryInterceptor implements StatementInspector {
    private final MeterRegistry meterRegistry;

    @Override
    public String inspect(String sql) {
        // Instrument tất cả queries
        return sql;
    }
}

// Hoặc dùng datasource proxy để log execution time mỗi query
// Sau đó alert khi P99 latency của specific query tăng

□ Không có SELECT * trong production code
□ Tất cả WHERE clauses được test với EXPLAIN ANALYZE
□ Không có N+1 query (verify với Hibernate statistics hoặc query counter)
□ Pagination dùng keyset (cursor-based) cho data > 10k rows
□ Aggregations xảy ra ở database, không phải application
□ JOINs không fetch data không cần thiết
□ NOT IN được replace bằng NOT EXISTS hoặc LEFT JOIN IS NULL
□ Queries được test với production data volume (không phải dev data 100 rows)
□ Query timeout được set (statement_timeout trong PostgreSQL)

-- Set query timeout theo session (Spring Boot)
@Bean
public DataSource dataSource() {
    HikariConfig config = new HikariConfig();
    config.setConnectionInitSql("SET statement_timeout = '30s'");
    return new HikariDataSource(config);
}

□ Mọi foreign key có index (PostgreSQL không tự tạo)
□ Columns thường xuyên trong WHERE, JOIN ON, ORDER BY có index
□ Composite indexes được sắp xếp đúng (equality trước, range sau)
□ Covering indexes cho top 10 hot queries
□ Unused indexes được identify và scheduled để drop
□ Index bloat được monitor (sau heavy UPDATE workload)
□ Partial indexes cho low-cardinality columns với skewed distribution

-- Kiểm tra foreign keys không có index (PostgreSQL)
SELECT
    conrelid::regclass AS table,
    conname AS constraint,
    array_to_string(conkey, ', ') AS columns
FROM pg_constraint
WHERE contype = 'f'
  AND conrelid::regclass::text NOT IN (
      SELECT tablename FROM pg_indexes WHERE tablename = conrelid::regclass::text
  );

-- Index bloat
SELECT
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

□ Tất cả @OneToMany và @ManyToMany là FetchType.LAZY
□ Không có MultipleBagFetchException workarounds bằng EAGER
□ JDBC batching được enable (hibernate.jdbc.batch_size)
□ SEQUENCE generation strategy thay vì IDENTITY
□ spring.jpa.open-in-view=false
□ @Transactional(readOnly=true) cho tất cả read-only service methods
□ Second-level cache được cấu hình cho stable reference data
□ Entity không load @Lob fields khi không cần
□ Persistence context được clear trong batch processing jobs

□ Pool size được tính đúng (công thức HikariCP) × số instances
□ max-lifetime < PostgreSQL idle_in_transaction_session_timeout
□ connection-timeout phù hợp với SLA
□ leak-detection-threshold được set
□ Pool metrics được expose và monitored
□ Alert được set cho pending connections > threshold
□ PgBouncer hoặc connection pooler ở database level nếu nhiều app instances

□ Schema migration (Flyway/Liquibase) test trên production data clone
□ Index creation dùng CONCURRENTLY (không block)
□ ALTER TABLE dùng phương pháp không lock khi có thể
□ ANALYZE chạy sau bulk data changes
□ Slow query log được enable ở production
□ Backup và rollback plan cho migration
□ Connection pool drain trước rolling restart

-- Safe index creation — không block production traffic
CREATE INDEX CONCURRENTLY idx_orders_merchant_status
ON orders (merchant_id, status)
WHERE status IN ('PENDING', 'PROCESSING');

-- Safe column addition (không lock nếu có default)
-- PostgreSQL 11+: Adding column with non-volatile default không rewrite table
ALTER TABLE orders ADD COLUMN processed_at TIMESTAMP;
-- Sau đó set default riêng
ALTER TABLE orders ALTER COLUMN processed_at SET DEFAULT NOW();

-- Query:
WHERE created_at > '2026-01-01' AND merchant_id = 456 AND status = 'COMPLETED'

-- Index hiện có: (created_at, merchant_id, status)
-- created_at là range condition → sau range condition, phần còn lại của index không được dùng
-- Optimizer chọn sequential scan vì index không selective đủ