Gejala UI Jank dan Diagnosis Temp B-Tree

Saat merender list panjang menggunakan komponen seperti FlatList atau FlashList di React Native, penurunan frame rate (UI jank) sering kali bukan disebabkan oleh rendering pipeline React, melainkan blocking operasi database pada thread native atau JSI (JavaScript Interface). Masalah ini memuncak ketika SQLite dipaksa mengeksekusi sorting di memori atau disk transien.

Ketika query memuat klausul WHERE dan ORDER BY tanpa indeks yang selaras, SQLite tidak dapat membaca baris data secara langsung dalam urutan yang diinginkan. SQLite terpaksa mengumpulkan seluruh baris yang lolos filter, lalu mengalokasikan struktur data sementara bernama Temp B-Tree untuk mengurutkan hasil sebelum mengembalikan data dengan batas LIMIT.

Untuk membuktikan keberadaan operasi ini, jalankan query melalui EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN
SELECT id, account_id, amount, created_at
FROM transactions
WHERE account_id = 'acc_123' AND status = 'COMPLETED'
ORDER BY created_at DESC
LIMIT 20;

Jika SQLite menghasilkan output seperti:

SCAN transactions
USE TEMP B-TREE FOR ORDER BY

Artinya engine melakukan full scan pada tabel dan mengalokasikan memori untuk sorting. Pada perangkat mobile dengan CPU hemat daya, proses ini memakan puluhan hingga ratusan milidetik, menahan execution thread dan mendegradasi responsivitas antarmuka.

Prinsip ESR: Equality, Sort, Range

Untuk menghilangkan pembentukan Temp B-Tree sepenuhnya, indeks harus dirancang mengikuti urutan preseden ESR (Equality, Sort, Range):

  1. Equality (=, IS): Kolom yang dicocokkan dengan nilai tunggal harus ditempatkan di posisi paling awal pada indeks. Kolom ini langsung membatasi pencarian ke satu subset leaf node B-tree.
  2. Sort (ORDER BY): Kolom yang menentukan urutan harus berada tepat setelah kolom equality. Dengan menaruh kolom sort di sini, B-tree sudah tersusun rapi sesuai urutan pembacaan data, sehingga SQLite dapat melakukan traversal langsung tanpa sorting sekunder.
  3. Range (<, >, BETWEEN, LIKE 'prefix%'): Kolom perbandingan rentang harus diletakkan paling akhir. Ketika SQLite mulai memindai rentang nilai pada satu kolom indeks, kolom-kolom setelahnya tidak lagi berada dalam urutan yang terprediksi secara global, sehingga pengurutan B-tree tidak dapat dimanfaatkan untuk ORDER BY berikutnya.

Implementasi pada React Native (op-sqlite)

Berikut implementasi konkret migrasi DDL indeks komposit dan pengujian query plan menggunakan library @op-engineering/op-sqlite:

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

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

// 1. Eksekusi DDL Indeks Komposit berbasis prinsip Equality -> Sort
export function applyOptimizationIndexes(): void {
  db.execute(`
    CREATE INDEX IF NOT EXISTS idx_transactions_acc_status_created 
    ON transactions (account_id, status, created_at DESC);
  `);
}

// 2. Query data transaksi teroptimasi
export function getRecentTransactions(accountId: string) {
  const query = `
    SELECT id, account_id, status, amount, created_at
    FROM transactions
    WHERE account_id = ? AND status = 'COMPLETED'
    ORDER BY created_at DESC
    LIMIT 20;
  `;

  // Verifikasi Rencana Eksekusi
  const plan = db.execute(`EXPLAIN QUERY PLAN ${query}`, [accountId]);
  console.log('Query Plan:', JSON.stringify(plan.rows?._array, null, 2));

  // Eksekusi data
  return db.execute(query, [accountId]);
}

Evaluasi Rencana Eksekusi Baru

Setelah indeks (account_id, status, created_at DESC) diterapkan, eksekusi query plan berubah menjadi:

SEARCH transactions USING INDEX idx_transactions_acc_status_created (account_id=? AND status=?)

Label USE TEMP B-TREE FOR ORDER BY tereliminasi secara total. SQLite melompat langsung ke entri index yang memenuhi kriteria account_id dan status, lalu membaca 20 baris pertama secara berurutan sesuai arah created_at DESC (streaming read). Latensi eksekusi turun dari rata-rata ~45ms (pada tabel berisi 50.000 data) menjadi <1ms.

Trade-Off: Write Amplification dan Storage Overhead

Penerapan indeks komposit bukan tanpa konsekuensi. Setiap indeks baru menambahkan beban write amplification pada operasi INSERT, UPDATE, dan DELETE:

  • I/O Amplification: Setiap kali baris baru ditambahkan ke tabel transactions, engine SQLite wajib memperbarui B-Tree tabel utama dan B-Tree indeks komposit secara atomik. Ini meningkatkan frekuensi sinkronisasi WAL (Write-Ahead Log).
  • File Size Bloat: Indeks komposit multi-kolom menyimpan salinan nilai kolom tersebut di leaf node indeks, memakan ruang disk yang signifikan pada perangkat dengan kapasitas penyimpanan terbatas.
  • Maintenance Cost: Modifikasi kolom yang terdaftar dalam indeks (misalnya update kolom status) memicu penghapusan dan penyisipan ulang node pada indeks, memicu fragmentasi halaman database jika checkpoint WAL jarang dijalankan.

Batasi pembuatan indeks hanya untuk query yang menjadi bottleneck pada alur navigasi utama atau virtualized list.

Kesalahan Umum dalam Indeks Multi-Kolom

1. Urutan Kolom Terbalik (Sort Sebelum Equality)

-- SALAH: Menaruh kolom sort di awal
CREATE INDEX idx_wrong ON transactions (created_at DESC, account_id, status);

Meskipun indeks memuat semua kolom yang dibutuhkan, SQLite harus memindai B-Tree dari awal hingga akhir berdasarkan waktu, lalu menyaring account_id dan status satu per satu. Indeks ini tidak bekerja efisien untuk filtering.

2. Menaruh Kolom Range Sebelum Kolom Sort

Perhatikan kasus filter saldo:

SELECT * FROM transactions 
WHERE account_id = 'acc_123' AND amount > 10000 
ORDER BY created_at DESC;

Jika dibuat indeks (account_id, amount, created_at DESC), SQLite menggunakan amount > 10000 sebagai batas scan. Karena baris-baris pada range tersebut tersusun berdasarkan nilai amount, urutan created_at DESC menjadi acak. Akibatnya, SQLite tetap memunculkan USE TEMP B-TREE FOR ORDER BY. Solusinya: pilih indeks (account_id, created_at DESC) jika query lebih membutuhkan eliminasi sort, lalu biarkan evaluasi amount > 10000 dilakukan saat pembacaan baris.

3. Mismatch Arah Sortir (ASC vs DESC)

SQLite mendukung pembacaan indeks secara terbalik (reverse scan). Indeks (created_at ASC) tetap bisa melayani ORDER BY created_at DESC tanpa Temp B-Tree. Namun, jika query menggunakan sorting multi-kolom seperti ORDER BY priority ASC, created_at DESC, indeks harus dideklarasikan dengan arah yang persis sama: (priority ASC, created_at DESC). Ketidakcocokan kombinasi arah arah pada multi-kolom sorting otomatis memicu Temp B-Tree.