Debug Pagination API: Mencegah Data Hilang karena OFFSET saat Tabel Berubah

Debug pagination API perlu dimulai dari asumsi bahwa isi dan urutan tabel dapat berubah di antara dua permintaan. Jika endpoint menggunakan LIMIT dan OFFSET, insert baru dapat menggeser posisi baris sehingga klien menerima data yang sama dua kali atau tidak menerima data yang semestinya berada pada halaman berikutnya.

Solusi yang lebih stabil adalah keyset pagination: halaman berikutnya ditentukan berdasarkan nilai baris terakhir yang telah diterima, bukan berdasarkan nomor posisi. Untuk data yang diurutkan menurut waktu, cursor sebaiknya menggunakan gabungan created_at dan id agar urutannya deterministik meskipun beberapa baris memiliki timestamp yang sama.

Gejala dari sisi klien

Misalkan endpoint mengurutkan posting terbaru terlebih dahulu dan mengembalikan tiga baris per halaman:

GET /api/posts?page=1&limit=3
GET /api/posts?page=2&limit=3

Gejala yang umum terlihat pada aplikasi klien meliputi:

Item terakhir halaman pertama muncul lagi pada halaman kedua.
Jumlah item unik lebih kecil daripada jumlah item yang telah diunduh.
Infinite scroll menampilkan kartu yang sama dua kali.
Item tertentu tidak pernah terlihat, terutama ketika ada insert, delete, atau perubahan kolom pengurutan.
Masalah sulit direproduksi pada lingkungan pengembangan karena tabel jarang berubah secara bersamaan.

Klien kadang menyembunyikan masalah dengan melakukan deduplikasi berdasarkan ID. Tindakan tersebut mencegah tampilan ganda, tetapi tidak memperbaiki baris yang terlewat dan dapat membuat jumlah item per halaman tampak tidak konsisten.

Mereproduksi masalah LIMIT/OFFSET

Kondisi awal

Gunakan pengurutan terbaru terlebih dahulu. Contoh berikut sengaja menyertakan id sebagai tie-breaker agar fokus pengujian berada pada pergeseran posisi:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 3 OFFSET 0;

Anggap hasil awal yang terurut adalah:

id: 106, 105, 104, 103, 102, 101

Permintaan halaman pertama mengembalikan:

106, 105, 104

Insert di antara dua permintaan

Sebelum klien meminta halaman kedua, proses lain membuat baris baru dengan ID 107. Urutan tabel kini menjadi:

107, 106, 105, 104, 103, 102, 101

Halaman kedua dijalankan dengan query berikut:

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

Hasilnya adalah:

104, 103, 102

ID 104 muncul kembali karena insert baru menggeser semua posisi setelah halaman pertama. Jika klien hanya mengambil dua halaman atau menghentikan pagination berdasarkan jumlah tertentu, sebagian data lama juga dapat tidak tercakup. Delete atau perubahan nilai pengurutan sebelum posisi offset dapat secara langsung membuat baris terlewat.

Insert pada posisi terdepan umumnya menyebabkan duplikasi dalam contoh urutan menurun ini. Kombinasi insert, delete, pembaruan kolom pengurutan, atau urutan yang tidak deterministik dapat menghasilkan duplikasi maupun data terlewat.

Root cause dan diagnosis pagination API

OFFSET menunjuk posisi, bukan identitas baris

OFFSET 3 berarti database harus melewati tiga baris pertama dari hasil saat query dijalankan. Nilai tersebut tidak menyatakan bahwa halaman berikutnya harus dimulai setelah ID 104. Ketika baris ditambahkan atau dihapus, posisi relatifnya ikut berubah.

Masalah menjadi lebih buruk jika query hanya menggunakan:

ORDER BY created_at DESC

Beberapa baris dapat memiliki created_at yang sama. Tanpa tie-breaker unik, database tidak diwajibkan mengembalikan baris-baris tersebut dalam urutan yang konsisten. Query tanpa ORDER BY sama sekali bahkan tidak memiliki jaminan urutan.

Catat parameter dan batas halaman

Tambahkan log terstruktur untuk membandingkan hasil antarpermintaan. Informasi yang berguna antara lain:

request_id dan identitas endpoint;
limit, offset, atau cursor yang telah didekode dan divalidasi;
filter serta arah pengurutan;
jumlah baris yang dikembalikan;
pasangan created_at dan id dari baris pertama serta terakhir;
daftar ID pada lingkungan debugging, dengan mempertimbangkan ukuran log dan sensitivitas data.

{
  "request_id": "req-82f1",
  "limit": 3,
  "offset": 3,
  "order": "created_at DESC, id DESC",
  "first_key": ["2025-02-18T09:42:10.120000Z", "104"],
  "last_key": ["2025-02-18T09:39:02.510000Z", "102"],
  "row_ids": ["104", "103", "102"]
}

Bandingkan last_key halaman pertama dengan first_key halaman kedua. Kemunculan ID yang sama pada kedua halaman menunjukkan bahwa posisi hasil telah bergeser. Jalankan juga SQL yang sama secara langsung dan periksa apakah seluruh kolom ORDER BY sudah dicatat dalam log.

Perbaikan dengan keyset pagination

Query halaman pertama

Untuk urutan terbaru terlebih dahulu, halaman pertama tidak memerlukan cursor:

SELECT id, created_at, title
FROM posts
WHERE status = :status
ORDER BY created_at DESC, id DESC
LIMIT :fetch_limit;

:fetch_limit biasanya diisi dengan limit + 1. Baris tambahan digunakan untuk menentukan apakah halaman berikutnya tersedia, lalu tidak disertakan dalam respons.

Query halaman berikutnya

Cursor dibuat dari created_at dan id milik baris terakhir yang benar-benar dikirim kepada klien. Untuk urutan menurun, halaman berikutnya hanya mengambil baris dengan pasangan kunci yang lebih kecil:

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

Beberapa database mendukung perbandingan tuple, misalnya (created_at, id) < (:created_at, :id). Bentuk kondisi eksplisit di atas lebih mudah dipindahkan antar-database dan memperjelas aturan tie-breaker.

Insert baru dengan nilai yang lebih besar daripada cursor tidak menggeser batas halaman berikutnya. Karena query mencari kunci yang benar-benar berada setelah baris terakhir, ID yang telah dikirim tidak akan muncul lagi.

Format dan pembuatan cursor

Cursor sebaiknya diperlakukan sebagai token buram oleh klien. Payload internal dapat berbentuk JSON berikut, lalu dikodekan dengan Base64 URL-safe:

{
  "v": 1,
  "created_at": "2025-02-18T09:42:10.120000Z",
  "id": "104",
  "direction": "next"
}

ID disimpan sebagai string agar aman untuk runtime yang tidak dapat merepresentasikan seluruh rentang integer database secara presisi. Timestamp harus menggunakan format UTC kanonis dengan presisi yang sesuai dengan kolom database.

Base64 hanya mengodekan data dan bukan mekanisme keamanan. Jika manipulasi cursor dapat memengaruhi otorisasi, filter, atau beban query, tandatangani payload dengan HMAC atau gunakan token acak yang merujuk ke state di server. Jangan memasukkan data sensitif karena isi Base64 mudah dibaca.

Contoh respons endpoint:

{
  "data": [
    {"id": "106", "created_at": "2025-02-18T09:45:00.000000Z"},
    {"id": "105", "created_at": "2025-02-18T09:44:00.000000Z"},
    {"id": "104", "created_at": "2025-02-18T09:42:10.120000Z"}
  ],
  "pagination": {
    "next_cursor": "eyJ2IjoxLC4uLn0",
    "has_more": true
  }
}

Validasi input cursor

Server harus menolak cursor tidak valid dengan respons 400 Bad Request, bukan meneruskan nilainya langsung ke SQL. Proses validasi minimal mencakup:

Batasi panjang token sebelum proses decode.
Decode Base64 URL-safe dan parse JSON dengan penanganan error.
Pastikan hanya versi cursor yang didukung yang diterima.
Validasi created_at sebagai timestamp yang valid dan normalisasikan ke UTC.
Validasi id sebagai tipe yang sesuai dengan primary key.
Validasi arah pagination dan batas limit.
Gunakan parameter terikat pada SQL; jangan menyusun query dengan konkatenasi string.
Pastikan cursor digunakan bersama filter dan tenant yang sama dengan halaman pertama.

Filter dapat disertakan dalam payload yang ditandatangani atau direpresentasikan sebagai hash. Ini mencegah cursor untuk daftar status=published digunakan kembali pada daftar dengan filter berbeda.

Arah urutan dan halaman sebelumnya

Untuk tampilan DESC, halaman berikutnya menggunakan operator <. Halaman sebelumnya menggunakan operator >, menjalankan query dalam urutan ASC, kemudian membalik hasil sebelum mengirimkannya agar tampilan tetap DESC:

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

Implementasi dua arah memerlukan definisi next_cursor, previous_cursor, dan has_more yang konsisten. Jika produk hanya membutuhkan infinite scroll maju, pagination satu arah lebih sederhana dan lebih sulit disalahgunakan.

Indeks, timestamp yang sama, dan batasan

Gunakan indeks yang sesuai

Agar database tidak melakukan pemindaian dan pengurutan besar untuk setiap halaman, tambahkan indeks yang mengikuti filter utama dan kolom pengurutan. Untuk query tanpa filter lain:

CREATE INDEX idx_posts_created_at_id
ON posts (created_at DESC, id DESC);

Jika seluruh query selalu dibatasi oleh tenant, indeks dapat dimulai dengan kolom tenant:

CREATE INDEX idx_posts_tenant_created_at_id
ON posts (tenant_id, created_at DESC, id DESC);

Susunan indeks harus mengikuti pola query aktual. Periksa rencana eksekusi database karena penambahan banyak kolom filter atau indeks yang terlalu lebar mempunyai biaya penyimpanan dan penulisan.

Tangani timestamp yang sama

created_at saja bukan cursor yang aman. Dua transaksi dapat membuat baris pada timestamp yang sama, terutama jika presisi kolom lebih rendah daripada laju insert. id menjadi tie-breaker unik sehingga setiap baris memiliki posisi total yang tidak ambigu.

Kolom pengurutan idealnya:

tidak bernilai NULL;
tidak berubah setelah baris dibuat;
memiliki tie-breaker unik;
menggunakan representasi timestamp yang konsisten.

Jika created_at dapat diedit, sebuah baris masih dapat berpindah melintasi cursor. Gunakan kolom urutan yang immutable, seperti waktu ingest atau sequence khusus, bila stabilitas traversal lebih penting.

Trade-off keyset pagination

Kelebihan: stabil terhadap insert di depan cursor, menghindari duplikasi akibat pergeseran posisi, dan biasanya tidak perlu melewati seluruh baris sebelumnya.
Keterbatasan: tidak mendukung lompatan langsung ke halaman ke-500 tanpa cursor antara.
Kontrak API berubah: klien menyimpan cursor, bukan menghitung nomor halaman.
Filter harus konsisten: perubahan filter atau arah urutan memerlukan traversal baru.
Bukan snapshot otomatis: insert baru yang posisinya berada setelah cursor masih dapat muncul pada halaman berikutnya.

Keyset pagination menjamin batas traversal yang stabil, tetapi tidak membuat snapshot lintas-request. Jika seluruh hasil harus mencerminkan kondisi pada satu waktu tertentu, pertimbangkan cutoff yang immutable, materialisasi daftar hasil, atau mekanisme snapshot khusus. Cutoff berdasarkan created_at saja tidak cukup jika aplikasi mengizinkan insert dengan timestamp lama.

LIMIT/OFFSET masih sesuai untuk tabel kecil, data yang praktis statis, atau antarmuka administratif yang memerlukan akses berdasarkan nomor halaman. Untuk feed aktif dan infinite scroll, keyset pagination biasanya lebih sesuai.

Pengujian regresi tanpa data hilang atau ganda

Pengujian harus memverifikasi identitas dan urutan baris, bukan hanya panjang respons. Buat data uji dengan beberapa nilai created_at yang sama agar tie-breaker ikut diuji.

Skenario utama

Buat sekumpulan baris awal dan simpan semua ID sebagai baseline.
Ambil halaman pertama dengan limit kecil.
Lakukan insert concurrent dengan timestamp lebih baru daripada baris pertama.
Ambil seluruh halaman berikutnya menggunakan cursor.
Pastikan tidak ada ID yang muncul lebih dari sekali.
Pastikan seluruh ID baseline ditemukan tepat satu kali.
Pastikan urutan setiap pasangan (created_at, id) selalu menurun.
Ulangi dengan beberapa baris yang memiliki timestamp identik.

Contoh pseudocode pengujian:

baselineIds = seedPostsWithDuplicateTimestamps()

page = api.listPosts({ limit: 3 })
received = page.data.map(row => row.id)

insertPost({ created_at: now(), title: 'Concurrent insert' })

while (page.pagination.has_more) {
  page = api.listPosts({
    limit: 3,
    cursor: page.pagination.next_cursor
  })
  received.push(...page.data.map(row => row.id))
}

assertEqual(size(unique(received)), received.length)
assertEveryIdAppearsExactlyOnce(received, baselineIds)
assertSortedDescendingByCreatedAtAndId(received)

Tambahkan pula pengujian untuk cursor rusak, versi cursor yang tidak didukung, limit berlebihan, penggunaan cursor dengan filter berbeda, halaman terakhir, dan hasil kosong. Jalankan pengujian OFFSET lama sebagai bukti reproduksi agar tim memahami kegagalan yang diperbaiki.

Untuk skenario concurrent yang deterministik, gunakan sinkronisasi dalam pengujian: tahan request kedua sampai insert selesai, lalu lanjutkan query. Hindari hanya mengandalkan jeda waktu karena dapat membuat pengujian tidak stabil.

Kesimpulan

Data hilang atau ganda pada pagination API bukan sekadar masalah tampilan klien. Penyebab utamanya adalah penggunaan posisi OFFSET pada kumpulan data yang berubah atau penggunaan ORDER BY yang tidak deterministik.

Gunakan keyset pagination dengan urutan total created_at DESC, id DESC, bentuk cursor dari baris terakhir, validasi token secara ketat, dan sediakan indeks yang sesuai. Lengkapi perbaikan dengan log batas halaman serta pengujian concurrent insert agar regresi dapat terdeteksi sebelum mencapai produksi.

Quick Search

shopping_cart Keranjang