Menjalankan SQLite pada lingkungan dengan batasan sumber daya ketat—seperti aplikasi embedded, IoT, atau browser engine pada Personal Digital Assistant (PDA)—memerlukan efisiensi alokasi memori yang ketat. Pada sistem dengan kapasitas RAM berkisar antara puluhan hingga ratusan megabita, query pagination yang buruk dapat memicu lonjakan penggunaan CPU, thrashing disk cache, hingga crash akibat Out-of-Memory (OOM).

Masalah performa ini umumnya berakar pada penggunaan klausa LIMIT ... OFFSET. Solusi arsitektural yang terbukti efisien untuk lingkungan minim sumber daya adalah keyset pagination SQLite (cursor-based pagination), yang memanfaatkan struktur B-tree index untuk traversal data berukuran konstan O(log N) tanpa memuat baris data yang dibuang ke memori kerja.

Bottleneck OFFSET pada Perangkat Embedded dan PDA

Pola paginasi konvensional umumnya mengandalkan offset numerik:

SELECT id, timestamp, payload 
FROM sensor_logs 
ORDER BY timestamp DESC 
LIMIT 50 OFFSET 100000;

Mekanisme internal SQLite mengharuskan engine untuk mengevaluasi dan memindai 100.000 baris pertama terlebih dahulu sebelum mengambil 50 baris yang diinginkan. SQLite harus membaca entri dari storage engine (pager), memuat halaman B-tree ke dalam cache memori (cache_size), melakukan parsing record, lalu membuang record-record tersebut satu per satu.

Dampaknya pada lingkungan ultra-low-memory:

  • Cache Eviction Brutal: Membaca ratusan ribu baris yang dibuang mendepak halaman database esensial dari page cache SQLite yang terbatas.
  • CPU Spikes: Pemrosesan iterasi row engine membebani CPU embedded yang memiliki clock speed rendah.
  • Risiko Alokasi Heap: Jika query membutuhkan pengurutan pada kolom non-indeks, SQLite mengalokasikan memori dinamis untuk temporary sorter (B-Tree ephemeral di heap atau temp storage). Jika alokasi melebihi kapasitas RAM, proses akan langsung dihentikan oleh OS melalui OOM killer.

Implementasi Keyset Pagination dengan Composite Index

Keyset pagination mengeliminasi pemindaian baris data yang tidak relevan dengan mengubah offset menjadi filter kondisi WHERE berdasarkan nilai kolom terakhir dari halaman sebelumnya.

1. Schema dan Composite B-Tree Index

Untuk mendukung pagination stabil dan deterministic, diperlukan composite index yang mencakup kolom pengurutan dan primary key sebagai tie-breaker (penentu unik jika nilai kolom sorting sama):

CREATE TABLE sensor_logs (
    id INTEGER PRIMARY KEY,
    timestamp INTEGER NOT NULL,
    sensor_id INTEGER NOT NULL,
    payload TEXT NOT NULL
);

-- Composite index untuk ORDER BY timestamp DESC, id DESC
CREATE INDEX idx_sensor_logs_cursor ON sensor_logs(timestamp DESC, id DESC);

2. Query Keyset Terstruktur

Untuk halaman pertama:

SELECT id, timestamp, payload 
FROM sensor_logs 
ORDER BY timestamp DESC, id DESC 
LIMIT 50;

Dapatkan nilai timestamp dan id dari baris terakhir pada result set halaman pertama (misal: timestamp = 1710005400, id = 45912). Gunakan tuple comparison (row value syntax) untuk mengambil halaman berikutnya:

SELECT id, timestamp, payload 
FROM sensor_logs 
WHERE (timestamp, id) < (1710005400, 45912)
ORDER BY timestamp DESC, id DESC 
LIMIT 50;

Alternatif tanpa tuple syntax (didukung di seluruh engine lama):

SELECT id, timestamp, payload 
FROM sensor_logs 
WHERE timestamp < 1710005400 
   OR (timestamp = 1710005400 AND id < 45912)
ORDER BY timestamp DESC, id DESC 
LIMIT 50;
Catatan: SQLite 3.15.0 ke atas telah mendukung row value comparison secara native. Engine mengeksekusinya secara efisien langsung melalui composite index tanpa mengevaluasi ekspresi OR secara redundant.

Analisis Execution Plan: OFFSET vs Keyset

Jalankan perintah EXPLAIN QUERY PLAN untuk membandingkan alur kerja SQLite engine.

Eksekusi Menggunakan OFFSET:

EXPLAIN QUERY PLAN 
SELECT id, timestamp, payload 
FROM sensor_logs 
ORDER BY timestamp DESC, id DESC 
LIMIT 50 OFFSET 100000;

-- Output:
-- SCAN sensor_logs USING INDEX idx_sensor_logs_cursor

Meskipun index digunakan, output SCAN menandakan SQLite tetap melintasi 100.050 entri daun B-tree. Operasi I/O memory pager tetap dilakukan untuk memverifikasi visibilitas data.

Eksekusi Menggunakan Keyset Pagination:

EXPLAIN QUERY PLAN 
SELECT id, timestamp, payload 
FROM sensor_logs 
WHERE (timestamp, id) < (1710005400, 45912)
ORDER BY timestamp DESC, id DESC 
LIMIT 50;

-- Output:
-- SEARCH sensor_logs USING INDEX idx_sensor_logs_cursor (timestamp<? AND (timestamp=? AND id<?))

Operasi berubah dari SCAN menjadi SEARCH. SQLite melakukan direct binary search di dalam B-tree untuk menemukan posisi tepat cursor dalam waktu O(log N), lalu segera membaca hanya 50 entri berikutnya. Kompleksitas pembacaan data bernilai konstan O(1) relatif terhadap kedalaman halaman.

Karakteristik I/O dan Efisiensi Memori

Pada dataset dengan 500.000 record pada SQLite dengan page size default 4096 byte:

  • Pendekatan OFFSET (Halaman Dalam): SQLite memuat ribuan database pages ke dalam pager cache. Jika PRAGMA cache_size dibatasi pada nilai rendah (misal: -2000 atau ~2MB), terjadi page faults berulang karena page lama di-evict dan di-load kembali secara intensif.
  • Pendekatan Keyset: SQLite hanya memuat tree branch nodes dari root ke leaf node (umumnya hanya berkisar antara 3 hingga 5 level kedalaman B-tree), ditambah halaman data yang menampung 50 baris target. Konsumsi memory page cache stabil di bawah 100KB per query.

Mitigasi Edge Cases pada Keyset SQLite

1. Tie-Breaker untuk Kolom Non-Unik

Jika pengurutan dilakukan pada kolom berulang tanpa tie-breaker unik (misalnya hanya WHERE timestamp < ?), data yang memiliki timestamp sama persis dengan baris pagination terakhir akan terlewati secara permanen. Menambahkan primary key id yang bersifat unik ke dalam composite index dan klausa perbandingan menjamin determinisme pagination.

2. Penanganan Nilai NULL

Secara default, SQLite menganggap nilai NULL lebih kecil dari nilai apa pun saat perbandingan B-tree. Jika kolom sorting dapat bernilai NULL, query filter perbandingan keyset biasa akan melewatkan baris atau gagal membaca urutan secara benar. Solusi terbaik pada sistem minim memori adalah menerapkan constraint NOT NULL pada kolom target pengurutan atau menentukan default fallback value.

3. Pagination Dua Arah (Bidirectional Paging)

Untuk mengimplementasikan tombol navigasi mundur (Previous Page) tanpa beralih ke offset, balik arah operator pembanding dan sorting, kemudian balik urutan array di sisi aplikasi klien:

-- Mengambil halaman sebelumnya secara efisien:
SELECT id, timestamp, payload 
FROM sensor_logs 
WHERE (timestamp, id) > (1710005400, 45912)
ORDER BY timestamp ASC, id ASC 
LIMIT 50;

Konfigurasi SQLite Pendukung untuk Lingkungan Ultra-Low-Memory

Terapkan pragma directives berikut sebelum menjalankan operasi paginasi:

-- Membatasi page cache SQLite menjadi sekitar 1MB (250 halaman @ 4KB)
PRAGMA cache_size = -250;

-- Mencegah disk temporary sorting membebani RAM
PRAGMA temp_store = MEMORY;

-- Nonaktifkan memory-mapped I/O jika virtual memory PDA sangat terbatas
PRAGMA mmap_size = 0;

Keyset pagination menggeser beban traversal data dari runtime memory ke struktur indeks yang stabil. Hal ini memastikan response time query tetap berada di rentang milidetik konstan serta menjaga footprint memori database engine tetap stabil di perangkat dengan resource hardware terbatas.