Masalah Indeks Standar pada Entitas Bersiklus

Aplikasi mobile offline-first mengelola entitas yang memiliki siklus hidup (lifecycle), seperti antrean sinkronisasi (sync queue) atau data berfitur soft delete. Masalah muncul saat developer membuat indeks B-tree standar pada kolom penanda status, misalnya sync_status atau deleted_at.

Pada tabel antrean sinkronisasi, mayoritas data (90-99%) umumnya berstatus SYNCED, sedangkan aplikasi hanya sering meminta data berstatus PENDING. Indeks B-tree konvensional tetap mendaftarkan setiap baris yang berstatus SYNCED. Kondisi ini memicu dua masalah kritis:

  • Index Bloat: Ukuran file database membengkak karena B-tree menyimpan pointer ke jutaan baris yang tidak pernah dicari.
  • Write Amplification: Setiap operasi INSERT dan UPDATE status sinkronisasi memaksa SQLite menyeimbangkan kembali (rebalance) struktur B-tree indeks, meningkatkan write I/O pada flash storage perangkat mobile.

Solusi arsitektural untuk problem ini adalah indeks parsial (partial index), yaitu indeks yang menyertakan klausul WHERE pada definisi DDL.

Komparasi DDL: Full Index vs Indeks Parsial

Berikut skema tabel representatif untuk modul outbox sinkronisasi di aplikasi React Native:

CREATE TABLE sync_queue (
    id TEXT PRIMARY KEY NOT NULL,
    entity_type TEXT NOT NULL,
    payload TEXT NOT NULL,
    sync_status TEXT NOT NULL, -- 'PENDING', 'SYNCED', 'FAILED'
    retry_count INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL
);

Pendekatan indeks konvensional yang tidak efisien:

-- Full Index: Mengindeks seluruh baris tanpa memedulikan status
CREATE INDEX idx_sync_queue_status_created 
ON sync_queue (sync_status, created_at);

Pendekatan indeks parsial yang optimal:

-- Partial Index: Hanya mengindeks baris yang relevan untuk query engine
CREATE INDEX idx_sync_queue_pending_created 
ON sync_queue (created_at) 
WHERE sync_status = 'PENDING';

Pada indeks parsial di atas, ukuran struktur B-tree hanya proporsional terhadap jumlah baris yang bernilai PENDING. Baris dengan status SYNCED dilewati sepenuhnya oleh engine indeks SQLite saat proses penulisan data.

Implementasi pada React Native

Implementasi menggunakan pustaka binding SQLite berkinerja tinggi seperti @op-engineering/op-sqlite:

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

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

export function setupDatabase(): void {
  db.execute(`
    CREATE TABLE IF NOT EXISTS sync_queue (
      id TEXT PRIMARY KEY NOT NULL,
      entity_type TEXT NOT NULL,
      payload TEXT NOT NULL,
      sync_status TEXT NOT NULL,
      retry_count INTEGER NOT NULL DEFAULT 0,
      created_at INTEGER NOT NULL
    );
  `);

  db.execute(`
    CREATE INDEX IF NOT EXISTS idx_sync_queue_pending 
    ON sync_queue (created_at ASC) 
    WHERE sync_status = 'PENDING';
  `);
}

export function getPendingQueueBatch(limit: number = 50) {
  return db.execute(
    `SELECT id, entity_type, payload 
     FROM sync_queue 
     WHERE sync_status = 'PENDING' 
     ORDER BY created_at ASC 
     LIMIT ?;`,
    [limit]
  );
}

Analisis EXPLAIN QUERY PLAN

Eksekusi EXPLAIN QUERY PLAN membuktikan perubahan jalur eksekusi SQLite.

1. Sebelum Optimasi (Tanpa Indeks Relevan)

EXPLAIN QUERY PLAN
SELECT id, payload 
FROM sync_queue 
WHERE sync_status = 'PENDING' 
ORDER BY created_at ASC;

Hasil eksekusi:

id | parent | notused | detail
---|--------|---------|---------------------------------------------------------
2  | 0      | 0       | SCAN TABLE sync_queue
3  | 0      | 0       | USE TEMP B-TREE FOR ORDER BY

SQLite melakukan SCAN TABLE penuh di seluruh baris tabel dan mengalokasikan memory/temporary storage untuk sorting (USE TEMP B-TREE).

2. Sesudah Optimasi (Menggunakan Indeks Parsial)

EXPLAIN QUERY PLAN
SELECT id, payload 
FROM sync_queue 
WHERE sync_status = 'PENDING' 
ORDER BY created_at ASC;

Hasil eksekusi:

id | parent | notused | detail
---|--------|---------|---------------------------------------------------------
3  | 0      | 0       | SEARCH TABLE sync_queue USING INDEX idx_sync_queue_pending (created_at>?)

Status berubah menjadi SEARCH TABLE langsung ke entri leaf node B-tree. Operasi sorting eksternal hilang karena data pada indeks parsial sudah terurut menurut created_at ASC.

Kondisi Saat Query Planner Mengabaikan Indeks

SQLite Query Planner hanya akan memanfaatkan indeks parsial jika predikat pada query mencakup (subsumes) predikat pada klausul WHERE milik indeks. Indeks parsial diabaikan dalam situasi berikut:

1. Predikat Menggunakan Parameter Binding

Query planner SQLite mengevaluasi pemanfaatan indeks parsial pada fase kompilasi query (prepare statement). SQLite tidak dapat memverifikasi isi variabel runtime sebelum binding:

-- INDEKS DIABAIKAN (Full table scan):
-- Query Planner tidak tahu nilai '?' pada compile-time
SELECT id FROM sync_queue 
WHERE sync_status = ? 
ORDER BY created_at ASC;

Solusi: Gunakan literal konstanta pada klausa predikat parsial, lalu binding parameter untuk variabel dinamis lainnya:

-- INDEKS DIGUNAKAN:
SELECT id FROM sync_queue 
WHERE sync_status = 'PENDING' AND entity_type = ? 
ORDER BY created_at ASC;

2. Predikat Query Lebih Longgar dari Predikat Indeks

Jika indeks dibuat dengan predikat WHERE sync_status = 'PENDING' AND retry_count < 3, query berikut tidak akan memanfaatkan indeks:

-- INDEKS DIABAIKAN:
-- Kueri mencakup data yang tidak ada di dalam indeks
SELECT id FROM sync_queue 
WHERE sync_status = 'PENDING';

3. Diskrepansi Nilai NULL pada Soft Delete

Untuk tabel yang menggunakan soft delete:

CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

Kueri berikut akan mengabaikan indeks:

-- INDEKS DIABAIKAN:
SELECT * FROM users WHERE email = 'user@example.com';

-- INDEKS DIGUNAKAN:
SELECT * FROM users WHERE email = 'user@example.com' AND deleted_at IS NULL;
Klausul WHERE pada kueri harus secara eksplisit menyertakan syarat yang identik dengan klausul indeks parsial.

Dampak I/O Write dan Storage Footprint

Penghematan Flash Memory

B-tree indeks standar mengalokasikan page 4KB untuk menyimpan data key dan pointer baris (RowID). Jika tabel memiliki 100.000 baris dengan 99.000 baris SYNCED dan 1.000 baris PENDING, indeks penuh menyimpan 100.000 pointer. Indeks parsial hanya menyimpan 1.000 pointer. Ini mengurangi konsumsi disk index hingga 99%.

Efisiensi Operasi DML (INSERT/UPDATE)

Ketika mutasi status dilakukan:

UPDATE sync_queue 
SET sync_status = 'SYNCED' 
WHERE id = 'item_123';

SQLite mengeksekusi penghapusan node dari idx_sync_queue_pending. Setelah baris berstatus SYNCED, operasi UPDATE berikutnya pada baris tersebut tidak akan memicu modifikasi struktur B-tree indeks lagi. Langkah ini mengeliminasi I/O write amplification pada storage mobile.