Optimasi SQLite untuk Aplikasi Production: Tips dari Kasus Nyata 2026

Gue awalnya ngejek SQLite. "Wah ini kan cuma buat prototype atau aplikasi HP," kata gue ke temen yang mau pakai SQLite buat backend SaaS-nya. Ternyata gue yang salah. Tahun lalu gue ditugasin benerin aplikasi analitik internal yang handle 40 juta row dengan SQLite, dan bro — setelah optimasi beneran, dia jalan lebih kenceng dari PostgreSQL di beberapa query. Artikel ini gue tulis biar lo nggak bikin kesalahan sama kayak gue.

Kenapa SQLite di Production Itu Masuk Akal

Sebelum masuk ke teknis, gue mau jelasin kapan SQLite cocok. Bukan cuma "kecil-kecilan". Dalam pengalaman gue:

• Write concurrency rendah: Aplikasi dengan <100 writes/detik tapi read tinggi — SQLite juara.
• Single-node: Lo nggak butuh distributed DB. Satu server cukup.
• Embedded / edge: IoT, desktop app, mobile backend lokal.
• Analitik baca-berat: Dashboard yang query data historis tapi jarang nulis.

Kasus nyata gue: aplikasi ticket support internal. 200 agent, 40 juta ticket, tapi write cuma pas ada tiket baru (rata-rata 5/detik saat sibuk). Read? Ribuan query/detik buat dashboard. PostgreSQL di VPS kecil malah jadi bottleneck karena overhead koneksi. SQLite dengan tuning yang bener malah smooth.

1. WAL Mode: Game Changer Pertama

Default SQLite pakai rollback journal, yang artinya write mengunci seluruh database. Satu write = bikin orang lain nunggu. WAL (Write-Ahead Logging) memisahkan read dan write sehingga keduanya bisa jalan barengan.

-- Aktifkan WAL mode
PRAGMA journal_mode = WAL;

-- Cek status
PRAGMA journal_mode;  -- bakal balikin 'wal'

Gue ukur di server: dengan rollback journal, 50 concurrent read + 5 write bikin read latency naik ke 800ms. Pakai WAL? Read tetep 20ms, write 30ms. Perbedaan 40x. Jangan pernah jalanin SQLite production tanpa WAL.

Catatan: WAL butuh filesystem yang support fsync yang bener. Di some NFS atau network drive, WAL bisa corrupt. Pakai local SSD atau NVMe. Itu sebabnya gue selalu sarankan baca panduan troubleshooting Linux buat pastiin disk lo sehat.

2. Strategi Indexing yang Bener

Index itu senjata bermata dua. Salah taruh, write lo melambat dan disk membengkak. Gue pelajari ini pas aplikasi tiket tiba-tiba write lambat 10x setelah nambah 6 index asal-asalan.

Index Berdasarkan Query Pattern

Jangan index semua kolom. Index cuma kolom yang lo WHERE, JOIN, atau ORDER BY sering. Contoh di tabel tiket:

-- Query sering: cari tiket by status + created_at
CREATE INDEX idx_tickets_status_created ON tickets(status, created_at);

-- Query lain: by assignee + priority
CREATE INDEX idx_tickets_assignee_priority ON tickets(assignee_id, priority);

Gunakan EXPLAIN QUERY PLAN

Sebelum optimasi, selalu cek apakah query lo pakai index:

EXPLAIN QUERY PLAN
SELECT * FROM tickets WHERE status = 'open' ORDER BY created_at DESC LIMIT 50;

-- Output bagus: "SEARCH tickets USING INDEX idx_tickets_status_created"
-- Output jelek: "SCAN tickets"  (artinya full table scan!)

Kasus	Tanpa Index	Dengan Index
Query 40jt row (filter status)	2.1 detik	8 ms
Query + sort created_at	3.4 detik	11 ms
Write (insert + 2 index)	0.3 ms	0.9 ms

Lihat trade-off-nya: write melambat 3x, tapi read 200x lebih cepet. Di kasus baca-berat, ini deal yang gue ambil dengan senang hati.

3. Memory-Mapped I/O (mmap)

SQLite bisa map file database ke memory virtual lewat mmap_size. Ini ngurangin syscall dan copy data, membuat read jauh lebih kenceng.

-- Alokasikan 512MB untuk mmap (sesuaikan RAM lo)
PRAGMA mmap_size = 536870912;

-- Cek
PRAGMA mmap_size;  -- balikin 536870912

Di server gue dengan 8GB RAM, gue set mmap ke 1GB. Hasil benchmark: read random 1 juta row turun dari 1.8 detik ke 0.4 detik. Tapi hati-hati — kalau database lebih gede dari mmap_size, sisa tetep dibaca dari disk. Dan mmap nggak work di filesystem jaringan.

4. Connection Pooling

Ini kesalahan paling sering gue liat di production. Developer buka-tutup koneksi tiap request. Di SQLite, buka koneksi = buka file + lock negotiation. Mahal.

Di Python (menggunakan sqlite3 + pool sederhana)

import sqlite3
from sqlite3 import dbapi2 as sqlite

conn = sqlite3.connect(
    'app.db',
    check_same_thread=False,
    timeout=30  # tunggu max 30 detik kalau DB lagi di-lock
)
conn.execute('PRAGMA journal_mode=WAL')
conn.execute('PRAGMA busy_timeout=30000')

# Di aplikasi nyata, pakai pool:
# from sqlalchemy import create_engine
# engine = create_engine('sqlite:///app.db', pool_size=10, max_overflow=20)

busy_timeout itu krusial. Tanpa itu, kalau dua write benturan, satu bakal error "database is locked". Dengan 30 detik timeout, SQLite sabar nunggu lock lepas. Di aplikasi gue, error "locked" turun dari 200/hari ke 0 setelah gue set ini.

5. VACUUM: Jangan Lupa Bersihin

SQLite nggak auto-reuse space dari row yang dihapus. Data yang lo DELETE tetep nyangkut di file, cuma ditandain "kosong". Lama-lama file membengkak dan read melambat.

-- Cek fragmentation
PRAGMA page_count;
PRAGMA freelist_count;  -- kalau gede, berarti banyak space terbuang

-- Bersihkan (butuh lock eksklusif, jalanin di maintenance window)
VACUUM;

Gue jalanin VACUUM seminggu sekali lewat cron saat traffic rendah. Sekali jalanin, file database turun dari 12GB ke 7GB dan query analitik kenceng 15%. Alternatif yang nggak lock: VACUUM INTO 'app_new.db' (SQLite 3.27+) yang bikin copy baru tanpa ngunci DB lama.

6. PRAGMA Lain yang Gue Rekomendasikan

Berikut setting lengkap yang gue pakai di production, semua di-set saat koneksi dibuka:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;      -- WAL sudah aman, NORMAL cukup (jangan FULL, terlalu lambat)
PRAGMA cache_size = -8000;         -- 8MB cache per koneksi (negatif = KB)
PRAGMA temp_store = MEMORY;        -- sort/temp di memory, bukan disk
PRAGMA mmap_size = 1073741824;    -- 1GB mmap
PRAGMA busy_timeout = 30000;       -- 30 detik tunggu lock
PRAGMA foreign_keys = ON;          -- enforce relasi

Satu catatan soal synchronous: di WAL mode, NORMAL udah aman dari corrupt kecuali power loss pas write. Kalau lo paranoid kehilangan data 1 detik terakhir, pakai FULL — tapi write lo bakal 3-5x lebih lambat. Gue pilih NORMAL karena ada UPS dan replica.

7. Benchmark Nyata (VPS 4 vCPU, 8GB RAM, NVMe)

Gue bikin test dengan 40 juta row, 6 index, WAL on, mmap 1GB. Tool: sqlite3 CLI + custom Go benchmark.

Operasi	Default Config	Tuned Config	Improvement
Point query by PK (1 row)	0.4 ms	0.05 ms	8x
Range query 10k row	120 ms	14 ms	8.5x
Full table scan	3.2 detik	0.9 detik	3.5x
Single insert	2.1 ms	0.8 ms	2.6x
Batch 1000 insert	1.8 detik	90 ms	20x
Concurrent read (50 user)	timeout	22 ms avg	stabil

Batch insert 20x lebih kenceng karena gue pakai BEGIN TRANSACTION + executemany + WAL. Transaksi per-insert itu pembunuh performa nomor satu di SQLite.

8. Kapan HARUS Pindah ke PostgreSQL/MySQL

Jujur aja, SQLite bukan solusi universal. Gue pindah ke PostgreSQL pas:

• Write concurrency tinggi: >500 writes/detik dari banyak client berbeda. WAL bantu, tapi satu file DB tetep jadi bottleneck.
• Butuh multi-server: SQLite itu single-file. Kalau lo butuh 3 app server nge-share DB, pindah ke client-server DB.
• Complex transaction antar tabel: SQLite support transaction, tapi SAVEPOINT dan isolation level terbatas.
• Butuh replication: SQLite nggak punya built-in replication. Lo harus pakai Litestream atau tool pihak ketiga.

Buat yang mau self-host stack data dengan kontrol penuh, gue bahas juga di panduan self-host soal isolasi service di VPS.

9. Backup Tanpa Downtime

Karena SQLite single-file, backup gampang: copy file. Tapi jangan cp pas ada write — bakal corrupt. Pakai cara resmi:

# Metode 1: .backup dot command (aman saat ada write)
sqlite3 app.db ".backup 'app.db.bak'"

# Metode 2: Litestream (continuous replication ke S3)
litestream replicate app.db s3://bucket/app.db

Gue pakai Litestream buat replicate ke Backblaze B2. Kalau server meledak, gue bisa restore dari 1 menit terakhir. Biaya? $0.005/hari. Worth it.

10. Monitoring dan Early Warning

Optimasi nggak beres kalau lo nggak monitor. Gue pasang script sederhana yang jalan tiap jam buat ngecek health SQLite:

#!/bin/bash
DB=app.db
SIZE=$(du -m $DB | cut -f1)
PAGES=$(sqlite3 $DB "PRAGMA page_count;")
FREE=$(sqlite3 $DB "PRAGMA freelist_count;")
WASTE=$(( FREE * 100 / PAGES ))
if [ "$WASTE" -gt 30 ]; then
  echo "WARNING: $DB $WASTE% fragmented" | mail -s "SQLite Alert" [email protected]
fi

Script ini nge-deteksi kalau fragmentation udah tinggi, biar gue bisa jadwalin VACUUM sebelum performa turun keliatan. Di produksi, gue kombinasi sama alert latency dari dashboard biar punya dua sinyal.

Penutup

SQLite di production itu bukan mitos. Dengan WAL, indexing yang tepat, mmap, pooling, dan VACUUM rutin, dia bisa nge-handle jutaan row dengan latency di bawah 20ms. Gue sendiri tetep pakai PostgreSQL buat aplikasi utama yang write-berat, tapi buat analitik internal dan edge app, SQLite jadi pilihan utama. Jangan underestimate tool "kecil" — kadang dialah yang nyelametin lo dari biaya infrastructure gila-gilaan.

Kalau lo serius bikin backend production-ready, jangan lupa hardening server lo dan pelajari Docker buat DevOps biar deploy-nya rapi. Dan buat yang pengen explore AI tanpa cloud, self-host LLM di VPS yang sama bisa jadi langkah berikutnya.