Audit Query Lambat SQLite: WAL, Index, dan Pagination Aman

Audit query lambat SQLite tidak cukup berhenti di “tambahkan index”. Saat data membesar, sumber bottleneck sering datang dari kombinasi query plan yang buruk, pagination berbasis OFFSET, COUNT(*) yang mahal, transaksi yang terlalu lama, dan perilaku konkurensi yang berubah ketika memakai mode Write-Ahead Logging (WAL).

Kalau aplikasi Anda mulai terasa lambat saat membuka daftar data, mencari riwayat, atau menulis event secara paralel, langkah yang tepat adalah audit terstruktur: ukur gejalanya, baca EXPLAIN QUERY PLAN, cek indeks terhadap pola akses nyata, lalu evaluasi apakah WAL memang membantu. Konteks bug lama SQLite yang pernah dianalisis dengan TLA+ berguna sebagai pengingat bahwa mode WAL dan konkurensi perlu dipahami dengan benar; bukan karena SQLite “tidak aman”, tetapi karena performa dan perilaku locking tidak boleh diasumsikan.

Intinya: SQLite bisa tetap sangat efektif untuk banyak aplikasi produksi, tetapi jika beban tumbuh, Anda perlu tahu batasnya. Audit performa harus berbasis pola query dan pola konkurensi, bukan sekadar menyalakan WAL atau menambah banyak indeks.

Gejala bottleneck yang perlu dicurigai

Mulai audit dari gejala yang nyata di aplikasi. Beberapa pola yang sering muncul:

Halaman daftar makin lambat saat nomor halaman membesar.
Pencarian atau filter terasa tidak konsisten: cepat untuk sebagian input, lambat untuk input lain.
Request tulis menumpuk saat ada banyak proses yang melakukan insert/update bersamaan.
Timeout atau “database is locked” muncul saat ada transaksi panjang.
CPU tinggi tetapi I/O rendah, tanda bahwa banyak pekerjaan terjadi di level scan/sort.
Waktu respons memburuk setelah ukuran database bertambah walau kode aplikasi tidak berubah.

Jangan langsung menyimpulkan penyebabnya. Query lambat di SQLite bisa berasal dari:

Full table scan karena indeks tidak cocok.
Sorting tambahan karena urutan hasil tidak bisa diambil langsung dari indeks.
OFFSET besar yang memaksa SQLite melewati banyak baris.
COUNT(*) terhadap himpunan data besar dan terfilter.
Checkpoint WAL, transaksi panjang, atau kontensi penulis.

Checklist audit query lambat SQLite

1. Tangkap query yang benar-benar lambat

Mulailah dari log aplikasi atau instrumentation sederhana. Catat minimal:

SQL final yang dieksekusi.
Parameter penting.
Durasi eksekusi.
Frekuensi kemunculan.
Konteks request atau job yang memanggil query.

Tujuannya adalah menghindari optimasi pada query yang jarang dipakai. Query yang lambat 500 ms tetapi dipanggil ribuan kali biasanya lebih berbahaya daripada query 2 detik yang dipakai sekali sehari.

2. Jalankan EXPLAIN QUERY PLAN

Untuk setiap query kandidat, lihat rencana eksekusinya:

EXPLAIN QUERY PLAN
SELECT id, created_at, status
FROM orders
WHERE customer_id = ? AND status = ?
ORDER BY created_at DESC
LIMIT 50;

Hal yang perlu diperhatikan:

Apakah SQLite melakukan SCAN pada tabel besar?
Apakah ia memakai SEARCH melalui indeks?
Apakah ada kebutuhan temporary B-tree untuk ORDER BY atau GROUP BY?
Apakah urutan kolom indeks cocok dengan pola WHERE dan ORDER BY?

Secara praktis, Anda ingin mengurangi scan penuh dan sort tambahan. Jika query memfilter lalu mengurutkan, indeks yang tepat sering kali harus melayani keduanya.

3. Cocokkan indeks dengan pola query, bukan dengan nama kolom populer

Kesalahan umum adalah membuat indeks satu per kolom secara acak. Indeks harus mengikuti pola akses nyata.

Contoh query:

SELECT id, customer_id, created_at, total
FROM orders
WHERE customer_id = ? AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Indeks yang lebih berguna biasanya indeks komposit yang mengikuti filter utama lalu urutan hasil:

CREATE INDEX idx_orders_customer_status_created_at
ON orders (customer_id, status, created_at DESC);

Mengapa ini bekerja?

customer_id dan status menyaring kandidat baris lebih dulu.
created_at DESC membantu mengambil hasil sesuai urutan tanpa sort terpisah.
LIMIT 50 menjadi lebih murah karena mesin tidak harus memproses semua baris lalu mengurutkannya.

Trade-off: setiap indeks menambah biaya tulis pada INSERT, UPDATE, dan DELETE, serta menambah ukuran file database. Karena itu, indeks berlebih bisa memperburuk throughput tulis.

4. Periksa apakah SELECT mengambil kolom berlebihan

Jika Anda hanya butuh 4 kolom untuk daftar, jangan pakai SELECT *. Membaca lebih banyak data dari yang diperlukan memperbesar I/O dan memori, terutama jika ada kolom teks besar atau blob.

-- Kurang baik
SELECT *
FROM events
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 20;

-- Lebih baik
SELECT id, type, created_at, summary
FROM events
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 20;

Anti-pattern OFFSET besar dan pagination yang aman

Penyebab klasik query lambat SQLite adalah pagination seperti ini:

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 200000;

Query tersebut tetap harus “melewati” banyak baris sebelum mengembalikan 20 hasil. Walau ada indeks, biaya akan tumbuh seiring nomor halaman. Pada data yang terus bertambah, ini makin mahal.

Before: OFFSET besar

SELECT id, title, created_at
FROM posts
WHERE published = 1
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;

Masalahnya:

Semakin besar OFFSET, semakin banyak kerja yang dibuang.
Hasil bisa tidak stabil jika data baru masuk di antara dua request halaman.
Pengguna bisa melihat duplikasi atau loncatan data.

After: keyset pagination

Gunakan penanda dari baris terakhir halaman sebelumnya, bukan nomor halaman absolut.

SELECT id, title, created_at
FROM posts
WHERE published = 1
  AND (created_at < ? OR (created_at = ? AND id < ?))
ORDER BY created_at DESC, id DESC
LIMIT 20;

Contoh parameter:

? pertama: created_at item terakhir di halaman sebelumnya
? kedua: nilai yang sama untuk menangani tie
? ketiga: id item terakhir

Indeks yang mendukung:

CREATE INDEX idx_posts_published_created_id
ON posts (published, created_at DESC, id DESC);

Mengapa keyset pagination lebih aman?

Tidak perlu melewati puluhan ribu baris.
Lebih stabil pada dataset yang terus berubah.
Biaya query cenderung tetap untuk halaman berikutnya.

Keterbatasan: keyset pagination tidak nyaman untuk kebutuhan “lompat ke halaman 500”. Jika UI benar-benar butuh navigasi acak berbasis nomor halaman, Anda mungkin perlu kompromi desain atau strategi materialisasi terpisah.

Dampak COUNT(*) yang sering diremehkan

Banyak endpoint daftar melakukan dua query:

Ambil data halaman saat ini.
Hitung total baris dengan COUNT(*).

Pada dataset besar, query hitung bisa sama mahalnya atau lebih mahal daripada query halaman itu sendiri, terutama bila ada filter kompleks.

SELECT COUNT(*)
FROM orders
WHERE customer_id = ? AND status = 'paid';

Masalah praktisnya:

Setiap refresh daftar bisa memaksa scan atau pencarian besar.
Jika dipanggil sangat sering, query hitung menjadi bottleneck tersembunyi.
Developer sering mengoptimasi query daftar tetapi lupa menghitung total.

Pilihan mitigasi:

Jangan tampilkan total absolut jika tidak dibutuhkan.
Tampilkan “lebih banyak hasil tersedia” berdasarkan ada/tidaknya baris berikutnya.
Cache hasil count untuk filter yang umum, jika konsistensi real-time tidak wajib.
Simpan agregat terpisah bila kasus bisnis memang selalu perlu angka total.

Catatan: COUNT(*) bukan selalu buruk. Ia menjadi masalah saat dipanggil sering pada himpunan data besar atau filter yang tidak didukung indeks dengan baik.

Memahami WAL: kapan membantu, kapan menambah kompleksitas

Mode WAL sering direkomendasikan untuk meningkatkan konkurensi baca-tulis di SQLite. Rekomendasi ini valid dalam banyak kasus, tetapi tidak berarti semua masalah performa selesai begitu WAL diaktifkan.

Apa yang WAL ubah secara praktis?

Dalam mode WAL, penulisan dicatat ke file log terpisah terlebih dahulu. Dampak pentingnya:

Reader dan writer lebih mudah berjalan bersamaan dibanding mode rollback journal klasik.
Beban baca-heavy dengan sesekali tulis sering mendapat manfaat.
Checkpoint menjadi bagian penting dari siklus I/O.

Aktivasi WAL umumnya seperti ini:

PRAGMA journal_mode = WAL;

Namun, memahami efeknya lebih penting daripada sekadar menyalakannya.

Kapan WAL membantu

Aplikasi memiliki banyak pembacaan dan penulisan kecil yang sering.
Anda ingin pembaca tidak terlalu terhambat oleh penulis.
Workload didominasi transaksi pendek.

Kapan WAL tidak menyelesaikan akar masalah

Query lambat terjadi karena indeks salah atau OFFSET besar.
Ada transaksi tulis panjang yang menahan giliran penulis lain.
Banyak proses menulis bersamaan ke satu file database dan throughput writer memang menjadi batas alami.
Checkpoint atau pertumbuhan file WAL tidak dipantau.

Konteks penting: bug lama WAL dan pelajaran untuk audit

Pembahasan tentang bug lama SQLite yang ditelusuri dengan TLA+ relevan sebagai pengingat bahwa sistem konkurensi dan recovery punya detail yang tidak selalu intuitif. Pelajarannya untuk engineer bukan “hindari SQLite”, melainkan:

Jangan membuat asumsi sederhana tentang perilaku baca/tulis paralel.
Pahami bahwa mode WAL membawa model operasional yang berbeda.
Uji beban realistis, termasuk crash recovery, restart, dan akses paralel.

Untuk audit performa, ini berarti Anda perlu memeriksa pola transaksi dan kontensi writer, bukan hanya query tunggal.

Transaksi panjang dan writer contention

SQLite tetap memiliki keterbatasan mendasar: hanya ada satu writer yang efektif pada satu waktu untuk satu database. WAL membantu konkurensi antara reader dan writer, tetapi tidak mengubah fakta bahwa penulis bisa saling menunggu.

Contoh anti-pattern transaksi panjang

BEGIN;

-- baca banyak data
SELECT * FROM jobs WHERE status = 'pending';

-- proses di aplikasi selama ratusan ms atau detik
-- lalu update satu per satu
UPDATE jobs SET status = 'running' WHERE id = ?;
UPDATE jobs SET status = 'running' WHERE id = ?;

COMMIT;

Masalahnya bukan hanya SQL-nya, tetapi durasi transaksi. Jika aplikasi membuka transaksi lalu melakukan pekerjaan non-database di tengah, writer lain akan menunggu lebih lama.

Perbaikan praktis

Pendekkan transaksi. Lakukan kerja non-SQL di luar transaksi jika memungkinkan.
Batch write secara masuk akal. Jangan satu transaksi per baris jika ada ribuan update kecil, tetapi jangan juga menahan transaksi terlalu lama.
Hindari interaksi jaringan di dalam transaksi.
Pastikan retry logic di aplikasi untuk kondisi lock sementara, jika memang sesuai kebutuhan.

Skenario before/after

Before: worker mengambil 500 item, memanggil API eksternal, lalu mengubah status dalam transaksi yang sama.

After:

Ambil ID kandidat secepat mungkin.
Tutup transaksi.
Kerjakan pemanggilan eksternal.
Buka transaksi pendek untuk update final.

Pola ini biasanya mengurangi kontensi writer jauh lebih efektif daripada sekadar menambah indeks.

Contoh audit nyata: dari scan penuh ke query yang stabil

Skenario

Anda punya tabel log aplikasi:

CREATE TABLE logs (
  id INTEGER PRIMARY KEY,
  tenant_id INTEGER NOT NULL,
  level TEXT NOT NULL,
  created_at TEXT NOT NULL,
  message TEXT NOT NULL
);

Endpoint daftar log menampilkan 50 log terbaru untuk satu tenant dan satu level tertentu.

Query awal:

SELECT id, level, created_at, message
FROM logs
WHERE tenant_id = ? AND level = ?
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Masalah yang ditemukan

OFFSET 5000 akan makin mahal.
Jika belum ada indeks yang sesuai, SQLite bisa scan besar lalu sort.
message mungkin kolom besar; jangan diambil jika daftar hanya menampilkan ringkasan.

Perbaikan

1. Ubah pagination ke keyset:

SELECT id, level, created_at
FROM logs
WHERE tenant_id = ?
  AND level = ?
  AND (created_at < ? OR (created_at = ? AND id < ?))
ORDER BY created_at DESC, id DESC
LIMIT 50;

2. Tambahkan indeks komposit:

CREATE INDEX idx_logs_tenant_level_created_id
ON logs (tenant_id, level, created_at DESC, id DESC);

3. Ambil kolom minimum pada daftar.

Verifikasi

Bandingkan EXPLAIN QUERY PLAN sebelum dan sesudah.
Ukur latensi halaman pertama dan halaman lanjut.
Pastikan hasil tetap konsisten saat data baru masuk.
Pantau apakah penambahan indeks memperlambat jalur insert secara signifikan.

Langkah verifikasi setelah optimasi

Optimasi yang benar harus diverifikasi. Checklist minimal:

Bandingkan query plan sebelum dan sesudah.
Ukur waktu eksekusi pada data realistis, bukan database kecil lokal yang kosong.
Uji skenario konkurensi: pembaca aktif saat writer melakukan insert/update.
Periksa ukuran dan laju pertumbuhan database setelah menambah indeks.
Uji halaman pagination lanjut, bukan hanya halaman pertama.
Cek query pendamping seperti COUNT(*), bukan hanya query utama.

Jika memakai WAL, tambahkan verifikasi operasional:

Apakah ada gejala kontensi writer saat traffic puncak?
Apakah transaksi tertentu berlangsung terlalu lama?
Apakah pola checkpoint atau ukuran file WAL menjadi masalah operasional?

Kesalahan umum saat audit query lambat SQLite

Menambahkan indeks tanpa membaca query plan.
Mengaktifkan WAL lalu menganggap masalah selesai.
Mengukur hanya pada dataset kecil.
Mengabaikan query count, sort, dan pagination.
Menyimpan transaksi terbuka saat melakukan kerja aplikasi.
Menggunakan OFFSET besar untuk tabel yang terus tumbuh.
Mengambil terlalu banyak kolom.

Kapan SQLite masih tepat, dan kapan perlu pindah arsitektur

SQLite masih sangat cocok jika:

Beban tulis tidak terlalu paralel.
Mayoritas akses adalah baca lokal dengan latensi rendah.
Anda menginginkan deployment sederhana tanpa server database terpisah.
Pola query bisa dioptimasi dengan indeks yang jelas.

Pertimbangkan arsitektur lain jika:

Banyak writer paralel menjadi kebutuhan inti.
Antrian tulis terus terbentuk walau transaksi sudah dipendekkan.
Query analitik berat bercampur dengan workload OLTP harian.
Replikasi, failover, atau skalabilitas horizontal menjadi kebutuhan utama.
Pemisahan beban baca dan tulis sudah tidak nyaman dilakukan di satu file database.

Jalur transisi yang umum bukan selalu langsung “ganti total”, tetapi bisa berupa:

Memindahkan pencarian/analitik ke sistem terpisah.
Memisahkan event log ber-volume tinggi dari database transaksi utama.
Mengalihkan beban tulis paralel ke database server yang memang dirancang untuk itu.

Penutup

Audit query lambat SQLite yang efektif berangkat dari fakta, bukan tebakan. Mulailah dari query yang benar-benar mahal, baca EXPLAIN QUERY PLAN, sesuaikan indeks dengan WHERE dan ORDER BY, hentikan penggunaan OFFSET besar, evaluasi COUNT(*), dan pendekkan transaksi yang menahan writer.

WAL dapat membantu konkurensi baca-tulis, tetapi ia bukan pengganti desain query yang baik. Konteks bug WAL lama yang pernah dianalisis secara formal seharusnya mendorong kita untuk lebih disiplin memahami model konkurensi SQLite. Jika setelah audit menyeluruh bottleneck tetap datang dari banyak writer paralel atau kebutuhan skala yang berbeda, itu tanda bahwa masalahnya mungkin bukan lagi di query, melainkan di pilihan arsitektur.

Quick Search

shopping_cart Keranjang