Konfigurasi dengan PRAGMA

Bawaan SQLite memprioritaskan keamanan data: setiap transaksi dijamin bertahan penuh di disk sebelum dianggap selesai. Pilihan itu aman, tetapi tidak selalu pas — aplikasi pembaca laporan bisa menukar sedikit durabilitas dengan kecepatan, dan aplikasi sibuk butuh memori cache lebih besar. PRAGMA adalah panel kenop konfigurasi SQLite: satu perintah sederhana untuk membaca dan mengubah perilaku engine. Bab ini memetakan kenop yang paling sering dipakai untuk performa, beserta batasan pentingnya: hampir semua PRAGMA berlaku per koneksi, bukan tersimpan di berkas database.

Membaca dan Mengatur Nilai #

Bentuk pertanyaan PRAGMA nama; membaca nilai saat ini; bentuk penugasan PRAGMA nama = nilai; mengubahnya. Sebagai alas latihan, kita buat dulu database toko kecil:

sqlite3 toko.db "CREATE TABLE pelanggan (id INTEGER PRIMARY KEY, nama TEXT NOT NULL, kota TEXT NOT NULL DEFAULT 'Jakarta', email TEXT); INSERT INTO pelanggan (nama, kota, email) VALUES ('Budi', 'Jakarta', '[email protected]'), ('Siti', 'Bandung', '[email protected]');"

Empat nilai bawaan yang paling sering diperiksa:

sqlite3 toko.db "PRAGMA cache_size; PRAGMA synchronous; PRAGMA temp_store; PRAGMA foreign_keys;"

Output yang Dihasilkan:

-2000
2
0
0

Durabilitas versus Kecepatan: synchronous #

PRAGMA synchronous mengatur seberapa keras engine SQLite memastikan transaksi benar-benar tertulis ke media penyimpanan sebelum COMMIT dianggap sukses:

sqlite3 toko.db "PRAGMA synchronous = NORMAL; PRAGMA synchronous; PRAGMA synchronous = FULL; PRAGMA synchronous;"

Output yang Dihasilkan:

1
2

Kombinasi journal_mode = WAL dengan synchronous = NORMAL adalah pasangan umum untuk aplikasi web — materi lengkapnya ada pada chapter Journal Modes dan WAL.

Journal Mode dan Checkpoint #

PRAGMA journal_mode mengatur mekanisme pencatat transaksi. Mode bawaan delete menulis berkas rollback journal; mode wal (Write-Ahead Logging) menulis perubahan ke berkas -wal dan mengizinkan pembaca serta penulis bekerja bersamaan:

sqlite3 toko.db "PRAGMA journal_mode;"

Output yang Dihasilkan:

delete

sqlite3 toko.db "PRAGMA journal_mode = wal;"

Output yang Dihasilkan:

wal

sqlite3 toko.db "INSERT INTO pelanggan (nama) VALUES ('Anton'); PRAGMA wal_checkpoint;"

Output yang Dihasilkan:

0|1|1

Kolom ketiga wal_checkpoint melaporkan jumlah halaman yang sudah dipindahkan dari berkas -wal ke database utama. Pasangan praktisnya, PRAGMA journal_size_limit = 8192; membatasi ukuran berkas -wal setelah checkpoint sehingga tidak tumbuh tanpa batas. Keduanya dibahas tuntas di section Transaksi dan Konsistensi.

Memori dan Cache #

PRAGMA cache_size mengatur memori untuk halaman-halaman database yang sedang aktif. Nilai negatif berarti KiB, nilai positif berarti jumlah halaman:

sqlite3 toko.db "PRAGMA cache_size = -8000; PRAGMA cache_size;"

Output yang Dihasilkan:

-8000

Nilai bawaan -2000 berarti cache 2.000 KiB; contoh di atas menaikkannya menjadi 8.000 KiB — query yang membaca berulang kali tabel besar akan merasakan bedanya. Pilihan cepat lainnya:

sqlite3 toko.db "PRAGMA temp_store = MEMORY; PRAGMA temp_store; PRAGMA temp_store = DEFAULT; PRAGMA temp_store;"

Output yang Dihasilkan:

2
0

Nilai 2 (MEMORY) menempatkan struktur sementara — seperti TEMP B-TREE pengurutan dari chapter sebelumnya — di RAM alih-alih berkas, sementara 0 (DEFAULT) kembali ke pengaturan build. Terakhir, PRAGMA mmap_size (misalnya PRAGMA mmap_size = 268435456; untuk 256 MiB) memetakan berkas database langsung ke memori virtual sehingga pembacaan halaman tidak lagi melewati pemanggilan sistem read().

Menahan Kunci: busy_timeout #

Ketika dua koneksi menulis bersamaan, koneksi kedua menerima galat database is locked alih-alih menunggu diam-diam. PRAGMA busy_timeout menyuruh engine SQLite menunggu sampai batas waktu (milidetik) sebelum menyerah:

sqlite3 toko.db "PRAGMA busy_timeout = 3000; PRAGMA busy_timeout;"

Output yang Dihasilkan:

3000
3000

Baris pertama muncul dari statement penugasan itu sendiri — PRAGMA yang di-assign nilai baru mengembalikan nilai tersebut, sama perilakunya dengan PRAGMA yang dibaca. Baris kedua adalah hasil pembacaan eksplisit yang kita minta.

Nilai bawaan 0 berarti tidak menunggu sama sekali. Untuk aplikasi dengan beberapa koneksi tulis, nilai beberapa detik mengubah galat langsung menjadi antrean singkat.

Mode Khusus: query_only dan foreign_keys #

PRAGMA query_only = ON membekukan koneksi menjadi hanya-baca — query berjalan normal, semua upaya menulis ditolak:

sqlite3 toko.db "PRAGMA query_only = ON; SELECT count(*) FROM pelanggan;"

Output yang Dihasilkan:

3

sqlite3 toko.db "PRAGMA query_only = ON; UPDATE pelanggan SET kota = 'Medan' WHERE id = 1;"

Output yang Dihasilkan:

Error: stepping, attempt to write a readonly database (8)

Perhatikan bahwa setiap perintah di atas dijalankan sebagai koneksi terpisah. Pada perintah terakhir, PRAGMA query_only tidak dinyalakan, sehingga UPDATE yang sama langsung berhasil:

sqlite3 toko.db "UPDATE pelanggan SET kota = 'Medan' WHERE id = 1; SELECT kota FROM pelanggan WHERE id = 1;"

Output yang Dihasilkan:

Medan

Perilaku ini berlaku untuk hampir semua PRAGMA: penegakan foreign key (dibahas di chapter Foreign Key), synchronous, cache_size — semuanya hidup dan mati bersama koneksi. Hanya sebagian kecil seperti journal_mode dan auto_vacuum yang nilainya tersimpan permanen di berkas database.

PRAGMA untuk Pemeriksaan #

Sejumlah PRAGMA berfungsi sebagai alat pemeriksa, bukan pengatur:

sqlite3 toko.db "PRAGMA quick_check;"

Output yang Dihasilkan:

ok

sqlite3 toko.db "PRAGMA table_info(pelanggan);"

Output yang Dihasilkan:

0|id|INTEGER|0||1
1|nama|TEXT|1||0
2|kota|TEXT|1|'Jakarta'|0
3|email|TEXT|0||0

quick_check memverifikasi integritas berkas secara ringkas (mengembalikan ok bila sehat), dan table_info memperlihatkan daftar kolom beserta tipe dan constraint-nya — enam kolomnya adalah cid, name, type, notnull, dflt_value, dan pk.