Laravel: Audit OFFSET Pagination saat Tabel Membesar

OFFSET pagination sering terlihat normal saat data masih kecil, tetapi menjadi mahal ketika tabel tumbuh besar. Gejala yang paling umum adalah halaman akhir makin lambat, CPU dan I/O database naik, serta query list memindai jauh lebih banyak baris daripada yang benar-benar dikirim ke aplikasi.

Di Laravel, masalah ini biasanya muncul pada endpoint admin, daftar transaksi, log aktivitas, atau feed internal yang memakai pola LIMIT ... OFFSET .... Solusi utamanya bukan sekadar menambah index, melainkan memahami cara database mengeksekusi query, lalu memutuskan kapan tetap memakai OFFSET dan kapan beralih ke keyset pagination atau cursor-based pagination.

Mengapa OFFSET pagination melambat saat tabel membesar

Query seperti berikut terlihat sederhana:

SELECT id, created_at, status
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 500000;

Masalahnya, database umumnya tetap harus melewati banyak baris sebelum mengambil 50 baris terakhir yang diminta. Walaupun ada index untuk kolom pengurutan, OFFSET yang besar berarti engine perlu membaca entri index dalam jumlah besar, lalu membuang sebagian besar hasil tersebut.

Akibat praktisnya:

Latensi halaman akhir naik karena query harus memproses banyak baris yang tidak dipakai.
CPU meningkat karena lebih banyak kerja pada sorting, scanning, dan evaluasi filter.
I/O meningkat jika data atau index tidak seluruhnya berada di cache.
Tekanan ke koneksi database bertambah karena request list yang berat menahan resource lebih lama.

Semakin besar nilai OFFSET, semakin besar biaya query. Ini sebabnya halaman 1 terasa cepat, tetapi halaman 10.000 mulai menyiksa.

Gejala nyata yang perlu diaudit di aplikasi Laravel

1. Halaman belakang jauh lebih lambat daripada halaman awal

Jika endpoint yang sama cepat di ?page=1 tetapi lambat di ?page=5000, itu sinyal klasik OFFSET besar.

2. Query list menjadi top contributor di slow query log

Sering terlihat query dengan pola ORDER BY ... LIMIT ... OFFSET ... mendominasi log lambat, terutama pada tabel transaksi, event, audit log, atau message history.

3. Rows examined jauh lebih besar daripada rows returned

Kalau database memeriksa ratusan ribu atau jutaan baris untuk mengembalikan puluhan baris, berarti ada inefisiensi struktural, bukan sekadar masalah koneksi atau jaringan.

4. Beban naik walau traffic tidak berubah signifikan

Ketika volume data bertambah, query list yang tadinya aman bisa perlahan menjadi bottleneck tanpa perubahan besar pada kode aplikasi.

Cara mendiagnosis OFFSET pagination di Laravel

Aktifkan query log di level aplikasi dengan hati-hati

Untuk audit lokal atau environment non-produksi, Anda bisa menangkap query yang benar-benar dijalankan Laravel. Tujuannya bukan hanya melihat SQL, tetapi juga memahami endpoint mana yang menghasilkan query berat.

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

DB::listen(function ($query) {
    Log::info('SQL', [
        'sql' => $query->sql,
        'bindings' => $query->bindings,
        'time_ms' => $query->time,
    ]);
});

Jangan menyalakan logging seperti ini sembarangan di produksi pada traffic tinggi karena bisa menambah overhead dan menghasilkan log berukuran besar. Untuk produksi, lebih aman memakai sampling, observability tool, atau mengaktifkannya sementara saat investigasi.

Gunakan slow query log di database

Slow query log berguna untuk melihat query yang memang mahal di sisi database, bukan hanya lambat dari sudut pandang aplikasi. Fokuskan audit pada query list yang:

memakai LIMIT ... OFFSET ...,
punya ORDER BY pada kolom yang sering dipakai,
muncul berulang dari API list atau admin panel,
memiliki waktu eksekusi yang naik seiring nomor halaman.

Yang perlu dicari bukan hanya durasinya, tetapi juga pola query dan frekuensinya. Satu query 2 detik yang jarang dipakai berbeda dampaknya dengan query 300 ms yang dipanggil ribuan kali.

Baca EXPLAIN untuk melihat biaya scan

EXPLAIN membantu memahami apakah database memakai index yang tepat, melakukan filesort, atau memindai terlalu banyak baris.

EXPLAIN
SELECT id, created_at, status
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 500000;

Hal yang perlu diperhatikan saat membaca EXPLAIN:

Jenis akses: apakah index dipakai atau malah terjadi scan yang lebih mahal.
Estimasi rows: jika angkanya tinggi untuk query list kecil, ada indikasi scan besar.
Extra: perhatikan indikasi sorting tambahan atau penggunaan temporary structure.
Kesesuaian index dengan WHERE dan ORDER BY: index yang ada belum tentu membantu pola query yang sebenarnya.

Jika query sudah memakai index tetapi tetap lambat pada OFFSET besar, itu bukan berarti index gagal. Sering kali masalah utamanya adalah database tetap harus berjalan jauh di index untuk melewati baris-baris awal.

OFFSET/LIMIT vs keyset pagination secara teknis

Bagaimana OFFSET/LIMIT bekerja

Pola OFFSET/LIMIT cocok untuk kebutuhan sederhana:

$orders = Order::query()
    ->where('status', 'paid')
    ->orderByDesc('created_at')
    ->orderByDesc('id')
    ->paginate(50);

Keunggulannya:

mudah dipakai di Laravel,
mendukung nomor halaman,
cocok untuk admin panel yang perlu lompat ke halaman tertentu,
mudah digabung dengan UI klasik yang menampilkan total halaman.

Kelemahannya:

biaya meningkat seiring OFFSET,
hasil bisa kurang stabil jika ada insert/delete di antara request halaman,
database tetap membaca banyak data yang akhirnya dibuang.

Bagaimana keyset pagination bekerja

Keyset pagination tidak meminta “halaman ke-N”, tetapi meminta “data setelah baris terakhir yang sudah saya lihat”. Query biasanya memakai kondisi berbasis kolom urut.

SELECT id, created_at, status
FROM orders
WHERE status = 'paid'
  AND (
    created_at < '2026-06-01 10:00:00'
    OR (created_at = '2026-06-01 10:00:00' AND id < 123456)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Dengan pola ini, database bisa langsung melanjutkan scan dari posisi terakhir pada index yang relevan, tanpa harus membuang ratusan ribu baris akibat OFFSET.

Keunggulan keyset pagination:

lebih efisien untuk halaman dalam atau data besar,
lebih stabil terhadap insert baru di awal dataset,
lebih ramah index scan karena query bergerak dari titik acuan, bukan menghitung dari awal.

Kekurangannya:

tidak natural untuk fitur “lompat ke halaman 237”,
perlu kolom urut yang stabil dan unik secara efektif,
lebih rumit jika sorting dan filtering sangat dinamis.

Dampak pada stabilitas hasil

OFFSET pagination rentan menghasilkan duplikasi atau item terlewat jika data berubah di antara dua request. Misalnya, user membuka halaman 1 lalu halaman 2, sementara ada baris baru masuk di awal hasil. Posisi absolut data bergeser, sehingga isi halaman bisa berubah.

Keyset pagination lebih stabil karena acuan utamanya adalah nilai baris terakhir yang sudah diterima, misalnya kombinasi created_at dan id. Selama urutannya deterministik, peluang hasil bergeser jauh lebih kecil.

Implementasi praktis di Laravel

Pemakaian OFFSET pagination di Eloquent

Ini pola yang paling umum:

$orders = Order::query()
    ->where('status', 'paid')
    ->orderByDesc('created_at')
    ->orderByDesc('id')
    ->paginate(50);

Secara internal, pendekatan ini menggunakan OFFSET/LIMIT. Untuk tabel kecil atau halaman awal, ini sering cukup baik.

Pemakaian cursor pagination di Laravel

Laravel menyediakan cursor pagination yang cocok untuk banyak kasus keyset pagination:

$orders = Order::query()
    ->where('status', 'paid')
    ->orderByDesc('created_at')
    ->orderByDesc('id')
    ->cursorPaginate(50);

Di sisi API, hasilnya biasanya membawa token cursor, bukan nomor halaman absolut. Ini cocok untuk endpoint yang memang fokus pada performa dan urutan berkelanjutan, misalnya daftar transaksi terbaru.

Namun, cursor pagination hanya aman jika urutannya deterministik. Mengurutkan hanya dengan created_at bisa bermasalah jika banyak baris memiliki timestamp yang sama. Karena itu, tambahkan tie-breaker seperti id.

Implementasi manual keyset dengan Query Builder

Jika Anda perlu kontrol lebih besar, misalnya untuk kompatibilitas API lama atau cursor kustom, gunakan Query Builder secara eksplisit.

$limit = 50;
$cursorCreatedAt = request('cursor_created_at');
$cursorId = request('cursor_id');

$query = DB::table('orders')
    ->select(['id', 'created_at', 'status', 'total'])
    ->where('status', 'paid')
    ->orderByDesc('created_at')
    ->orderByDesc('id')
    ->limit($limit);

if ($cursorCreatedAt && $cursorId) {
    $query->where(function ($q) use ($cursorCreatedAt, $cursorId) {
        $q->where('created_at', '<', $cursorCreatedAt)
          ->orWhere(function ($q2) use ($cursorCreatedAt, $cursorId) {
              $q2->where('created_at', '=', $cursorCreatedAt)
                 ->where('id', '<', $cursorId);
          });
    });
}

$rows = $query->get();

Pola ini merepresentasikan “ambil data setelah pasangan (created_at, id) terakhir”. Untuk arah ascending, operator dan urutannya perlu disesuaikan.

Desain index yang relevan

Index yang baik harus mengikuti pola akses query, bukan sekadar menambahkan index pada setiap kolom filter.

Contoh pola query

SELECT id, created_at, status
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

Untuk query seperti ini, pertimbangkan index yang menyelaraskan kolom filter dan urutan sort. Secara umum, pola berikut sering membantu:

(status, created_at, id)

Alasannya:

status dipakai untuk menyaring subset data,
created_at dipakai untuk urutan utama,
id menjadi tie-breaker agar urutan stabil.

Hal penting yang sering terlewat:

Jika filter berubah-ubah drastis, satu index tidak akan optimal untuk semua kombinasi.
Jika sorting juga dinamis, keyset pagination menjadi lebih sulit karena setiap urutan idealnya didukung index yang sesuai.
Index besar menambah biaya write, storage, dan maintenance.

Index tidak menghilangkan biaya OFFSET besar. Index hanya membuat jalur scan lebih efisien. Jika Anda tetap meminta OFFSET raksasa, database masih harus melangkah jauh di struktur index.

Trade-off saat sorting dan filtering dinamis

Kasus yang mudah

Jika endpoint selalu diurutkan berdasarkan created_at desc, id desc dan filternya terbatas, keyset pagination biasanya sangat cocok.

Kasus yang sulit

Jika user bisa bebas mengurutkan berdasarkan banyak kolom seperti nama, status, total, tanggal, lalu menambah berbagai filter kombinatif, maka tantangannya meningkat:

Anda sulit menyediakan index ideal untuk semua variasi.
Keyset harus mendefinisikan cursor berbeda untuk tiap urutan.
Urutan yang tidak unik perlu tie-breaker tambahan.
Beberapa sort mungkin secara bisnis tidak butuh halaman dalam, sehingga offset masih cukup.

Pendekatan pragmatis yang sering berhasil:

Gunakan keyset untuk endpoint traffic tinggi dan urutan tetap.
Pertahankan OFFSET untuk admin panel eksploratif dengan volume akses lebih rendah.
Batasi pilihan sort pada endpoint publik yang sensitif performa.
Tolak atau batasi halaman sangat dalam pada query yang tidak bisa dioptimalkan secara masuk akal.

Strategi migrasi aman tanpa memutus API atau admin panel lama

1. Inventaris endpoint yang benar-benar bermasalah

Jangan migrasikan semua pagination sekaligus. Fokus pada query yang muncul di slow query log, memiliki traffic nyata, dan menunjukkan pertumbuhan biaya saat data bertambah.

2. Pisahkan use case API publik dan admin panel

Admin panel sering tetap butuh nomor halaman dan total record. API publik atau aplikasi internal feed-style biasanya lebih mudah dipindahkan ke cursor.

3. Tambahkan endpoint atau mode baru terlebih dahulu

Alih-alih mengubah kontrak lama secara mendadak, sediakan jalur transisi seperti:

endpoint baru dengan cursor pagination, atau
parameter baru semacam pagination=cursor.

Dengan cara ini, client lama tetap berjalan, sementara client baru bisa berpindah tanpa downtime logika bisnis.

4. Pertahankan urutan yang konsisten

Sebelum migrasi, pastikan semua query memiliki urutan deterministik. Jika sebelumnya hanya orderBy('created_at', 'desc'), tambahkan tie-breaker seperti orderBy('id', 'desc').

5. Lakukan rollout bertahap dan bandingkan metrik

Pantau:

durasi query,
rows examined,
CPU database,
error rate pada client,
perbedaan hasil antara endpoint lama dan baru untuk subset request.

6. Jangan memaksa cursor untuk semua antarmuka

Jika admin benar-benar butuh lompat ke halaman tertentu, mempertahankan OFFSET pada UI tersebut bisa jadi keputusan yang benar, selama ada batasan wajar dan ekspektasi performanya jelas.

Kesalahan umum saat audit

Menyalahkan Laravel sepenuhnya. Akar masalah biasanya ada pada pola query dan skala data, bukan framework semata.
Mengandalkan index tunggal pada kolom sort tanpa memikirkan filter dan tie-breaker.
Mengurutkan berdasarkan kolom yang tidak stabil sehingga hasil cursor bisa duplikat atau lompat.
Menuntut total halaman real-time pada dataset sangat besar sambil berharap performa tetap murah.
Mengaktifkan query log berlebihan di produksi hingga menambah overhead baru.

Debugging tips yang praktis

Bandingkan performa query halaman awal versus halaman sangat dalam dengan filter yang sama.
Lihat apakah ORDER BY sesuai dengan urutan index yang tersedia.
Pastikan query tidak melakukan select * jika hanya beberapa kolom yang dibutuhkan.
Periksa apakah join atau eager loading memperburuk query utama list.
Jika memakai cursor, uji kasus data baru masuk di tengah navigasi untuk memastikan stabilitas hasil.

Kapan cursor pagination bukan solusi terbaik

Walaupun sering lebih efisien, cursor pagination bukan jawaban universal. Ada beberapa situasi di mana OFFSET masih lebih cocok atau setidaknya lebih praktis:

UI membutuhkan lompat langsung ke halaman tertentu.
Total halaman harus ditampilkan secara eksplisit dan akurat.
Urutan sangat dinamis dengan banyak kombinasi sort yang tidak semuanya bisa didukung index efektif.
Dataset hasil kecil sehingga biaya OFFSET masih dapat diterima.
Query analitik atau reporting ad hoc yang memang tidak dirancang sebagai feed berurutan.

Dalam kondisi seperti ini, optimasi yang lebih realistis bisa berupa pembatasan halaman maksimum, caching hasil tertentu, penyederhanaan filter, atau memisahkan kebutuhan reporting dari endpoint transaksi harian.

Checklist audit OFFSET Pagination di Laravel

Identifikasi endpoint Laravel yang memakai paginate() pada tabel besar.
Bandingkan latency halaman awal dan halaman belakang.
Periksa slow query log untuk pola LIMIT ... OFFSET ....
Gunakan EXPLAIN untuk melihat rows, akses index, dan indikasi sort tambahan.
Pastikan urutan query deterministik, misalnya created_at plus id.
Tinjau index agar selaras dengan WHERE dan ORDER BY.
Putuskan endpoint mana yang cocok dipindah ke keyset/cursor.
Siapkan migrasi kompatibel tanpa memutus API atau admin lama.
Ukur ulang dampak perubahan dengan metrik database dan aplikasi.
Jangan gunakan cursor jika kebutuhan produk bergantung pada nomor halaman absolut.

Intinya, audit OFFSET pagination saat tabel membesar bukan hanya soal menemukan query lambat, tetapi memahami mengapa database bekerja terlalu keras untuk mengembalikan sedikit data. Di Laravel, kombinasi diagnosis yang tepat, desain index yang sesuai, dan keputusan migrasi yang selektif biasanya memberi hasil jauh lebih baik daripada sekadar menambah resource database.

Quick Search

shopping_cart Keranjang