PostgreSQL Full-Text Search: tsvector, tsquery, Türkçe Configuration, unaccent ve pg_trgm

Sürüm notu: Bu yazıdaki örnekler PostgreSQL 18.7 (resmî postgres:18 Docker image'ı) üzerinde, 02'de kurduğumuz Bookstore lab'ında, şema yeniden yüklendikten hemen sonra çalıştırılmıştır; collation karşılaştırması için bir sorgu PostgreSQL 17.11'de de denenmiştir.

Önce Tahmin Et

Sitedeki serilerin ortak örnek uygulaması Bookstore (bir online kitapçı) kataloğuna Türkçe klasikler ekliyor. 10-full-text.sql, 40.000 İngilizce demo başlığın yanına on iki Türk klasiğini, bu lab için yazdığımız kısa açıklamalarla ekliyor. Çalıkuşu'nun açıklaması şu: "Feride'nin İstanbul'dan Anadolu'ya öğretmen olarak gidişi ve kasabalarda geçen yılları." Kullanıcının yazabileceği altı arama terimini PostgreSQL'in Türkçe full-text search configuration'ıyla deniyoruz:

SELECT q, to_tsvector('turkish', description) @@ websearch_to_tsquery('turkish', q) AS found
FROM books, unnest(ARRAY['istanbul', 'kasabalar', 'kasaba', 'öğretmen', 'öğretmenler', 'anadoluda']) AS q
WHERE title = 'Çalıkuşu';

ILIKE '%kasaba%' bunların çoğunu bulurdu ama "kasabalar"ı bulamazdı. Stemming yapan (kelimeyi köküne indiren) full-text search hangilerini bulur? "kasabalar" bulunup "kasaba" bulunmayabilir mi?

      q      | found
-------------+-------
 istanbul    | t
 kasabalar   | t
 kasaba      | f
 öğretmen    | t
 öğretmenler | f
 anadoluda   | f

Metinde geçen "kasabalarda" ile aranan "kasabalar" eşleşti, ama "kasaba" eşleşmedi. "öğretmen" bulundu, "öğretmenler" bulunamadı. Bu yazıda nedenini görüyoruz: tsvector ve tsquery'nin nasıl çalıştığı, Türkçe stemmer'ın sınırları, noktalı/noktasız i ve 18'deki collation değişikliği, unaccent ile Türkçe karaktersiz arama, Bookstore için ağırlıklı ve index'li arama, pg_trgm ile typo toleransı ve ne zaman Elasticsearch'e geçmek gerektiği.

tsvector ve tsquery

Full-text search iki tipten oluşur. tsvector belgenin işlenmiş hâli: metin kelimelere bölünür, stop word'ler (ve, çok, için gibi tek başına anlam taşımayan sık kelimeler) atılır, kalan kelimeler sözlükten geçirilip lexeme'e (kelimenin normalize edilmiş kökü) çevrilir ve konumlarıyla saklanır. tsquery ise aynı işlemden geçmiş arama ifadesi. @@ operatörü ikisini eşleştirir.

SELECT to_tsvector('turkish', description) FROM books WHERE title = 'Çalıkuşu';

                                                   to_tsvector
------------------------------------------------------------------------------------------------------------------
 'anadol':5 'dan':4 'feri':1 'geçe':12 'gidiş':9 'istanbul':3 'kasaba':11 'nin':2 'olarak':8 'yıl':13 'öğretme':7

Tahminin cevabı burada. "kasabalarda" 'kasaba' olarak saklanmış; aranan "kasaba" ise 'kasap''a dönüşüyor. "öğretmen" iki tarafta da aynı kelime olduğu için 'öğretme''de buluştu, "öğretmenler" ise 'öğretmen' oldu. Eşleşme, iki tarafın aynı lexeme'e inmesine bağlı; stemmer bir kelimenin farklı çekimlerini her zaman aynı köke indiremiyor.

Kullanıcı girdisini tsquery'ye çevirmek için websearch_to_tsquery kullan: arama kutularının alışılmış syntax'ını anlar ve hatalı girdide exception fırlatmaz. Tırnak içi ifade (<->, "hemen ardından gelen"), or ve başına eksi konan dışlama:

SELECT websearch_to_tsquery('turkish_unaccent', '"saatleri ayarlama" or calikusu -huzur');

            websearch_to_tsquery
--------------------------------------------
 'saat' <-> 'ayarla' | 'calikus' & !'huzur'

to_tsquery ise operatörleri (&, |, !, :*) doğrudan bekler; kullanıcı metnini ona vermek syntax hatasıyla sonuçlanır.

Türkçe Configuration: Stemming ve Sınırları

turkish configuration'ı Snowball projesinin Türkçe stemmer'ını kullanıyor. Eklemeli bir dilde kural tabanlı kök bulmak zor; kelime kelime bakınca sınırlar belirginleşiyor:

      w      |     lexemes
-------------+-----------------
 kasaba      | 'kasap':1
 kasabalarda | 'kasaba':1
 öğretmen    | 'öğretme':1
 öğretmenler | 'öğretmen':1
 roman       | 'roma':1
 romanlar    | 'roman':1
 aşk         | 'aşk':1
 aşkı        | 'aşkı':1
 Efendi'nin  | 'efe':1 'nin':2

Üç tür sorun görülüyor. Aynı kelimenin yalın ve çekimli hâli farklı köklere iniyor (kasaba/kasabalarda, roman/romanlar). Bazı ekler hiç atılmıyor (aşkı). Kesme işaretli özel isimler iki ayrı token'a bölünüyor ve ek ayrı bir "kelime" olarak saklanıyor (Efendi'nin). Üstelik "roman" kelimesinin lexeme'i 'roma' olduğu için "Roma" araması roman geçen her açıklamaya yakın sonuç verebilir.

Bunu kabul edilebilir kılan iki yöntem var. Birincisi prefix araması: stemming yapmayan simple configuration ile :* ekli sorgu, kelimenin başıyla eşleşir. Eklemeli bir dilde bu çoğu zaman stemmer'dan daha öngörülebilir:

SELECT to_tsquery('simple', 'kasaba:*') @@ to_tsvector('simple', description) AS kasaba,
       to_tsquery('simple', 'anadolu:*') @@ to_tsvector('simple', description) AS anadolu
FROM books WHERE title = 'Çalıkuşu';

 kasaba | anadolu
--------+---------
 t      | t

İkincisi, iki yöntemi birleştirmek: aynı satır için hem turkish hem simple vektörü tutup sorguyu ikisine birden sormak. Kullanıcının yazdığı her kelimeye :* eklemek kısa terimlerde çok sonuç döndürür; en az üç harf gibi bir sınır koy.

Noktalı ve Noktasız i: 18'deki Collation Değişikliği

Lexeme'ler küçük harfe çevrilerek saklanır ve bunu hangi kuralın yaptığı önemli. Lab database'i libc en_US.utf8 ile kurulu; aynı cümleyi bir de ICU (International Components for Unicode; dil kurallarını içeren Unicode kütüphanesi) tr-TR locale'iyle kurulmuş bir database'te çalıştırdık:

SELECT to_tsvector('turkish', 'IŞIK İSTANBUL ışık istanbul');

/* PostgreSQL 18, libc en_US.utf8 */
 'istanbul':2,4 'işik':1 'ışık':3

/* PostgreSQL 18, ICU tr-TR */
 'istanbul':2,4 'ışık':1,3

/* PostgreSQL 17, ICU tr-TR */
 'istanbul':2,4 'işik':1 'ışık':3

libc'nin İngilizce kuralı "I"yı "i"ye çevirdiği için büyük harfle yazılmış "IŞIK", "ışık" aramasıyla eşleşmiyor. ICU tr-TR'de iki yazım aynı lexeme'e iniyor. Asıl önemli satır üçüncüsü: PostgreSQL 17, database ICU olsa bile full-text search için her zaman libc kurallarını kullanıyordu. 18'de bu değişti; full-text search ve pg_trgm artık database'in varsayılan collation provider'ını kullanıyor. Sürüm notları, ICU ya da builtin provider'lı bir cluster'ı pg_upgrade ile 18'e taşıdıktan sonra full-text ve pg_trgm index'lerinin yeniden oluşturulmasını öneriyor; aksi hâlde index'teki eski lexeme'ler ile yeni sorgunun lexeme'leri farklı olabilir. Türkçe içerik ağırlıklı yeni bir database kuruyorsan ICU tr-TR'yi baştan seçmek (03'teki collation bölümü) bu sorunu ortadan kaldırır.

unaccent: Türkçe Karaktersiz Arama

Kullanıcıların önemli bir kısmı Türkçe karakter kullanmadan yazar: "calikusu", "ogretmenler". unaccent extension'ı (trusted; owner kurabilir) aksanlı harfleri sadeleştiren bir sözlük. Türkçe configuration'ın kopyasında stemmer'dan önce çalışacak şekilde yerleştiriyoruz:

CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE TEXT SEARCH CONFIGURATION turkish_unaccent (COPY = turkish);
ALTER TEXT SEARCH CONFIGURATION turkish_unaccent
    ALTER MAPPING FOR hword, hword_part, word WITH unaccent, turkish_stem;

      w      |   turkish    | turkish_unaccent
-------------+--------------+------------------
 Çalıkuşu    | 'çalıkuş':1  | 'calikus':1
 calikusu    | 'calikus':1  | 'calikus':1
 öğretmenler | 'öğretmen':1 | 'ogretmen':1
 ogretmenler | 'ogretmen':1 | 'ogretmen':1

Artık iki yazım aynı lexeme'de buluşuyor. Bedeli de var: stemmer, Türkçe ünlü uyumuna dayanan kurallarını sadeleşmiş harflerle çalıştırınca bazı ekleri kaçırıyor (örneğin "Savaşı" sadeleşince 'savasi' olarak kalıyor). Belge ve sorgu aynı configuration'dan geçtiği sürece tutarlı, ama ham turkish'ten biraz daha kaba bir eşleşme.

Bookstore Araması: setweight, Generated Column ve GIN

Arama başlıkta, yazar adında ve açıklamada yapılacak; başlıkta geçmesi en değerli. setweight her parçaya bir ağırlık (A en yüksek, D en düşük) veriyor. Generated column yalnızca satırın kendi kolonlarını kullanabildiği için yazar adını satıra kopyaladık (author_name; güncel tutmak uygulamanın işi). İngilizce ve Türkçe kitaplar farklı configuration'la işleniyor:

ALTER TABLE books ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
    CASE language
        WHEN 'tr' THEN setweight(to_tsvector('turkish_unaccent', title), 'A')
                    || setweight(to_tsvector('turkish_unaccent', coalesce(author_name, '')), 'B')
                    || setweight(to_tsvector('turkish_unaccent', coalesce(description, '')), 'C')
        ELSE setweight(to_tsvector('english', title), 'A')
          || setweight(to_tsvector('english', coalesce(author_name, '')), 'B')
          || setweight(to_tsvector('english', coalesce(description, '')), 'C')
    END) STORED;
CREATE INDEX ix_books_search_vector ON books USING gin (search_vector);

STORED'u açıkça yazmak şart: 04'te gördüğümüz gibi 18'de generated column'ların varsayılanı virtual ve virtual kolona index konamıyor. to_tsvector'ın configuration'ı açıkça yazılan iki parametreli hâli immutable olduğu için generated column'da kullanılabiliyor. Sorguda kullanıcının dilini bilmediğimiz için iki configuration'ın sorgusunu || (veya) ile birleştiriyoruz:

\set q 'yusuf'
WITH q AS (SELECT websearch_to_tsquery('turkish_unaccent', :'q') || websearch_to_tsquery('english', :'q') AS query)
SELECT book_id, title, author_name, round(ts_rank(search_vector, query)::numeric, 4) AS rank
FROM books, q WHERE search_vector @@ query ORDER BY rank DESC, book_id LIMIT 5;

 book_id |      title      |  author_name   |  rank
---------+-----------------+----------------+--------
   40002 | Kuyucaklı Yusuf | Sabahattin Ali | 0.6383
   40009 | Aylak Adam      | Yusuf Atılgan  | 0.2432

Başlığında ve açıklamasında "Yusuf" geçen kitap, yalnızca yazar adında geçen kitabın önünde. Sonuç listesinde eşleşen yeri göstermek için ts_headline:

     title      |            snippet
----------------+--------------------------------
 Huzur          | [İstanbul]'da, İkinci
 Çalıkuşu       | [İstanbul]'dan Anadolu
 Sinekli Bakkal | [İstanbul]'un bir mahallesinde
 Aylak Adam     | [İstanbul] sokaklarında
 Kiralık Konak  | [İstanbul] konağında

ts_headline belgenin orijinal metnini yeniden işler; pahalıdır, yalnızca sayfada gösterilen satırlar için çağır. Sayfalama için sıralamaya tekil bir kolon ekle (ORDER BY rank DESC, book_id); 2.000 "hobbit" sonucunun hepsi aynı rank'i aldı, ikinci kolon olmadan sayfalar arasında satırlar yer değiştirebilir. Toplam sonuç sayısını count(*) OVER () aynı sorguda veriyor. Plan, GIN (Generalized Inverted Index; her lexeme için onu içeren satırların listesini tutan index) index'inin kullanıldığını gösteriyor:

 Bitmap Heap Scan on books (actual rows=1.00 loops=1)
   Recheck Cond: (search_vector @@ '''calikus'''::tsquery)
   Buffers: shared hit=4
   ->  Bitmap Index Scan on ix_books_search_vector (actual rows=1.00 loops=1)
         Index Cond: (search_vector @@ '''calikus'''::tsquery)

pg_trgm ile Typo Toleransı

Full-text search yazım hatasını affetmez: "madona" bir lexeme'dir ve "madonna" ile eşleşmez. Burada 06'da ILIKE için kullandığımız pg_trgm devreye giriyor. similarity, iki metnin ortak trigram oranını verir; % operatörü bu oran eşiği (pg_trgm.similarity_threshold, varsayılan 0,3) aşınca doğru döner ve GIN trigram index'ini kullanır:

CREATE INDEX ix_books_title_trgm ON books USING gin (title gin_trgm_ops);
SELECT title, similarity(title, 'kurk mantolu madona') AS sim
FROM books WHERE title % 'kurk mantolu madona' ORDER BY sim DESC LIMIT 5;

        title         |    sim
----------------------+-----------
 Kürk Mantolu Madonna | 0.6086956

Bir harf farkına ve bir eksik harfe rağmen 0,61 benzerlik. Uzun bir başlığın içindeki tek kelimeyi aramak için word_similarity ve <% operatörü daha uygun: "ayarlma" için "Saatleri Ayarlama Enstitüsü"nün tamamına benzerlik 0,5 iken kelime benzerliği 0,625. Pratikte arama ekranı önce full-text ile arar, sonuç yoksa ya da azsa trigram ile "Bunu mu demek istediniz?" önerisi üretir.

Ne Zaman Elasticsearch?

PostgreSQL'in full-text search'ü verinin yanında, transaction ile tutarlı ve ek altyapı gerektirmeyen bir çözüm. Bookstore gibi on binlerce, hatta milyonlarca kayıtlık bir katalogda çoğu ihtiyacı karşılar. Elasticsearch ya da OpenSearch'e geçmeyi düşündüren durumlar şunlar: kategori, yazar, fiyat aralığı gibi facet'lerin sayılarıyla birlikte hızlı hesaplanması; yazarken öneri (autocomplete) ve eş anlamlı kelime yönetimi; Türkçe için daha iyi analyzer'lar (morfolojik analiz eklentileri); relevance'ı (BM25, alan bazında boost, tıklama verisiyle ayar) ayrıntılı ayarlama ihtiyacı; arama trafiğinin veritabanından ayrılması. Bunların bedeli, verinin iki yerde tutulması ve senkronizasyon. Ayrıntılar Elasticsearch ile Arama serisinde. Anlam tabanlı (vector) arama ve keyword aramayla birleştirilmesi ise RAG (Retrieval-Augmented Generation; modelin cevabını aranıp bulunan belgelerle besleme) serisinin hybrid search yazısının konusu.

Üretimde Dikkat

Vektörü her sorguda to_tsvector(...) ile hesaplatma; generated column ya da expression index kullan. Expression index'te sorgudaki ifade index'teki ile birebir aynı olmalı (06); generated column bu riski ortadan kaldırır.

Configuration'ı değiştirirsen (yeni stop word listesi, unaccent eklemek) mevcut vektörler eski kurala göre kalır. Generated column'ı yeniden hesaplatmak için ALTER TABLE ... ALTER COLUMN search_vector SET EXPRESSION AS (...) (17+) tabloyu yeniden yazar ve süre boyunca kilitler; büyük tabloda bunu bakım işi olarak planla.

Büyük GIN index'lerde yazma maliyeti yüksektir; GIN, eklemeleri önce bir "pending list"te biriktirir ve VACUUM sırasında index'e işler. Yoğun yazılan tablolarda gin_pending_list_limit ve autovacuum ayarları sorgu süresini etkiler.

Sık Düşülen Tuzaklar

Belgeyi bir configuration'la, sorguyu başka bir configuration'la işlemek: to_tsvector('turkish', ...) ile plainto_tsquery('simple', ...) farklı lexeme'ler üretir ve sessizce sonuç bulamazsın. Configuration'ı iki parametreli fonksiyonlarla her zaman açıkça ver; varsayılan default_text_search_config'e güvenme.

Türkçe stemmer'dan mükemmellik beklemek: yukarıdaki tablo gerçek. Kritik aramalar için prefix aramasını ve trigram önerisini birlikte kullan, sonuçları gerçek kullanıcı sorgularıyla test et.

ts_rank'i bütün sonuçlar için hesaplatmak: eşleşen 2.000 satırın hepsi için rank hesaplanıp sıralanır. Çok genel terimlerde sonuç sayısını sınırla ya da en az üç harfli terim şartı koy.

Küçük Bir Deneme

★☆☆ ts_debug('turkish', 'İstanbul''un kitapçıları') çıktısında her token'ın hangi sözlükten geçtiğine bak. Kesme işaretinden sonraki ek ne oluyor?

★★☆ search_vector'e ağırlığı D olan dördüncü bir parça olarak simple configuration'lı açıklamayı ekle ve "kasaba" aramasının Çalıkuşu'nu kasaba:* prefix sorgusuyla bulmasını sağla. İki sorguyu || ile nasıl birleştirdin?

★★★ "sinekli bakal" ve "tutunamayınlar" gibi hatalı aramalarda önce full-text ile arayan, sonuç bulamazsa word_similarity ile en yakın üç başlığı öneren tek bir SQL fonksiyonu yaz. Eşik değerini nasıl seçtin?

Bir sonraki yazıda Blok B'ye, .NET tarafına geçiyoruz: Npgsql ve Dapper ile NpgsqlDataSource, connection pooling, type mapping, binary COPY ile toplu yükleme ve LISTEN/NOTIFY.