Pada aplikasi React Native berskala besar yang mengadopsi arsitektur local-first atau sinkronisasi offline, SQLite kerap menampung lebih dari 100.000 baris data lokal. Saat dataset membesar, latensi query analitik, pencarian, maupun agregasi pada antarmuka pengguna mulai meningkat secara signifikan. Penyebab dominan dari degradasi performa ini bukanlah transfer data melalui React Native bridge atau JSI, melainkan operasi internal SQLite yang disebut rowid table lookup.

Artikel ini membahas mekanisme teknis eliminasi table lookup tersebut menggunakan Covering Index, memvalidasinya melalui output EXPLAIN QUERY PLAN, serta mengukur dampak latensinya pada runtime React Native berbasis JSI seperti @op-engineering/op-sqlite.

Anatomi Bottleneck Latensi: B-Tree Table Lookup

Secara default, SQLite menyimpan tabel standar sebagai B-Tree yang diindeks berdasarkan integer 64-bit unik yaitu rowid. Ketika indeks sekunder (secondary index) dibuat pada sebuah kolom, SQLite membentuk struktur B-Tree terpisah yang menyimpan nilai kolom tersebut beserta pointer rowid yang bersesuaian.

Ketika query dijalankan menggunakan indeks sekunder reguler, SQLite melakukan proses dua langkah:

  1. Menelusuri B-Tree milik secondary index untuk menemukan kecocokan filter dan mengambil nilai rowid.
  2. Melakukan penelusuran ulang (dereferensi) ke dalam B-Tree tabel utama menggunakan rowid tersebut untuk mengambil kolom-kolom lain yang diminta pada klausul SELECT.

Langkah kedua inilah yang disebut table lookup. Ketika query menghasilkan ratusan hingga ribuan baris, langkah ini memicu random disk I/O berulang pada penyimpanan flash perangkat mobile (UFS atau eMMC). Meskipun database page berada di dalam cache memori, penelusuran pointer B-Tree traversal secara berulang tetap membebani CPU thread native secara signifikan.

Mekanisme Covering Index

Covering Index adalah indeks sekunder yang memuat seluruh kolom yang dibutuhkan oleh suatu query, baik kolom yang digunakan pada klausul WHERE, JOIN, ORDER BY, maupun SELECT.

Ketika semua data yang diminta sudah terkandung di dalam daun (leaf page) B-Tree indeks sekunder, SQLite mengevaluasi query sepenuhnya dari indeks tersebut tanpa pernah menyentuh B-Tree tabel utama. Operasi penelusuran ke tabel utama dieliminasi secara total.

Analisis Query Plan: SCAN vs USING INDEX vs USING COVERING INDEX

Untuk memastikan SQLite mengabaikan pembacaan tabel utama, evaluasi rencana eksekusi query menggunakan perintah EXPLAIN QUERY PLAN. Terdapat tiga klasifikasi utama keluaran operator:

  • SCAN TABLE [table_name]: Full table scan. SQLite membaca seluruh baris dalam tabel utama dari awal hingga akhir. Kompleksitas O(N). Menandakan ketiadaan indeks yang sesuai.
  • SEARCH TABLE [table_name] USING INDEX [index_name]: Indeks sekunder digunakan untuk menyaring baris, namun SQLite masih melakukan dereferensi B-Tree tabel utama via rowid untuk membaca kolom proyeksi.
  • SEARCH TABLE [table_name] USING COVERING INDEX [index_name]: Status optimal. Query diselesaikan sepenuhnya di dalam daun indeks sekunder. Nol akses ke B-Tree tabel utama.

Implementasi DDL Composite Index

Skenario berikut menggunakan tabel transaksi kasir atau log aktivitas lokal pada aplikasi mobile:

CREATE TABLE transactions (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    status TEXT NOT NULL,
    amount REAL NOT NULL,
    created_at INTEGER NOT NULL,
    notes TEXT,
    payload_json TEXT
);

Query target yang dieksekusi secara periodik pada dashboard lokal:

SELECT status, amount, created_at 
FROM transactions 
WHERE user_id = 'usr_102938' AND status = 'COMPLETED'
ORDER BY created_at DESC;

1. Indeks Naif (Secondary Index Standar)

Jika indeks dibuat hanya untuk kolom filter:

CREATE INDEX idx_transactions_filter ON transactions(user_id, status);

Verifikasi query plan:

EXPLAIN QUERY PLAN
SELECT status, amount, created_at 
FROM transactions 
WHERE user_id = 'usr_102938' AND status = 'COMPLETED'
ORDER BY created_at DESC;

-- Output:
-- SEARCH TABLE transactions USING INDEX idx_transactions_filter (user_id=? AND status=?)

Meskipun filter cepat, SQLite tetap membaca leaf table utama baris demi baris untuk mengambil amount dan created_at, lalu melakukan operasi sorting di memori (B-Tree sort).

2. Indeks Optimal (Covering Index)

Rancang composite index dengan menyusun urutan kolom secara presisi: filter equality pertama, filter ordering kedua, lalu kolom proyeksi tambahan di akhir:

CREATE INDEX idx_transactions_covering 
ON transactions(user_id, status, created_at DESC, amount);

Verifikasi ulang query plan:

EXPLAIN QUERY PLAN
SELECT status, amount, created_at 
FROM transactions 
WHERE user_id = 'usr_102938' AND status = 'COMPLETED'
ORDER BY created_at DESC;

-- Output:
-- SEARCH TABLE transactions USING COVERING INDEX idx_transactions_covering (user_id=? AND status=?)

SQLite menandai eksekusi dengan USING COVERING INDEX. Seluruh data (status, amount, created_at) dipetik langsung dari leaf page indeks dalam urutan yang sudah tersortir tanpa alokasi temporary sort B-Tree.

Eksekusi dan Komparasi Latensi pada React Native

Pengujian dilakukan menggunakan library JSI direct invocation @op-engineering/op-sqlite pada dataset sintetis 150.000 baris dalam tabel transactions.

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

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

function executeBenchmark() {
  const query = `
    SELECT status, amount, created_at 
    FROM transactions 
    WHERE user_id = ? AND status = ?
    ORDER BY created_at DESC
  `;
  const params = ['usr_102938', 'COMPLETED'];

  // Validasi Query Plan
  const plan = db.execute(`EXPLAIN QUERY PLAN ${query}`, params);
  console.log('Query Plan:', plan.rows?._array);

  // Pengukuran Latensi Native Execution
  const start = performance.now();
  const result = db.execute(query, params);
  const latency = performance.now() - start;

  console.log(`Rows fetched: ${result.rows?.length}, Latency: ${latency.toFixed(2)}ms`);
}

Hasil Pengukuran (Rata-rata 50 kali eksekusi pada Android Mid-Range SoC):

  • Tanpa Indeks (Table Scan): ~140ms - 190ms (UI thread frame drop berpotensi terjadi jika query berjalan di background pool yang jenuh).
  • Standard Composite Index (idx_transactions_filter): ~28ms - 45ms (Bottleneck pada dereferensi rowid dan page cache miss).
  • Covering Index (idx_transactions_covering): ~2.1ms - 4.5ms (Eliminasi total table lookup dan eliminasi cost disk read pada main table).

Trade-Off Arsitektural

Penerapan covering index menghasilkan percepatan signifikan pada operasi pembacaan (SELECT), tetapi membawa konsekuensi sistemik yang harus dikalkulasi secara matang:

1. Write Amplification

Setiap mutasi data melalui operasi INSERT, UPDATE, atau DELETE harus memperbarui B-Tree tabel utama sekaligus setiap indeks sekunder yang terasosiasi. Menambahkan kolom seperti amount ke dalam indeks berarti setiap perubahan nilai amount akan memicu penulisan ulang node B-Tree indeks, menyebabkan fragmentasi page dan I/O write amplification.

2. Disk Storage Overhead

Indeks sekunder menyimpan salinan data aktual dari kolom-kolom yang dicakup. Pada dataset dengan 200.000 baris, composite index yang mencakup empat kolom dapat menambah ukuran file .sqlite sebesar 8 MB hingga 20 MB tergantung tipe data. Pada perangkat mobile dengan kapasitas penyimpanan terbatas, inflasi ukuran database lokal ini harus dikontrol.

Gunakan covering index secara selektif pada query dengan frekuensi eksekusi sangat tinggi (hot queries) yang langsung memblokir interaksi antarmuka (UI-blocking) atau query yang menarik lebih dari 100 baris per eksekusi. Jangan gunakan covering index untuk query batch latar belakang (background sync) yang tidak berdampak langsung pada frame rate aplikasi.