SQLite ANALYZE ve VACUUM: İstatistik ve Alan Geri Kazanımı

İki Farklı Bakım İşi

ANALYZE ve VACUUM çoğu zaman birlikte anılır ama aslında bambaşka sorunları çözerler.

• ANALYZE, sorgu planlayıcının daha akıllı kararlar verebilmesi için verileriniz hakkında istatistik toplar. Bu bilgileri sqlite_stat1 adındaki tabloya yazar; gerçek satırlarınıza dokunmaz.
• VACUUM ise dosyanın kendisini yeniden inşa ederek kullanılmayan sayfaları geri kazanır ve depolamayı birleştirir. Sorgu planlarını doğrudan değiştirmez.

Sorgular yanlış indeksi seçiyorsa ihtiyacınız olan ANALYZE'dır. Çok sayıda silme işleminden sonra dosya olması gerekenden büyük kaldıysa VACUUM çalıştırmalısınız. Bu ikisini karıştırmak, boşa harcanan bakım zamanı demek.

ANALYZE Aslında Ne Yapıyor?

Sorgu planlayıcı tahmin yürütmek zorunda. WHERE status = 'active' gibi bir koşul gördüğünde, kaç satırın eşleşeceğini kestirmesi gerekir — bir tane mi, bir milyon mu? Bu tahmine göre indeks mi kullanacağına yoksa tabloyu baştan sona mı tarayacağına karar verir. İstatistik olmadan kaba sezgilere mahkûm kalır.

ANALYZE her indeksi gezerek değerlerin nasıl dağıldığına dair özet bilgileri kaydeder:

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;

sqlite_stat1 tablosundaki satır, planlayıcıya indeksin kabaca kaç satır içerdiğini ve tipik bir anahtarın ne kadar tekrar ettiğini söyler. Bir dahaki sefere WHERE status = 'pending' sorgusunu çalıştırdığında, planlayıcı pending değerinin nadir olduğunu bilir ve indekse başvurur; WHERE status = 'shipped' içinse tam tarama yapmanın daha ucuza geleceğine karar verebilir.

Tüm veritabanı yerine tek bir tabloyu veya indeksi de analiz edebilirsiniz:

ANALYZE orders;
ANALYZE idx_orders_status;

ANALYZE komutunu toplu veri yüklemelerinden sonra, büyük şema değişikliklerinin ardından ya da dağılımı kayan tablolarda planlayıcının kötü planlar seçtiğini fark ettiğinde çalıştır.

PRAGMA optimize: Modern Varsayılan

Her bağlantı kapanışında körlemesine ANALYZE çalıştırmak israftır — çoğu zaman, fark yaratacak kadar bir şey değişmemiştir. SQLite bunun için daha akıllı bir sarmalayıcı sunuyor:

PRAGMA optimize;

PRAGMA optimize, veritabanının son analizden bu yana ne kadar değiştiğini kontrol eder ve yalnızca ihtiyaç duyan tablolar üzerinde ANALYZE çalıştırır. Resmi tavsiye şu: uzun ömürlü her bağlantıda, kapatmadan hemen önce çağırın; saatlerce açık kalan bağlantılarda ise belli aralıklarla tetikleyin.

Bir şey değişmediyse maliyeti yok denecek kadar düşük, değiştiyse de işini yapıyor. Önce optimize'a uzanın; ham ANALYZE'ı yalnızca yenilemeyi zorla tetiklemeniz gerektiğinde kullanın.

VACUUM Aslında Ne Yapar?

Satır sildiğinizde ya da bir tabloyu drop ettiğinizde SQLite o sayfaları boş olarak işaretler ama dosyayı küçültmez. Boşa çıkan sayfalar sonraki insert'lerde tekrar kullanılır, yani çoğu zaman sorun olmaz. Ne var ki yıllar süren yoğun değişimle birlikte iki şey birikir:

1. İşletim sisteminin göremediği boş alan. Canlı veriniz sadece 800 MB olsa da .db dosyanız 2 GB olarak kalır.
2. Parçalanma (fragmentation). Aynı tablonun satırları birbirine komşu olmayan sayfalara dağılır, bu da tarama performansını düşürür.

VACUUM her iki sorunu da şöyle çözer: tüm veritabanını sıfırdan, sıkıca paketlenmiş yeni bir dosyaya kopyalar ve orijinalin yerine geçirir:

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 'kayıt ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

VACUUM çalıştıktan sonra dosya, sanki o 100 satırı sıfırdan eklemişsiniz gibi bir boyuta iner. Yan etki olarak rowid'ler aynı kalır, ama diskteki yerleşim yeniden bitişik (contiguous) hale gelir.

Çalıştırmadan önce bilmeniz gereken birkaç şey var:

• İşlem süresince veritabanı üzerinde özel kilit (exclusive lock) alır. Başka hiçbir bağlantı yazamaz.
• Diskte veritabanının yaklaşık iki katı kadar boş alan ister — yeni dosyayı eskisinin yanında oluşturur.
• Bir transaction içinde çalışmaz; açık aktif bir transaction varsa hata verir.
• Birkaç GB'lık bir veritabanında epey uzun sürebilir. Buna göre planlayın.

SQLite VACUUM ne zaman çalıştırılır?

Çoğu uygulama için cevap basit: somut bir gerekçe yoksa çalıştırmayın.

VACUUM çalıştırmak için iyi nedenler:

• Büyük bir tabloyu yeni drop ettiniz ya da çok sayıda satır sildiniz ve disk alanını geri almak istiyorsunuz.
• Veritabanı yıllardır yoğun kullanımda ve tablo tarayan sorgular eskisinden yavaş hissettiriyor.
• Bir release ile birlikte veritabanı dosyası dağıtacaksınız ve dosyayı olabildiğince küçültmek istiyorsunuz.

Kötü nedenler:

• "Garanti olsun diye." Her seferinde dosyanın tamamını yeniden yazıyor. Canlı bir sistemde bunu yapmanın hiçbir "garantisi" yok.
• Her toplu silme işleminden sonra. Boşalan sayfalar zaten yeniden kullanılacaktı.

auto_vacuum ve incremental VACUUM

SQLite'ın boş sayfaları otomatik yönetmesini istiyorsanız auto_vacuum ayarını veritabanını oluştururken vermeniz gerekir — sonradan değiştirmek için tam bir vacuum yapmak şart:

PRAGMA auto_vacuum = INCREMENTAL;

Üç farklı mod var:

• NONE (varsayılan): boşa çıkan sayfalar dosyada kalır ve sonraki INSERT işlemlerinde tekrar kullanılır.
• FULL: sayfa boşaltan her commit, dosyayı da kısaltır. Pratik bir seçim ama maliyetini her transaction ödüyor.
• INCREMENTAL: SQLite boş sayfaları takip eder, ancak siz isteyene kadar serbest bırakmaz:

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) komutu, en fazla N adet boş sayfayı işletim sistemine geri verir. Hızlıdır, exclusive kilidi uzun süre tutmaz ve zamanlanmış görev olarak rahatça çalıştırılabilir. Tam bir VACUUM çalıştırmanın maliyetine girmeden kompakt kalması gereken, yazma yoğun veritabanları için en ideal çözüm budur.

VACUUM INTO: Kompakt Bir Kopya Dışa Aktarma

VACUUM INTO, orijinal dosyaya hiç dokunmadan yeni bir dosyaya tertemiz, kompakt bir kopya yazar:

VACUUM INTO 'backup.db';

Bunun gerçekten işe yaradığı yerler şunlar:

• Yedekleme. Çıktı, tutarlı ve baştan sona vakumlanmış bir anlık görüntü olur — yarım yazılmış sayfa yok, uğraşacağınız bir .wal dosyası da yok. Dosyayı cp ile kopyalamaktan çok daha sağlıklı.
• Yazıcıları uzun süre kilitlemeden küçültme. Yan dosyaya vakumlayıp ardından atomik biçimde yer değiştirirsiniz. Böylece yazıcılar tüm vacuum süresi boyunca bloklanmaz.
• Dağıtım. Geliştirme veritabanının ufak ve fragmentasyonsuz bir kopyasını paylaşabilirsiniz.

Hedef dosyanın önceden var olmaması gerekir. Varsa hata alırsınız.

Pratik bir bakım reçetesi

Tipik bir uygulama veritabanı için:

-- Uzun ömürlü her bağlantıda, kapatmadan önce:
PRAGMA optimize;

-- Büyük bir toplu yükleme veya şema değişikliğinden sonra:
ANALYZE;

-- Çok fazla veri sildikten sonra ve diski geri kazanmak istediğinizde:
VACUUM;

-- Yedeklemeler için:
VACUUM INTO '/backups/app-2026-04-23.db';

Veritabanı yoğun şekilde yazma/silme alıyorsa ve 7/24 ayakta duruyorsa, oluşturma anında auto_vacuum = INCREMENTAL ayarını yapın ve düşük trafikli saatlerde — mesela günde bir kez — PRAGMA incremental_vacuum(N) komutunu periyodik olarak çalıştırın.

"Dosyam neden bu kadar büyük?" sorusunu teşhis etmek

Olan biteni anlamak için işinize yarayacak iki pragma var:

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 = dosyanın güncel boyutu.
• freelist_count × page_size = boşa harcanan, kullanılmayan sayfa boyutu.

freelist_count, page_count'a oranla büyükse, VACUUM (veya incremental_vacuum) çalıştırmak dosya boyutunu gözle görülür biçimde küçültür. Küçükse dosya zaten verimli şekilde paketlenmiştir ve VACUUM bir işe yaramaz.

Sık Yapılan Hatalar

• VACUUM'u transaction içinde çalıştırmak. Çalışmaz. Önce commit etmeniz gerekir.
• VACUUM'un boş disk alanı istediğini unutmak. 10 GB'lık bir veritabanını vakumlamak için yaklaşık 10 GB daha boş alana ihtiyacınız vardır.
• Veriler oluştuktan sonra auto_vacuum ayarlamak. Bir sonraki tam VACUUM'a kadar hiçbir etkisi olmaz. İstiyorsanız bunu veritabanı ilk oluşturulduğunda ayarlayın.
• ANALYZE çalıştırıp dosyanın küçülmesini beklemek. O VACUUM'un işi.
• VACUUM çalıştırıp daha iyi sorgu planları beklemek. O da ANALYZE'in işi.

İki komut birbirini tamamlar; biri diğerinin yerine geçmez.

Sırada: Transaction'lar

VACUUM gibi bakım komutları, şimdiye kadar verili kabul ettiğimiz bir konuyu gün yüzüne çıkarıyor: SQLite'ın transaction modeli, neyi ne zaman kilitlediği. Sonraki bölüm tam buradan başlıyor — transaction'lar nasıl çalışır, BEGIN / COMMIT / ROLLBACK aslında neyi garanti eder ve birden fazla ifadeden oluşan işleri atomik tutmak için bunları nasıl kullanırsınız.