Spring Boot: Offset Pagination Makin Lambat? Bedah Query dan Index

Jika endpoint list di Spring Boot terasa cepat di halaman 1 tetapi mulai lambat di halaman 100, 500, atau 2000, penyebabnya sering bukan di Java atau network, melainkan di pola query SQL yang dihasilkan oleh offset pagination. Masalah utamanya sederhana: semakin besar OFFSET, semakin banyak baris yang harus dilewati database sebelum mengirim LIMIT baris yang benar-benar dibutuhkan.

Pada PostgreSQL maupun MySQL, gejala ini makin jelas saat endpoint memakai kombinasi filter + sorting + pagination. Tanpa index yang tepat, database bisa melakukan scan besar, sort mahal, lalu membuang ribuan sampai jutaan baris hanya untuk menampilkan 20 data. Di artikel ini, kita fokus ke studi kasus nyata di Spring Boot: bagaimana query JPA terbentuk, cara membaca EXPLAIN/EXPLAIN ANALYZE, efek OFFSET besar, peran index komposit, kapan offset masih layak dipakai, dan kapan lebih aman beralih ke cursor/keyset pagination.

Studi kasus: endpoint list dengan filter dan sorting

Misalkan kita punya endpoint daftar order untuk dashboard admin:

GET /api/orders?status=PAID&customerId=42&sort=createdAt,desc&page=250&size=20

Secara umum, developer Spring Boot akan memakai Pageable dari Spring Data JPA karena praktis. Contoh entitas:

@Entity
@Table(name = "orders")
public class OrderEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private Long customerId;

    @Enumerated(EnumType.STRING)
    @Column(nullable = false, length = 20)
    private OrderStatus status;

    @Column(nullable = false)
    private BigDecimal totalAmount;

    @Column(nullable = false)
    private Instant createdAt;

    // getter/setter
}

Repository JPA-nya bisa seperti ini:

public interface OrderRepository extends JpaRepository<OrderEntity, Long> {

    Page<OrderEntity> findByCustomerIdAndStatus(
            Long customerId,
            OrderStatus status,
            Pageable pageable
    );
}

Contoh pemakaian di service:

Pageable pageable = PageRequest.of(page, size, Sort.by(Sort.Direction.DESC, "createdAt"));
Page<OrderEntity> result = orderRepository.findByCustomerIdAndStatus(customerId, status, pageable);

Secara konsep ini benar. Tetapi SQL yang dihasilkan umumnya kurang lebih seperti berikut:

select o.id, o.customer_id, o.status, o.total_amount, o.created_at
from orders o
where o.customer_id = ?
  and o.status = ?
order by o.created_at desc
limit ? offset ?;

Dan jika Anda mengembalikan Page<T>, Spring Data biasanya juga perlu menjalankan count query:

select count(*)
from orders o
where o.customer_id = ?
  and o.status = ?;

Dua query ini sama-sama penting dalam diagnosis. Sering kali bottleneck bukan hanya query data, tetapi juga query count(*).

Kenapa offset pagination makin lambat saat data tumbuh

Apa yang sebenarnya dilakukan database

LIMIT 20 OFFSET 5000 tidak berarti database bisa langsung melompat ke baris ke-5001 secara gratis. Untuk menghasilkan urutan yang benar, database tetap perlu:

mencari baris yang cocok dengan filter,
mengurutkan sesuai ORDER BY jika index tidak mendukung urutan itu,
melewati 5000 baris pertama,
baru mengembalikan 20 baris berikutnya.

Artinya, biaya query tumbuh seiring bertambahnya offset. Hal ini sangat terasa bila:

filter tidak selektif,
sorting dilakukan pada kolom yang tidak cocok dengan index,
baris yang dibaca besar jumlahnya,
query harus menyentuh tabel utama berkali-kali,
hasil harus disortir terlebih dahulu dalam memori atau disk.

Gejala umum di production

Halaman awal cepat, halaman jauh makin lambat.
Endpoint list spike saat user membuka page besar atau export bertahap.
CPU database naik saat traffic naik, walau query terlihat sederhana.
Slow query log dipenuhi query LIMIT ... OFFSET ....
P95/P99 latency endpoint jauh lebih buruk daripada rata-rata.
Count query ikut mahal pada filter tertentu.

Jika endpoint list lambat hanya pada page besar, itu petunjuk kuat bahwa masalah ada pada offset pagination atau index yang tidak mendukung filter dan sort.

Bedah query Spring Boot dengan EXPLAIN dan EXPLAIN ANALYZE

Query yang perlu diperiksa

Jangan menebak. Ambil SQL nyata yang dijalankan aplikasi, termasuk parameter aktual. Cara praktis:

aktifkan log SQL Hibernate/JPA di environment non-production,
pakai APM atau JDBC proxy untuk menangkap query dan durasi,
cek slow query log di MySQL atau extension/statistik query di PostgreSQL,
uji query dengan nilai parameter yang sama seperti di production.

Contoh query nyata:

select o.id, o.customer_id, o.status, o.total_amount, o.created_at
from orders o
where o.customer_id = 42
  and o.status = 'PAID'
order by o.created_at desc
limit 20 offset 5000;

Membaca EXPLAIN secara praktis

Di PostgreSQL:

EXPLAIN ANALYZE
select o.id, o.customer_id, o.status, o.total_amount, o.created_at
from orders o
where o.customer_id = 42
  and o.status = 'PAID'
order by o.created_at desc
limit 20 offset 5000;

Di MySQL:

EXPLAIN ANALYZE
select o.id, o.customer_id, o.status, o.total_amount, o.created_at
from orders o
where o.customer_id = 42
  and o.status = 'PAID'
order by o.created_at desc
limit 20 offset 5000;

Yang perlu dicari dari output plan, bukan sekadar istilah vendor-specific, tetapi pola berikut:

Rows examined / actual rows jauh lebih besar daripada 20 baris yang diminta.
Sort muncul eksplisit dan biayanya besar.
Sequential scan / full scan / filesort terjadi pada tabel besar.
Index scan ada, tetapi tetap membaca banyak baris lalu membuang sebagian besar karena offset.
Count query juga membaca banyak baris karena filter tidak didukung index.

Interpretasi sederhananya:

Kalau plan menunjukkan scan besar lalu sort, index Anda belum cocok dengan filter + order.
Kalau plan sudah memakai index, tetapi tetap membaca ribuan baris untuk offset besar, berarti bottleneck-nya memang sifat alami offset pagination.

Kesalahan analisis yang sering terjadi

Hanya mengukur page 0 atau page 1.
Melihat query data, tetapi mengabaikan count query.
Mengira adanya index tunggal di tiap kolom otomatis cukup.
Mengoptimasi Java mapping padahal SQL plan jelas buruk.
Menguji dengan data sedikit sehingga planner terlihat baik-baik saja.

Peran index komposit: bukan sekadar “ada index”

Kenapa index tunggal sering tidak cukup

Misalnya Anda punya index terpisah pada customer_id, status, dan created_at. Itu belum tentu efektif untuk query:

where customer_id = ? and status = ?
order by created_at desc

Database mungkin masih perlu:

mengambil banyak candidate rows dari index filter, lalu
melakukan sort tambahan karena urutan di index tidak sesuai kebutuhan query.

Di sinilah index komposit jauh lebih berpengaruh.

Contoh index komposit yang relevan

Untuk query studi kasus, index yang umumnya lebih cocok adalah:

create index idx_orders_customer_status_created_at
on orders (customer_id, status, created_at desc);

Jika engine atau kebutuhan Anda tidak membedakan arah sort secara signifikan, inti utamanya tetap sama: susun index agar mendukung kolom filter dulu, lalu kolom sorting.

Kenapa urutannya penting?

customer_id dan status dipakai untuk menyaring data.
created_at dipakai untuk mengurutkan hasil yang sudah tersaring.
Dengan index yang sesuai, database lebih mungkin mengambil baris dalam urutan yang sudah benar tanpa sort mahal.

Sebelum optimasi vs sesudah optimasi

Sebelum:

index terpisah atau bahkan tidak ada index yang cocok,
plan menunjukkan scan besar, sort, lalu offset,
latency naik tajam pada page besar.

Sesudah menambah index komposit:

filter lebih selektif lewat index,
sorting lebih sering bisa mengikuti urutan index,
jumlah baris yang perlu diproses turun,
halaman kecil dan menengah biasanya jauh membaik.

Namun penting: index komposit tidak menghilangkan biaya offset besar. Ia hanya mengurangi biaya scan/sort yang tidak perlu. Saat OFFSET sudah sangat besar, database tetap harus melewati banyak entri index atau baris hasil.

Covering index dan kapan berguna

Covering index berarti semua kolom yang dibutuhkan query tersedia di index, sehingga database bisa mengurangi akses tambahan ke tabel utama. Untuk query list ringan, ini sering membantu.

Misalnya jika endpoint hanya perlu:

id, customer_id, status, created_at

dan index mencakup kolom-kolom itu, engine bisa lebih efisien dibanding harus bolak-balik ke heap/table untuk setiap row. Tetapi trade-off-nya:

index jadi lebih besar,
biaya write meningkat,
tidak semua query cocok dijadikan covering index.

Jadi, prioritaskan dulu index komposit yang cocok dengan pola WHERE dan ORDER BY, baru pertimbangkan covering index bila query list sangat sering dipanggil dan kolom yang diambil relatif kecil.

Contoh implementasi praktis di Spring Boot

Memakai JPA Page tetap boleh, asal paham SQL-nya

Contoh endpoint biasa:

@GetMapping("/api/orders")
public Page<OrderDto> listOrders(
        @RequestParam Long customerId,
        @RequestParam OrderStatus status,
        @PageableDefault(size = 20, sort = "createdAt", direction = Sort.Direction.DESC)
        Pageable pageable
) {
    return orderRepository.findByCustomerIdAndStatus(customerId, status, pageable)
            .map(this::toDto);
}

Pola ini masih masuk akal bila:

jumlah data tidak terlalu besar,
user biasanya hanya membuka beberapa halaman awal,
ada kebutuhan nomor halaman total,
query sudah didukung index yang tepat.

Kurangi data yang diambil jika endpoint hanya untuk list

Jangan ambil entity penuh jika UI hanya butuh beberapa kolom. Gunakan projection atau DTO query agar I/O dan object mapping lebih ringan.

public interface OrderSummary {
    Long getId();
    Long getCustomerId();
    OrderStatus getStatus();
    Instant getCreatedAt();
}

public interface OrderRepository extends JpaRepository<OrderEntity, Long> {
    Page<OrderSummary> findByCustomerIdAndStatus(
            Long customerId,
            OrderStatus status,
            Pageable pageable
    );
}

Ini bukan solusi utama untuk offset besar, tetapi tetap mengurangi beban transfer data dan hydration entity.

Waspadai count query dari Page

Jika Anda memakai Page<T>, framework biasanya menjalankan query data plus query count. Untuk beberapa endpoint, total jumlah halaman memang dibutuhkan. Tetapi untuk infinite scroll atau list biasa, Anda sering cukup memakai Slice<T> agar tidak perlu count total.

public interface OrderRepository extends JpaRepository<OrderEntity, Long> {
    Slice<OrderSummary> findByCustomerIdAndStatus(
            Long customerId,
            OrderStatus status,
            Pageable pageable
    );
}

Keuntungannya: Anda menghindari count(*) yang kadang mahal. Kekurangannya: API tidak punya informasi total halaman atau total elemen.

Kapan offset pagination masih layak dipakai

Offset bukan anti-pattern mutlak. Ia tetap cocok jika kebutuhan produk memang nomor halaman tradisional dan skala query masih masuk akal.

Gunakan offset pagination jika:

dataset per filter relatif kecil,
mayoritas akses ada di halaman awal,
pengguna perlu lompat ke page tertentu,
sort sederhana dan stabil,
index komposit sudah mendukung query,
count total benar-benar dibutuhkan oleh UI.

Contoh umum: dashboard admin internal dengan volume moderat dan kebutuhan Page 1, 2, 3....

Kapan beralih ke cursor/keyset pagination

Jika traffic besar, data terus tumbuh, dan user lebih sering menekan “load more” daripada lompat ke nomor halaman tertentu, cursor/keyset pagination hampir selalu lebih stabil untuk performa.

Kenapa keyset lebih cepat

Alih-alih berkata “lewati 5000 baris”, keyset berkata “ambil 20 baris setelah nilai terakhir yang sudah saya lihat”. Query jadi mencari dari titik tertentu di index, bukan membuang ribuan baris dulu.

Contoh untuk urutan created_at desc, id desc:

select o.id, o.customer_id, o.status, o.total_amount, o.created_at
from orders o
where o.customer_id = ?
  and o.status = ?
  and (o.created_at, o.id) < (?, ?)
order by o.created_at desc, o.id desc
limit 20;

Pola ini biasanya membutuhkan index yang sesuai, misalnya secara konsep:

create index idx_orders_customer_status_created_id
on orders (customer_id, status, created_at desc, id desc);

Kenapa perlu kolom tie-breaker

Jika banyak row punya created_at yang sama, pagination bisa duplikat atau lompat data bila hanya memakai satu kolom sort. Karena itu, tambahkan kolom unik dan stabil seperti id sebagai tie-breaker.

Trade-off keyset pagination

Lebih cepat dan konsisten pada data besar.
Cocok untuk infinite scroll, feed, timeline, atau sinkronisasi bertahap.
Tidak cocok jika UI wajib lompat ke page 237.
Implementasi API sedikit lebih kompleks karena memakai cursor.
Sort harus stabil dan jelas.

Contoh respons API berbasis cursor

{
  "items": [
    { "id": 9912, "createdAt": "2026-06-20T10:11:12Z", "status": "PAID" }
  ],
  "nextCursor": "2026-06-20T10:11:12Z_9912"
}

Cursor bisa berupa gabungan nilai sort yang diencode. Yang penting adalah ia mewakili posisi terakhir secara stabil dan aman dipakai ulang di request berikutnya.

Masalah count query yang sering terlupakan

Kenapa count bisa mahal

Pada endpoint JPA berbasis Page, query count sering tampak sederhana, tetapi bisa mahal jika:

filter tidak didukung index,
join banyak tabel,
kondisi pencarian kompleks,
jumlah row cocok sangat besar.

Query count tidak punya LIMIT, jadi seluruh set yang cocok tetap harus dihitung.

Cara menanganinya

Gunakan Slice jika total count tidak dibutuhkan.
Pastikan filter count query juga didukung index.
Hindari join yang tidak perlu pada count query kustom.
Untuk kasus tertentu, gunakan count terpisah atau perkiraan jika kebutuhan bisnis mengizinkan.

Yang perlu diingat: optimasi query data saja tidak cukup jika 40-60% waktu endpoint habis di count query.

Checklist diagnosis bottleneck SQL nyata

Ambil query aktual dari aplikasi, bukan versi tebakan.
Uji dengan parameter production-like, termasuk offset besar.
Bandingkan page kecil vs page besar untuk melihat pola degradasi.
Jalankan EXPLAIN/EXPLAIN ANALYZE pada query data dan count query.
Cek apakah plan melakukan sort besar atau scan banyak row.
Verifikasi urutan index terhadap pola WHERE lalu ORDER BY.
Pastikan sort stabil; tambahkan tie-breaker seperti id bila perlu.
Kurangi kolom select jika endpoint hanya butuh ringkasan.
Nilai kebutuhan count total; jika tidak wajib, pertimbangkan Slice.
Jika offset tetap besar walau index sudah benar, evaluasi keyset pagination.

Kesimpulan

Penyebab utama offset pagination makin lambat di Spring Boot bukan pada Pageable-nya, melainkan pada cara database mengeksekusi LIMIT/OFFSET di atas data yang terus tumbuh. Index komposit yang sesuai dengan filter dan sorting bisa mengurangi scan dan sort yang tidak perlu, tetapi tidak bisa menghapus biaya dasar dari OFFSET besar.

Untuk endpoint list dengan PostgreSQL atau MySQL, langkah praktisnya adalah: pahami SQL yang dihasilkan JPA, cek plan dengan EXPLAIN/EXPLAIN ANALYZE, optimalkan index komposit, evaluasi count query, dan gunakan offset hanya jika kebutuhan produk memang cocok. Jika user lebih sering melakukan navigasi berurutan di dataset besar, cursor/keyset pagination biasanya menjadi pilihan yang lebih stabil dan efisien.

Quick Search

shopping_cart Keranjang