Elasticsearch'süz Arama: SQLite FTS5 ile Küçük Ölçekli Tam Metin İndeksleme

SQLite FTS5, ayrı bir arama sunucusu kurmadan uygulamanızın veritabanı dosyasının içinde tam metin indeksleme yapmanızı sağlayan bir SQLite modülüdür. Birkaç yüz bin belgeye kadar ölçeklenen projelerde Elasticsearch ya da Meilisearch kurmak çoğu zaman gereğinden büyük bir adımdır: ek bir süreç, ek bellek, ek bir yedekleme ve izleme yükü demektir. FTS5 bu yükü ortadan kaldırır, çünkü indeks de verinin durduğu dosyada yaşar.

Bu yazıda dört somut parçayı ele alıyorum: harici içerik tablosuyla çalışan bir FTS5 şeması ve indeksi tutarlı tutan tetikleyiciler, bm25 sıralaması ile snippet üretimi, kullanıcı girdisini FTS5 sözdizimi hatalarına karşı güvenli hale getirme ve Türkçe metin için tokenizer davranışı. Kod örnekleri yalnızca Python'un standart sqlite3 modülünü kullanır; ek paket gerekmez.

Veriyi zaten SQLite'ta tutuyorsanız, küçük ama sağlam veri boru hatları yazısında anlattığım düzende olduğu gibi, arama katmanı da aynı dosyada kalabilir. Böylece yedekleme, taşıma ve geri yükleme işleri tek dosyaya indirgenir.

FTS5 nedir ve hangi ölçekte yeterlidir?

FTS5, SQLite'ın tam metin arama modülüdür; metni kelimelere (token) böler, ters indeks kurar ve MATCH operatörüyle kelime, ifade, ön ek ve boolean sorgularına cevap verir. Tek yazar, çok okuyucu düzeninde çalışan, belge sayısı yüz binler mertebesinde kalan ve arama sonuçlarının uygulamanın kendi süreci içinde üretildiği projeler için yeterlidir. Ölçek sınırını belge sayısından çok yazma sıklığı ve eşzamanlı yazar sayısı belirler.

FTS5'in sizin için önemli parçaları şunlardır:

• Sanal tablo: CREATE VIRTUAL TABLE ... USING fts5(...) ile kurulur; normal tablo gibi sorgulanır.
• Harici içerik modu: metnin kopyasını saklamaz, asıl tabloya işaret eder; disk kullanımı düşer, tutarlılık sorumluluğu size geçer.
• Yardımcı fonksiyonlar: bm25() alaka puanı, snippet() ve highlight() eşleşme vurgulama.
• Tokenizer'lar: unicode61 (varsayılan), ascii, porter (yalnız İngilizce kök bulma) ve trigram.
• Bakım komutları: rebuild indeksi sıfırdan kurar, optimize parçalı indeksi birleştirir.

Python'un getirdiği SQLite derlemesinde FTS5 olup olmadığını kesin olarak bilmek için tahmin yürütmeyin; sqlite3.connect(':memory:').execute("CREATE VIRTUAL TABLE t USING fts5(x)") çalışıyorsa modül var, no such module: fts5 hatası alıyorsanız yok demektir.

Harici içerik tablosu ve tetikleyiciler indeksi nasıl tutarlı tutar?

Harici içerik (external content) FTS5 tablosu, belge metnini kendi içinde kopyalamaz; content='docs' seçeneğiyle asıl tabloya işaret eder ve yalnızca indeksi saklar. Bu düzen disk kullanımını azaltır ama tutarlılık sorumluluğunu size bırakır: docs tablosuna yazılan her ekleme, güncelleme ve silme aynı işlem içinde indekse de yansımalıdır. Üç tetikleyiciyle bunu garanti altına almak en güvenli yoldur.

Silme işleminde kritik nokta şudur: AFTER DELETE tetikleyicisi çalıştığında satır içerik tablosundan çoktan gitmiştir, oysa FTS5 bir belgeyi indeksten çıkarmak için o belgenin eski metnine ihtiyaç duyar. Doğru kalıp, tabloya kendi adını taşıyan sütunla 'delete' komutu göndermektir: INSERT INTO docs_fts(docs_fts, rowid, title, body) VALUES('delete', old.id, old.title, old.body). Eski metni eksik ya da farklı verirseniz indeks hata vermeden bozulur.

import sqlite3

SEMA = """
CREATE TABLE IF NOT EXISTS docs (
    id    INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body  TEXT NOT NULL
);

CREATE VIRTUAL TABLE IF NOT EXISTS docs_fts USING fts5(
    title, body,
    content='docs', content_rowid='id',
    tokenize='unicode61 remove_diacritics 1'
);

CREATE TRIGGER IF NOT EXISTS docs_ai AFTER INSERT ON docs BEGIN
    INSERT INTO docs_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER IF NOT EXISTS docs_ad AFTER DELETE ON docs BEGIN
    INSERT INTO docs_fts(docs_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER IF NOT EXISTS docs_au AFTER UPDATE ON docs BEGIN
    INSERT INTO docs_fts(docs_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO docs_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;
"""


def baglan(yol='arama.db'):
    con = sqlite3.connect(yol)
    con.executescript(SEMA)
    return con


if __name__ == '__main__':
    con = baglan(':memory:')
    con.executemany(
        'INSERT INTO docs(title, body) VALUES (?, ?)',
        [
            ('Watermark ile artimli cekme', 'Son gorulen zaman damgasini saklayip yalniz yeni satirlari cek.'),
            ('SQLite yedekleme', 'Online backup API ile canli dosyayi kopyala.'),
        ],
    )
    con.execute("UPDATE docs SET body = body || ' Sonra upsert uygula.' WHERE id = 1")
    con.execute('DELETE FROM docs WHERE id = 2')
    con.commit()
    print(con.execute("SELECT count(*) FROM docs_fts WHERE docs_fts MATCH 'upsert'").fetchone())   # (1,)
    print(con.execute("SELECT count(*) FROM docs_fts WHERE docs_fts MATCH 'backup'").fetchone())   # (0,)

Şemayı kurarken şu noktalara dikkat edin:

• Tetikleyiciler docs tablosuyla aynı işlemde (transaction) çalışır; işlem geri alınırsa indeks de geri alınır.
• Toplu ilk yüklemede önce docs tablosunu doldurup ardından INSERT INTO docs_fts(docs_fts) VALUES('rebuild') çalıştırmak, satır satır tetikleyici çalıştırmaktan daha hızlıdır.
• İndeks ile içerik bir gün ayrışırsa (örneğin tetikleyiciler kapalıyken elle veri taşındıysa) çözüm yine rebuild komutudur.
• Dış kaynaktan beslenen belgeler için artımlı çekme ve upsert deseni docs tablosuna yazar; tetikleyiciler gerisini halleder.

bm25 sıralaması, snippet ve güvenli sorgu nasıl kurulur?

bm25(), FTS5'in yerleşik alaka puanı fonksiyonudur; her sütun için ayrı bir ağırlık alır ve daha iyi eşleşme için daha küçük (negatif) değer döndürdüğünden sonuçlar artan sırada sıralanır. snippet() eşleşen kelimelerin çevresinden kısa bir parça keser ve eşleşmeleri verdiğiniz işaretlerle sarar. Kullanıcı girdisini ise doğrudan MATCH'e vermemek gerekir; FTS5 sorgu dilinin kendi sözdizimi vardır ve parantez, tırnak ya da iki nokta gibi karakterler hata üretir.

Girdiyi olduğu gibi geçirirseniz, kullanıcının yazdığı veri ( araması Python'da şu hatayla düşer: sqlite3.OperationalError: fts5: syntax error near "(". Tek bir çift tırnak ya da NOT gibi ayrılmış bir kelime de benzer hatalar verir. Çözüm, her kelimeyi çift tırnak içine almak ve içerideki çift tırnakları ikilemektir; böylece FTS5 girdiyi sözdizimi değil düz metin olarak okur.

import re
import sqlite3


def guvenli_sorgu(metin, on_ek=True):
    """Kullanici girdisini FTS5 sozdizimi hatalarina karsi guvenli hale getirir.
    Her kelime cift tirnaga alinir; iceride kalan cift tirnak ikilenir."""
    kelimeler = re.findall(r'\w+', metin)
    if not kelimeler:
        return ''
    parcalar = []
    for k in kelimeler:
        k = k.replace('"', '""')
        parcalar.append(f'"{k}"*' if on_ek else f'"{k}"')
    return ' AND '.join(parcalar)


def ara(con, metin, limit=20):
    q = guvenli_sorgu(metin)
    if not q:
        return []
    sql = """
    SELECT d.id,
           d.title,
           snippet(docs_fts, 1, '<mark>', '</mark>', ' ... ', 12) AS parca,
           bm25(docs_fts, 5.0, 1.0) AS skor
    FROM docs_fts
    JOIN docs AS d ON d.id = docs_fts.rowid
    WHERE docs_fts MATCH ?
    ORDER BY skor
    LIMIT ?
    """
    return con.execute(sql, (q, limit)).fetchall()


if __name__ == '__main__':
    con = sqlite3.connect('arama.db')
    for satir in ara(con, 'artimli (cekme'):
        print(satir)

Fonksiyonun parametrelerini şu şekilde okuyun:

• bm25(docs_fts, 5.0, 1.0): başlık sütunundaki eşleşme gövdedekinden beş kat ağır sayılır; sütun sırası tablo tanımındaki sıradır.
• snippet(docs_fts, 1, ...): ikinci parametre sütun indeksidir (0 = title, 1 = body); son parametre parçanın en fazla kaç token olacağını belirler.
• "kelime"*: tırnaklı kelimenin ardındaki yıldız ön ek eşleşmesi sağlar; "artim" yazan kullanıcı "artımlı" belgesini bulur.
• Tüm belge metnini vurgulamak istiyorsanız highlight(docs_fts, 1, '', '') kullanın; uzun belgelerde snippet daha ekonomiktir.

Türkçe metinde unicode61 ve remove_diacritics ne yapar?

unicode61, FTS5'in varsayılan tokenizer'ıdır; metni Unicode karakter sınıflarına göre kelimelere böler, harfleri küçültür ve remove_diacritics seçeneği açıkken bazı aksanlı harfleri temel harfe indirger. Türkçe için can alıcı soru, ş/s, ğ/g, ç/c gibi çiftlerin ve özellikle ı/i ile İ/I ayrımının bu indirgemede nasıl ele alındığıdır. Belgeler bu davranışı harf harf listelemediği için kendi SQLite sürümünüzde test etmenizi öneririm; aşağıdaki betik sonucu birkaç saniyede gösterir.

import sqlite3

con = sqlite3.connect(':memory:')
print('SQLite surumu:', sqlite3.sqlite_version)
con.execute("CREATE VIRTUAL TABLE t USING fts5(x, tokenize='unicode61 remove_diacritics 1')")
con.executemany('INSERT INTO t VALUES (?)', [
    ('ışık',), ('isik',), ('şeker',), ('seker',),
    ('İstanbul',), ('istanbul',), ('ISTANBUL',),
])
for sorgu in ['ışık', 'isik', 'şeker', 'seker', 'İstanbul', 'istanbul', 'ISTANBUL']:
    satirlar = con.execute('SELECT x FROM t WHERE t MATCH ?', (f'"{sorgu}"',)).fetchall()
    print(f'{sorgu!r:12} -> {[s[0] for s in satirlar]}')

Çıktıda hangi sorgunun hangi satırları getirdiğine bakın; ı/i çiftinde ya da büyük İ'de beklemediğiniz bir ayrışma görüyorsanız iki strateji var:

• Uygulama tarafında normalizasyon: Türkçe kurallarına göre küçültüp (I → ı, İ → i) isteğe bağlı olarak aksanları da silen normalize bir kopyayı docs tablosunda ayrı bir sütunda tutun ve FTS5'e o sütunu indeksletin. Sorguyu da aynı fonksiyondan geçirin; tokenizer'ın ne yaptığı önemsizleşir.
• trigram tokenizer: SQLite 3.34.0 ile gelen tokenize='trigram' kelime yerine üç karakterlik dizileri indeksler; kelime ortasından eşleşme ve LIKE sorgularını hızlandırır. Bedeli daha büyük indeks ve en az üç karakterlik sorgu zorunluluğudur.
• Kök bulma yok: porter tokenizer yalnız İngilizce içindir; "belgeler" ile "belge"yi eşleştirmek için ön ek sorgusu ("belge"*) çoğu durumda yeterli bir yaklaşımdır.

Kişisel veri ve FTS5'in yetmediği durumlar

Kişisel veri içeren belgeleri indekslemek, KVKK açısından o veriyi ikinci bir yerde daha işlemek demektir; indeks de veri işleme kapsamındadır. Silme talebi geldiğinde belgenin yalnız docs tablosundan değil indeksten de çıkması, snippet çıktısında kimlik numarası veya telefon gibi alanların görünmemesi ve arama loglarının saklama süresinin belirlenmesi gerekir. Tetikleyici düzeni ilk şartı kendiliğinden karşılar.

• Silme: AFTER DELETE tetikleyicisi indeksi temizler; ancak veritabanı yedekleri indeksi de içerir, yedek saklama süresini imha politikasına ekleyin.
• Snippet: hassas sütunları FTS5 tablosuna hiç eklemeyin ya da snippet'i yalnız yetkili kullanıcıya gösterin; vurgulama metni kullanıcıya olduğu gibi gider.
• Arama logları: kullanıcının aradığı metin kişisel veri olabilir; log dosyasına ham sorguyu yazıyorsanız süresini ve erişimini tanımlayın.

FTS5 küçük ölçekte yeterlidir ama her ihtiyacı karşılamaz. Aşağıdaki tablo hangi noktada ayrı bir arama motoruna geçmeniz gerektiğini özetler:

İhtiyaç	SQLite FTS5	Elasticsearch / Meilisearch
Çok sayıda eşzamanlı yazar	Tek yazar kilidi; WAL modunda okuyucular beklemez	Dağıtık yazma, replikasyon
Yazım hatası toleransı	Yok; trigram ile kısmi çözüm	Yerleşik (fuzzy / typo tolerance)
Facet ve toplulaştırma	Normal SQL GROUP BY ile elle	Yerleşik facet, aggregation
Dile özel analiz (Türkçe kök bulma)	Yok; uygulama tarafında normalizasyon	Analyzer / stemmer eklentileri
Birden çok sunucuya yayılma	Tek dosya, tek makine	Küme mimarisi
Vektör / anlamsal arama	Ayrı eklenti gerekir	Yerleşik ya da entegre

Tablodaki satırlardan ikisi ya da daha fazlası projenizde gerçek bir gereksinimse arama sunucusu haklı bir yatırımdır; yalnızca "belki ileride gerekir" diyorsanız FTS5 ile başlayıp veriyi taşımak her zaman mümkündür.

Özetle: SQLite FTS5, harici içerik tablosu ve üç tetikleyiciyle yüz binlerce belgeyi ek sunucu kurmadan indeksler; bm25 ve snippet ile kullanıcıya iyi bir sonuç listesi sunar; Türkçe karakter davranışını ise varsaymak yerine kendi sürümünüzde test etmeniz gerekir. Veri boru hatları, arama katmanı ya da otomasyon konularında birlikte çalışmak isterseniz ana sayfada danışmanlık alanlarımı görebilir, iletişim formundan bana ulaşabilirsiniz.