VACUUM dan Maintenance Database

Menghapus jutaan baris dari tabel seharusnya membuat database lebih ramping — tetapi berkas database SQLite tidak mengecil setelah DELETE. Engine SQLite mempertahankan halaman-halaman yang sudah kosong di dalam berkas agar operasi selanjutnya bisa memakainya kembali tanpa meminta ruang baru ke sistem operasi. Perilaku ini masuk akal untuk database yang terus tumbuh, tetapi menjadi pemborosan ketika data besar benar-benar pergi untuk selamanya. Bab ini membahas cara mengembalikan ruang itu lewat VACUUM, opsi auto_vacuum, serta dua alat perawatan rutin: REINDEX dan PRAGMA optimize.

Mengapa Berkas Tidak Mengecil setelah DELETE #

Kita buktikan dengan database toko berisi 200.000 baris log yang setiap barisnya berisi teks panjang:

CREATE TABLE log_pesanan (
  id INTEGER PRIMARY KEY,
  kode TEXT NOT NULL,
  payload TEXT NOT NULL
);

WITH RECURSIVE angka(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM angka WHERE x < 200000
)
INSERT INTO log_pesanan (kode, payload)
SELECT 'LOG-' || printf('%06d', x),
       printf('payload panjang nomor %d untuk mengisi halaman database dengan data', x)
FROM angka;
SELECT count(*) FROM log_pesanan;

Output yang Dihasilkan:

200000

ls -l toko.db

Output yang Dihasilkan:

-rw-r--r-- 1 z z 18636800 Oct 13 05:16 toko.db

Sekarang hapus 90 persen barisnya:

DELETE FROM log_pesanan WHERE id > 20000;
SELECT count(*) FROM log_pesanan;
PRAGMA freelist_count;
PRAGMA page_count;
PRAGMA page_size;

Output yang Dihasilkan:

20000
4102
4550
4096

ls -l toko.db

Output yang Dihasilkan:

-rw-r--r-- 1 z z 18636800 Oct 13 05:16 toko.db

Angka-angka di atas menceritakan semuanya. Berkas masih 18.636.800 byte — tepat 4.550 halaman kali 4.096 byte — padahal 4.102 halaman di antaranya sudah kosong (freelist_count) dan hanya 448 halaman yang masih berisi data. Halaman kosong itu akan dipakai ulang oleh INSERT berikutnya, sehingga ukuran berkas hanya berhenti bertumbuh, tidak mundur.

VACUUM: Membangun Ulang Berkas #

VACUUM membangun ulang seluruh berkas database: engine SQLite menyalin data ke berkas sementara yang padat, lalu menggantikan berkas lama dengan salinan baru. Perintahnya sendiri berjalan tanpa output:

VACUUM;

Kita periksa hasilnya dari dua sisi — halaman kosong yang tersisa, lalu ukuran berkas:

PRAGMA freelist_count;
PRAGMA page_count;

Output yang Dihasilkan:

0
448

Freelist kini nol: seluruh 4.102 halaman kosong telah dibuang, tersisa tepat 448 halaman yang benar-benar berisi data. Ukuran berkasnya pun ikut menyusut:

ls -l toko.db

Output yang Dihasilkan:

-rw-r--r-- 1 z z 1835008 Oct 13 05:16 toko.db

Berkas turun dari 18.636.800 byte menjadi 1.835.008 byte — sekitar 90 persen ruangnya kembali ke sistem operasi. VACUUM butuh ruang kosong kira-kira dua kali ukuran database (berkas lama plus salinan sementara) dan tidak boleh berjalan di dalam transaksi yang terbuka:

sqlite3 toko.db "BEGIN; VACUUM;"

Output yang Dihasilkan:

Error: stepping, cannot VACUUM from within a transaction

VACUUM INTO: Salinan Rapi Tanpa Mengganggu #

Sejak SQLite 3.27.0, VACUUM INTO menulis salinan database yang sudah dipadatkan ke berkas baru tanpa menyentuh berkas asli — berguna untuk backup sambil merapikan:

VACUUM INTO 'backup-toko.db';

Isi salinan bisa langsung dibaca seperti database biasa:

sqlite3 backup-toko.db "SELECT count(*) FROM log_pesanan;"

Output yang Dihasilkan:

20000

Salinan memuat data yang sama persis dalam struktur yang baru ditata ulang. Ukurannya pun sama dengan hasil VACUUM:

ls -l toko.db backup-toko.db

Output yang Dihasilkan:

-rw-r--r-- 1 z z 1835008 Oct 13 05:16 backup-toko.db
-rw-r--r-- 1 z z 1835008 Oct 13 05:16 toko.db

Teknik backup yang lebih lengkap (termasuk perintah .backup) dibahas pada chapter Backup dan Restore.

auto_vacuum: Tiga Pilihan Kebijakan #

Menjalankan VACUUM secara manual kadang terlalu kasar. PRAGMA auto_vacuum menawarkan otomasi dengan tiga mode yang harus dipilih sebelum tabel pertama dibuat:

Perbandingan FULL dan INCREMENTAL pada database baru berisi 100.000 baris arsip yang lalu dihapus 90 persen:

PRAGMA auto_vacuum = FULL;
CREATE TABLE arsip (id INTEGER PRIMARY KEY, isi TEXT);
WITH RECURSIVE angka(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM angka WHERE x < 100000
)
INSERT INTO arsip (isi) SELECT 'isi arsip nomor ' || printf('%06d', x) FROM angka;
DELETE FROM arsip WHERE id > 10000;
PRAGMA freelist_count;
PRAGMA page_count;

Output yang Dihasilkan:

0
77

Dengan FULL, freelist_count langsung 0 — 90 persen berkas sudah dipangkas begitu DELETE selesai. Versi INCREMENTAL menahannya:

PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE arsip (id INTEGER PRIMARY KEY, isi TEXT);
WITH RECURSIVE angka(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM angka WHERE x < 100000
)
INSERT INTO arsip (isi) SELECT 'isi arsip nomor ' || printf('%06d', x) FROM angka;
DELETE FROM arsip WHERE id > 10000;
PRAGMA freelist_count;
PRAGMA incremental_vacuum;
PRAGMA freelist_count;

Output yang Dihasilkan:

687
0

Sebelum pemangkasan, 687 halaman menunggu di freelist; PRAGMA incremental_vacuum — dengan atau tanpa argumen jumlah halaman — memindahkannya keluar berkas. Mode INCREMENTAL cocok untuk database besar yang tidak sanggup berhenti lama demi satu VACUUM penuh.

REINDEX dan PRAGMA optimize #

Dua perintah perawatan melengkapi gambaran ini. REINDEX membangun ulang index yang ditunjuknya — berguna setelah kerusakan minor, perubahan collation, atau migrasi besar. Sebagai bahan latihan, kita siapkan tabel log_pesanan berisi 20.000 baris kode unik, lalu buat index di atasnya:

CREATE TABLE log_pesanan (kode TEXT);
WITH RECURSIVE angka(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM angka WHERE x < 20000
)
INSERT INTO log_pesanan SELECT 'LOG-' || printf('%05d', x) FROM angka;
CREATE INDEX idx_log_kode ON log_pesanan(kode);
REINDEX;

Perintah ini berjalan tanpa output. PRAGMA optimize (hadir sejak SQLite 3.18.0) memeriksa statistik query planner dan menjalankan ANALYZE hanya pada tabel yang dinilai perlu — jauh lebih murah daripada ANALYZE penuh yang dibahas pada chapter ANALYZE dan Statistik Query Planner:

PRAGMA optimize;
SELECT tbl, idx, stat FROM sqlite_stat1;

Output yang Dihasilkan:

log_pesanan|idx_log_kode|20000 1

PRAGMA optimize juga diam — efeknya terlihat dari terisinya sqlite_stat1. Pola praktisnya: jalankan sekali sebelum koneksi ditutup. Terakhir, kesehatan berkas sebaiknya diperiksa berkala dengan PRAGMA quick_check — chapter Integrity Check dan Perbaikan membahas pemeriksaan menyeluruh beserta penanganan kerusakannya.