Optimasi Pagination SQL di Bun dengan Cursor dan Indeks yang Tepat

Optimasi pagination SQL di Bun menjadi penting ketika endpoint list yang awalnya terasa cepat mulai melambat saat tabel bertambah besar. Penyebab paling umum adalah penggunaan LIMIT ... OFFSET untuk halaman yang makin jauh. Query tetap terlihat sederhana, tetapi beban database naik karena ia harus membaca, mengurutkan, lalu melewati banyak baris sebelum mengembalikan hasil.

Solusi yang biasanya lebih stabil adalah cursor pagination. Alih-alih berkata “ambil halaman ke-200”, client berkata “ambil data setelah item terakhir yang saya terima”. Dengan kombinasi kolom sort yang konsisten dan indeks yang sesuai, database bisa langsung melanjutkan pembacaan dari posisi yang relevan. Di artikel ini kita fokus pada implementasi praktis di Bun, contoh endpoint sederhana, query SQL, desain indeks, dasar membaca EXPLAIN, serta jebakan umum saat data berubah di tengah proses paginasi.

Masalah nyata: kenapa OFFSET makin lambat

Pagination dengan OFFSET biasanya terlihat seperti ini:

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;

Secara fungsional ini benar. Masalahnya, database tidak punya jalan pintas ajaib untuk langsung meloncat ke baris ke-10001 dalam semua kasus. Ia tetap perlu mencari, mengurutkan, atau menelusuri sejumlah besar row terlebih dahulu, lalu membuang 10000 row sebelum mengembalikan 20 row berikutnya.

Ada beberapa efek samping dari pendekatan ini:

Latensi naik seiring nomor halaman. Halaman awal mungkin cepat, halaman jauh bisa terasa berat.
Beban I/O dan CPU meningkat, terutama jika query perlu sort besar atau indeks tidak cocok.
Hasil bisa tidak stabil jika data berubah di antara request, misalnya ada row baru masuk di urutan teratas.

Untuk tabel kecil atau admin panel internal, ini belum tentu masalah. Tetapi untuk endpoint publik dengan traffic tinggi atau data yang tumbuh terus, OFFSET sering menjadi bottleneck pertama.

Kapan OFFSET masih cukup, dan kapan harus pindah ke cursor

OFFSET masih cukup jika

Jumlah data relatif kecil.
Halaman yang diakses umumnya hanya halaman awal.
Query sederhana dan indeks sudah mendukung ORDER BY.
Anda butuh navigasi nomor halaman yang eksplisit, misalnya halaman 1, 2, 3, dan seterusnya.

Pertimbangkan cursor jika

Tabel besar dan terus bertambah.
Pengguna sering melakukan infinite scroll atau “load more”.
Query list adalah endpoint panas yang dipanggil sangat sering.
Latency halaman jauh dengan OFFSET mulai tinggi.
Anda butuh hasil yang lebih stabil saat data baru masuk.

Aturan praktis: jika beban utama adalah list terbaru berdasarkan waktu atau ID, cursor pagination hampir selalu lebih cocok daripada OFFSET.

Fondasi yang benar: urutan sort harus deterministik

Sebelum bicara implementasi, ada satu aturan penting: sort harus unik dan stabil. Kesalahan umum adalah hanya mengurutkan berdasarkan created_at DESC. Jika dua row punya created_at yang sama, urutan antar-row tidak dijamin konsisten.

Gunakan tie-breaker yang unik, biasanya id:

ORDER BY created_at DESC, id DESC

Dengan urutan ini, setiap row punya posisi yang jelas. Cursor juga harus membawa kedua nilai tersebut, bukan hanya salah satunya.

Skema contoh dan indeks yang relevan

Misalkan kita punya tabel berikut:

CREATE TABLE posts (
  id BIGINT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL
);

Kita ingin endpoint list hanya menampilkan post berstatus published, diurutkan dari yang terbaru:

SELECT id, title, created_at
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20;

Agar query ini efisien, indeks harus mengikuti pola filter lalu sort. Indeks yang masuk akal:

CREATE INDEX idx_posts_status_created_id
ON posts (status, created_at DESC, id DESC);

Kenapa urutannya seperti itu?

status dipakai pada klausa WHERE.
created_at dan id dipakai pada ORDER BY.
Dengan indeks yang searah dengan pola query, database lebih mudah mengambil row yang sesuai tanpa sort besar terpisah.

Jika query Anda tidak selalu memfilter status, mungkin diperlukan indeks lain. Satu indeks tidak otomatis ideal untuk semua variasi query.

Dampak sort pada indeks

ORDER BY yang cocok dengan indeks sangat menentukan. Contoh masalah yang sering terjadi:

Indeks ada, tetapi urutan kolom tidak sesuai dengan query.
Query mengurutkan dengan kombinasi kolom yang tidak ada di indeks.
Query memakai fungsi pada kolom sort, misalnya ORDER BY DATE(created_at), sehingga indeks sulit dimanfaatkan secara optimal.

Jika database harus melakukan sort di luar indeks pada data besar, biaya query bisa melonjak.

Contoh endpoint list di Bun dengan OFFSET

Sebelum beralih ke cursor, mari lihat bentuk endpoint sederhana di Bun dengan OFFSET agar masalahnya jelas. Contoh berikut memakai pola generik koneksi SQL. Sesuaikan dengan driver atau adapter yang Anda gunakan.

import { serve } from "bun";
import { sql } from "./db";

serve({
  port: 3000,
  async fetch(req) {
    const url = new URL(req.url);

    if (url.pathname !== "/posts") {
      return new Response("Not Found", { status: 404 });
    }

    const limit = Math.min(Number(url.searchParams.get("limit") || 20), 100);
    const page = Math.max(Number(url.searchParams.get("page") || 1), 1);
    const offset = (page - 1) * limit;

    const rows = await sql`
      SELECT id, title, created_at
      FROM posts
      WHERE status = 'published'
      ORDER BY created_at DESC, id DESC
      LIMIT ${limit} OFFSET ${offset}
    `;

    return Response.json({
      data: rows,
      pagination: { page, limit }
    });
  }
});

Untuk halaman awal, ini sering cukup. Tetapi begitu page besar, biaya query ikut besar.

Mengganti ke cursor pagination

Pada cursor pagination, client mengirim penanda item terakhir yang sudah diterima. Misalnya item terakhir memiliki:

created_at = 2025-01-10T12:30:00Z
id = 8451

Query berikut akan mengambil 20 item berikutnya dalam urutan created_at DESC, id DESC:

SELECT id, title, created_at
FROM posts
WHERE status = 'published'
  AND (
    created_at < '2025-01-10T12:30:00Z'
    OR (created_at = '2025-01-10T12:30:00Z' AND id < 8451)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

Logikanya:

Ambil row yang tanggalnya lebih lama dari cursor.
Jika tanggal sama, ambil row dengan id lebih kecil.
Urutan tetap konsisten dengan ORDER BY.

Ini jauh lebih efisien dibanding OFFSET besar karena database bisa melanjutkan dari posisi yang dekat dengan cursor, terutama jika indeks mendukung.

Encoding cursor untuk API

Jangan kirim cursor sebagai dua parameter mentah jika Anda ingin API lebih rapi. Bungkus menjadi string opaque, misalnya Base64 dari JSON:

{"created_at":"2025-01-10T12:30:00Z","id":8451}

Client tidak perlu tahu isi detailnya, hanya perlu mengirim balik cursor tersebut untuk halaman berikutnya.

Implementasi endpoint cursor pagination di Bun

Berikut contoh implementasi sederhana. Fokusnya adalah alur, bukan ketergantungan pada library tertentu.

import { serve } from "bun";
import { sql } from "./db";

function encodeCursor(row) {
  return Buffer.from(JSON.stringify({
    created_at: row.created_at,
    id: row.id
  })).toString("base64url");
}

function decodeCursor(cursor) {
  const raw = Buffer.from(cursor, "base64url").toString("utf8");
  return JSON.parse(raw);
}

serve({
  port: 3000,
  async fetch(req) {
    const url = new URL(req.url);

    if (url.pathname !== "/posts") {
      return new Response("Not Found", { status: 404 });
    }

    const limit = Math.min(Number(url.searchParams.get("limit") || 20), 100);
    const cursor = url.searchParams.get("cursor");

    let rows;

    if (!cursor) {
      rows = await sql`
        SELECT id, title, created_at
        FROM posts
        WHERE status = 'published'
        ORDER BY created_at DESC, id DESC
        LIMIT ${limit}
      `;
    } else {
      const { created_at, id } = decodeCursor(cursor);

      rows = await sql`
        SELECT id, title, created_at
        FROM posts
        WHERE status = 'published'
          AND (
            created_at < ${created_at}
            OR (created_at = ${created_at} AND id < ${id})
          )
        ORDER BY created_at DESC, id DESC
        LIMIT ${limit}
      `;
    }

    const nextCursor = rows.length > 0 ? encodeCursor(rows[rows.length - 1]) : null;

    return Response.json({
      data: rows,
      next_cursor: rows.length === limit ? nextCursor : null
    });
  }
});

Kenapa pola ini bekerja

Query tetap sempit: database tidak perlu membuang ribuan row seperti OFFSET besar.
Urutan konsisten: created_at DESC, id DESC dipakai baik pada ORDER BY maupun kondisi cursor.
Lebih cocok untuk data yang terus bertambah: item baru di bagian atas tidak menggeser halaman lama seperti pada pagination berbasis page number.

Jebakan umum: hasil duplikat atau terlewat saat data berubah

Cursor pagination bukan berarti kebal terhadap semua perubahan data. Ada beberapa jebakan penting:

1. Sort tidak unik

Jika Anda hanya menggunakan created_at tanpa id sebagai tie-breaker, item bisa muncul ganda atau hilang antar halaman. Selalu gunakan urutan deterministik.

2. Kolom sort berubah setelah data dibaca

Jika kolom yang dipakai untuk sorting dapat diupdate, posisi item bisa berpindah. Contohnya, Anda mengurutkan berdasarkan updated_at lalu ada proses yang memperbarui row lama. Akibatnya, item dapat muncul lagi atau malah terlewat.

Jika memungkinkan, gunakan kolom yang lebih stabil untuk paginasi, seperti created_at + id.

3. Data baru masuk di tengah navigasi

Pada OFFSET, item baru di bagian atas bisa menggeser semua halaman berikutnya. Cursor cenderung lebih stabil untuk skenario ini, tetapi tetap tidak sama dengan snapshot transaksi penuh. Jika kebutuhan Anda adalah konsistensi mutlak terhadap satu snapshot data, desainnya lebih kompleks dan tidak cukup dengan cursor biasa.

4. Cursor mudah dimanipulasi atau rusak

Jika cursor dikirim sebagai Base64 JSON biasa, client technically bisa memodifikasi isinya. Ini tidak selalu masalah, tetapi Anda perlu memvalidasi format dan tipe nilainya. Jika dibutuhkan, cursor bisa ditandatangani.

Membaca EXPLAIN secara dasar untuk melihat bottleneck

Sebelum mengoptimasi, lihat rencana eksekusi query. Gunakan EXPLAIN atau variasi yang tersedia pada database Anda. Tujuannya bukan menghafal semua detail, tetapi menjawab pertanyaan ini:

Apakah query memakai indeks yang saya harapkan?
Apakah database melakukan sort tambahan yang mahal?
Apakah jumlah row yang dipindai jauh lebih besar daripada row yang dikembalikan?

Contoh:

EXPLAIN
SELECT id, title, created_at
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;

Saat membaca output EXPLAIN, perhatikan hal-hal berikut secara umum:

Jenis scan: apakah memakai index scan/range scan, atau justru full table scan/sequential scan.
Sort: apakah ada langkah sort eksplisit. Jika ya, cek apakah indeks sebenarnya belum cocok.
Rows examined / estimated rows: angka besar menandakan banyak data dibaca untuk hasil sedikit.
Filter: apakah kondisi WHERE diterapkan lebih awal atau setelah banyak row dibaca.

Tanda bahaya umum: query mengembalikan 20 row, tetapi planner memperkirakan atau benar-benar memindai puluhan ribu row. Ini sering terjadi pada OFFSET besar atau indeks yang tidak sesuai.

Membandingkan EXPLAIN untuk OFFSET vs cursor

Pada query cursor yang baik, Anda berharap melihat penggunaan indeks yang lebih terarah, dengan jumlah row yang dibaca lebih dekat ke ukuran halaman. Pada query OFFSET besar, planner bisa tetap memakai indeks, tetapi biaya tetap meningkat karena banyak row harus dilewati.

Jangan hanya melihat apakah indeks dipakai atau tidak. Indeks dipakai belum tentu berarti query sudah efisien.

Desain indeks: pola yang biasanya efektif

1. Indeks mengikuti filter dan urutan sort

Jika query utama Anda adalah:

WHERE status = 'published'
ORDER BY created_at DESC, id DESC

Maka indeks seperti ini umumnya relevan:

(status, created_at DESC, id DESC)

2. Jangan berharap satu indeks cocok untuk semua endpoint

Misalnya ada endpoint lain yang memfilter berdasarkan author_id dan sort berdasarkan created_at. Itu mungkin membutuhkan indeks berbeda:

(author_id, created_at DESC, id DESC)

Menggabungkan terlalu banyak kebutuhan ke satu indeks besar sering berakhir tidak optimal.

3. Waspadai biaya write

Setiap indeks tambahan membantu sebagian query baca, tetapi menambah biaya saat insert/update/delete. Jadi optimasi indeks harus berdasarkan pola akses nyata, bukan asumsi.

4. Kolom select yang terlalu lebar

Walau fokus utama ada di pagination, memilih terlalu banyak kolom besar juga memperlambat query dan transfer data. Untuk endpoint list, ambil kolom yang benar-benar dibutuhkan.

Checklist implementasi endpoint list yang tetap cepat

Tentukan pola akses utama: page number atau infinite scroll.
Pastikan ORDER BY deterministik, misalnya created_at DESC, id DESC.
Buat indeks yang sesuai dengan WHERE dan ORDER BY.
Batasi limit maksimum agar client tidak meminta terlalu banyak row sekaligus.
Untuk data besar, ganti OFFSET dengan cursor.
Encode cursor dari kolom sort + tie-breaker unik.
Validasi cursor yang masuk agar format salah tidak memicu error tak terkontrol.
Gunakan EXPLAIN untuk memastikan query benar-benar memanfaatkan indeks.
Uji saat data bertambah, bukan hanya saat tabel masih kecil.
Pantau query lambat di production agar optimasi berdasarkan data nyata.

Contoh keputusan praktis

Pilih OFFSET jika

Anda butuh nomor halaman yang eksplisit.
Data tidak terlalu besar.
Akses mayoritas hanya sampai halaman awal.
Query sudah cukup cepat dan tidak menjadi bottleneck.

Pilih cursor jika

Endpoint list adalah jalur panas dengan data besar.
UI memakai “load more” atau infinite scroll.
Anda ingin performa yang lebih stabil untuk halaman lanjut.
Urutan data berbasis waktu/ID dan cocok dengan indeks.

Kesalahan yang sering terjadi saat optimasi pagination SQL di Bun

Fokus hanya pada kode API, padahal bottleneck utamanya ada di query dan indeks.
Menganggap LIMIT kecil pasti murah. LIMIT 20 dengan OFFSET 50000 tetap bisa mahal.
Mengurutkan tanpa tie-breaker unik, lalu bingung kenapa hasil tidak konsisten.
Menambah indeks sembarangan tanpa memeriksa pola query sebenarnya.
Tidak pernah membaca EXPLAIN, sehingga optimasi hanya berdasarkan tebakan.

Penutup: panduan keputusan singkat

Jika endpoint list Anda di Bun masih melayani data kecil dan kebutuhan utamanya adalah nomor halaman, OFFSET masih layak dipakai. Tetapi jika data terus tumbuh, halaman jauh mulai lambat, atau UI Anda berbentuk infinite scroll, cursor pagination biasanya merupakan langkah yang lebih tepat.

Kunci utamanya bukan hanya mengganti format API, tetapi memastikan tiga hal bekerja bersama: sort yang deterministik, query cursor yang benar, dan indeks yang sesuai. Setelah itu, validasi dengan EXPLAIN. Kalau database masih membaca terlalu banyak row atau melakukan sort besar, kemungkinan masalahnya bukan pada Bun, melainkan pada desain query dan indeks.

Singkatnya:

OFFSET cocok untuk kasus sederhana dan kecil.
Cursor cocok untuk skala lebih besar dan akses berurutan.
Indeks yang tepat menentukan apakah pagination benar-benar cepat.
EXPLAIN membantu Anda membuktikan optimasi, bukan sekadar berharap.

Quick Search

shopping_cart Keranjang