Aplikasi self-hosted berbasis SQLite pada arsitektur single-node homelab sering mengalami latensi tinggi dan galat database is locked saat beban baca dan tulis berjalan bersamaan. Masalah ini umumnya bersumber dari konfigurasi default rollback journal dan query table scan yang memaksa I/O disk tinggi pada media penyimpanan bertipe consumer SSD atau kartu SD.

Konfigurasi PRAGMA untuk Mengatasi Disk I/O Bottleneck

Secara default, SQLite menggunakan mode DELETE journal yang mengunci seluruh database saat operasi tulis dan memerlukan sinkronisasi disk ganda per transaksi. Ubah perilaku ini menggunakan PRAGMA saat koneksi database diinisialisasi:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -64000;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;

Mekanisme Write-Ahead Logging (WAL)

Mode WAL memungkinkan operasi pembacaan (reader) tidak memblokir penulisan (writer), dan operasi penulisan tidak memblokir pembacaan. Perubahan dicatat ke file terpisah berekstensi -wal sebelum diintegrasikan kembali ke database utama melalui proses checkpointing. Perintah PRAGMA synchronous = NORMAL aman digunakan pada mode WAL karena integritas data tetap terjamin terhadap crash aplikasi maupun OS, sekaligus memangkas frekuensi pemanggilan fsync() ke disk.

Pengaturan cache_size = -64000 mengalokasikan RAM sebesar 64 MB untuk page cache in-memory, meminimalkan round-trip pembacaan blok data berulang dari disk.

Penyusunan Docker Compose untuk Homelab SQLite

Penyimpanan file SQLite di dalam kontainer memerlukan volume mount lokal langsung. Hindari network share seperti NFS atau SMB karena implementasi POSIX locking di protokol tersebut sering memicu korupsi file pada mode WAL.

services:
  app:
    image: ghcr.io/example/homelab-service:latest
    restart: unless-stopped
    volumes:
      - /opt/homelab/data:/data
    environment:
      - DATABASE_URL=sqlite:////data/app.db
Catatan: Pastikan direktori /opt/homelab/data pada host memiliki izin akses UID/GID yang sesuai dengan user proses kontainer agar file app.db-wal dan app.db-shm dapat dibuat secara otomatis saat runtime.

Mencegah Table Scan dengan Covering Index

Index standar mempercepat pencarian baris (row locator), tetapi SQLite tetap harus mengakses B-Tree tabel utama untuk membaca kolom yang belum tercakup di index (book lookup). Hal ini memicu read I/O acak.

Covering index menyimpan seluruh kolom yang dibutuhkan oleh query langsung di dalam B-Tree index, sehingga database engine tidak perlu mengakses halaman tabel utama sama sekali.

Contoh Skema dan Implementasi

Perhatikan struktur tabel log metrik berikut:

CREATE TABLE metric_logs (
    id INTEGER PRIMARY KEY,
    device_id TEXT NOT NULL,
    timestamp INTEGER NOT NULL,
    cpu_load REAL,
    memory_usage REAL
);

Jika sistem sering menjalankan query monitoring berikut:

SELECT timestamp, cpu_load 
FROM metric_logs 
WHERE device_id = 'srv-01' 
ORDER BY timestamp DESC 
LIMIT 50;

Index standar pada device_id saja masih mewajibkan SQLite mengambil nilai timestamp dan cpu_load dari tabel data. Buat composite covering index berikut:

CREATE INDEX idx_metrics_covering 
ON metric_logs (device_id, timestamp DESC, cpu_load);

Verifikasi Menggunakan EXPLAIN QUERY PLAN

Gunakan prefix EXPLAIN QUERY PLAN untuk memverifikasi apakah engine SQLite menggunakan covering index atau melakukan scan tabel yang membebani disk.

EXPLAIN QUERY PLAN
SELECT timestamp, cpu_load 
FROM metric_logs 
WHERE device_id = 'srv-01' 
ORDER BY timestamp DESC 
LIMIT 50;

Hasil output yang optimal harus menunjukkan keterangan USING COVERING INDEX:

SEARCH metric_logs USING COVERING INDEX idx_metrics_covering (device_id=?)

Jika output menampilkan SCAN metric_logs, SQLite memindai seluruh baris tabel secara sekuensial. Jika output menampilkan USING INDEX idx_name tanpa kata COVERING, SQLite masih melakukan traversal tambahan ke tabel utama untuk mengambil data kolom yang hilang dari index.

Trade-off dan Limitasi

  • Ukuran Penyimpanan: Covering index menduplikasi data kolom ke dalam file index, memperbesar ukuran total database di disk host.
  • Overhead Operasi Tulis: Setiap operasi INSERT, UPDATE, atau DELETE membutuhkan penulisan ke B-Tree tabel utama sekaligus ke B-Tree index yang mencakup kolom terkait. Batasi covering index hanya pada query baca yang paling sering dieksekusi.
  • Ukuran File WAL: Jika operasi baca berjalan sangat lama tanpa henti, file -wal tidak dapat melakukan checkpoint otomatis, menyebabkan ukuran file membengkak dan memperlambat pencarian halaman baru. Atur checkpoint berkala jika aplikasi memiliki writer intensif.