Optimasi SQLite untuk Aplikasi Production: Tips

SQLite sering dianggap remeh — sekadar "database untuk prototype atau aplikasi HP". Padahal, dengan konfigurasi yang tepat, SQLite mampu menangani beban produksi yang serius. Studi kasus nyata: sebuah aplikasi analitik internal dengan 40 juta baris data berhasil dioptimasi sehingga beberapa query-nya berjalan lebih cepat dibanding PostgreSQL di hardware yang sama. Artikel ini membahas langkah-langkah optimasi yang terbukti, lengkap dengan benchmark dan referensi resmi.

Kenapa SQLite di Production Itu Masuk Akal

Sebelum masuk ke teknis, penting memahami kapan SQLite cocok digunakan di produksi. Bukan hanya untuk aplikasi "kecil-kecilan":

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

Contoh kasus nyata: aplikasi ticket support internal dengan 200 agen dan 40 juta tiket. Write hanya terjadi saat tiket baru masuk (rata-rata 5/detik saat sibuk), sementara read mencapai ribuan query/detik untuk dashboard. PostgreSQL di VPS kecil justru menjadi bottleneck karena overhead koneksi, sedangkan SQLite dengan tuning yang tepat berjalan stabil.

1. WAL Mode: Game Changer Pertama

Default SQLite memakai rollback journal, yang artinya write mengunci seluruh database. Satu write = membuat operasi lain menunggu. WAL (Write-Ahead Logging) memisahkan read dan write sehingga keduanya bisa berjalan bersamaan.

-- Aktifkan WAL mode
PRAGMA journal_mode = WAL;

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

Hasil pengukuran di server: dengan rollback journal, 50 concurrent read + 5 write membuat read latency naik ke 800 ms. Dengan WAL, read tetap 20 ms dan write 30 ms — peningkatan sekitar 40x. SQLite production sebaiknya tidak dijalankan tanpa WAL mode.

Catatan: WAL membutuhkan filesystem dengan dukungan fsync yang benar. Di sebagian NFS atau network drive, WAL berisiko korup. Gunakan local SSD atau NVMe — dan pastikan kesehatan disk terpantau, misalnya lewat panduan Monitoring Server Self-Hosted: Netdata, Grafana, Prometheus.

2. Strategi Indexing yang Bener

Index adalah senjata bermata dua: jika salah penempatan, write melambat dan disk membengkak. Kasus nyata: aplikasi tiket mengalami write lambat 10x setelah penambahan 6 index tanpa perencanaan.

Index Berdasarkan Query Pattern

Jangan mengindex semua kolom. Index hanya perlu dibuat pada kolom yang sering dipakai di klausa WHERE, JOIN, atau ORDER BY. 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 memakai 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

Perhatikan trade-off-nya: write melambat sekitar 3x, tetapi read 200x lebih cepat. Untuk workload baca-berat, trade-off ini sangat menguntungkan.

3. Memory-Mapped I/O (mmap)

SQLite bisa memetakan file database ke memory virtual lewat mmap_size. Ini mengurangi syscall dan copy data, membuat read jauh lebih cepat.

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

-- Cek
PRAGMA mmap_size;  -- balikin 536870912

Pada server dengan 8GB RAM, mmap diset ke 1GB. Hasil benchmark: read random 1 juta row turun dari 1,8 detik ke 0,4 detik. Perlu diingat — jika ukuran database lebih besar dari mmap_size, sisa data tetap dibaca dari disk, dan mmap tidak berfungsi optimal di filesystem jaringan.

4. Connection Pooling

Ini adalah kesalahan paling umum di production: developer membuka-menutup koneksi di setiap request. Di SQLite, membuka koneksi berarti membuka file + negosiasi lock — operasi yang 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 sangat krusial. Tanpa setting ini, jika dua write bentrok, salah satunya akan error "database is locked". Dengan timeout 30 detik, SQLite menunggu lock lepas dengan sabar. Di studi kasus aplikasi tiket, error "locked" turun dari 200/hari menjadi 0 setelah setting ini diterapkan.

5. VACUUM: Jangan Lupa Bersihin

SQLite tidak otomatis me-reuse space dari row yang dihapus. Data yang di-DELETE tetap nyangkut di file, hanya ditandai "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;

VACUUM dijadwalkan seminggu sekali via cron saat traffic rendah. Sekali eksekusi, file database turun dari 12GB ke 7GB dan query analitik membaik 15%. Alternatif tanpa lock: VACUUM INTO 'app_new.db' (SQLite 3.27+) yang membuat salinan baru tanpa mengunci database lama.

6. PRAGMA Lain yang Direkomendasikan

Berikut setting lengkap yang terbukti efektif 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

Catatan soal synchronous: di WAL mode, nilai NORMAL sudah aman dari korupsi kecuali power loss saat write. Jika menginginkan keamanan ekstra untuk data 1 detik terakhir, gunakan FULL — tetapi write akan 3-5x lebih lambat. NORMAL menjadi pilihan umum karena didukung UPS dan replika.

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

Pengujian dilakukan dengan 40 juta row, 6 index, WAL aktif, mmap 1GB, menggunakan sqlite3 CLI dan benchmark Go kustom.

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 cepat karena memakai BEGIN TRANSACTION + executemany + WAL. Transaksi per-insert adalah pembunuh performa nomor satu di SQLite.

8. Kapan HARUS Pindah ke PostgreSQL/MySQL

Jujur, SQLite bukan solusi universal. Migrasi ke PostgreSQL diperlukan ketika:

• Write concurrency tinggi: >500 writes/detik dari banyak client berbeda. WAL membantu, tapi satu file DB tetap menjadi bottleneck.
• Butuh multi-server: SQLite itu single-file. Jika butuh 3 app server yang berbagi DB, pindah ke client-server DB.
• Complex transaction antar tabel: SQLite mendukung transaction, tapi SAVEPOINT dan isolation level terbatas.
• Butuh replication: SQLite tidak punya built-in replication; harus memakai Litestream atau tool pihak ketiga.

Untuk yang ingin self-host stack data dengan kontrol penuh, panduan Self-Host Coolify di VPS Ubuntu membahas isolasi service di VPS.

9. Backup Tanpa Downtime

Karena SQLite single-file, backup mudah: copy file. Tapi jangan cp saat ada write — bisa corrupt. Gunakan 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

Litestream dapat dipakai untuk replikasi ke Backblaze B2. Jika server gagal, data bisa di-restore hingga 1 menit terakhir dengan biaya sekitar $0,005/hari — investasi yang sepadan.

10. Monitoring dan Early Warning

Optimasi tidak lengkap tanpa monitoring. Script sederhana berikut berjalan tiap jam untuk mengecek kesehatan 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
  sqlite3 $DB "VACUUM;"
fi

Script ini berjalan otomatis setiap jam. Jika waste lebih dari 30%, database langsung di-vacuum. Sederhana tapi efektif.

Kesimpulan

SQLite sangat powerful untuk aplikasi production jika tahu cara mengoptimasi. Dengan WAL mode, indexing yang tepat, dan monitoring, SQLite mampu menangani beban yang jauh di luar ekspektasi. Mulai dari skala kecil, ukur performa, lalu scale dari sana.

Panduan lengkap deployment tersedia di artikel CI/CD Pipeline GitHub Actions. Untuk belajar Go di backend, lihat juga REST API Go + PostgreSQL.

Sumber & Validasi

Data dan benchmark pada artikel ini merujuk pada dokumentasi resmi serta studi teknis berikut:

• SQLite Documentation: Write-Ahead Logging — perilaku WAL, concurrency, dan checkpointing.
• SQLite Documentation: PRAGMA Statements — referensi resmi journal_mode, synchronous, mmap_size, dan busy_timeout.
• Micrologics: SQLite in Production — Optimizing WAL Mode, Concurrency, and VFS Layers.
• PhotoStructure: How to VACUUM SQLite in WAL Mode — teknik VACUUM dan checkpoint.
• Pavan Rangani: SQLite in Production — Modern Applications Guide 2026.