CodeIgniter 4: Ganti OFFSET dengan Keyset Pagination yang Stabil

Jika endpoint daftar data di CodeIgniter 4 mulai terasa berat saat pengguna membuka halaman yang makin tinggi, penyebabnya sering bukan sekadar jumlah data, tetapi cara pagination-nya. OFFSET/LIMIT memaksa database melewati banyak baris sebelum menemukan data yang perlu dikirim, sehingga query halaman 1 dan halaman 10.000 memiliki biaya yang sangat berbeda.

Solusi yang lebih stabil adalah keyset pagination. Alih-alih berkata “lompat ke baris ke-100.000”, aplikasi meminta “ambil data setelah kombinasi nilai terakhir yang saya lihat”. Pendekatan ini biasanya lebih efisien, lebih konsisten untuk data yang terus berubah, dan lebih cocok untuk API atau feed dengan urutan yang jelas.

Mengapa OFFSET/LIMIT melambat pada tabel besar

Gejala yang umum terlihat

Beberapa tanda bahwa pagination berbasis OFFSET mulai menjadi masalah:

Halaman awal cepat, tetapi halaman tinggi makin lambat.
Beban CPU dan I/O database naik saat user menelusuri daftar panjang.
Response time API daftar data tidak konsisten.
Data terasa “loncat” atau “duplikat” ketika ada insert baru di tengah proses browsing.

Penyebab di level SQL

Query seperti berikut terlihat sederhana:

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

Masalahnya, database tetap perlu mencari urutan hasil, lalu melewati 100.000 baris sebelum mengembalikan 20 baris berikutnya. Meskipun ada indeks yang membantu pengurutan, OFFSET besar tetap menambah kerja karena mesin query harus berjalan semakin jauh di dalam hasil terurut.

Dengan kata lain, OFFSET tidak benar-benar “lompat gratis”. Semakin tinggi halaman, semakin banyak baris yang harus dipindai atau dilewati.

Dampak ke UX dan beban database

UX menurun: halaman berikutnya terasa semakin lambat.
Beban database meningkat: query berat yang sebenarnya hanya mengembalikan sedikit data.
Hasil tidak stabil: jika ada data baru masuk, posisi baris dapat bergeser sehingga pengguna melihat data ganda atau melewatkan data tertentu.

Apa itu keyset pagination dan kenapa lebih stabil

Keyset pagination menggunakan nilai kolom pengurutan terakhir sebagai penanda halaman berikutnya. Jadi, bukan “halaman 500”, tetapi “ambil 20 data setelah baris dengan created_at dan id tertentu”.

Contoh untuk urutan terbaru lebih dulu:

SELECT id, title, created_at
FROM posts
WHERE (created_at < :last_created_at)
   OR (created_at = :last_created_at AND id < :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Pendekatan ini bekerja baik karena database bisa memanfaatkan indeks untuk langsung melanjutkan pencarian dari titik terakhir, bukan menghitung dari awal lalu membuang ribuan baris.

Mengapa perlu dua kolom: created_at dan id

Jika hanya mengurutkan berdasarkan created_at, hasil bisa tidak stabil ketika banyak baris memiliki timestamp yang sama. Karena itu, tambahkan id sebagai tie-breaker.

Urutan yang umum dan stabil:

ORDER BY created_at DESC, id DESC
Cursor menyimpan dua nilai: created_at dan id

Dengan begitu, setiap posisi pagination memiliki urutan yang deterministik.

Desain indeks yang cocok

Keyset pagination sangat bergantung pada urutan yang konsisten dan indeks yang sesuai. Jika query Anda mengurutkan data dengan:

ORDER BY created_at DESC, id DESC

maka indeks komposit yang relevan adalah:

CREATE INDEX idx_posts_created_id ON posts (created_at, id);

Pada banyak database relasional, arah ASC/DESC pada indeks memiliki perilaku yang bergantung mesin database. Karena itu, yang paling penting adalah urutan kolom indeks sesuai dengan urutan pencarian dan pengurutan query. Jangan terlalu bergantung pada asumsi yang sangat spesifik mesin jika belum diverifikasi.

Aturan praktis desain indeks

Kolom filter tetap harus dipertimbangkan lebih dulu jika query selalu memfilter subset data tertentu.
Kolom pengurutan utama dan tie-breaker sebaiknya berada dalam indeks yang sama.
Hindari pengurutan pada ekspresi atau fungsi jika ingin indeks efektif, kecuali memang sudah didukung dan dirancang khusus.

Contoh jika daftar selalu difilter per status:

-- Query sering seperti:
-- WHERE status = 'published'
-- ORDER BY created_at DESC, id DESC

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

Urutan indeks harus mengikuti pola akses query yang dominan, bukan sekadar menebak.

Contoh query raw SQL untuk next dan prev cursor

Halaman pertama

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

Kenapa 21? Jika ukuran halaman 20, ambil satu baris ekstra untuk mendeteksi apakah masih ada halaman berikutnya. Jika hasil lebih dari 20, berarti has_next_page bernilai true.

Halaman berikutnya dengan next cursor

SELECT id, title, created_at
FROM posts
WHERE status = 'published'
  AND (
    created_at < :cursor_created_at
    OR (created_at = :cursor_created_at AND id < :cursor_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 21;

Halaman sebelumnya dengan prev cursor

Prev cursor sedikit lebih rumit. Salah satu pola umum:

Ambil data yang “lebih baru” dari cursor saat ini dengan operator kebalikan.
Urutkan ASC agar database mengambil baris terdekat lebih dulu.
Balik lagi urutan hasil di aplikasi sebelum dikirim ke klien.

SELECT id, title, created_at
FROM posts
WHERE status = 'published'
  AND (
    created_at > :cursor_created_at
    OR (created_at = :cursor_created_at AND id > :cursor_id)
  )
ORDER BY created_at ASC, id ASC
LIMIT 21;

Setelah itu, hasil dibalik menjadi created_at DESC, id DESC agar konsisten dengan tampilan utama.

Catatan: Jika aplikasi Anda tidak benar-benar membutuhkan tombol “halaman sebelumnya”, implementasi hanya next cursor jauh lebih sederhana dan sering cukup untuk feed, log, atau daftar transaksi.

Implementasi di CodeIgniter 4 dengan Query Builder

Di CodeIgniter 4, Anda bisa tetap memakai Query Builder, tetapi untuk kondisi keyset majemuk kadang lebih jelas menggunakan where() mentah dengan binding parameter yang aman.

Contoh endpoint daftar data

<?php

namespace App\Controllers;

use CodeIgniter\RESTful\ResourceController;
use Config\Database;

class PostController extends ResourceController
{
    public function index()
    {
        $db = Database::connect();
        $builder = $db->table('posts');

        $limit = (int) ($this->request->getGet('limit') ?? 20);
        $limit = max(1, min($limit, 100));

        $cursor = $this->request->getGet('cursor');
        $direction = $this->request->getGet('direction') ?? 'next';

        $builder->select('id, title, created_at')
                ->where('status', 'published');

        if ($cursor) {
            $decoded = $this->decodeCursor($cursor);
            if (! $decoded) {
                return $this->failValidationErrors('Cursor tidak valid.');
            }

            $createdAt = $decoded['created_at'];
            $id = (int) $decoded['id'];

            if ($direction === 'prev') {
                $builder->where("(created_at > '{$createdAt}' OR (created_at = '{$createdAt}' AND id > {$id}))", null, false)
                        ->orderBy('created_at', 'ASC')
                        ->orderBy('id', 'ASC');
            } else {
                $builder->where("(created_at < '{$createdAt}' OR (created_at = '{$createdAt}' AND id < {$id}))", null, false)
                        ->orderBy('created_at', 'DESC')
                        ->orderBy('id', 'DESC');
            }
        } else {
            $builder->orderBy('created_at', 'DESC')
                    ->orderBy('id', 'DESC');
        }

        $rows = $builder->limit($limit + 1)->get()->getResultArray();

        $hasMore = count($rows) > $limit;
        if ($hasMore) {
            array_pop($rows);
        }

        if ($direction === 'prev') {
            $rows = array_reverse($rows);
        }

        $nextCursor = null;
        $prevCursor = null;

        if (! empty($rows)) {
            $first = $rows[0];
            $last = $rows[count($rows) - 1];

            $prevCursor = $this->encodeCursor($first['created_at'], $first['id']);
            $nextCursor = $hasMore ? $this->encodeCursor($last['created_at'], $last['id']) : null;
        }

        return $this->respond([
            'data' => $rows,
            'meta' => [
                'limit' => $limit,
                'has_more' => $hasMore,
                'next_cursor' => $nextCursor,
                'prev_cursor' => $prevCursor,
            ],
        ]);
    }

    private function encodeCursor(string $createdAt, int $id): string
    {
        return rtrim(strtr(base64_encode(json_encode([
            'created_at' => $createdAt,
            'id' => $id,
        ])), '+/', '-_'), '=');
    }

    private function decodeCursor(string $cursor): ?array
    {
        $decoded = base64_decode(strtr($cursor, '-_', '+/'), true);
        if ($decoded === false) {
            return null;
        }

        $data = json_decode($decoded, true);
        if (! is_array($data) || ! isset($data['created_at'], $data['id'])) {
            return null;
        }

        if (! is_string($data['created_at']) || ! is_numeric($data['id'])) {
            return null;
        }

        return [
            'created_at' => $data['created_at'],
            'id' => (int) $data['id'],
        ];
    }
}

Catatan penting tentang binding parameter

Contoh di atas menunjukkan bentuk yang mudah dibaca, tetapi untuk produksi sebaiknya utamakan parameter binding daripada menyisipkan nilai ke string SQL mentah. Pada Query Builder, kondisi tuple seperti ini kadang lebih nyaman dikerjakan dengan query mentah yang tetap memakai binding.

Versi raw query yang lebih aman:

$sql = "
    SELECT id, title, created_at
    FROM posts
    WHERE status = ?
      AND (
        created_at < ?
        OR (created_at = ? AND id < ?)
      )
    ORDER BY created_at DESC, id DESC
    LIMIT ?
";

$rows = $db->query($sql, [
    'published',
    $createdAt,
    $createdAt,
    $id,
    $limit + 1,
])->getResultArray();

Jika Query Builder menjadi terlalu dipaksa untuk kondisi kompleks, raw SQL dengan binding justru sering lebih aman dan lebih mudah diaudit.

Format cursor yang baik

Cursor sebaiknya:

Tidak bergantung pada nomor halaman.
Menyimpan semua nilai yang dibutuhkan untuk melanjutkan urutan.
Mudah divalidasi.
Tidak harus terenkripsi, tetapi setidaknya dibuat opaque agar klien tidak tergantung pada struktur internal.

Format yang praktis adalah JSON yang di-encode dengan base64 URL-safe:

{"created_at":"2026-01-15 10:30:00","id":12345}

Lalu diubah menjadi string cursor, misalnya:

eyJjcmVhdGVkX2F0IjoiMjAyNi0wMS0xNSAxMDozMDowMCIsImlkIjoxMjM0NX0

Validasi input cursor

Jangan langsung percaya nilai dari query string. Minimal validasi:

Base64 dapat didecode.
JSON valid.
Field yang wajib ada: created_at dan id.
Tipe data sesuai.
Nilai limit dibatasi agar tidak berlebihan.

Jika perlu, tambahkan tanda tangan HMAC agar cursor tidak mudah dimodifikasi klien. Ini berguna bila Anda ingin memastikan cursor benar-benar diterbitkan oleh server.

Menangani data baru agar tidak lompat atau duplikat

Ini salah satu alasan utama keyset pagination yang stabil lebih baik daripada OFFSET. Saat ada data baru masuk di bagian atas daftar, OFFSET dapat menyebabkan item bergeser antarhalaman. Akibatnya pengguna bisa melihat item yang sama dua kali atau kehilangan item tertentu.

Kenapa keyset lebih tahan terhadap insert baru

Karena next page didefinisikan sebagai “data setelah baris terakhir yang sudah dilihat”, insert baru di atas daftar tidak mengubah batas bawah halaman berikutnya. Jadi hasil lebih konsisten selama urutannya deterministik.

Namun tetap ada batasannya

Keyset pagination tidak otomatis memberi snapshot historis yang benar-benar beku. Jika ada update pada kolom pengurutan, misalnya created_at diubah atau Anda mengurutkan berdasarkan kolom yang bisa berubah, item tetap bisa berpindah posisi.

Praktik yang aman:

Gunakan kolom pengurutan yang stabil, misalnya created_at yang tidak diubah setelah insert.
Gunakan id sebagai tie-breaker unik.
Hindari sort berdasarkan kolom yang sering berubah jika ingin hasil konsisten saat user menelusuri banyak halaman.

Jika perlu konsistensi lebih ketat

Untuk use case audit, ekspor, atau pelacakan riwayat yang sangat sensitif, Anda mungkin perlu strategi tambahan seperti:

Membatasi hasil sampai snapshot watermark tertentu, misalnya hanya data dengan created_at <= waktu_awal_request.
Menggunakan transaksi atau mekanisme snapshot sesuai kemampuan database, jika benar-benar diperlukan.

Namun pendekatan ini menambah kompleksitas dan tidak selalu perlu untuk daftar biasa.

Audit performa dengan EXPLAIN

Jangan migrasi hanya berdasarkan asumsi. Audit query sekarang dan query pengganti dengan EXPLAIN.

Apa yang perlu diperiksa

Apakah query memakai indeks yang diharapkan.
Apakah ada tanda pemindaian baris yang besar.
Apakah urutan hasil memicu sorting tambahan yang mahal.
Apakah kondisi filter dan order cocok dengan indeks komposit yang tersedia.

Contoh:

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

EXPLAIN
SELECT id, title, created_at
FROM posts
WHERE status = 'published'
  AND (
    created_at < '2026-01-15 10:30:00'
    OR (created_at = '2026-01-15 10:30:00' AND id < 12345)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

Tips membaca hasil audit

Nama kolom dan format output EXPLAIN berbeda antar database, jadi fokus pada prinsip umumnya:

Query keyset idealnya menunjukkan penggunaan indeks untuk melanjutkan pencarian, bukan menghitung banyak baris lalu membuangnya.
Jika masih terjadi full scan atau sort berat, cek kembali indeks dan pola query.
Jika ada filter tambahan, mungkin indeks lama tidak lagi cocok.

Trade-off OFFSET vs keyset pagination

Kapan OFFSET masih masuk akal

Ukuran tabel kecil atau sedang.
User memang harus lompat langsung ke halaman tertentu, misalnya halaman 37.
Kebutuhan admin panel sederhana yang tidak terlalu sensitif pada performa tinggi.

Kapan keyset lebih tepat

Tabel besar.
Daftar dengan urutan kronologis atau monotonic yang jelas.
Feed, log, transaksi, aktivitas, komentar, notifikasi, atau API infinite scroll.
Saat konsistensi hasil lebih penting daripada akses “halaman ke-N”.

Kekurangan keyset pagination

Tidak cocok untuk loncat arbitrer ke nomor halaman tertentu.
Implementasi prev cursor lebih kompleks.
Perlu urutan yang benar-benar deterministik.
Menjadi lebih rumit jika sort dan filter sangat dinamis.

Batasan saat sort dan filter kompleks

Keyset pagination paling enak saat query memiliki urutan yang stabil dan terbatas, misalnya selalu created_at DESC, id DESC. Tantangan muncul saat pengguna bebas memilih banyak kombinasi:

Sort berdasarkan beberapa kolom yang bisa berubah-ubah.
Filter opsional yang sangat banyak.
Pencarian full-text yang hasil relevansinya tidak stabil.

Dalam situasi seperti itu:

Tidak semua kombinasi query layak diberi keyset pagination.
Anda mungkin perlu membatasi opsi sort yang didukung.
Setiap pola query populer mungkin butuh indeks khusus.
Untuk pencarian berbasis skor relevansi, hasil bisa sulit dibuat deterministik tanpa tie-breaker tambahan.

Pilih area yang paling terasa lambat terlebih dahulu, bukan mencoba mengganti semua endpoint sekaligus.

Checklist migrasi aman tanpa downtime

Identifikasi endpoint paling berat
Mulai dari query daftar yang memakai OFFSET tinggi dan sering dipanggil.
Pastikan urutan data stabil
Tentukan kolom sort utama dan tie-breaker unik, misalnya created_at dan id.
Tambahkan indeks yang diperlukan
Buat indeks komposit yang sesuai pola filter dan order. Lakukan dengan prosedur yang aman sesuai database Anda.
Audit dengan EXPLAIN
Bandingkan query lama dan query keyset sebelum mengubah API publik.
Implementasikan endpoint paralel
Jangan langsung mengganti endpoint lama. Tambahkan mode cursor-based terlebih dahulu.
Tambahkan validasi cursor dan limit
Cegah input tidak valid atau limit berlebihan.
Uji konsistensi data
Pastikan tidak ada duplikasi atau data hilang saat ada insert baru selama pagination berjalan.
Uji klien
Sesuaikan frontend atau konsumen API agar memakai next_cursor dan, jika perlu, prev_cursor.
Pantau query setelah rilis
Lihat perubahan latency, error rate, dan pola penggunaan.
Depresiasi OFFSET secara bertahap
Setelah semua klien migrasi, baru nonaktifkan akses pagination lama.

Kesalahan yang sering terjadi

Hanya sort by created_at tanpa tie-breaker
Berisiko menghasilkan urutan tidak stabil.
Cursor tidak memuat semua kolom order
Akibatnya halaman berikutnya bisa salah.
Tetap memakai kolom sort yang mudah berubah
Data bisa berpindah posisi saat user sedang menelusuri halaman.
Indeks tidak sesuai urutan query
Keyset tidak otomatis cepat tanpa indeks yang tepat.
Menganggap keyset cocok untuk semua use case
Untuk halaman numerik acak, OFFSET masih lebih sederhana.

Penutup

Jika pagination di CodeIgniter 4 mulai berat pada halaman tinggi, mengganti OFFSET/LIMIT dengan keyset pagination yang stabil sering menjadi perbaikan yang paling masuk akal. Kuncinya bukan hanya mengganti query, tetapi memastikan urutan deterministik, indeks komposit sesuai, cursor tervalidasi, dan perilaku aplikasi tetap konsisten saat data baru masuk.

Mulailah dari satu endpoint yang paling bermasalah, audit dengan EXPLAIN, lalu migrasikan bertahap tanpa downtime. Dengan pendekatan ini, Anda bisa menurunkan beban database sekaligus memperbaiki pengalaman pengguna pada daftar data besar.

Quick Search

shopping_cart Keranjang