Keyset Pagination vs OFFSET saat Data SQL Terus Bertambah

Keyset Pagination vs OFFSET menjadi penting ketika tabel SQL terus menerima insert, update, atau delete. Pada kondisi seperti ini, query dengan LIMIT ... OFFSET ... sering tampak sederhana di awal, tetapi biaya aksesnya membesar seiring pertumbuhan data dan nomor halaman yang makin jauh.

Jawaban singkatnya: OFFSET cenderung mahal karena database tetap harus melewati sejumlah baris sebelum mengembalikan hasil, sedangkan keyset pagination meminta database melanjutkan dari posisi terakhir yang sudah diketahui, biasanya lewat kolom terurut yang diindeks. Pada data yang mutable, pendekatan ini umumnya lebih stabil untuk performa dan lebih aman dari hasil yang lompat atau duplikat, meski ada trade-off pada navigasi halaman dan desain API.

Mengapa struktur akses memengaruhi biaya query

Dalam struktur data, biaya akses ditentukan oleh cara data disusun. Ide ini mirip dengan pelajaran dari RRB-Trees: struktur yang berbeda memberi biaya akses, update, dan traversal yang berbeda. Intinya bukan membandingkan database dengan struktur immutable secara langsung, tetapi menekankan bahwa cara Anda menelusuri data menentukan performa.

Pada pagination SQL, perbedaan utamanya adalah:

OFFSET pagination menelusuri hasil terurut lalu membuang sejumlah baris di depan.
Keyset pagination melanjutkan pencarian dari kunci terakhir, sehingga mesin query bisa langsung mencari titik awal berikutnya lewat index yang sesuai.

Untuk tabel kecil, perbedaannya mungkin belum terasa. Untuk tabel besar yang terus berubah, biaya traversal ini menjadi sangat nyata.

Mengapa OFFSET makin mahal saat data bertambah

Cara kerja OFFSET yang sering disalahpahami

Query seperti berikut terlihat murah:

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

Masalahnya, database tidak punya tombol ajaib untuk "lompat" langsung ke baris ke-10001 dalam hasil akhir yang sudah diurutkan, terutama setelah mempertimbangkan filter, visibilitas row, dan urutan. Secara praktis, engine biasanya tetap perlu membaca atau menelusuri banyak entri untuk menemukan 20 baris yang diminta setelah melewati 10.000 baris pertama.

Akibatnya:

Halaman awal mungkin cepat.
Halaman yang makin dalam biasanya makin mahal.
Lonjakan data baru membuat kedalaman halaman tertentu menjadi makin berat dari waktu ke waktu.

Index tidak selalu menyelesaikan masalah OFFSET

Kesalahan umum adalah menganggap, "sudah ada index, berarti OFFSET aman." Index memang membantu untuk ORDER BY dan WHERE, tetapi OFFSET tetap menyuruh database melewati baris. Jika Anda meminta OFFSET 100000, index hanya membantu traversal menjadi lebih terarah, bukan menghilangkan kebutuhan untuk melewati banyak entri.

Ini terutama terasa ketika:

Tabel sangat besar.
Urutan query sering dipakai untuk halaman jauh.
Query harus mengambil kolom yang tidak tercakup oleh index sehingga perlu lookup tambahan ke heap/table.
Data sering berubah, menyebabkan cache dan pola akses kurang stabil.

Gejala di sistem produksi

Beberapa gejala query pagination berbasis OFFSET mulai bermasalah:

Endpoint list cepat di halaman 1, tetapi lambat di halaman 100 atau 1000.
Latency p95/p99 naik seiring umur sistem dan pertumbuhan tabel.
CPU database meningkat untuk query baca sederhana.
Query plan menunjukkan banyak row dibaca dibanding row yang dikembalikan.
Pengguna melihat item duplikat atau terlewat saat data baru masuk di antara perpindahan halaman.

Kenapa keyset pagination lebih stabil

Prinsip dasar keyset pagination

Alih-alih bilang, "berikan halaman ke-501", keyset pagination bilang, "berikan 20 baris setelah item terakhir yang tadi saya terima." Itu sebabnya ia juga sering disebut seek pagination.

Contoh:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ('2025-07-01 10:15:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Dengan index yang sesuai, database dapat langsung mencari posisi (created_at, id) lalu membaca 20 baris berikutnya sesuai urutan. Biaya akses jadi lebih dekat ke biaya "cari titik awal + baca sedikit baris", bukan "lewati ribuan atau jutaan baris dulu".

Mengapa lebih cocok untuk data yang mutable

Pada sistem yang terus menerima insert baru, hasil halaman berbasis OFFSET mudah bergeser. Misalnya:

Pengguna membuka halaman 1.
Beberapa row baru masuk di atas urutan.
Pengguna membuka halaman 2 dengan OFFSET 20.

Karena posisi data sudah bergeser, halaman 2 bisa memuat item yang sudah pernah tampil di halaman 1, atau justru melewatkan item tertentu.

Keyset pagination lebih stabil karena anchor-nya adalah nilai data terakhir yang benar-benar diterima, bukan nomor posisi relatif yang mudah berubah.

Contoh nyata: OFFSET vs keyset

Skema dan index

Misalkan ada tabel artikel:

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

Jika endpoint hanya menampilkan artikel terbit terbaru, query umum adalah:

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

Index yang ingin diselaraskan dengan pola query:

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

Urutannya penting. Database harus bisa memakai index untuk:

menyaring status,
menjaga urutan created_at DESC, id DESC,
dan melanjutkan pencarian dari cursor terakhir.

Versi OFFSET

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

Meski index ada, engine tetap berpotensi menelusuri sangat banyak entri sebelum mendapatkan 20 hasil akhir.

Versi keyset

Halaman pertama:

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

Misalkan baris terakhir yang diterima adalah:

created_at = '2025-07-01 10:15:00'
id = 987654

Halaman berikutnya:

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
  AND (created_at, id) < ('2025-07-01 10:15:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Jika database atau gaya SQL Anda tidak memakai perbandingan tuple, tulis kondisi ekuivalennya secara eksplisit:

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

Pola ini penting untuk menjaga urutan yang deterministik saat created_at tidak unik.

EXPLAIN: apa yang perlu dilihat

Saat mendiagnosis query lambat, jalankan EXPLAIN atau EXPLAIN ANALYZE sesuai kemampuan dan kebijakan lingkungan Anda. Fokus pada hal berikut:

Apakah index yang tepat dipakai?
Berapa banyak row yang dibaca dibanding row yang dikembalikan?
Apakah ada sort besar? Jika iya, berarti index belum selaras dengan ORDER BY.
Apakah ada filter residual yang membuat banyak row dibuang setelah dibaca?

Pada query OFFSET yang dalam, Anda biasanya akan melihat pembacaan row jauh lebih besar daripada 20 row hasil akhir. Pada keyset yang baik, jumlah row yang diproses cenderung jauh lebih dekat ke LIMIT.

Jangan hanya melihat apakah query memakai index. Lihat juga berapa banyak pekerjaan yang tetap dilakukan meski index dipakai.

Memilih kolom index untuk ORDER BY + WHERE

Aturan praktis

Agar keyset pagination efektif, index harus mengikuti pola query Anda. Secara umum:

Kolom filter yang stabil dan selektif sering diletakkan di depan.
Kolom urut utama harus ada di index.
Tambahkan tie-breaker unik, biasanya id, agar urutan total deterministik.

Contoh pola yang baik:

WHERE tenant_id = ? AND status = 'published'
ORDER BY created_at DESC, id DESC

Index yang relevan bisa berbentuk:

(tenant_id, status, created_at DESC, id DESC)

Kesalahan desain index yang umum

Mengindeks hanya created_at padahal query juga selalu memfilter status.
Tidak menambahkan id sebagai tie-breaker untuk kolom sort yang tidak unik.
Mengurutkan berdasarkan ekspresi atau kolom yang tidak punya dukungan index yang cocok.
Mengambil terlalu banyak kolom besar sehingga keuntungan index traversal hilang karena banyak lookup tambahan.

Trade-off konsistensi hasil saat data berubah di tengah paging

OFFSET lebih rentan bergeser

Jika row baru masuk atau row lama dihapus di antara request halaman, hasil berbasis OFFSET dapat berubah secara relatif terhadap posisi. Ini memunculkan:

duplikasi item antarhalaman,
item yang terlewat,
hasil yang sulit direkonsiliasi oleh client.

Keyset lebih stabil, tetapi bukan berarti snapshot

Keyset pagination tidak otomatis memberi snapshot konsisten seluruh dataset lintas banyak request. Ia hanya lebih stabil secara posisi. Jika data berubah di tengah pagination:

row baru yang lebih "atas" dari cursor mungkin tidak terlihat pada perjalanan paging saat ini,
row yang diperbarui pada kolom sort bisa berpindah posisi,
row yang dihapus tentu bisa hilang dari halaman berikutnya.

Untuk banyak API feed atau timeline, ini dapat diterima. Untuk laporan yang menuntut konsistensi penuh, Anda mungkin butuh strategi lain: snapshot transaksi, materialized view, export job, atau pembatas waktu tetap.

Praktik yang membantu

Gunakan kolom sort yang immutability-nya tinggi, misalnya created_at daripada updated_at, jika kebutuhan bisnis memungkinkan.
Sertakan tie-breaker unik.
Simpan cursor secara eksplisit di API, bukan nomor halaman.
Jika perlu konsistensi sempit, tambahkan batas atas seperti created_at <= waktu_awal_request agar sesi paging melihat jendela data yang lebih stabil.

Batasan untuk sort non-unik dan sort kompleks

Sort non-unik wajib punya tie-breaker

Jika Anda mengurutkan hanya dengan created_at DESC, beberapa row bisa punya timestamp sama. Tanpa tie-breaker, urutan antar row tidak deterministik. Ini berbahaya untuk keyset pagination karena cursor menjadi ambigu.

Solusi umum:

ORDER BY created_at DESC, id DESC

dan cursor juga harus memuat kedua nilai itu.

Sort yang tidak cocok untuk keyset

Keyset pagination kurang cocok bila Anda perlu:

lompat langsung ke halaman 500,
sort dinamis ke banyak kombinasi yang jarang dipakai,
sort berdasarkan ekspresi berat atau agregasi yang sulit diindeks,
sort acak seperti ORDER BY RAND() atau padanannya,
hasil yang benar-benar stabil secara snapshot tanpa desain tambahan.

Dalam kasus seperti ini, OFFSET mungkin tetap dipakai, atau Anda perlu solusi lain seperti precomputed ranking, search engine, atau endpoint yang dibatasi pada kedalaman halaman tertentu.

Anti-pattern yang sering ditemui

OFFSET dalam untuk infinite scroll. Ini hampir selalu tanda desain yang bisa diperbaiki.
Mengandalkan nomor halaman untuk feed real-time. Hasil mudah bergeser.
ORDER BY kolom non-unik tanpa tie-breaker. Menyebabkan duplikasi atau missing rows.
Cursor hanya berisi id padahal urutan memakai created_at, id. Cursor tidak mewakili posisi sebenarnya.
Index tidak mengikuti urutan WHERE + ORDER BY. Query tetap harus melakukan banyak kerja.
Memakai updated_at sebagai sort utama pada data yang sering berubah. Item bisa meloncat-loncat antarhalaman.

Checklist migrasi dari OFFSET ke keyset pagination

Identifikasi endpoint yang paling mahal. Cari query list dengan OFFSET besar atau latency yang naik seiring kedalaman halaman.
Tentukan urutan yang stabil. Pilih kolom sort utama dan tie-breaker unik, misalnya created_at DESC, id DESC.
Pastikan ada index yang selaras. Gabungkan kolom WHERE dan ORDER BY dalam urutan yang masuk akal.
Ubah kontrak API. Ganti page dan offset menjadi cursor atau next_token.
Encode cursor dengan aman. Simpan nilai sort yang diperlukan, misalnya created_at dan id. Biasanya di-encode agar opaque bagi client.
Uji data berubah di tengah paging. Simulasikan insert dan delete antar-request untuk memastikan perilaku dipahami.
Bandingkan EXPLAIN sebelum dan sesudah. Verifikasi bahwa jumlah row yang diproses turun signifikan.
Sediakan fallback bila perlu. Untuk admin UI yang butuh lompat ke halaman tertentu, mungkin tetap pertahankan OFFSET di area terbatas.

Kapan keyset pagination tidak cocok

Meski sering lebih baik untuk performa, keyset pagination bukan jawaban untuk semua kasus. Pendekatan ini kurang cocok bila:

produk sangat bergantung pada navigasi halaman numerik acak,
pengguna harus bisa melompat ke halaman tertentu secara presisi,
sort berubah-ubah bebas tanpa index yang sesuai,
dataset hasil berasal dari query agregasi kompleks yang tidak mudah di-seek,
Anda memerlukan snapshot lintas banyak halaman tanpa perubahan hasil.

Untuk tabel kecil atau query admin sesekali, OFFSET bisa tetap cukup praktis dan lebih sederhana. Yang penting adalah memahami kapan kesederhanaan itu mulai dibayar mahal oleh pertumbuhan data.

Rekomendasi praktis

Jika aplikasi Anda memiliki feed, timeline, log, daftar transaksi, daftar event, atau endpoint infinite scroll di atas tabel yang terus bertambah, utamakan keyset pagination. Pilih kolom urut yang stabil, tambahkan tie-breaker unik, dan buat index yang selaras dengan WHERE serta ORDER BY.

Gunakan OFFSET bila kebutuhan utamanya adalah kesederhanaan, jumlah data masih kecil, atau UI memang memerlukan nomor halaman dan lompat acak. Namun, jika Anda mulai melihat query makin lambat seiring pertumbuhan data, jangan berhenti pada jawaban "sudah ada index". Periksa rencana eksekusi, hitung row yang dibaca, dan pertimbangkan migrasi ke keyset sebagai perubahan desain akses data, bukan sekadar optimasi kecil.

Quick Search

shopping_cart Keranjang