Ketika aplikasi produksi menggunakan SQLite, query lambat bisa muncul seiring bertambahnya data dan pola akses berubah. Artikel ini langsung menjelaskan cara menemukan penyebab bottleneck melalui log dan EXPLAIN, memperbaiki struktur index, membandingkan teknik pagination, serta menjaga performa saat data tumbuh, sebagaimana pelajaran dari “Learning a few things about running SQLite”.

1. Mengidentifikasi Bottleneck dengan Log dan EXPLAIN

Langkah pertama adalah melihat query dari sudut pandang runtime. Pastikan aplikasi mencatat query yang mengeksekusi lebih lama dari ambang tertentu—misalnya 100 ms. Log ini bisa berasal dari lapisan ORM, middleware, atau callback pragmas SQLite seperti sqlite3_profile. Catatan utama dari pengalaman menjalankan SQLite adalah: data log memberi konteks nyata untuk prioritas tuning.

Setelah menemukan query bermasalah, gunakan EXPLAIN QUERY PLAN untuk memahami bagaimana SQLite mengakses tabel.

EXPLAIN QUERY PLAN
SELECT * FROM transaksi
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 20;

Output-nya menunjukkan apakah SQLite memindai seluruh tabel (SCAN TABLE) atau memanfaatkan index (SEARCH TABLE). Fokuskan optimasi pada query yang masih melakukan full scan meskipun memiliki filter yang jelas.

Debugging tip: perhatikan output cost di EXPLAIN; perbedaan antara index scan dan table scan bisa mencapai beberapa order besar saat data tumbuh.

2. Menentukan dan Mengatur Index yang Tepat

Tempatkan index berdasarkan kondisi WHERE dan klausa ORDER BY yang paling sering digunakan. Misalnya query sebelumnya mengandalkan user_id dan created_at secara bersamaan. Index komposit membantu SQLite mempertahankan urutan hasil tanpa tambahan sort.

CREATE INDEX idx_transaksi_user_created
ON transaksi(user_id, created_at DESC);

Urutan kolom dalam index penting karena SQLite hanya menggunakan prefix index. Jadi index (user_id, created_at) mendukung filter user_id dan order by created_at, sementara kebalikannya tidak mendukung filter saja.

Catatan lain: index meningkatkan kecepatan baca tapi memperlambat INSERT/UPDATE. Pertimbangkan frekuensi penulisan. Untuk tabel dengan banyak penulisan, pilih minimal index yang menyelesaikan query paling kritis.

3. Pagination: LIMIT/OFFSET vs Keyset

Pagination dengan LIMIT dan OFFSET mudah diimplementasikan, tapi OFFSET besar menuntut SQLite menyelesaikan semua baris sebelumnya. Ketika dataset tumbuh, halaman akhir menjadi lambat karena SQLite harus melakukan scan sampai ke baris offset.

Solusi praktis: keyset pagination, yaitu menggunakan nilai kolom terakhir dari halaman sebelumnya sebagai batas.

SELECT * FROM transaksi
WHERE user_id = ?
AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Dengan index komposit yang sesuai, query ini hanya mengakses kebutuhan data terbaru, menghindari overhead OFFSET. Trade-off: keyset tidak memungkinkan langsung melompat ke halaman arbitrer, jadi cocok untuk UI daftar bertahap.

Kalau harus tetap menggunakan OFFSET—misalnya untuk API yang menyajikan nomor halaman—kombinasikan dengan caching jumlah total hasil agar client tidak meminta halaman terakhir tanpa perlu.

4. Memantau dan Menjaga Performa Saat Data Tumbuh

SQLite tidak otomatis memperingatkan ketika index tidak lagi efektif atau statistik berubah. Setidaknya lakukan monitoring berikut:

  • Log latency query dengan threshold dan simpan sample EXPLAIN untuk query yang berubah pola.
  • VACUUM dan ANALYZE secara berkala untuk menjaga statistik index tetap up-to-date tanpa menyebabkan downtime besar.
  • Perhatikan ukuran file database dan cache page. Laporan dari benchmark internal menunjukkan pentingnya PRAGMA cache_size dan PRAGMA journal_mode sesuai beban.

Untuk pertumbuhan data, tentukan ukuran kritik sebelum melakukan partisi logis (misalnya memindahkan data lama ke tabel arsip). Jangan abaikan efek index pada penulisan—setiap index tambahan menambah biaya update.

5. Ringkasan Strategi

Optimasi query lambat SQLite membutuhkan siklus observasi dan tindakan. Gunakan log untuk menemukan query yang menonjol, lalu EXPLAIN QUERY PLAN untuk membaca rencana eksekusi. Tambahkan index komposit yang mencerminkan filter dan order by, serta pilih pagination yang sesuai dengan pola akses. Akhirnya, rajin pantau dan perbarui statistik agar performa tetap stabil walau data terus bertambah.

Dengan pendekatan ini, Anda bisa menjaga SQLite tetap responsif tanpa mengorbankan integritas data atau terlalu banyak menambah kompleksitas arsitektur.