Implementasi SQLite di dalam container berbasis Alpine Linux kerap menghadapi degradasi performa saat volume data melampaui jutaan baris. Masalah utamanya bersumber dari dua faktor: pemindaian offset pagination dengan kompleksitas $O(N)$ yang membuang data di memori, serta lonjakan latensi I/O disk saat Write-Ahead Logging (WAL) mengeksekusi auto-checkpoint di atas layer filesystem container (overlayfs).

Akar Masalah: Pemindaian OFFSET dan Karakteristik I/O Alpine

Pola pagination konvensional menggunakan klausa LIMIT M OFFSET N memaksa mesin kueri SQLite membaca $N + M$ baris B-Tree dari disk atau page cache, lalu mengabaikan $N$ baris pertama hanya untuk mengambil $M$ baris terakhir. Seiring bertambahnya nilai $N$, page cache tertekan, memicu cache eviction dan pembacaan halaman database berulang.

Kondisi ini memburuk di Alpine Linux karena dua faktor sistemik:

  • Layer Storage OverlayFS: Secara default, penulisan di dalam container tanpa volume persisten melewati driver storage overlay2. Penulisan ke file WAL SQLite dan sinkronisasi ke file database utama melalui operasi fsync() mengalami amplifikasi overhead I/O yang signifikan.
  • musl libc dan Alokasi Memori: Berbeda dengan glibc, allocator memori pada musl libc di Alpine Linux dioptimalkan untuk footprint kecil, bukan konkurensi alokasi intensif. Operasi baca disk masif akibat scan OFFSET memicu thrashing alokasi buffer internal SQLite.
  • Stall Auto-Checkpoint WAL: SQLite default menjalankan checkpoint otomatis setiap 1000 halaman WAL. Saat query pagination membaca halaman WAL bersamaan dengan thread lain yang menulis, SQLite memblokir akses atau tertahan menunggu lock commit, memicu latensi tinggi (checkpoint stalls).

Skema Database dan Indeks Komposit

Untuk pagination deterministik, SQLite membutuhkan indeks komposit yang mencakup kolom pengurutan dan kunci unik (biasanya Primary Key) guna memecah nilai duplikat (tie-breaker).

CREATE TABLE audit_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    payload TEXT,
    created_at TEXT NOT NULL
);

-- Indeks komposit untuk keyset pagination urutan descending
CREATE INDEX idx_audit_events_created_id 
ON audit_events (created_at DESC, id DESC);

Transisi dari OFFSET ke Keyset Pagination

Keyset pagination (cursor-based pagination) bekerja dengan mencari titik awal pembacaan secara langsung menggunakan nilai record terakhir dari halaman sebelumnya.

1. Kueri Naif Berbasis OFFSET (Anti-pattern)

-- Mengambil halaman ke-1000 dengan ukuran batch 20 baris
SELECT id, tenant_id, event_type, created_at 
FROM audit_events 
ORDER BY created_at DESC, id DESC 
LIMIT 20 OFFSET 20000;

2. Kueri Keyset Deterministik (Optimasi)

SQLite mendukung row value comparison (komparasi tuple) sejak versi 3.15.0, memungkinkan pencarian koordinat komposit secara langsung:

-- Halaman pertama
SELECT id, tenant_id, event_type, created_at 
FROM audit_events 
ORDER BY created_at DESC, id DESC 
LIMIT 20;

-- Halaman berikutnya (menggunakan nilai baris terakhir: :last_created_at, :last_id)
SELECT id, tenant_id, event_type, created_at 
FROM audit_events 
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC 
LIMIT 20;

Catatan: Jika driver SQLite bahasa pemrograman Anda tidak mendukung syntax tuple comparison, ekspansi ekspresi logika berikut ekuivalen secara semantik:

WHERE created_at < :last_created_at 
   OR (created_at = :last_created_at AND id < :last_id)
ORDER BY created_at DESC, id DESC 
LIMIT 20;

Analisis Rencana Eksekusi: EXPLAIN QUERY PLAN

Jalankan EXPLAIN QUERY PLAN untuk memverifikasi jalur akses B-Tree SQLite.

Hasil Analisis Query OFFSET

EXPLAIN QUERY PLAN 
SELECT id, created_at FROM audit_events 
ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 20000;
QUERY PLAN
`--SCAN audit_events USING INDEX idx_audit_events_created_id

SQLite menggunakan SCAN index penuh. Mesin harus melintasi 20.020 entri B-Tree secara sekuensial dari daun ke daun.

Hasil Analisis Query Keyset

EXPLAIN QUERY PLAN 
SELECT id, created_at FROM audit_events 
WHERE (created_at, id) < ('2026-03-30 10:00:00', 450123) 
ORDER BY created_at DESC, id DESC LIMIT 20;
QUERY PLAN
`--SEARCH audit_events USING INDEX idx_audit_events_created_id (created_at<?)

SQLite melakukan SEARCH index. Pencarian berbasis biner melompat ke root indeks lalu turun langsung ke node daun target dalam kompleksitas $O(\log N)$, hanya membaca tepat 20 baris yang diminta.

Konfigurasi PRAGMA untuk Lingkungan Alpine

Terapkan setelan PRAGMA berikut saat inisialisasi koneksi database untuk meminimalkan I/O disk dan mengoptimalkan pemanfaatan memori container.

-- Mengaktifkan Write-Ahead Logging
PRAGMA journal_mode = WAL;

-- Menghindari fsync() ganda pada setiap transaksi (aman dalam mode WAL)
PRAGMA synchronous = NORMAL;

-- Alokasi cache 64MB (nilai negatif merepresentasikan KiB)
PRAGMA cache_size = -65536;

-- Memori virtual untuk SQLite (Memory-Mapped I/O)
PRAGMA mmap_size = 268435456; -- 256MB

-- Simpan tabel dan indeks sementara di RAM
PRAGMA temp_store = MEMORY;

-- Menghilangkan auto-checkpoint berkala selama query intensif
PRAGMA wal_autocheckpoint = 0;

Mitigasi Checkpoint Stall dan I/O Container

Menonaktifkan wal_autocheckpoint mencegah query pagination tersendat saat file WAL mencapai threshold default (1000 halaman). Namun, file WAL tidak boleh dibiarkan membesar tanpa batas karena memperlambat pembacaan.

1. Jalankan Checkpoint Terjadwal di Thread/Worker Terpisah

Gunakan worker terpisah untuk memindahkan data dari WAL ke file database utama secara non-blocking:

-- Eksekusi checkpoint non-blocking secara periodik (misal: tiap 60 detik)
PRAGMA wal_checkpoint(PASSIVE);

Metode PASSIVE memastikan checkpoint berjalan tanpa memaksa pembaca atau penulis menunggu lock rilis. Jika seluruh transaksi aktif selesai, lakukan reduksi ukuran file:

-- Jalankan saat off-peak untuk truncating file WAL ke ukuran 0
PRAGMA wal_checkpoint(TRUNCATE);

2. Atasi OverlayFS I/O Overhead

Jangan pernah menaruh file SQLite langsung di root filesystem container Alpine. Mount storage menggunakan Docker Named Volume atau Bind Mount langsung ke filesystem host (ext4/xfs) dengan opsi mount noatime.

# Contoh Docker compose yang menghindari overlayfs bottleneck
services:
  app:
    image: app-sqlite-alpine:3.24
    volumes:
      - sqlite_data:/var/lib/sqlite/data

volumes:
  sqlite_data:
    driver: local

Rangkuman Trade-Off Keyset Pagination

  • Kelebihan: Performa deterministik konstan $O(1)$ untuk navigasi halaman ke mana pun, page cache stabil, latensi stabil di angka sub-milidetik.
  • Kelemahan: Tidak dapat melompat langsung ke halaman acak (misal: tombol 'Lompat ke halaman 45'). Hanya mendukung navigasi maju (Next) dan mundur (Previous).
  • Persyaratan Wajib: Kolom pagination harus bersifat deterministik, terurut secara terbalik atau lurus, dan terindeks penuh bersama Primary Key.