PRAGMA SQLite: Pengaturan Koneksi untuk Produksi

PRAGMA Adalah Cara Berbicara dengan Engine

PRAGMA adalah pernyataan khusus SQLite yang membaca atau mengubah perilaku engine. Kamu menjalankannya seperti SQL lainnya, tapi alih-alih menyentuh datamu, pernyataan ini menyentuh konfigurasi database.

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

Jika dijalankan sebagai query, PRAGMA mengembalikan nilainya saat ini. Jika dijalankan sebagai penugasan, PRAGMA mengubah nilainya:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

Model berpikirnya: kebanyakan PRAGMA berlaku per koneksi. Buka koneksi baru, dan nilai default-nya kembali lagi. Itulah sebabnya kode produksi biasanya punya satu blok kecil pernyataan PRAGMA yang dijalankan tepat setelah setiap koneksi dibuat.

Dasar untuk Produksi

Jika kamu hanya mengingat lima PRAGMA, ingatlah yang ini:

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

SELECT 'configured' AS status;

Itu default yang masuk akal untuk hampir semua aplikasi yang memakai SQLite sebagai penyimpanan utamanya. Masing-masing layak dipahami satu per satu, dan sisa halaman ini membahasnya.

journal_mode = WAL

Journal mode mengatur cara SQLite membuat penulisan menjadi tahan lama. Default-nya, DELETE, memakai rollback journal: penulis memblokir pembaca dan pembaca memblokir penulis. Tidak masalah untuk tool CLI, tapi menyiksa untuk aplikasi web.

WAL (Write-Ahead Logging) membalik keadaan itu. Pembaca dan penulis tidak saling memblokir: pembaca melihat snapshot yang konsisten saat seorang penulis sedang melakukan commit. Kamu tetap hanya mendapat satu penulis dalam satu waktu, tapi pembacaan tetap cepat meskipun bebannya tinggi.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

Beberapa hal yang perlu kamu tahu:

• journal_mode bersifat persisten: begitu diatur, pengaturannya tetap berlaku untuk file database itu. Kamu tidak perlu mengaturnya di setiap koneksi, tapi mengaturnya juga tidak ada ruginya.
• WAL membuat dua file tambahan di samping .db-mu: -wal dan -shm. Jangan hapus keduanya selama database sedang terbuka.
• WAL tidak bekerja dengan baik di filesystem jaringan (NFS, SMB). Simpan database di disk lokal.

Ada dokumen terpisah tentang WAL mode dan konkurensi yang membahasnya lebih dalam. Untuk sekarang: nyalakan saja.

synchronous = NORMAL

synchronous mengatur seberapa agresif SQLite menulis data ke disk. Pertukarannya adalah ketahanan data lawan kecepatan.

• FULL (default): tulis ke disk setelah setiap commit. Ketahanan maksimal. Lebih lambat.
• NORMAL: tulis ke disk di titik-titik checkpoint yang aman. Aman jika dipakai dengan WAL. Lebih cepat.
• OFF: biarkan sistem operasi yang memutuskan. Cepat, tapi berisiko rusak jika listrik padam.

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

Integer di hasilnya (1) berarti NORMAL. Dengan WAL mode, NORMAL adalah pengaturan yang disarankan: kamu tidak kehilangan transaksi yang sudah di-commit saat crash, kamu hanya berisiko kehilangan transaksi paling baru saat listrik padam. Untuk kebanyakan aplikasi, itulah keseimbangan yang tepat.

Jangan pakai OFF kecuali kamu sedang mengisi database sekali pakai yang bisa kamu buat ulang dari awal.

foreign_keys = ON

Yang satu ini sering menjebak orang. SQLite mendukung foreign key, tapi penegakannya mati secara default, dan pengaturannya berlaku per koneksi:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO books (title, author_id) VALUES ('Ghosts', 999);

Dengan foreign_keys = ON, insert terakhir itu gagal karena tidak ada penulis dengan id 999. Tanpa PRAGMA itu, SQLite dengan senang hati menulis baris yatim tersebut, dan kamu baru menemukan kekacauannya berbulan-bulan kemudian.

Jalankan PRAGMA foreign_keys = ON; sebagai pernyataan paling pertama di setiap koneksi baru. Kebanyakan ORM melakukannya secara otomatis; jika kamu memakai driver mentah, itu tanggung jawabmu.

busy_timeout = 5000

SQLite hanya mengizinkan satu penulis dalam satu waktu. Jika koneksi kedua mencoba menulis saat koneksi pertama sedang di tengah transaksi, koneksi kedua mendapat SQLITE_BUSY dan langsung menyerah, secara default.

busy_timeout menyuruh SQLite untuk menunggu dan mencoba lagi:

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

Nilainya dalam milidetik. 5000 berarti "tunggu kunci itu paling lama 5 detik sebelum menyerah." Jika digabung dengan WAL, ini menghilangkan sebagian besar error database is locked yang tidak perlu di aplikasi yang berjalan bersamaan.

Jika kamu sampai menaikkan nilainya di atas 30 detik, solusi sebenarnya kemungkinan besar adalah transaksi yang lebih pendek, bukan timeout yang lebih panjang.

cache_size

cache_size mengatur berapa banyak halaman database yang disimpan SQLite di memori. Cache yang lebih besar berarti lebih sedikit pembacaan disk, yang berarti query lebih cepat untuk data yang sering diakses.

Nilainya punya dua bentuk:

• Angka positif: jumlah halaman. Dengan ukuran halaman default 4 KB, 2000 sama dengan 8 MB.
• Angka negatif: kibibyte. -20000 sama dengan 20 MB, berapa pun ukuran halamannya.

PRAGMA cache_size = -20000;
PRAGMA cache_size;

Bentuk negatif lebih mudah dipahami: kamu bilang "beri aku cache 20 MB" alih-alih berhitung dengan ukuran halaman. Untuk aplikasi kecil, 20 sampai 50 MB sudah lebih dari cukup. Untuk beban kerja yang banyak membaca pada database yang lebih besar, naikkan lagi. Seperti synchronous, cache_size juga berlaku per koneksi.

mmap_size

Memory-mapped I/O memungkinkan SQLite membaca bagian file database langsung dari page cache sistem operasi, tanpa perlu menyalinnya. Ini bisa mempercepat pembacaan di database yang besar:

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

Itu 256 MB. SQLite akan memetakan paling banyak sebesar itu dari database ke memori jika ada ruangnya. Sistem operasi yang mengatur paging-nya, jadi kamu sebenarnya tidak mengalokasikan 256 MB di awal, melainkan mengizinkan pemetaan sampai sebesar itu.

mmap_size unggul di beban kerja yang banyak membaca. Pengaturan ini juga tidak berbahaya di database kecil. Nilai default-nya konservatif, jadi menaikkannya biasanya menguntungkan.

PRAGMA optimize

Query planner memakai statistik untuk memilih index. Statistik yang usang berarti rencana query yang buruk. PRAGMA optimize memperbarui statistik itu dengan biaya murah:

PRAGMA optimize;

Pola yang disarankan adalah menjalankannya tepat sebelum menutup koneksi yang berumur panjang: saat aplikasi dimatikan, atau di akhir request handler yang memegang koneksi cukup lama. Perintah ini cepat (biasanya hitungan milidetik) dan hanya bekerja jika memang ada yang perlu diperbarui.

Perintah ini tidak sama dengan ANALYZE, yang membangun ulang statistik secara penuh. optimize adalah versi ringannya yang boleh sering dijalankan.

Membaca Semua Pengaturan

Jika kamu ingin melihat konfigurasi sebuah koneksi saat ini, query PRAGMA-nya tanpa penugasan:

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA foreign_keys;
PRAGMA busy_timeout;
PRAGMA cache_size;
PRAGMA page_size;
PRAGMA temp_store;

Ini berguna saat debugging: jika kamu terhubung dari driver lain lalu bertanya-tanya kenapa perilakunya berubah, penyebabnya hampir selalu perbedaan PRAGMA.

Ada juga PRAGMA pragma_list; yang menampilkan setiap PRAGMA yang didukung build tersebut:

PRAGMA pragma_list;

Bukan sesuatu yang perlu kamu hafalkan, tapi praktis saat kamu membutuhkannya.

Pengaturan yang Diatur Saat Pembuatan, Bukan Saat Berjalan

Beberapa PRAGMA mengatur file database itu sendiri dan hanya berlaku sebelum ada tabel yang dibuat:

• PRAGMA page_size = 8192;: ukuran halaman di disk. Default-nya 4096, yang sudah cukup untuk kebanyakan beban kerja. Halaman yang lebih besar membantu untuk baris yang besar.
• PRAGMA encoding = 'UTF-8';: encoding teks.

PRAGMA page_size = 8192;
PRAGMA encoding = 'UTF-8';
CREATE TABLE ...

Jika kamu mengubah page_size pada database yang sudah ada, kamu perlu menjalankan VACUUM agar perubahannya berlaku. Atur sekali saat pembuatan, lalu lupakan.

Potongan Kode Pengaturan Koneksi yang Nyata

Di kode aplikasi, bagian ini biasanya ada di kode yang membuka koneksi. Secara konsep:

-- Run once on every new connection:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- Run periodically, or before close:
PRAGMA optimize;

temp_store = MEMORY menyimpan tabel dan index sementara di RAM, yang mempercepat query yang perlu mengurutkan atau mengagregasi tanpa index.

Itulah seluruh daftar periksa untuk produksi. Setengah lusin baris, dan SQLite berubah dari "cukup untuk development" menjadi "layak untuk beban kerja sungguhan."

Selanjutnya: Error yang Umum

Meskipun PRAGMA-mu sudah bagus, kamu tetap akan bertemu error SQLite yang biasa muncul: database is locked, disk I/O error, constraint failed. Halaman berikutnya membahas arti sebenarnya dari masing-masing error dan cara memperbaikinya.