Atasi Slow Query JPA: Optimasi Covering Index dan DTO Projection

Mengambil entity penuh pada tabel bervolume jutaan baris adalah penyebab paling umum degradasi performa pada aplikasi Spring Data JPA. Saat JPA memuat entity lengkap, seluruh kolom diambil dari disk ke memori, didaftarkan ke Hibernate Persistence Context (L1 cache), dan dipantau untuk proses dirty checking. Beban I/O database melonjak akibat pembacaan heap table, sementara alokasi memori JVM terbebani oleh lifecycle management entity yang sebenarnya tidak dimutasi.

Solusi deterministik untuk masalah ini memerlukan dua langkah simultan: memangkas lifecycle entity di sisi aplikasi menggunakan DTO projection berbasis Java Record, dan mengeliminasi akses fisik ke heap table di sisi database menggunakan covering index.

Akar Masalah: Overhead Entity Lifecycle dan Heap Table Access

Saat repository memanggil query seperti List<Order> findByCustomerIdAndStatus(...), Hibernate mengeksekusi query SELECT *. Pendekatan ini menimbulkan dua bottleneck utama:

Overhead Aplikasi: JPA membungkus setiap record ke dalam instance entity terkelola. Hibernate menyimpan snapshot data untuk dirty checking, melipatgandakan konsumsi heap JVM, dan memicu garbage collection (GC) pause berulang pada volume data besar.
Overhead Database: Database harus membaca indeks terlebih dahulu untuk menemukan Tuple ID (TID/pointer baris), lalu membaca halaman tabel fisik (heap pages) untuk mengambil sisa kolom yang diminta. Jika kolom bertipe teks panjang atau JSONB terlibat, database terpaksa membaca blok TOAST (The Oversized-Attribute Storage Technique), yang memperlambat waktu respons hingga ratusan milidetik.

Langkah 1: Profiling Query dengan Hibernate Statistics dan EXPLAIN ANALYZE

Sebelum melakukan optimasi, ukur konsumsi waktu query di sisi aplikasi dan database menggunakan konfigurasi profiling bawaan.

Konfigurasi Hibernate Statistics

Aktifkan metrik eksekusi pada file application.yml untuk memantau durasi eksekusi JDBC batch dan alokasi entity:

spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true
        format_sql: false
logging:
  level:
    org.hibernate.stat: DEBUG
    org.hibernate.SQL: DEBUG

Statistik ini menampilkan metrik detail di log aplikasi, mencakup total query yang dijalankan, waktu koneksi JDBC, serta jumlah entity yang dimuat ke Persistence Context.

Inspeksi Baseline Database Plan

Jalankan EXPLAIN (ANALYZE, BUFFERS) pada query baseline di PostgreSQL:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders 
WHERE customer_id = 'c0a80123-8c90-11ee-b9d1-0242ac120002' 
  AND status = 'COMPLETED';

Output tipikal query baseline tanpa optimasi:

Bitmap Heap Scan on orders  (cost=12.45..1284.10 rows=450 width=218) (actual time=0.412..8.910 rows=450 loops=1)
  Recheck Cond: ((customer_id = 'c0a80123-8c90-11ee-b9d1-0242ac120002'::uuid) AND (status = 'COMPLETED'::text))
  Buffers: shared hit=42 read=312
  ->  Bitmap Index Scan on idx_orders_customer_status  (cost=0.00..12.34 rows=450 width=0) (actual time=0.089..0.089 rows=450 loops=1)
        Index Cond: ((customer_id = 'c0a80123-8c90-11ee-b9d1-0242ac120002'::uuid) AND (status = 'COMPLETED'::text))
        Buffers: shared hit=4
Planning Time: 0.184 ms
Execution Time: 9.120 ms

Perhatikan parameter Buffers: shared hit=42 read=312 pada Bitmap Heap Scan. Database terpaksa mengambil 354 block dari memory buffer dan disk hanya untuk membaca kolom-kolom yang ada di heap table.

Langkah 2: Memangkas Overhead Memori via DTO Record Projection

Gunakan Java Record untuk mendefinisikan read-only projection. Objek record tidak dikelola oleh Persistence Context, sehingga Hibernate memproses hasilnya secara langsung sebagai raw object tanpa proxy, snapshot dirty checking, atau pemantauan identitas.

Definisi Entity JPA dan DTO Record

package com.example.order.entity;

import jakarta.persistence.*;
import java.math.BigDecimal;
import java.time.Instant;
import java.util.UUID;

@Entity
@Table(name = "orders")
public class OrderEntity {
    @Id
    private UUID id;
    
    @Column(name = "customer_id", nullable = false)
    private UUID customerId;
    
    @Column(name = "status", nullable = false)
    private String status;
    
    @Column(name = "total_amount", nullable = false)
    private BigDecimal totalAmount;
    
    @Column(name = "notes")
    private String notes;
    
    @Column(name = "metadata", columnDefinition = "jsonb")
    private String metadata;
    
    @Column(name = "created_at", nullable = false)
    private Instant createdAt;

    // Getters and Setters diabaikan untuk keringkasan
}

package com.example.order.dto;

import java.math.BigDecimal;
import java.time.Instant;
import java.util.UUID;

public record OrderSummaryResponse(
    UUID id,
    UUID customerId,
    String status,
    BigDecimal totalAmount,
    Instant createdAt
) {}

Repository dengan Constructor Expression

Gunakan JPQL constructor expression untuk memetakan hasil query langsung ke Record. Hindari entity-level query jika operasi yang dilakukan adalah read-only:

package com.example.order.repository;

import com.example.order.dto.OrderSummaryResponse;
import com.example.order.entity.OrderEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
import java.util.UUID;

public interface OrderRepository extends JpaRepository<OrderEntity, UUID> {

    @Query("""
        SELECT new com.example.order.dto.OrderSummaryResponse(
            o.id,
            o.customerId,
            o.status,
            o.totalAmount,
            o.createdAt
        )
        FROM OrderEntity o
        WHERE o.customerId = :customerId 
          AND o.status = :status
        """)
    List<OrderSummaryResponse> findSummariesByCustomerAndStatus(
        @Param("customerId") UUID customerId,
        @Param("status") String status
    );
}

Langkah 3: Menghilangkan Table Heap Access dengan Covering Index

Membatasi kolom pada tingkat query JPQL saja tidak cukup. Database tetap harus mengunjungi heap table untuk mengambil nilai id, total_amount, dan created_at jika indeks yang tersedia hanya mencakup customer_id dan status.

Covering index menyelesaikan masalah ini dengan menyematkan seluruh kolom yang dibutuhkan oleh klausa SELECT ke dalam struktur daun (leaf nodes) dari B-Tree index.

DDL PostgreSQL Menggunakan Klausa INCLUDE

Klausa INCLUDE memasukkan payload data ke leaf nodes indeks tanpa menambahkannya ke kunci pencarian pohon B-Tree, menjaga ukuran B-Tree root/branch tetap kompak:

CREATE INDEX idx_orders_customer_status_covering 
ON orders (customer_id, status) 
INCLUDE (id, total_amount, created_at);

Catatan: Tempatkan kolom yang digunakan dalam filter klausa WHERE pada bagian primary index keys (customer_id, status). Letakkan kolom yang hanya dibutuhkan untuk output SELECT pada klausa INCLUDE.

Evaluasi Execution Plan: Heap Scan vs. Index Only Scan

Setelah covering index diterapkan dan query JPQL dipetakan ke DTO, periksa ulang execution plan di PostgreSQL:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, status, total_amount, created_at 
FROM orders 
WHERE customer_id = 'c0a80123-8c90-11ee-b9d1-0242ac120002' 
  AND status = 'COMPLETED';

Output query plan setelah optimasi:

Index Only Scan using idx_orders_customer_status_covering on orders  (cost=0.42..15.60 rows=450 width=56) (actual time=0.034..0.122 rows=450 loops=1)
  Index Cond: ((customer_id = 'c0a80123-8c90-11ee-b9d1-0242ac120002'::uuid) AND (status = 'COMPLETED'::text))
  Heap Fetches: 0
  Buffers: shared hit=5
Planning Time: 0.115 ms
Execution Time: 0.155 ms

Analisis Metrik

Execution Time: Turun dari 9.120 ms menjadi 0.155 ms (peningkatan performa lebih dari 50x).
Shared Buffers: Berkurang dari 354 buffers menjadi hanya 5 buffers. Database tidak perlu memuat blok heap tabel ke buffer pool.
Heap Fetches: 0: Semua data berhasil dipenuhi langsung dari memory index page tanpa menyentuh disk fisik tabel.

Batasan, Trade-off, dan Pertimbangan Teknis

Penerapan DTO projection dan covering index membutuhkan evaluasi sistemik pada alur aplikasi:

Write Amplification: Setiap penambahan kolom pada klausul INCLUDE memperbesar ukuran fisik indeks. Operasi INSERT, UPDATE, dan DELETE akan mengalami sedikit penurunan throughput karena index pages harus diperbarui secara sinkron.
Visibility Map dan VACUUM: PostgreSQL memerlukan Visibility Map yang bersih untuk menjalankan Index Only Scan tanpa heap fetch. Jika tabel mengalami mutasi tinggi dan proses AUTOVACUUM terlambat berjalan, Heap Fetches akan meningkat karena engine perlu memeriksa MVCC visibility di heap page. Pastikan parameter autovacuum_vacuum_scale_factor disesuaikan pada tabel transaksional tinggi.
Immutability: DTO projection bersifat disconnected. Jika data yang diambil perlu diubah lalu disimpan kembali, entity lifecycle reguler atau query @Modifying eksplisit tetap harus digunakan.

Quick Search

shopping_cart Keranjang