KAL
Menu

Pemeriksaan Integritas

PRAGMA integrity_check, PRAGMA optimize, dan VACUUM untuk memulihkan ruang.

Daftar isi

Basis data yang dioperasikan berbulan-bulan membutuhkan pemeliharaan terjadwal, mulai dari pemeriksaan kerusakan, penalaan rencana kueri, sampai pemulihan ruang yang ditinggalkan penghapusan baris. Topik ini membahas PRAGMA integrity_check dan quick_check untuk memeriksa struktur, PRAGMA optimize sebagai pemeliharaan rutin, serta VACUUM yang membangun ulang berkas basis data. Seluruhnya dijalankan lewat sqlite3 pada satu basis data perpustakaan yang sama.

PRAGMA integrity_check dan quick_check#

PRAGMA integrity_check memeriksa kesehatan struktur basis data secara menyeluruh, dari setiap halaman hingga korespondensi isi index dengan isi tabelnya. Basis data yang sehat menjawab satu baris berupa ok, sedangkan kerusakan dilaporkan baris demi baris lengkap dengan halaman yang bermasalah. Pemeriksaan ini tepat dijalankan setelah kejadian tidak normal, misalnya daya padam di tengah penulisan, basis data yang disalin ketika masih terbuka, atau media penyimpanan yang mulai tidak dapat diandalkan.

Pendampingnya, PRAGMA quick_check, menjalankan sebagian besar pemeriksaan yang sama tetapi melewati pemeriksaan silang isi index dengan isi tabel, sehingga selesai lebih cepat pada basis data besar. Keduanya bekerja pada basis data apa pun, termasuk basis data dalam memori yang dibuat melalui :memory:. Blok berikut memeriksa basis data kecil berisi tiga buku dengan kedua pragma sekaligus.

sql 
CREATE TABLE buku (
  id INTEGER PRIMARY KEY,
  judul TEXT NOT NULL,
  penulis TEXT NOT NULL
);
INSERT INTO buku (judul, penulis) VALUES
  ('Anak Sungai', 'Rahmat Hidayat'),
  ('Rumah Pohon', 'Lina Marlina'),
  ('Lentera Malam', 'Dewi Anggraini');
PRAGMA integrity_check;
PRAGMA quick_check;
text Keluaran 
ok
ok

Dua baris ok menegaskan bahwa struktur tabel dan indexnya konsisten. Pada basis data yang rusak, kedua pragma ini mengubah jawabannya menjadi daftar lokasi kerusakan, dan tidak ada pernyataan tulis yang dapat menghidupkan kembali data yang hilang, sehingga jalan keluarnya tinggal memulihkan dari cadangan. Pemeriksaan berkala memperpendek jarak antara kerusakan terjadi dan kerusakan ketahuan.

PRAGMA optimize#

PRAGMA optimize menjalankan pemeliharaan ringan untuk perencana kueri, yaitu memperbarui statistik pada tabel yang belum dianalisis atau yang statistiknya sudah usang, sehingga pemilihan index pada kueri berikutnya lebih tepat. Dokumentasi SQLite menganjurkan pemanggilan ini sebelum koneksi ditutup, terutama pada aplikasi berumur panjang yang koneksi terbukanya bertahan berhari-hari. Biayanya kecil dan ia tidak pernah mengubah data aplikasi, karena hasil pekerjaannya berupa statistik untuk perencana.

Blok berikut menyiapkan basis data perpustakaan berisi tiga anggota dan sepuluh ribu baris peminjaman, lalu menjalankan pemeliharaan rutin di atasnya. PRAGMA optimize tidak menampilkan baris keluaran apa pun, sehingga PRAGMA integrity_check disertakan sebagai penutup yang memberi satu baris jawaban.

bash 
rm -f perpustakaan.db
sqlite3 perpustakaan.db <<'SQL'
CREATE TABLE anggota (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  saldo_denda INTEGER NOT NULL DEFAULT 0
);
INSERT INTO anggota (nama, saldo_denda) VALUES
  ('Budi Santoso', 3000),
  ('Citra Kirana', 1500),
  ('Dewi Lestari', 0);
CREATE TABLE peminjaman (
  id INTEGER PRIMARY KEY,
  anggota_id INTEGER NOT NULL,
  buku_id INTEGER NOT NULL,
  tanggal TEXT NOT NULL
);
INSERT INTO peminjaman (anggota_id, buku_id, tanggal)
WITH RECURSIVE deret(i) AS (
  SELECT 1 UNION ALL SELECT i + 1 FROM deret WHERE i < 10000
)
SELECT 1 + (i % 30), 1 + (i % 50), '2026-01-' || printf('%02d', 1 + (i % 28)) FROM deret;
PRAGMA optimize;
PRAGMA integrity_check;
SQL
text Keluaran 
ok

Satu baris ok di situ berasal dari integrity_check, sedangkan optimize diam tanpa keluaran meskipun pekerjaannya selesai. Diamnya itu normal dan bukan tanda gagal, karena pragma ini memang tidak menampilkan laporan atas pekerjaannya. Basis data yang sama dipakai dua bagian berikutnya untuk memeragakan pemulihan ruang.

VACUUM memulihkan ruang#

Penghapusan baris pada SQLite tidak memperkecil berkas. Halaman yang menjadi kosong masuk ke freelist, yaitu daftar halaman bebas yang akan dipakai ulang oleh penyisipan berikutnya, sedangkan ukuran berkasnya tetap. Keadaan itu terbaca lewat dua pragma, yaitu PRAGMA page_count untuk jumlah halaman dan PRAGMA freelist_count untuk jumlah halaman yang menganggur.

bash 
sqlite3 perpustakaan.db <<'SQL'
PRAGMA page_count;
PRAGMA freelist_count;
SQL
text Keluaran 
57
0

Sepuluh ribu baris peminjaman beserta tabel anggotanya menempati 57 halaman dan tidak ada satu pun halaman yang menganggur. Blok berikut menghapus separuh riwayat peminjaman, mengukur ulang, menjalankan VACUUM, lalu mengukur untuk ketiga kalinya.

bash 
sqlite3 perpustakaan.db <<'SQL'
DELETE FROM peminjaman WHERE id > 5000;
PRAGMA page_count;
PRAGMA freelist_count;
VACUUM;
PRAGMA page_count;
PRAGMA freelist_count;
SQL
text Keluaran 
57
27
30
0

Setelah penghapusan, jumlah halaman tetap 57 tetapi 27 halaman di antaranya kini menganggur, dan ukuran berkas di disk tidak berubah sedikit pun. VACUUM membangun ulang seluruh berkas dari awal, menyalin isi yang masih dipakai ke berkas baru yang rapat, lalu menggantikan berkas lama, sehingga jumlah halaman turun menjadi 30 dan freelist kembali kosong. Ruang yang dipulihkan berupa 27 halaman pada basis data contoh ini, dan porsinya jauh lebih besar pada basis data yang kerap dihapus.

Batasan VACUUM#

VACUUM adalah operasi berat, bukan perintah harian. Ia membutuhkan ruang kosong di disk seukuran basis data yang dibangun ulang, karena berkas baru disusun sampai selesai sebelum menggantikan yang lama. Ia juga menulis ulang seluruh isi, sehingga biayanya bertumbuh sebanding dengan ukuran basis data, bukan dengan jumlah ruang yang dipulihkan.

Operasi ini menolak berjalan di tengah transaksi aktif. Blok berikut membuka transaksi lalu memanggil VACUUM untuk menangkap pesan errornya.

bash 
sqlite3 perpustakaan.db <<'SQL'
BEGIN;
VACUUM;
SQL
text Keluaran 
Error near line 2: cannot VACUUM from within a transaction

Pesan errornya menyebut sebabnya secara langsung, yaitu VACUUM tidak dapat dijalankan dari dalam transaksi. Batasan itu sejalan dengan perannya sebagai operasi yang menulis ulang seluruh berkas, sehingga ia harus menjadi satu unit kerja tersendiri di luar transaksi mana pun. Pemanggilan yang tepat dijadwalkan pada waktu tenang, dipastikan tidak ada transaksi terbuka, dan diberi ruang disk yang cukup.

Jadwal VACUUM yang sehat mengikuti pola pertumbuhan basis datanya, misalnya sebulan sekali pada arsip yang sering dihapus atau hanya setelah penghapusan besar. Basis data yang isinya terus bertambah dan jarang dihapus jarang membutuhkannya, karena halaman bebasnya segera dipakai ulang. Pemutusan keputusan ini dari ukuran berkas yang sesungguhnya membuat VACUUM jarang dipanggil tanpa alasan.

Ringkasan#

  • PRAGMA integrity_check memeriksa seluruh struktur dan menjawab ok pada basis data sehat, sedangkan quick_check adalah versi ringannya yang melewati pemeriksaan silang index.
  • PRAGMA optimize memperbarui statistik perencana kueri dan dianjurkan dipanggil sebelum koneksi ditutup.
  • Penghapusan baris memindahkan halaman ke freelist tanpa mengecilkan berkas, dan VACUUM membangun ulang berkas untuk memulihkannya.
  • VACUUM membutuhkan ruang kosong seukuran basis data dan tidak dapat dijalankan di tengah transaksi.

Referensi#