PostgreSQL Full-Text Arama

Site içi arama kutusunun arkasında genellikle WHERE baslik ILIKE '%aranan%' durur. Birkaç bin kayıtla sorun çıkarmaz, ama tablo büyüdüğünde her arama tam tablo taramasına dönüşür ve sayfa saniyelerce yüklenmez. Üstelik bu yöntem "sunucular" yazan kullanıcıya "sunucu" içeren yazıları bulamaz, sonuçları alaka düzeyine göre sıralayamaz ve birden fazla kelimeyi mantıklı biçimde birleştiremez.

PostgreSQL full-text arama, bu üç problemi de veritabanının içinde çözer; ayrı bir arama motoru kurmadan, işletmeden ve senkron tutmadan. Bu rehberde tsvector ve tsquery tiplerinin nasıl çalıştığını, GIN indeksiyle nasıl hızlandırıldığını, sonuçların alaka düzeyine göre nasıl sıralandığını ve Türkçe metinlerde karşılaşacağınız gerçek sınırları anlatacağım. Sonunda, hangi durumda PostgreSQL'in yeteceğini ve hangi noktada ayrı bir arama motoruna geçmeniz gerektiğini de netleştireceğiz.

tsvector ve tsquery Mantığı#

Full-text aramanın temeli, metni aranabilir bir yapıya dönüştürmektir. to_tsvector fonksiyonu bir metni alır, kelimelere böler, gereksiz kelimeleri (stop words) atar, kalanları köklerine indirir ve her birinin belgedeki konumunu saklar.

SELECT to_tsvector('english', 'The servers are running fast and the caches are warm');
-- Sonuç: 'cach':8 'fast':5 'run':4 'server':2 'warm':11

Görüyorsunuz: the, are, and atılmış; servers kökü server, running kökü run, caches kökü cach olmuş. Rakamlar kelimenin belgedeki sırasıdır ve daha sonra ifade araması için kullanılır.

Aranan tarafta tsquery vardır. İki metin doğrudan karşılaştırılmaz; tsvector ile tsquery eşleştirme operatörü @@ üzerinden karşılaştırılır:

SELECT to_tsvector('english', 'The servers are running fast')
       @@ to_tsquery('english', 'server & fast');
-- t (true)

Sorgu oluşturmak için üç fonksiyon vardır ve doğru olanı seçmek önemlidir:

Kullanıcının yazdığı metni doğrudan to_tsquery'ye vermek klasik bir hatadır: kullanıcı c++ ya da & yazdığında sorgu hata fırlatır ve arama sayfanız 500 döner. Kullanıcı girdisi için her zaman websearch_to_tsquery kullanın:

SELECT websearch_to_tsquery('english', '"sunucu yonetimi" -windows hosting');
-- 'sunucu' <-> 'yonetimi' & !'windows' & 'hosting'

Bu fonksiyon tırnak içindeki ifadeleri bitişiklik operatörüne, başındaki eksiyi olumsuzlamaya çevirir ve hiçbir girdide hata vermez.

Aranabilir Sütun Kurmak#

Her sorguda to_tsvector çağırmak hem pahalıdır hem de indekslemeyi zorlaştırır. PostgreSQL 12 ile gelen oluşturulmuş sütun (generated column) bu işi çok temiz çözer: sütun otomatik hesaplanır, otomatik güncellenir ve normal bir sütun gibi indekslenir.

CREATE TABLE makaleler (
    id          bigserial PRIMARY KEY,
    baslik      text NOT NULL,
    ozet        text,
    govde       text NOT NULL,
    yayin_tarihi date NOT NULL DEFAULT current_date,
    arama       tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', coalesce(baslik, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(ozet,   '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(govde,  '')), 'C')
    ) STORED
);

Üç ayrıntı burada kritik:

1. coalesce zorunludur. NULL bir alan tüm birleştirmeyi NULL yapar ve o satır hiçbir aramada çıkmaz — sessiz ve bulması zor bir hata.
2. setweight alaka düzeyi için ağırlık atar. A en yüksek, D en düşüktür. Başlıkta geçen kelime, gövdede geçenden daha değerlidir.
3. Sözlük adı sabit yazılmalıdır. Oluşturulmuş sütunlar yalnızca değişmez (immutable) ifade kabul eder; to_tsvector(metin) gibi tek argümanlı biçim oturumun default_text_search_config ayarına bağlı olduğu için kabul edilmez.

Şimdi GIN indeksini ekleyin — bu adım olmadan aramanız hâlâ tam tablo taraması yapar:

CREATE INDEX idx_makaleler_arama ON makaleler USING GIN (arama);

Arama sorgusu artık şu kadar basit:

SELECT id, baslik
FROM makaleler
WHERE arama @@ websearch_to_tsquery('simple', 'postgresql indeks')
LIMIT 20;

EXPLAIN (ANALYZE, BUFFERS) çıktısında Bitmap Index Scan on idx_makaleler_arama satırını görmelisiniz. GIN indekslerinin genel davranışı için PostgreSQL index türleri yazısına bakabilirsiniz.

PostgreSQL 11 ve öncesindeyseniz oluşturulmuş sütun yoktur; normal bir tsvector sütunu açıp bir trigger ile güncellersiniz:

ALTER TABLE makaleler ADD COLUMN arama tsvector;

CREATE OR REPLACE FUNCTION makale_arama_guncelle()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.arama :=
        setweight(to_tsvector('simple', coalesce(NEW.baslik, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(NEW.govde,  '')), 'C');
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_makale_arama
BEFORE INSERT OR UPDATE OF baslik, govde ON makaleler
FOR EACH ROW EXECUTE FUNCTION makale_arama_guncelle();

Trigger tabanlı yaklaşımın tuzakları için veritabanı trigger'ları yazısına göz atın; özellikle toplu yüklemelerde performans etkisi hissedilir.

Türkçe Metinlerde Durum#

Şimdi işin can alıcı kısmına gelelim. PostgreSQL çekirdeği çok sayıda dil için sözlük içerir ama Türkçe için yerleşik bir kök bulucu (stemmer) sözlüğü gelmez. Yani to_tsvector('turkish', ...) çağrısı standart kurulumda hata verir.

Bunun pratikte üç sonucu var. Birincisi, simple sözlüğünü kullanmanız gerekir; bu sözlük kök bulmaz, sadece küçük harfe çevirip kelimeleri ayırır. Sonuç olarak "sunucular" araması "sunucu" içeren belgeleri bulamaz. İkincisi, Türkçe stop word listesi de yoktur, yani "ve", "ile", "bir" gibi kelimeler indekse girer. Üçüncüsü, arama kalitesi İngilizce metinlere göre belirgin şekilde düşüktür.

Kurulumunuzda hangi sözlüklerin bulunduğunu görün:

SELECT cfgname FROM pg_ts_config ORDER BY cfgname;

Uygulamada işe yarayan üç yaklaşım var.

Birincisi, unaccent ile Türkçe karakter duyarsızlığı sağlamak. Kullanıcı "sunucu guvenligi" yazdığında "sunucu güvenliği" içeren belgeleri bulması gerekir:

CREATE EXTENSION IF NOT EXISTS unaccent;

-- Kendi yapılandırmanızı oluşturun
CREATE TEXT SEARCH CONFIGURATION tr_basit (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION tr_basit
    ALTER MAPPING FOR hword, hword_part, word WITH unaccent, simple;

SELECT to_tsvector('tr_basit', 'Sunucu Güvenliği ve Şifreleme');
-- 'guvenligi':2 'sifreleme':4 'sunucu':1 've':3

Artık aranabilir sütununuzu tr_basit yapılandırmasıyla kurabilir, hem büyük-küçük harf hem aksan farkını ortadan kaldırabilirsiniz.

İkincisi, kendi eş anlamlı sözlüğünüzü eklemek. Sık kullanılan Türkçe kelimelerin çekimlerini elle eşleştirebilirsiniz. $SHAREDIR/tsearch_data/ altına bir .syn dosyası koyarsınız:

; /usr/share/postgresql/16/tsearch_data/tr_esanlam.syn
sunucular   sunucu
sunucuya    sunucu
sunucunun   sunucu
veritabani  veritabanı

Bu, tam bir kök bulucu değildir ama sitenizin en çok aranan 200 terimini kapsarsanız kullanıcı deneyimi belirgin biçimde düzelir.

Üçüncüsü ve en pratik olanı, pg_trgm ile birleştirmek. Üçlü karakter (trigram) benzerliği dilden bağımsız çalışır ve yazım hatalarını da tolere eder:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_makaleler_baslik_trgm ON makaleler USING GIN (baslik gin_trgm_ops);

-- "postgres" yazan kullanıcıya "PostgreSQL" başlıklarını da getirir
SELECT baslik, similarity(baslik, 'postgres yedek') AS skor
FROM makaleler
WHERE baslik % 'postgres yedek'
ORDER BY skor DESC
LIMIT 10;

% operatörü, pg_trgm.similarity_threshold ayarına (varsayılan 0.3) göre benzerlik eşiğini uygular. Türkçe içerikli sitelerde en iyi sonuç, full-text aramayı ana yöntem, trigram benzerliğini de "sonuç bulunamadı" durumundaki yedek yöntem olarak kullanmaktır.

Sonuçları Sıralamak#

Alaka düzeyi sıralaması olmadan full-text arama yarım kalır. PostgreSQL iki sıralama fonksiyonu sunar:

WITH sorgu AS (SELECT websearch_to_tsquery('tr_basit', 'postgresql indeks') AS q)
SELECT
    m.id,
    m.baslik,
    ts_rank(m.arama, s.q)        AS skor,
    ts_rank_cd(m.arama, s.q)     AS yakinlik_skoru
FROM makaleler m, sorgu s
WHERE m.arama @@ s.q
ORDER BY skor DESC
LIMIT 20;

ts_rank kelime sıklığına ve setweight ile verdiğiniz ağırlıklara bakar. ts_rank_cd ise ek olarak kelimelerin belgede birbirine ne kadar yakın olduğunu dikkate alır; uzun metinlerde genelde daha iyi sonuç verir.

Ağırlıkları özelleştirmek isterseniz dört elemanlı bir dizi verebilirsiniz — sıra {D, C, B, A} şeklindedir:

-- Başlıkta geçmeyi çok daha değerli say
SELECT baslik, ts_rank('{0.1, 0.2, 0.4, 1.0}', arama, q) AS skor
FROM makaleler, websearch_to_tsquery('tr_basit', 'yedekleme') AS q
WHERE arama @@ q
ORDER BY skor DESC;

Gerçek dünyada saf alaka düzeyi yetmez; tazelik ve popülerlik de sıralamaya girer:

SELECT
    baslik,
    ts_rank(arama, q) * (1.0 / (1 + extract(epoch FROM now() - yayin_tarihi) / 2592000.0)) AS son_skor
FROM makaleler, websearch_to_tsquery('tr_basit', 'ssl sertifikasi') AS q
WHERE arama @@ q
ORDER BY son_skor DESC
LIMIT 20;

Buradaki bölen, yayından bu yana geçen ayı hesaba katar; bir aylık yazının skoru yarıya iner. Katsayıları kendi içeriğinize göre ayarlayın.

Kullanıcıya eşleşen kısmı göstermek için ts_headline kullanılır:

SELECT ts_headline('tr_basit', govde, q,
       'StartSel=[[, StopSel=]], MaxWords=35, MinWords=15')
FROM makaleler, websearch_to_tsquery('tr_basit', 'index') AS q
WHERE arama @@ q LIMIT 5;

Dikkat: ts_headline indeks kullanamaz ve her satır için belgeyi baştan işler. Bu yüzden yalnızca son sonuç kümesine uygulayın; LIMIT uygulanmış bir alt sorgunun dışında çağırın, yoksa binlerce satır için çalışır ve sorgunuz çöker.

Sık Yapılan Hatalar#

Kullanıcı girdisini to_tsquery'ye vermek. Kullanıcı & veya : yazdığında sorgu hata fırlatır. websearch_to_tsquery kullanın; hiçbir girdide hata vermez.

coalesce unutmak. Bir alanı NULL olan satır, birleştirme sonucu NULL olduğu için hiçbir aramada çıkmaz. Bu hatayı fark etmek aylar alır çünkü hata mesajı yoktur, sadece bazı kayıtlar bulunamaz.

GIN indeksini unutmak. tsvector sütunu tek başına hız getirmez; indekssiz her arama tam tablo taramasıdır. EXPLAIN ile doğrulayın.

Sözlük tutarsızlığı. Sütunu simple ile oluşturup sorguyu english ile kurarsanız eşleşme olmaz. Yapılandırma adını uygulama genelinde tek bir yerde sabitleyin.

ts_headline'ı filtreden önce çağırmak. Her aday satır için belgeyi işlediğinden, LIMIT'ten önce çağrılırsa arama süresi katlanır. Doğru desen, önce eşleşmeleri sıralayıp sınırlamak, sonra dış sorguda vurgulamaktır:

SELECT id, baslik, ts_headline('tr_basit', govde, q, 'MaxWords=30') AS vurgulu
FROM (
    SELECT m.id, m.baslik, m.govde, s.q
    FROM makaleler m, websearch_to_tsquery('tr_basit', 'replikasyon') AS s(q)
    WHERE m.arama @@ s.q
    ORDER BY ts_rank(m.arama, s.q) DESC
    LIMIT 20
) t;

Toplu yüklemede indeksi açık bırakmak. Milyonlarca satır yüklerken GIN indeksi yazmayı ciddi biçimde yavaşlatır. Yükleme öncesi indeksi düşürüp sonra CREATE INDEX CONCURRENTLY ile yeniden oluşturmak çok daha hızlıdır. Alternatif olarak fastupdate ayarını kullanabilirsiniz.

Arama tablosunun bakımını atlamak. tsvector sütunu yer kaplar ve GIN indeksi zamanla şişer. Bakım için PostgreSQL VACUUM ve autovacuum yazısındaki REINDEX CONCURRENTLY yaklaşımını uygulayın.

Ne Zaman Ayrı Arama Motoruna Geçmeli#

PostgreSQL full-text arama, birkaç milyon belgeye kadar rahatça ölçeklenir ve şu avantajı sunar: arama indeksi verinin kendisiyle aynı işlemde güncellenir. Yani senkronizasyon gecikmesi yoktur, ayrı bir servis işletmez, ayrı bir yedekleme akışı kurmazsınız.

Ayrı bir arama motoruna geçmeyi düşünmeniz gereken işaretler şunlar: onlarca milyon belgeyle çalışıyorsanız, gelişmiş dil analizi (Türkçe kök bulma, eş anlamlılar, yazım düzeltme) arama kalitesinin merkezindeyse, yüzlerce eşzamanlı arama isteği alıyorsanız ya da yönlü gezinme (faceted search) ve kişiselleştirilmiş sıralama gibi özellikler gerekiyorsa. Bunların hiçbiri sizin için geçerli değilse, ayrı bir arama motoru işletmenin bakım maliyeti kazancından büyük olur.

Sıkça Sorulan Sorular#

PostgreSQL Türkçe full-text arama destekliyor mu#

Kısmen. PostgreSQL çekirdeğinde Türkçe için yerleşik bir kök bulucu sözlük bulunmaz, bu yüzden to_tsvector('turkish', ...) standart kurulumda çalışmaz. Pratik çözüm, simple yapılandırmasını unaccent eklentisiyle birleştirip Türkçe karakter duyarsızlığı sağlamak ve gerekiyorsa kendi eş anlamlı sözlüğünüzü eklemektir. Yazım hatası toleransı için pg_trgm benzerlik aramasını yedek yöntem olarak eklemek de kaliteyi belirgin şekilde artırır.

tsvector sütunu ne kadar yer kaplar#

Genellikle kaynak metnin yarısı ile aynısı arasında bir yer kaplar; kısa ve tekrarlı metinlerde daha az, uzun ve zengin sözcük dağarcıklı metinlerde daha fazla. GIN indeksi de buna eklenir ve bazen sütunun kendisinden büyük olabilir. Gövde metnini tsvector'e dahil edip etmemeyi, disk maliyeti ile arama kalitesi arasında bir denge olarak değerlendirin.

ILIKE yerine neden full-text arama kullanmalıyım#

ILIKE '%kelime%' sorgusu indeks kullanamaz, yani her aramada tüm tabloyu tarar ve tablo büyüdükçe doğrusal olarak yavaşlar. Ayrıca kelime köklerini eşleştiremez, sonuçları alaka düzeyine göre sıralayamaz ve çok kelimeli sorguları mantıklı biçimde birleştiremez. Full-text arama bunların hepsini GIN indeksi üzerinden yapar ve büyük tablolarda milisaniyeler seviyesinde kalır.

websearch_to_tsquery ile to_tsquery farkı nedir#

to_tsquery operatör söz dizimi bekler ve geçersiz girdi geldiğinde hata fırlatır; bu yüzden kullanıcıdan gelen ham metinle kullanmak tehlikelidir. websearch_to_tsquery ise Google benzeri bir söz dizimini kabul eder: tırnak içi ifadeler, or kelimesi ve eksi işaretiyle dışlama. En önemlisi, hiçbir girdide hata vermez, bu yüzden arama kutuları için doğru seçim odur.

Arama sonuçlarını nasıl sıralarım#

ts_rank ya da ts_rank_cd fonksiyonlarıyla bir alaka skoru hesaplayıp ona göre sıralayın. Kaliteyi artırmak için aranabilir sütunu oluştururken setweight ile başlığa A, özete B, gövdeye C ağırlığı verin; böylece başlıkta geçen kelime daha değerli sayılır. Gerçek uygulamalarda bu skoru içeriğin tazeliği veya popülerliğiyle çarparak birleşik bir sıralama üretmek yaygın ve etkili bir yöntemdir.

Full-text arama indeksi ne zaman yeniden oluşturulmalı#

Oluşturulmuş sütun veya trigger kullanıyorsanız indeks kendiliğinden güncel kalır, elle yeniden oluşturmanız gerekmez. Ancak yoğun güncelleme alan tablolarda GIN indeksi zamanla şişer; REINDEX INDEX CONCURRENTLY ile tabloyu kilitlemeden tazeleyebilirsiniz. Ayrıca arama yapılandırmanızı (sözlük, eş anlamlılar) değiştirdiğinizde tsvector sütununu yeniden hesaplamanız gerekir, çünkü eski satırlar eski kurallarla üretilmiştir.

Kapanış#

PostgreSQL full-text arama, ayrı bir arama motoru işletmeden ciddi bir site içi arama kurmanızı sağlar; üstelik indeks veriyle aynı işlemde güncellendiği için senkronizasyon derdi yaşamazsınız. Aklınızda tutmanız gereken dört alışkanlık: kullanıcı girdisini her zaman websearch_to_tsquery ile işleyin, aranabilir sütunda coalesce ve setweight kullanmayı asla atlamayın, GIN indeksinin gerçekten kullanıldığını EXPLAIN ile doğrulayın ve ts_headline çağrısını mutlaka LIMIT uygulanmış sonuç kümesinin dışında yapın.

Arama iş yükü bellek ve disk erişimine çok duyarlıdır; GIN indeksi belleğe sığdığı sürece sonuçlar milisaniyelerle ölçülür, sığmadığında hissedilir biçimde yavaşlar. Kaynakları garantili VDS ve bulut sunucu paketlerimizde PostgreSQL'i kendi ayarlarınızla ölçekleyebilir, kurulum ve izleme yükünü sunucu yönetimi hizmetimize bırakabilirsiniz. İçerik odaklı siteler için WordPress hosting paketlerimize ve arama görünürlüğünü artırmak için SEO hizmetimize de göz atabilirsiniz.