PostgreSQL Full Text Search: tsvector, tsquery ve Türkçe Arama

LIKE yerine neden full text search?

Kılavuz, LIKE, ILIKE, ~ ve ~* operatörlerinin modern bir arama ihtiyacı için üç açıdan yetersiz kaldığını söyler. Birincisi dil desteği yoktur: "kitap" aradığınızda "kitapları" geçen satırı bulmak için her türevi ayrı ayrı yazmanız gerekir. İkincisi sonuçları alakaya göre sıralamazlar; bin satır eşleştiğinde hangisinin önce gösterileceğine dair bilgi vermezler. Üçüncüsü, baştan joker karakterle başlayan desenlerde sıradan indeks işe yaramaz ve her aramada tüm satırların taranması gerekir.

Full text search bu üç sorunu ön işleme ile çözer. Belge önce parser tarafından token denen parçalara ayrılır ve her token türü (kelime, sayı, e-posta adresi, URL) belirlenir. Ardından sözlükler her token için normalleştirilmiş bir lexeme üretir: harfler küçültülür, ekler atılır, "ve", "da" gibi aramada işe yaramayan stop word'ler elenir. Sonuç, konum bilgisiyle birlikte sıralı bir lexeme listesidir ve indekslenebilir.

tsvector ve tsquery: iki temel tip

tsvector bir belgenin işlenmiş hâlidir: tekrarsız, alfabetik sıralı lexeme'ler ve her birinin belgede geçtiği konumlar. tsquery ise aranacak lexeme'leri & (VE), | (VEYA), ! (DEĞİL) ve <-> (ARDINDAN GELİR) operatörleriyle birleştirir. Türkçe yapılandırmayla bir cümleyi işleyelim:

SELECT to_tsvector('turkish', 'Kitaplar masanın üstünde, kitapları rafa koydum.');

                 to_tsvector                 
---------------------------------------------
 'kitap':1,4 'ko':6 'masa':2 'raf':5 'üst':3
(1 row)

"Kitaplar" ve "kitapları" aynı kitap lexeme'ine indi ve 1 ile 4 numaralı konumlarda kaydedildi. Noktalama işaretleri atıldı. "koydum" kelimesinin ko olması ise Snowball stemmer'ının algoritmik çalıştığını, sözlük bilgisi kullanmadığını gösterir; bu konuya aşağıda döneceğiz. Aynı cümleyi simple yapılandırmasıyla işlerseniz yalnızca küçük harfe çevirme yapılır, ek atılmaz:

SELECT to_tsvector('simple', 'Yedekler ve Sunucular') AS simple_sonuc,
       to_tsvector('turkish', 'Yedekler ve Sunucular') AS turkish_sonuc;

           simple_sonuc            |    turkish_sonuc     
-----------------------------------+----------------------
 'sunucular':3 've':2 'yedekler':1 | 'sunucu':3 'yedek':1
(1 row)

Burada iki fark var: turkish yapılandırması "ve" kelimesini stop word olarak attı ve çoğul eklerini kaldırdı. Kurulumda gelen yapılandırmaların listesini psql'de \dF ile görebilirsiniz; PostgreSQL 18.1 kümemizde pg_ts_config kataloğu 30 yapılandırma döndürdü ve turkish bunlardan biri.

\dF turkish

            List of text search configurations
   Schema   |  Name   |            Description             
------------+---------+------------------------------------
 pg_catalog | turkish | configuration for turkish language
(1 row)

Eşleşme operatörü @@ ve sık yapılan normalleştirme hatası

Eşleşme @@ ile yapılır; tiplerin hangi sırada yazıldığı fark etmez. Kılavuzun özellikle uyardığı nokta şudur: bir metni doğrudan ::tsvector ile tipe çevirirseniz normalleştirme yapılmaz, çünkü tsvector girdisinin zaten lexeme olduğu varsayılır. Aşağıdaki iki sorgu aynı kelimelerle farklı sonuç verir:

SELECT 'yedekler'::tsvector @@ 'yedek'::tsquery;                          -- sonuç: f
SELECT to_tsvector('turkish', 'yedekler') @@ to_tsquery('turkish', 'yedek'); -- sonuç: t

@@ operatörü text girdiyi de kabul eder: text @@ tsquery biçimi to_tsvector(x) @@ y, text @@ text biçimi ise to_tsvector(x) @@ plainto_tsquery(y) ile eşdeğerdir. Bu kısa yazımlar yapılandırma adı almadığı için default_text_search_config ayarını kullanır. Bizim test kümemizde bu değer pg_catalog.english idi; Türkçe metinle çalışacaksanız ya her çağrıda 'turkish' yazın ya da ayarı oturum veya veritabanı düzeyinde değiştirin.

SHOW default_text_search_config;              -- pg_catalog.english
SET default_text_search_config = 'pg_catalog.turkish';
SELECT to_tsvector('Yedekler ve Sunucular');   -- 'sunucu':3 'yedek':1

Ayarı kalıcı yapmanın kılavuzdaki yolları: tüm küme için postgresql.conf, tek veritabanı için ALTER DATABASE ... SET default_text_search_config = ..., tek oturum için SET. Parametrelerin nasıl ayarlandığı postgresql.conf ve ALTER SYSTEM yazısında anlatılıyor.

Kullanıcı girdisinden tsquery üretmek

Dört fonksiyon vardır ve aralarındaki fark, girdiye ne kadar güvendiğinizle ilgilidir. to_tsquery girdinin zaten tsquery sözdizimine uymasını bekler; kelimeler arasında operatör yoksa hata verir. Bu yüzden arama kutusuna yazılan ham metni ona vermek doğru değildir:

SELECT to_tsquery('turkish', 'yedek sunucu');

ERROR:  syntax error in tsquery: "yedek sunucu"

plainto_tsquery metni normalleştirip kalan kelimelerin arasına & koyar, operatörleri tanımaz. phraseto_tsquery aynı işi <-> ile yapar ve atılan stop word'lerin yerini <N> mesafesiyle korur. websearch_to_tsquery ise web arama motorlarına benzeyen bir sözdizimi sunar ve kılavuza göre hiçbir zaman sözdizimi hatası vermez; bu da onu doğrudan kullanıcı girdisi için en uygun seçenek yapar. Tırnak içi ifade, eksi işareti ve "or" kelimesi şöyle çevrilir:

SELECT websearch_to_tsquery('turkish', '"geri yükleme" -kampanya veya indeks');

            websearch_to_tsquery            
--------------------------------------------
 'ger' <-> 'yükle' & !'kampanya' & 'indeks'
(1 row)

Bu çıktıda iki ders var. Birincisi, VEYA anlamına gelen anahtar kelime İngilizce or'dur; "veya" yazıldığında Türkçe stop word listesinde olduğu için sessizce atıldı ve sorgu üç koşulun hepsini isteyen bir VE sorgusuna dönüştü. Aynı girdiyi yedek or indeks diye yazınca sonuç 'yedek' | 'indeks' olur. İkincisi, bozuk girdi hata üretmez: ((( yedek & girdisi yalnızca 'yedek' sorgusuna dönüştü.

Tablo, generated column ve GIN indeksi

Kılavuz iki indeks yaklaşımı gösterir. İlki ifade indeksidir: CREATE INDEX ... USING GIN (to_tsvector('english', body)). Bu durumda yalnızca iki argümanlı, yapılandırma adı açıkça yazılmış to_tsvector kullanılabilir; çünkü indeks içeriği default_text_search_config değişince değişmemelidir. Sorgunun da aynı ifadeyi aynı yapılandırma adıyla yazması gerekir, yoksa indeks kullanılmaz. İkinci yaklaşım ayrı bir tsvector sütunudur ve kılavuz bunu stored generated column ile güncel tutmayı önerir. Bu yolun avantajı, sorguda yapılandırmayı tekrar yazmak zorunda kalmamanız ve eşleşmeleri doğrularken to_tsvector'ın yeniden hesaplanmamasıdır. Generated column ayrıntıları için CREATE TABLE ve ALTER TABLE yazısına bakın.

CREATE DATABASE ornek_fts;
\c ornek_fts
CREATE TABLE makale (
    id      integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    baslik  text NOT NULL,
    govde   text,
    arama   tsvector GENERATED ALWAYS AS (
              setweight(to_tsvector('turkish', coalesce(baslik, '')), 'A') ||
              setweight(to_tsvector('turkish', coalesce(govde, '')), 'D')
            ) STORED
);
INSERT INTO makale (baslik, govde) VALUES
 ('Sunucu yedekleme rehberi', 'Veritabanı yedeklerini her gece başka bir sunucuya kopyalayın.'),
 ('Veritabanı indeksleri', 'Doğru indeks sorguları hızlandırır; yanlış indeks yazmayı yavaşlatır.'),
 ('Yedek almanın püf noktaları', 'Yedekler düzenli olarak geri yüklenerek denenmelidir.'),
 ('Kampanya duyurusu', NULL);
CREATE INDEX makale_arama_idx ON makale USING GIN (arama);

SELECT id, baslik FROM makale WHERE arama @@ to_tsquery('turkish', 'yedek');

 id |           baslik            
----+-----------------------------
  1 | Sunucu yedekleme rehberi
  3 | Yedek almanın püf noktaları
(2 rows)

coalesce kullanımı süs değildir: to_tsvector(NULL) sonucu NULL olur ve || ile birleştirilen ifadenin tamamını NULL yapar. Dördüncü satırın gövdesi NULL olduğu hâlde başlığı aranabilir kaldı. setweight ise başlıktan gelen lexeme'lere A, gövdeden gelenlere D etiketi koydu; bu etiketler sıralamada kullanılacak. Üçüncü satırın ham vektörü etiketleri açıkça gösterir:

SELECT arama FROM makale WHERE id = 3;

                                                 arama                                                  
--------------------------------------------------------------------------------------------------------
 'alma':2A 'denenmel':10 'düzenli':6 'ger':8 'nokta':4A 'olarak':7 'püf':3A 'yedek':1A,5 'yüklenerek':9
(1 row)

Dört satırlık bu tabloda planlayıcı normalde Seq Scan seçti; bu beklenen bir durumdur. İndeksin kullanılabildiğini görmek için yalnızca test amacıyla enable_seqscan kapatıldığında plan şöyle oldu. Plan okumayı EXPLAIN ANALYZE plan okuma yazısında ayrıntılı bulabilirsiniz.

SET enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT id FROM makale WHERE arama @@ to_tsquery('turkish', 'yedek');

                     QUERY PLAN                      
-----------------------------------------------------
 Bitmap Heap Scan on makale
   Recheck Cond: (arama @@ '''yedek'''::tsquery)
   ->  Bitmap Index Scan on makale_arama_idx
         Index Cond: (arama @@ '''yedek'''::tsquery)
(4 rows)

İfade indeksindeki tuzağı da aynı tabloda görelim. govde için to_tsvector('turkish', govde) ifadesiyle bir GIN indeksi kurduk, sonra default_text_search_config değerini turkish yaptık. Sorgu yapılandırma adını yazınca indeks kullanıldı; adı yazmayan sorgu ise ayar aynı dile işaret etse bile indeksi kullanamadı ve plan devre dışı bırakılmış sıralı taramaya düştü:

CREATE INDEX makale_govde_idx ON makale USING GIN (to_tsvector('turkish', govde));
SET default_text_search_config = 'pg_catalog.turkish';
EXPLAIN (COSTS OFF) SELECT id FROM makale WHERE to_tsvector(govde) @@ to_tsquery('turkish', 'indeks');

                       QUERY PLAN                        
---------------------------------------------------------
 Seq Scan on makale
   Disabled: true
   Filter: (to_tsvector(govde) @@ '''indeks'''::tsquery)
(3 rows)

GIN mi GiST mi sorusuna kılavuzun cevabı nettir: tercih edilen tür GIN'dir. GIN ters indekstir, her lexeme için eşleşen satırların sıkıştırılmış listesini tutar; ancak ağırlık etiketlerini saklamaz, bu yüzden ağırlık içeren sorgularda tablo satırı yeniden kontrol edilir. GiST ise her belgeyi sabit uzunlukta bir imzayla temsil ettiği için kayıplıdır (varsayılan imza 124 bayt, en fazla 2024 bayt) ve sahte eşleşmeleri elemek için tabloya gitmek zorundadır. GIN indeksinin oluşturma süresi maintenance_work_mem artırılarak çoğu zaman kısalır, GiST bu ayara duyarlı değildir. Diğer indeks türlerinin genel karşılaştırması PostgreSQL indeks türleri yazısında.

Sonuçları sıralamak: ts_rank ve ts_rank_cd

ts_rank eşleşen lexeme'lerin sıklığına, ts_rank_cd ise ek olarak eşleşmelerin birbirine yakınlığına (cover density) bakar. ts_rank_cd konum bilgisi ister; strip ile konumları atılmış bir vektörde sonucu sıfırdır. Ağırlık dizisi verilmezse D, C, B, A için varsayılan değerler sırasıyla 0.1, 0.2, 0.4 ve 1.0'dır. Bu yüzden başlıkta geçen kelime gövdede geçen kelimeden on kat ağır sayılır:

SELECT id, baslik, round(ts_rank(arama, q)::numeric, 4) AS puan
FROM makale, to_tsquery('turkish', 'yedek | veritabanı') AS q
WHERE arama @@ q
ORDER BY puan DESC;

 id |           baslik            |  puan  
----+-----------------------------+--------
  3 | Yedek almanın püf noktaları | 0.3116
  2 | Veritabanı indeksleri       | 0.3040
  1 | Sunucu yedekleme rehberi    | 0.0608
(3 rows)

Birinci satır gövdesinde "yedekleri" geçmesine rağmen en alta düştü, çünkü sorgudaki kelimeler orada yalnızca D ağırlıklı gövdede bulunuyor; başlıktaki "yedekleme" ise stemmer tarafından yedekle lexeme'ine indirildiği için yedek ile eşleşmedi. Sıralama fonksiyonlarının son argümanı bir bit maskesidir: 0 belge uzunluğunu yok sayar, 1 ve 2 uzunluğa göre böler, 32 ise puanı rank/(rank+1) ile 0 ile 1 arasına çeker. Kılavuz, bu fonksiyonların küresel istatistik kullanmadığını, dolayısıyla puanın "yüzde kaç alakalı" gibi yorumlanamayacağını vurgular. Ayrıca sıralama her eşleşen satırın vektörüne bakmayı gerektirdiği için çok sayıda eşleşmede maliyetlidir; kılavuz bunu da açıkça belirtir.

Vurgulu özet: ts_headline

ts_headline belgenin sorguyla ilgili bir parçasını döndürür ve eşleşen kelimeleri varsayılan olarak <b> etiketleriyle sarar. StartSel, StopSel, MaxWords, MinWords, MaxFragments gibi seçenekler virgülle ayrılmış metin olarak verilir:

SELECT ts_headline('turkish', govde, to_tsquery('turkish', 'indeks'),
       'StartSel=[, StopSel=]') FROM makale WHERE id = 2;

                                ts_headline                                
---------------------------------------------------------------------------
 Doğru [indeks] sorguları hızlandırır; yanlış [indeks] yazmayı yavaşlatır.
(1 row)

Türkçe ile çalışırken dikkat edilecekler

Stemmer sözlük değildir. turkish yapılandırması Snowball projesinin Türkçe kurallarını kullanır ve kurallar bazen beklenmedik kökler üretir. Test kümemizde ts_lexize('turkish_stem', 'sunucu') sonucu {sunuç}, "sunucular" ve "sunucuya" ise {sunucu} oldu. Yani yalın "sunucu" araması çoğul "sunucular" geçen belgeyi bulamayabilir. Önemli kelimelerinizi ts_lexize ve ts_debug ile deneyin; ts_debug her token'ın türünü, hangi sözlükten geçtiğini ve hangi lexeme'e dönüştüğünü gösterir:

SELECT alias, token, dictionary, lexemes
FROM ts_debug('turkish', 'Ankara''da 3 yeni şube açıldı: info@ornek.com');

   alias   |     token      |  dictionary  |     lexemes      
-----------+----------------+--------------+------------------
 asciiword | Ankara         | turkish_stem | {ankar}
 blank     | '              |              | 
 asciiword | da             | turkish_stem | {}
 blank     |                |              | 
 uint      | 3              | simple       | {3}
 blank     |                |              | 
 asciiword | yeni           | turkish_stem | {ye}
 blank     |                |              | 
 word      | şube           | turkish_stem | {şube}
 blank     |                |              | 
 word      | açıldı         | turkish_stem | {açıl}
 blank     | :              |              | 
 email     | info@ornek.com | simple       | {info@ornek.com}
(13 rows)

Önek araması da kökten geçer. to_tsquery('turkish', 'yede:*') sonucu 'ye':* oldu; stemmer öneki kısalttığı için sorgu "yeni" gibi ilgisiz kelimelerle de eşleşir. Otomatik tamamlama gibi önek ağırlıklı ihtiyaçlarda önekin nasıl normalleştiğini mutlaka kontrol edin.

Büyük I ve küçük ı. Kelimeleri küçük harfe çevirme işlemi veritabanının yerel ayarına (collation sağlayıcısı ve LC_CTYPE) göre yapılır. Builtin C.UTF-8 yerel ayarlı veritabanımızda to_tsvector('turkish', 'IŞIK ışık İSTANBUL Istanbul') sonucu 'istanbul':3,4 'işik':1 'ışık':2 oldu; büyük harfle yazılan IŞIK noktalı i ile küçültüldüğü için "ışık" ile ayrı lexeme'e dönüştü ve to_tsvector('turkish', 'IŞIK') @@ to_tsquery('turkish', 'ışık') sonucu f çıktı. ICU sağlayıcısı ve tr-TR yerel ayarıyla oluşturulan bir veritabanında aynı ifade 'ışık':1,2 verdi. Hangi veritabanının hangi yerel ayarla oluşturulacağı collation ve Türkçe sıralama yazısının konusudur.

CREATE DATABASE ornek_fts_tr TEMPLATE template0
  LOCALE_PROVIDER icu ICU_LOCALE 'tr-TR' LOCALE 'C';
\c ornek_fts_tr
SELECT to_tsvector('turkish', 'IŞIK ışık İSTANBUL Istanbul');

             to_tsvector              
--------------------------------------
 'istanbul':3 'ıstanbul':4 'ışık':1,2
(1 row)

Sınırlar ve sık yapılan hatalar

Kılavuzun listelediği sınırlar günlük kullanımda nadiren aşılır ama bilinmesi gerekir: bir lexeme 2 kilobayttan, bir tsvector 1 megabayttan kısa olmalıdır; konum değerleri 1 ile 16.383 arasındadır ve bir lexeme için en fazla 256 konum tutulur; bir tsquery içindeki düğüm sayısı 32.768'den az olmalıdır. Veri tipi sayfasına göre 16.383'ten büyük konumlar sessizce 16.383 yapılır; yani çok uzun bir belgenin sonlarındaki kelimeler aynı konumu paylaşır ve yakınlığa dayalı hesaplar bu bölümde ayırt edici olmaktan çıkar.

• Sorguda ve indekste farklı yapılandırma kullanmak: indeks to_tsvector('turkish', ...) ile kurulduysa sorgu da aynı ifadeyi yazmalıdır, yoksa indeks atlanır.
• Ham kullanıcı girdisini to_tsquery'ye vermek: boşluk içeren her girdi sözdizimi hatası üretir; websearch_to_tsquery veya plainto_tsquery kullanın.
• coalesce unutmak: NULL bir sütun bütün vektörü NULL yapar ve satır hiçbir aramada bulunmaz.
• Metni ::tsvector ile çevirmek: normalleştirme yapılmaz, çekimli kelimeler eşleşmez.
• Yapılandırma vermeden to_tsvector(metin) yazmak: sonuç default_text_search_config değerine bağlı kalır ve İngilizce kurallarla işlenebilir.
• Arama girdisini SQL metnine yapıştırmak: girdiyi her zaman parametre olarak geçin; ayrıntı SQL injection önleme yazısında.

Sık Sorulan Sorular

Türkçe full text search için ek bir eklenti gerekir mi?

Hayır. turkish yapılandırması ve turkish_stem sözlüğü kurulumla gelir; test kümemizde \dF turkish doğrudan listeledi. Daha ince ayar isterseniz kılavuzun 12.6 bölümündeki Ispell, eşanlamlı ve thesaurus sözlükleriyle kendi yapılandırmanızı CREATE TEXT SEARCH CONFIGURATION ile kurabilirsiniz.

Generated column mı, ifade indeksi mi?

İkisi de çalışır. Ayrı sütun, sorgularda yapılandırma adını tekrar yazma zorunluluğunu kaldırır ve eşleşme doğrulamasında vektörü yeniden hesaplamaz; karşılığında diskte ek yer kaplar. İfade indeksi ek sütun istemez ama sorgunun indeks ifadesini birebir tekrarlaması gerekir.

websearch_to_tsquery Türkçe "veya" kelimesini anlar mı?

Hayır. Fonksiyon VEYA için yalnızca or kelimesini tanır. Türkçe "veya" stop word olduğu için atılır ve sorgu VE sorgusuna döner. Kullanıcılarınıza "or" yazmalarını söylemek ya da girdiyi uygulama tarafında dönüştürmek gerekir.

Ranking puanını yüzde olarak gösterebilir miyim?

Kılavuza göre sıralama fonksiyonları yalnızca yerel bilgiye baktığı için adil bir yüzde hesaplanamaz. 32 normalleştirme seçeneği puanı 0 ile 1 arasına çeker ama bu yalnızca görünüşü değiştirir, sıralamayı değiştirmez.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: PostgreSQL öğrenme rehberi · String fonksiyonları ve LIKE · İndeks türleri · Collation ve Türkçe sıralama · JSONB kullanımı · PostgreSQL performans optimizasyonu