Masalah Performa OFFSET Pagination pada Local DB

Saat aplikasi React Native memuat puluhan ribu baris data offline (misalnya data cache transaksi atau riwayat chat), implementasi infinite scroll umum menggunakan query berbasis LIMIT dan OFFSET:

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

Mekanisme ini tidak efisien. SQLite harus membaca dan melintasi 10.020 baris dari storage sebelum membuang 10.000 baris pertama untuk menyajikan 20 baris target. Kompleksitas komputasinya adalah O(N).

Ketika pengguna melakukan scroll cepat pada FlashList atau FlatList, eksekusi query deep-offset memicu I/O disk tinggi dan menahan thread database. Meskipun query dijalankan secara asynchronous melalui JSI (JavaScript Interface) pada pustaka modern, waktu eksekusi yang melonjak dari ~2ms ke >100ms menyebabkan antrean eksekusi menumpuk, micro-stutter, dan UI jank (frame rate turun di bawah 60 FPS).

Diagnosis Menggunakan EXPLAIN QUERY PLAN

Untuk mengidentifikasi inefisiensi query sebelum optimasi, periksa eksekusi SQLite engine melalui EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN 
SELECT id, title, created_at 
FROM transactions 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

Jika tabel tidak memiliki indeks yang sesuai, output akan menampilkan:

SCAN transactions
USE TEMP B-TREE FOR ORDER BY

Pola SCAN menandakan Full Table Scan. Keberadaan TEMP B-TREE berarti SQLite menyalin seluruh data ke struktur memori sementara hanya untuk melakukan sorting. Ini adalah akar penyebab frame drop pada perangkat mobile dengan spesifikasi CPU/storage terbatas.

Solusi: Keyset Pagination (Seek Method)

Keyset pagination (seek method) mengeliminasi pemindaian baris terdahulu dengan memfilter baris menggunakan titik henti terakhir (cursor). Pendekatan ini mengubah kompleksitas dari O(N) menjadi O(log N) atau O(1) jika memanfaatkan traversal B-Tree secara langsung.

1. Pembuatan Composite Index

Kolom pengurutan membutuhkan composite index. Karena field timestamp seperti created_at berpotensi memiliki nilai kembar (identik), tambahkan primary key id sebagai tie-breaker penjamin keunikan urutan:

CREATE INDEX idx_transactions_created_at_id 
ON transactions (created_at DESC, id DESC);

2. Pola Query Keyset

Query awal untuk halaman pertama:

SELECT id, title, created_at 
FROM transactions 
ORDER BY created_at DESC, id DESC 
LIMIT 20;

Query halaman berikutnya menerima nilai created_at dan id dari record terakhir baris sebelumnya:

SELECT id, title, created_at 
FROM transactions 
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;
Catatan: SQLite 3.15+ mendukung row-value comparisons secara native dengan sintaks WHERE (created_at, id) < (:last_created_at, :last_id). Namun, sintaks eksplisit OR di atas tetap lebih aman pada engine mobile lama guna memastikan index range scan terpilih secara konsisten.

Validasi kembali dengan EXPLAIN QUERY PLAN:

SEARCH transactions USING INDEX idx_transactions_created_at_id (created_at<?)

Status SEARCH mengonfirmasi bahwa SQLite langsung melompat ke lokasi B-Tree yang tepat tanpa membaca baris yang sudah diabaikan.

Implementasi pada React Native (op-sqlite)

Berikut implementasi fungsi repositori menggunakan @op-engineering/op-sqlite (atau dapat diadaptasikan ke expo-sqlite modern berbasis JSI):

import { open } from '@op-engineering/op-sqlite';

const db = open({ name: 'records.db' });

export interface TransactionRecord {
  id: number;
  title: string;
  created_at: number;
}

export interface KeysetCursor {
  lastCreatedAt: number;
  lastId: number;
}

export const fetchTransactionsKeyset = async (
  pageSize: number,
  cursor?: KeysetCursor
): Promise<TransactionRecord[]> => {
  if (!cursor) {
    const result = await db.executeAsync(
      `SELECT id, title, created_at 
       FROM transactions 
       ORDER BY created_at DESC, id DESC 
       LIMIT ?;`,
      [pageSize]
    );
    return (result.rows?._array ?? []) as TransactionRecord[];
  }

  const result = await db.executeAsync(
    `SELECT id, title, created_at 
     FROM transactions 
     WHERE (created_at < ?) 
        OR (created_at = ? AND id < ?)
     ORDER BY created_at DESC, id DESC 
     LIMIT ?;`,
    [cursor.lastCreatedAt, cursor.lastCreatedAt, cursor.lastId, pageSize]
  );

  return (result.rows?._array ?? []) as TransactionRecord[];
};

Integrasi dengan List Component

Saat menyambungkan fungsi ini ke FlashList, simpan pointer cursor terakhir dalam React state atau ref:

import React, { useState, useCallback, useRef } from 'react';
import { FlashList } from '@shopify/flash-list';
import { fetchTransactionsKeyset, TransactionRecord, KeysetCursor } from './db';

const PAGE_SIZE = 25;

export const TransactionList = () => {
  const [data, setData] = useState<TransactionRecord[]>([]);
  const [isLoading, setIsLoading] = useState(false);
  const cursorRef = useRef<KeysetCursor | null>(null);
  const hasMoreRef = useRef(true);

  const loadMore = useCallback(async () => {
    if (isLoading || !hasMoreRef.current) return;
    setIsLoading(true);

    try {
      const nextRows = await fetchTransactionsKeyset(PAGE_SIZE, cursorRef.current ?? undefined);
      
      if (nextRows.length < PAGE_SIZE) {
        hasMoreRef.current = false;
      }

      if (nextRows.length > 0) {
        const lastItem = nextRows[nextRows.length - 1];
        cursorRef.current = {
          lastCreatedAt: lastItem.created_at,
          lastId: lastItem.id,
        };
        setData((prev) => [...prev, ...nextRows]);
      }
    } finally {
      setIsLoading(false);
    }
  }, [isLoading]);

  return (
    <FlashList
      data={data}
      estimatedItemSize={64}
      keyExtractor={(item) => item.id.toString()}
      onEndReached={loadMore}
      onEndReachedThreshold={0.5}
      renderItem={({ item }) => (
        <ItemRow title={item.title} timestamp={item.created_at} />
      )}
    />
  );
};

Perbandingan Latensi dan Stabilitas FPS

Pada pengujian lokal dengan database berisi 50.000 baris di perangkat mid-range Android:

  • Offset Query (Halaman 1, Offset 0): ~1.2ms
  • Offset Query (Halaman 500, Offset 10.000): ~85ms - 130ms (memicu drop ke ~42 FPS saat scroll cepat)
  • Keyset Query (Awal vs Halaman 500+): Konstan stabil di rentang 1.1ms - 2.4ms (FPS bertahan di rentang 58 - 60 FPS).

Trade-offs Keyset Pagination

  • Tidak mendukung arbitrary page jumping: Pengguna tidak bisa langsung melompat dari halaman 1 ke halaman 20. Struktur ini khusus untuk infinite scrolling atau tombol Next/Previous.
  • Ketergantungan sorting dua arah: Untuk navigasi mundur (previous), arah klausa perbandingan > dan ORDER BY ... ASC harus dibalik secara terprogram, kemudian array hasil dibalik (reverse) kembali di client.