ANALYZE dan VACUUM di SQLite: Statistik dan Mengambil Kembali Ruang

Dua Tugas Pemeliharaan yang Berbeda

ANALYZE dan VACUUM sering disebut bersamaan, tapi keduanya memperbaiki masalah yang berbeda.

• ANALYZE mengumpulkan statistik tentang datamu agar query planner membuat pilihan yang lebih cerdas. Ia menulis ke tabel bernama sqlite_stat1 dan tidak menyentuh baris datamu.
• VACUUM membangun ulang file itu sendiri untuk mengambil kembali page yang tidak terpakai dan merapikan fragmentasi penyimpanan. Ia tidak mengubah rencana query secara langsung.

Kalau query memilih index yang salah, kamu butuh ANALYZE. Kalau ukuran file lebih besar dari seharusnya setelah banyak penghapusan, kamu butuh VACUUM. Mencampuradukkan keduanya membuang banyak waktu pemeliharaan.

Apa yang Sebenarnya Dilakukan ANALYZE

Query planner harus menebak. Saat melihat WHERE status = 'active', ia perlu memperkirakan berapa baris yang cocok, satu atau sejuta, untuk memutuskan apakah akan memakai index atau memindai tabel. Tanpa statistik, ia kembali ke aturan perkiraan yang kasar.

ANALYZE menelusuri setiap index dan mencatat ringkasan tentang bagaimana nilai-nilai tersebar:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT,
  customer_id INTEGER
);

CREATE INDEX idx_orders_status ON orders(status);

INSERT INTO orders(status, customer_id) VALUES
  ('shipped', 1), ('shipped', 2), ('shipped', 3),
  ('shipped', 4), ('pending', 5);

ANALYZE;

SELECT * FROM sqlite_stat1;

Baris di sqlite_stat1 memberi tahu planner kira-kira berapa baris yang dimiliki index dan berapa duplikat yang dimiliki sebuah key pada umumnya. Lain kali kamu menjalankan query WHERE status = 'pending', planner tahu bahwa pending jarang muncul dan memakai index. Untuk WHERE status = 'shipped', planner mungkin memutuskan bahwa pemindaian lebih murah.

Kamu bisa menganalisis satu tabel atau satu index saja, bukan seluruh database:

ANALYZE orders;
ANALYZE idx_orders_status;

Jalankan ANALYZE setelah memuat data dalam jumlah besar, setelah perubahan skema yang besar, atau saat kamu melihat planner memilih rencana yang buruk pada tabel yang sebaran datanya sudah bergeser.

PRAGMA optimize: Pilihan Bawaan yang Modern

Menjalankan ANALYZE membabi buta setiap kali koneksi ditutup itu boros, karena sebagian besar waktu tidak ada perubahan yang cukup berarti. SQLite menyediakan pembungkus yang lebih cerdas:

PRAGMA optimize;

PRAGMA optimize memeriksa seberapa banyak database berubah sejak analisis terakhir dan menjalankan ANALYZE hanya pada tabel yang membutuhkannya. Rekomendasi resminya adalah memanggilnya di setiap koneksi berumur panjang tepat sebelum ditutup, dan secara berkala pada koneksi yang tetap terbuka berjam-jam.

Perintah ini murah kalau tidak ada yang berubah dan efektif kalau ada yang berubah. Pakai optimize lebih dulu, dan pakai ANALYZE mentah hanya kalau kamu perlu memaksa pembaruan.

Apa yang Sebenarnya Dilakukan VACUUM

Saat kamu menghapus baris atau membuang tabel, SQLite menandai page tersebut sebagai kosong tapi tidak mengecilkan file. Page kosong itu dipakai ulang oleh insert berikutnya, jadi biasanya ini tidak masalah. Tapi ada dua hal yang menumpuk selama database terus berubah:

1. Ruang kosong yang tidak terlihat oleh sistem operasi. File .db milikmu tetap berukuran 2 GB meskipun data aktifnya hanya 800 MB.
2. Fragmentasi. Baris dari tabel yang sama akhirnya tersebar di page yang tidak berdampingan, sehingga pemindaian menjadi lebih lambat.

VACUUM memperbaiki keduanya dengan menyalin seluruh database ke file baru yang tersusun rapat, lalu mengganti file aslinya:

CREATE TABLE logs (id INTEGER PRIMARY KEY, body TEXT);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO logs(body) SELECT 'entry ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

Setelah VACUUM, ukuran file sama seperti kalau kamu hanya memasukkan 100 baris yang tersisa dari awal. Efek sampingnya, semua rowid tetap sama, tapi tata letak di disk kembali berurutan.

Beberapa hal yang perlu kamu tahu sebelum menjalankannya:

• Perintah ini butuh lock eksklusif pada database selama berjalan. Tidak ada koneksi lain yang bisa menulis.
• Perintah ini butuh ruang disk kosong kira-kira dua kali ukuran database, karena file baru dibangun di samping file lama.
• Perintah ini tidak bisa berjalan di dalam transaksi, dan akan error kalau ada transaksi aktif yang masih terbuka.
• Pada database berukuran beberapa GB, prosesnya bisa memakan waktu lama. Rencanakan dengan baik.

Kapan Sebaiknya Menjalankan VACUUM

Untuk sebagian besar aplikasi: jangan, kecuali ada perubahan tertentu.

Alasan yang baik untuk menjalankan VACUUM:

• Kamu baru saja membuang tabel besar atau menghapus baris dalam jumlah sangat banyak dan ingin ruang disk kembali.
• Database sudah terus berubah selama bertahun-tahun dan query yang memindai tabel terasa lebih lambat dari dulu.
• Kamu mengirim file database sebagai bagian dari rilis dan ingin ukurannya sekecil mungkin.

Alasan yang buruk:

• "Biar aman saja." Perintah ini menulis ulang seluruh file setiap kali dijalankan. Tidak ada yang aman dari melakukan itu di sistem yang sedang berjalan.
• Setelah setiap kelompok penghapusan. Page yang dibebaskan toh akan dipakai ulang.

auto_vacuum dan Incremental VACUUM

Kalau kamu ingin SQLite mengelola page kosong secara otomatis, atur auto_vacuum saat database dibuat. Pengaturan ini tidak bisa diubah belakangan tanpa vacuum penuh:

PRAGMA auto_vacuum = INCREMENTAL;

Ada tiga mode:

• NONE (bawaan): page kosong tetap berada di file dan dipakai ulang oleh insert berikutnya.
• FULL: setiap commit yang membebaskan page juga memotong ukuran file. Praktis, tapi setiap transaksi menanggung biayanya.
• INCREMENTAL: SQLite mencatat page kosong tapi baru melepaskannya saat kamu memintanya:

PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t;

PRAGMA incremental_vacuum(100);
PRAGMA freelist_count;

PRAGMA incremental_vacuum(N) mengembalikan hingga N page kosong ke sistem operasi. Prosesnya cepat, tidak memegang lock eksklusif lama, dan bisa kamu jalankan sesuai jadwal. Inilah titik ideal untuk database yang sering ditulisi dan harus tetap ringkas tanpa biaya VACUUM penuh.

VACUUM INTO: Mengekspor Salinan yang Ringkas

VACUUM INTO menulis salinan baru yang ringkas ke file baru tanpa menyentuh file aslinya:

VACUUM INTO 'backup.db';

Ini sangat berguna:

• Backup. Hasilnya adalah snapshot yang konsisten dan sudah di-vacuum sepenuhnya: tidak ada page yang setengah tertulis, tidak ada .wal yang perlu dipikirkan. Lebih baik daripada menyalin file dengan cp.
• Mengecilkan file tanpa memblokir penulis terlalu lama. Kamu melakukan vacuum ke file terpisah, lalu menukarnya secara atomik. Penulis tidak diblokir selama seluruh proses vacuum.
• Distribusi. Kirim salinan database pengembangan yang kecil dan bebas fragmentasi.

File tujuan tidak boleh sudah ada. Kalau sudah ada, kamu akan mendapat error.

Resep Pemeliharaan yang Praktis

Untuk database aplikasi pada umumnya:

-- On each long-lived connection, before close:
PRAGMA optimize;

-- After a large bulk load or schema change:
ANALYZE;

-- After you delete a lot of data and want disk back:
VACUUM;

-- For backups:
VACUUM INTO '/backups/app-2026-04-23.db';

Kalau database sangat sering ditulisi dan dihapus serta tetap online 24/7, atur auto_vacuum = INCREMENTAL saat database dibuat dan jalankan PRAGMA incremental_vacuum(N) secara berkala, misalnya sekali sehari saat lalu lintas sedang sepi.

Mendiagnosis "Kenapa File Saya Begitu Besar?"

Dua pragma memberi tahu apa yang sedang terjadi:

CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 10000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t WHERE x > 100;

PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA page_size;

• page_count × page_size = ukuran file saat ini.
• freelist_count × page_size = byte yang terbuang di page yang tidak terpakai.

Kalau freelist_count adalah bagian besar dari page_count, VACUUM (atau incremental_vacuum) akan mengecilkan file secara nyata. Kalau kecil, file sudah tersusun efisien dan VACUUM tidak akan membantu.

Kesalahan Umum

• Menjalankan VACUUM di dalam transaksi. Tidak bisa. Lakukan commit lebih dulu.
• Lupa bahwa VACUUM butuh ruang disk kosong. Database 10 GB butuh sekitar 10 GB ruang kosong lagi untuk di-vacuum.
• Mengatur auto_vacuum setelah data sudah ada. Pengaturan ini tidak berpengaruh sampai VACUUM penuh berikutnya. Atur saat database dibuat kalau kamu menginginkannya.
• Menjalankan ANALYZE dan berharap file menjadi lebih kecil. Itu tugas VACUUM.
• Menjalankan VACUUM dan berharap rencana query menjadi lebih baik. Itu tugas ANALYZE.

Kedua perintah ini saling melengkapi, dan yang satu tidak menggantikan yang lain.

Berikutnya: Transaksi

Perintah pemeliharaan seperti VACUUM menyoroti sesuatu yang selama ini kita anggap biasa: model transaksi SQLite dan apa yang dikuncinya, serta kapan. Bab berikutnya dimulai dari sana: cara kerja transaksi, apa yang sebenarnya dijamin oleh BEGIN / COMMIT / ROLLBACK, dan cara memakainya agar pekerjaan yang terdiri dari banyak pernyataan tetap atomik.