PostgreSQL’de Full-Text Search Yapılandırması

yazar

Veritabanlarında arama işlevi kurarken çoğu geliştirici ilk içgüdüyle LIKE '%kelime%' sorgusuna sarılır. Bu yaklaşım küçük veri setlerinde işe yarar ama tablo büyüdükçe performans felç olur, Türkçe karakter sorunları baş gösterir ve alakasız sonuçlar kullanıcıyı bunaltır. PostgreSQL’in yerleşik Full-Text Search motoru bu sorunların tamamına kalıcı çözüm sunar. Üstelik harici bir Elasticsearch kurulumuna gerek kalmadan, veritabanının kendi içinde güçlü bir metin arama altyapısı elde edersiniz.

Full-Text Search Temel Kavramları

PostgreSQL’de tam metin araması iki temel veri tipi üzerine kuruludur.

tsvector, bir belgeyi aramalara uygun hale getirilen normalize edilmiş kelime listesi olarak saklar. Kelimelerin konumu ve ağırlığı da bu yapıda tutulur.

tsquery, arama sorgusunu temsil eder. Mantıksal operatörler (&, |, !) ve önek arama (kelime:*) destekler.

Bu iki tipi bir araya getiren @@ operatörü, eşleşme kontrolünü yapar.

-- Temel kavram demosu
SELECT 'postgresql veritabani yonetimi'::tsvector @@ 'veritabani'::tsquery;
-- Sonuç: true

SELECT to_tsvector('turkish', 'PostgreSQL güçlü bir veritabanı sistemidir') 
       @@ to_tsquery('turkish', 'veritabanı & güçlü');
-- Sonuç: true

to_tsvector() fonksiyonu metni alır, belirtilen dil konfigürasyonuna göre kelimeleri köküne indirger (stemming), stop word’leri kaldırır ve sıralı bir leksem listesi üretir. to_tsquery() ise arama terimlerini aynı süreçten geçirip sorgu yapısına dönüştürür.

Türkçe Dil Desteği Kurulumu

PostgreSQL varsayılan olarak Türkçe text search konfigürasyonu içermez. Bu nedenle ya mevcut bir konfigürasyonu temel alarak özel bir yapı oluşturmanız ya da pg_trgm uzantısıyla trigram tabanlı arama yapmanız gerekir.

Önce mevcut konfigürasyonları görelim:

-- Mevcut text search konfigürasyonları
SELECT cfgname, cfgparser FROM pg_ts_config;

-- Dil bazlı sözlükleri listele
SELECT dictname, dicttemplate FROM pg_ts_dict;

Türkçe için basit ama etkili bir yaklaşım, simple konfigürasyonunu temel almaktır. Bu konfigürasyon stemming yapmaz, sadece küçük harfe dönüştürür. Türkçe morfolojisinin karmaşıklığı göz önünde bulundurulduğunda bu yaklaşım çoğu zaman daha tahmin edilebilir sonuçlar verir.

-- Özel Türkçe konfigürasyon oluşturma
CREATE TEXT SEARCH CONFIGURATION turkish_simple (COPY = simple);

-- Türkçe stop word dosyası oluşturma
-- /usr/share/postgresql/16/tsearch_data/turkish.stop dosyasını oluşturun
-- İçeriğe şu kelimeleri ekleyin: ve, veya, ile, bu, şu, bir, de, da, ki...

-- Konfigürasyonu varsayılan olarak ayarlama (oturum bazlı)
SET default_text_search_config = 'turkish_simple';

Alternatif olarak unaccent uzantısını kullanarak Türkçe karakterleri normalize edebilirsiniz:

-- Unaccent uzantısını yükle
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Unaccent kullanan özel sözlük
CREATE TEXT SEARCH DICTIONARY turkish_unaccent (
    TEMPLATE = unaccent,
    RULES = 'unaccent'
);

-- Konfigürasyonu unaccent ile güncelle
CREATE TEXT SEARCH CONFIGURATION turkish_full (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION turkish_full
    ALTER MAPPING FOR hword, hword_part, word
    WITH turkish_unaccent, simple;

Gerçek Dünya Senaryosu: Ürün Kataloğu Arama Sistemi

Bir e-ticaret platformu için ürün arama sistemi kuralım. Ürünlerin adı, açıklaması ve kategori bilgisi üzerinde arama yapılacak, farklı alanlara farklı ağırlıklar verilecek.

-- Ürün tablosu oluşturma
CREATE TABLE urunler (
    id SERIAL PRIMARY KEY,
    urun_adi VARCHAR(255) NOT NULL,
    aciklama TEXT,
    kategori VARCHAR(100),
    marka VARCHAR(100),
    fiyat DECIMAL(10,2),
    stok INTEGER DEFAULT 0,
    arama_vektoru TSVECTOR,
    olusturma_tarihi TIMESTAMP DEFAULT NOW()
);

-- Örnek veri ekleme
INSERT INTO urunler (urun_adi, aciklama, kategori, marka, fiyat, stok) VALUES
('Sony WH-1000XM5 Kulaklık', 'Gürültü engelleyici kablosuz kulaklık, 30 saat pil ömrü', 'Elektronik', 'Sony', 4999.90, 45),
('Samsung Galaxy S24 Ultra', 'Yapay zeka destekli akıllı telefon, 200MP kamera', 'Telefon', 'Samsung', 42999.00, 12),
('Apple MacBook Pro M3', 'M3 çipli profesyonel dizüstü bilgisayar', 'Bilgisayar', 'Apple', 89999.00, 8),
('Logitech MX Master 3', 'Ergonomik kablosuz mouse, programlanabilir tuşlar', 'Aksesuar', 'Logitech', 1299.90, 78);

Şimdi ağırlıklı vektör oluşturma mantığını kuralım. PostgreSQL’de setweight() fonksiyonu ile farklı alanlara A, B, C, D ağırlıkları atanır (A en yüksek ağırlık):

-- Arama vektörünü hesaplayan fonksiyon
CREATE OR REPLACE FUNCTION urun_arama_vektoru_guncelle()
RETURNS TRIGGER AS $$
BEGIN
    NEW.arama_vektoru :=
        setweight(to_tsvector('simple', COALESCE(NEW.urun_adi, '')), 'A') ||
        setweight(to_tsvector('simple', COALESCE(NEW.marka, '')), 'B') ||
        setweight(to_tsvector('simple', COALESCE(NEW.kategori, '')), 'C') ||
        setweight(to_tsvector('simple', COALESCE(NEW.aciklama, '')), 'D');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger oluşturma
CREATE TRIGGER urun_arama_vektoru_trigger
    BEFORE INSERT OR UPDATE ON urunler
    FOR EACH ROW EXECUTE FUNCTION urun_arama_vektoru_guncelle();

-- Mevcut kayıtları güncelle
UPDATE urunler SET urun_adi = urun_adi;

-- GIN indeks oluşturma (full-text search için ideal)
CREATE INDEX idx_urunler_arama ON urunler USING GIN(arama_vektoru);

Arama Sorguları ve Operatörler

Temel altyapıyı kurduktan sonra etkili sorgular yazmaya geçelim:

-- Basit kelime araması
SELECT urun_adi, marka, fiyat,
       ts_rank(arama_vektoru, to_tsquery('simple', 'kulaklık')) AS skor
FROM urunler
WHERE arama_vektoru @@ to_tsquery('simple', 'kulaklık')
ORDER BY skor DESC;

-- VE operatörü: Her iki kelime de geçmeli
SELECT urun_adi FROM urunler
WHERE arama_vektoru @@ to_tsquery('simple', 'kablosuz & kulaklık');

-- VEYA operatörü: En az biri geçmeli
SELECT urun_adi FROM urunler
WHERE arama_vektoru @@ to_tsquery('simple', 'kulaklık | mouse');

-- DEĞİL operatörü: Belirtilen kelime geçmemeli
SELECT urun_adi FROM urunler
WHERE arama_vektoru @@ to_tsquery('simple', 'kablosuz & !kulaklık');

-- Önek araması: "kab" ile başlayan kelimeler
SELECT urun_adi FROM urunler
WHERE arama_vektoru @@ to_tsquery('simple', 'kab:*');

-- websearch_to_tsquery: Kullanıcı dostu arama
SELECT urun_adi FROM urunler
WHERE arama_vektoru @@ websearch_to_tsquery('simple', '"kablosuz kulaklık" -ucuz');

websearch_to_tsquery() fonksiyonu Google tarzı arama sözdizimini destekler. Çift tırnak içindeki ifadeler tam eşleşme arar, eksi işareti kelimeyi hariç tutar. Kullanıcı arayüzlerinden gelen ham arama girişlerini işlemek için en güvenli seçenektir.

Sonuç Sıralama ve Alaka Düzeyi

Sonuçları anlamlı biçimde sıralamak için ts_rank() ve ts_rank_cd() fonksiyonlarını kullanırız. ts_rank_cd() cover density algoritmasını uygular ve birbirine yakın kelimeleri daha yüksek puanlar:

-- Gelişmiş sıralama sorgusu
SELECT 
    urun_adi,
    marka,
    fiyat,
    ts_rank_cd(arama_vektoru, sorgu, 32) AS skor,
    ts_headline('simple', aciklama, sorgu, 
                'MaxWords=15, MinWords=5, ShortWord=3, 
                 HighlightAll=false, MaxFragments=3') AS ozet
FROM urunler,
     to_tsquery('simple', 'kablosuz & kulaklık') sorgu
WHERE arama_vektoru @@ sorgu
ORDER BY skor DESC
LIMIT 20;

ts_headline() fonksiyonu arama sonuçları sayfasında eşleşen metni vurgulayarak göstermek için kullanılır. Parametreler:

  • MaxWords: Özette gösterilecek maksimum kelime sayısı
  • MinWords: Minimum kelime sayısı
  • ShortWord: Bu uzunluktan kısa kelimeler başlangıç/bitiş noktası olarak kullanılmaz
  • MaxFragments: Kaç ayrı metin parçası gösterileceği
  • StartSel / StopSel: Vurgulama için kullanılacak HTML etiketleri (varsayılan )

Trigram Tabanlı Fuzzy Search

Yazım hatalarına toleranslı arama için pg_trgm uzantısı devreye girer. Full-text search ile trigram aramayı birleştirmek çok güçlü bir kullanıcı deneyimi sağlar:

-- pg_trgm uzantısını aktifleştir
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Trigram indeksi oluştur
CREATE INDEX idx_urunler_trgm ON urunler 
USING GIN(urun_adi gin_trgm_ops);

-- Benzerlik araması (similarity score)
SELECT urun_adi, similarity(urun_adi, 'Soniy Kulaklık') AS benzerlik
FROM urunler
WHERE similarity(urun_adi, 'Soniy Kulaklık') > 0.1
ORDER BY benzerlik DESC;

-- Full-text ve trigram aramasını birleştir
SELECT 
    urun_adi,
    marka,
    fiyat,
    CASE 
        WHEN arama_vektoru @@ websearch_to_tsquery('simple', 'kulaklık') 
        THEN ts_rank(arama_vektoru, websearch_to_tsquery('simple', 'kulaklık')) * 2
        ELSE similarity(urun_adi, 'kulaklık')
    END AS toplam_skor
FROM urunler
WHERE 
    arama_vektoru @@ websearch_to_tsquery('simple', 'kulaklık')
    OR similarity(urun_adi, 'kulaklık') > 0.15
ORDER BY toplam_skor DESC
LIMIT 10;

GIN ve GiST İndeks Karşılaştırması

Full-text search için iki temel indeks tipi vardır:

GIN (Generalized Inverted Index):

  • Okuma performansı üstündür
  • Yazma işlemleri daha yavaştır (büyük tablolarda önemli)
  • fastupdate parametresiyle yazma performansı iyileştirilebilir
  • Üretim ortamları için genellikle tercih edilir

GiST (Generalized Search Tree):

  • Yazma performansı daha iyidir
  • Okuma sorguları GIN kadar hızlı değildir
  • Kayıp indeks (lossy index) olduğundan heap’e geri dönüş gerekebilir
  • Sık güncellenen tablolar için düşünülebilir
-- GIN indeks (önerilen)
CREATE INDEX idx_icerik_gin ON belgeler USING GIN(arama_vektoru);

-- GIN fastupdate ile (çok sık yazma varsa)
CREATE INDEX idx_icerik_gin_fast ON belgeler 
USING GIN(arama_vektoru) WITH (fastupdate = on);

-- GiST indeks
CREATE INDEX idx_icerik_gist ON belgeler USING GiST(arama_vektoru);

-- İndeks boyutunu kontrol et
SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS boyut
FROM pg_indexes
WHERE tablename = 'belgeler';

Performans İzleme ve Optimizasyon

Arama sorgularının gerçekten indeks kullandığını doğrulamak kritik önemdedir:

-- Sorgu planını incele
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT urun_adi, ts_rank(arama_vektoru, sorgu) AS skor
FROM urunler, to_tsquery('simple', 'kulaklık') sorgu
WHERE arama_vektoru @@ sorgu
ORDER BY skor DESC
LIMIT 10;

-- İstatistikleri güncelle (indeks kullanımını etkiler)
ANALYZE urunler;

-- Yavaş full-text sorgularını yakala
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
WHERE query LIKE '%arama_vektoru%'
ORDER BY mean_exec_time DESC
LIMIT 10;

-- postgresql.conf ayarları (performans için)
-- work_mem = 64MB        # Sıralama işlemleri için
-- effective_cache_size = 4GB  # Sorgu planlayıcı için ipucu
-- random_page_cost = 1.1      # SSD kullanıyorsanız

Büyük tablolarda arama vektörünü gerçek zamanlı hesaplamak yerine ayrı bir kolonda saklamak ve trigger ile güncel tutmak performans açısından kritiktir. Ancak trigger yaklaşımının da bir maliyeti vardır: her INSERT ve UPDATE işlemi tetikleyiciyi çalıştırır. Çok yoğun yazma işlemi olan tablolarda GENERATED ALWAYS sütunları veya ayrı bir arama tablosu (search index table) mimarisi değerlendirilebilir.

Çok Dilli İçerik Senaryosu

Farklı dillerde içerik barındıran bir blog platformu için dinamik dil seçimi yapalım:

-- Çok dilli makale tablosu
CREATE TABLE makaleler (
    id SERIAL PRIMARY KEY,
    baslik VARCHAR(500),
    icerik TEXT,
    dil VARCHAR(20) DEFAULT 'turkish_simple',
    arama_vektoru TSVECTOR
);

-- Dile göre dinamik vektör oluşturan trigger
CREATE OR REPLACE FUNCTION makale_vektoru_guncelle()
RETURNS TRIGGER AS $$
DECLARE
    dil_konfigurasyonu REGCONFIG;
BEGIN
    -- Geçerli dil konfigürasyonunu belirle
    BEGIN
        dil_konfigurasyonu := NEW.dil::REGCONFIG;
    EXCEPTION WHEN OTHERS THEN
        dil_konfigurasyonu := 'simple';
    END;
    
    NEW.arama_vektoru :=
        setweight(to_tsvector(dil_konfigurasyonu, 
                              COALESCE(NEW.baslik, '')), 'A') ||
        setweight(to_tsvector(dil_konfigurasyonu, 
                              COALESCE(NEW.icerik, '')), 'D');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER makale_arama_trigger
    BEFORE INSERT OR UPDATE ON makaleler
    FOR EACH ROW EXECUTE FUNCTION makale_vektoru_guncelle();

-- Dile göre filtrelenmiş arama
SELECT baslik, dil,
       ts_rank(arama_vektoru, to_tsquery('simple', 'veritabanı')) AS skor
FROM makaleler
WHERE dil = 'turkish_simple'
  AND arama_vektoru @@ to_tsquery('simple', 'veritabanı')
ORDER BY skor DESC;

Partitioned Tablolarda Full-Text Search

Milyonlarca kayıt barındıran büyük tablolarda tablo bölümleme ile arama performansını artırabilirsiniz:

-- Tarih bazlı bölümlenmiş log tablosu
CREATE TABLE arama_loglari (
    id BIGSERIAL,
    kullanici_id INTEGER,
    arama_terimi TEXT,
    sonuc_sayisi INTEGER,
    islem_suresi_ms INTEGER,
    tarih TIMESTAMP DEFAULT NOW()
) PARTITION BY RANGE (tarih);

-- Aylık bölümler
CREATE TABLE arama_loglari_2024_01 
    PARTITION OF arama_loglari
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE arama_loglari_2024_02 
    PARTITION OF arama_loglari
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Her bölüme indeks
CREATE INDEX ON arama_loglari_2024_01 (tarih, kullanici_id);

-- En çok aranan terimler analizi
SELECT arama_terimi, COUNT(*) AS arama_sayisi, 
       AVG(sonuc_sayisi) AS ort_sonuc,
       AVG(islem_suresi_ms) AS ort_sure
FROM arama_loglari
WHERE tarih >= NOW() - INTERVAL '30 days'
GROUP BY arama_terimi
ORDER BY arama_sayisi DESC
LIMIT 20;

Materialized View ile Arama Optimizasyonu

Birden fazla tabloyu birleştiren karmaşık arama senaryolarında materialized view yaklaşımı oldukça etkilidir:

-- Birleşik arama view'u
CREATE MATERIALIZED VIEW urun_arama_mv AS
SELECT 
    u.id,
    u.urun_adi,
    u.fiyat,
    u.stok,
    k.kategori_adi,
    m.marka_adi,
    setweight(to_tsvector('simple', COALESCE(u.urun_adi, '')), 'A') ||
    setweight(to_tsvector('simple', COALESCE(m.marka_adi, '')), 'B') ||
    setweight(to_tsvector('simple', COALESCE(k.kategori_adi, '')), 'C') ||
    setweight(to_tsvector('simple', COALESCE(u.aciklama, '')), 'D') 
    AS arama_vektoru
FROM urunler u
LEFT JOIN kategoriler k ON u.kategori_id = k.id
LEFT JOIN markalar m ON u.marka_id = m.id
WHERE u.stok > 0 AND u.aktif = true;

-- View üzerinde indeks
CREATE UNIQUE INDEX ON urun_arama_mv (id);
CREATE INDEX ON urun_arama_mv USING GIN (arama_vektoru);

-- View'u yenileme (cron job ile periyodik çalıştırılabilir)
REFRESH MATERIALIZED VIEW CONCURRENTLY urun_arama_mv;

-- Yenileme görevi için pg_cron (opsiyonel uzantı)
SELECT cron.schedule('arama-mv-yenile', '*/15 * * * *', 
    'REFRESH MATERIALIZED VIEW CONCURRENTLY urun_arama_mv');

CONCURRENTLY parametresi yenileme sırasında view’un kilitlenmemesini sağlar, bu sayede kullanıcılar yenileme devam ederken aramaya devam edebilir.

Sonuç

PostgreSQL Full-Text Search, doğru yapılandırıldığında harici bir arama motoruna ihtiyaç duymadan kurumsal düzeyde metin arama çözümü sunar. Özetlemek gerekirse:

  • tsvector + GIN indeks kombinasyonu temel performans garantisini verir
  • Trigger bazlı vektör güncelleme veri tutarlılığını otomatik korur
  • ts_rank_cd() ve ağırlıklı alanlar (A/B/C/D) alakalı sıralama sağlar
  • pg_trgm ile yazım toleransı eklenerek kullanıcı deneyimi iyileştirilir
  • Materialized view çok tablolu senaryolarda sorgu karmaşıklığını azaltır
  • websearch_to_tsquery() kullanıcı girdisini güvenle işler

Türkçe içerik için simple konfigürasyonu ile başlamak, unaccent uzantısıyla karakter normalizasyonu eklemek ve gerekmesi halinde özel sözlük oluşturmak en sağlıklı yoldur. Elasticsearch’ün sunduğu analitik yetenekler veya gerçek zamanlı yatay ölçekleme ihtiyacı olmayan projelerde PostgreSQL FTS, hem altyapı karmaşıklığını hem de operasyonel maliyeti ciddi ölçüde düşürür.

Yorum yapın