PostgreSQL'de JSONB ve Full-Text Search Rehberi

PostgreSQL, ilişkisel modelin yanında iki güçlü yeteneği birlikte sunar: yarı yapılandırılmış veri için JSONB ve metin arama için full-text search (FTS). Bu rehber, bu iki özelliği birlikte kullanırken karşılaşılan tipik kararları ve tuzakları ele alır. Hedef kitle, SQL ve indeksleme temellerini bilen orta seviye geliştiricilerdir.

Önkoşullar ve Sürüm Kapsamı

• PostgreSQL 12 ve üzeri varsayılmıştır. JSONB operatörleri ve GIN indeksleri çok daha eski sürümlerde de mevcuttur; ancak burada anlatılan davranışlar için 12+ taban alınmıştır.
• FTS için tsvector, tsquery, to_tsvector, to_tsquery, plainto_tsquery, websearch_to_tsquery ve ts_rank fonksiyonları kullanılır. websearch_to_tsquery 11 ve sonrasında mevcuttur.
• Türkçe metinlerde dil yapılandırması (turkish config) kelime köklerini Türkçe kurallarına göre işler. Bu rehberdeki örneklerde simple ve turkish yapılandırmaları ayrı ayrı ele alınır.

JSONB Temelleri

JSONB, JSON verisini ikili biçimde saklar; JSON tipinden farkı, girişte anahtar sırasını korumaması ve tekrarlı anahtarları son değerle değiştirmesidir. Buna karşılık indekslenebilir ve operatörlerle sorgulanabilir.

Temel operatörler:

• -> : JSON değeri döndürür (JSON/JSONB).
• ->> : Metin (text) döndürür.
• #> : Yol (path) ile JSON değeri döndürür.
• #>> : Yol ile metin döndürür.
• @> : Sol taraf sağ tarafı içeriyor mu (containment).
• ? : Anahtar/değer var mı.
• ?| : Verilen anahtarlardan en az biri var mı.
• ?& : Verilen anahtarların tümü var mı.

Örnek tablo:

CREATE TABLE urunler (
    id       bigserial PRIMARY KEY,
    ad       text NOT NULL,
    ozellikler jsonb NOT NULL DEFAULT '{}'::jsonb,
    olusturma timestamptz NOT NULL DEFAULT now()
);

Örnek kayıt ve sorgular:

INSERT INTO urunler (ad, ozellikler) VALUES
('Kablosuz Kulaklık', '{"renk":"siyah","bluetooth":true,"fiyat":1499,"etiketler":["ses","kablosuz"]}'),
('Mekanik Klavye',   '{"renk":"beyaz","bluetooth":false,"fiyat":2199,"etiketler":["girdi","mekanik"]}');

-- Renk alanını metin olarak çek
SELECT ad, ozellikler->>'renk' AS renk FROM urunler;

-- İç içe yol ile okuma
SELECT ozellikler#>>'{etiketler,0}' AS ilk_etiket FROM urunler;

-- Containment: bluetooth true olanlar
SELECT ad FROM urunler WHERE ozellikler @> '{"bluetooth": true}'::jsonb;

-- Anahtar varlığı
SELECT ad FROM urunler WHERE ozellikler ? 'fiyat';

Beklenen davranış: ->> her zaman text döndürür; sayısal karşılaştırma yapmak istiyorsanız uygun bir türe çevirmeniz gerekir. @> containment operatörü, dizilerde sıralamayı değil eleman varlığını esas alır.

JSONB İndeksleme

@>, ?, ?|, ?& gibi operatörler için standart GIN indeksi kullanılır:

CREATE INDEX idx_urunler_ozellikler ON urunler USING gin (ozellikler);

Belirli bir yol üzerinden sık sorguluyorsanız ifade indeksi tercih edilebilir:

CREATE INDEX idx_urunler_renk ON urunler ((ozellikler->>'renk'));

jsonb_path_ops operatör sınıfı, yalnızca containment (@>) sorguları için daha küçük ve hızlı bir indeks üretir; ancak ? operatörünü desteklemez:

CREATE INDEX idx_urunler_ozellikler_path ON urunler USING gin (ozellikler jsonb_path_ops);

Karar: Hem @> hem de anahtar varlığı sorguları yapıyorsanız varsayılan jsonb_ops sınıfını kullanın. Yalnızca containment ağırlıklıysa jsonb_path_ops avantajlıdır.

Full-Text Search Temelleri

FTS, metni tsvector'e, sorguyu tsquery'ye dönüştürüp eşleştirir. İki temel kavram:

• tsvector: Normalize edilmiş lexeme'ler ve pozisyonları.
• tsquery: Aranacak lexeme'ler ve mantıksal operatörler (&, |, !, <->).

SELECT to_tsvector('simple', 'PostgreSQL veritabanı güçlüdür');
-- 'güçlüdür':4 'postgresql':1 'veritabanı':2

SELECT to_tsquery('simple', 'postgresql & veritabanı');

simple yapılandırması stemming uygulamaz; kelimeleri olduğu gibi (küçük harfe indirerek) tutar. Türkçe için turkish yapılandırması kullanılabilir:

SELECT to_tsvector('turkish', 'Kitaplarımı masanın üzerine koydum');

Beklenen davranış: Türkçe stemming, ekleri kırparak kökleri eşleştirmeye çalışır. Bazı kelimelerde beklenmedik sonuçlar çıkabilir; kritik alanlarda çıktıyı doğrulamanız gerekir.

Kullanıcı Girdisini Sorguya Çevirme

Kullanıcı girdisini doğrudan to_tsquery'ye vermek hatalıdır; özel karakterler sözdizimi hatası üretir. Bunun yerine:

• plainto_tsquery: Serbest metni AND ile birleştirir.
• websearch_to_tsquery: "tam ifade", -hariç, veya gibi web arama sözdizimini yorumlar (PostgreSQL 11+).
• phraseto_tsquery: Kelime sırasını koruyan ifade sorgusu.

SELECT websearch_to_tsquery('turkish', 'postgresql "json indeks" -mysql');

İndeksleme ve Sorgulama

tsvector sütunu üzerinde GIN indeksi kurulur:

CREATE TABLE makaleler (
    id      bigserial PRIMARY KEY,
    baslik  text NOT NULL,
    icerik  text NOT NULL,
    arama   tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('turkish', coalesce(baslik, '')), 'A') ||
        setweight(to_tsvector('turkish', coalesce(icerik, '')), 'B')
    ) STORED
);

CREATE INDEX idx_makaleler_arama ON makaleler USING gin (arama);

GENERATED ALWAYS AS ... STORED sütunu PostgreSQL 12 ile geldi; bu sayede uygulama tarafında tetikleyici yazmaya gerek kalmaz. setweight, başlığa daha yüksek ağırlık vererek sıralamada öne çıkmasını sağlar.

Sorgu:

SELECT id, baslik,
       ts_rank(arama, q) AS skor
FROM makaleler, websearch_to_tsquery('turkish', 'json indeks') AS q
WHERE arama @@ q
ORDER BY skor DESC
LIMIT 20;

Beklenen davranış: @@ operatörü GIN indeksini kullanır; ts_rank sıralama için uygundur. Çok büyük tablolarda sıralama maliyetini düşürmek için ts_rank_cd veya önce WHERE ile daraltıp sonra sıralamak gerekebilir.

JSONB ve FTS'i Birlikte Kullanmak

Yaygın senaryo: kayıtların bir kısmı sabit sütunlarda, bir kısmı JSONB içinde tutulur; arama ise her ikisini kapsamalıdır.

Örnek: urunler.ozellikler içindeki aciklama ve etiketler alanlarını aramaya dahil etmek.

ALTER TABLE urunler
ADD COLUMN arama tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('turkish', coalesce(ad, '')), 'A') ||
    setweight(to_tsvector('turkish', coalesce(ozellikler->>'aciklama', '')), 'B') ||
    setweight(to_tsvector('turkish',
        coalesce(array_to_string(
            ARRAY(SELECT jsonb_array_elements_text(ozellikler->'etiketler')), ' '), '')
    ), 'C')
) STORED;

CREATE INDEX idx_urunler_arama ON urunler USING gin (arama);

Not: GENERATED ifadesinde alt sorgu kullanılamaz; yukarıdaki gibi jsonb_array_elements_text çağrısını ARRAY(...) içinde toplamak gerekir. Bu ifade, tablo oluşturulurken veya ALTER TABLE sırasında değerlendirilir; sonraki güncellemelerde otomatik yeniden hesaplanır.

Sorgu:

SELECT id, ad
FROM urunler, websearch_to_tsquery('turkish', 'kablosuz ses') AS q
WHERE arama @@ q
  AND ozellikler @> '{"bluetooth": true}'::jsonb
ORDER BY ts_rank(arama, q) DESC;

Beklenen davranış: Planlayıcı, arama @@ q için GIN, ozellikler @> ... için ayrı GIN indeksini kullanabilir ve sonuçları birleştirir. İki farklı GIN indeksini tek sorguda kullanmak her zaman ideal plan üretmez; EXPLAIN (ANALYZE, BUFFERS) ile doğrulayın.

Yaygın Hatalar

• to_tsquery'ye ham kullanıcı girdisi vermek. websearch_to_tsquery veya plainto_tsquery kullanın.
• JSON yerine JSONB kullanmamak. İndeksleme ve operatör desteği için JSONB tercih edilir.
• jsonb_path_ops ile ? operatörünü kullanmaya çalışmak. Bu operatör sınıfı ? desteklemez; sessizce indeks kullanılmaz.
• -> ve ->> karıştırmak. WHERE ozellikler->'fiyat' = '1499' çalışmaz; ->> ile metin karşılaştırması yapın veya (ozellikler->>'fiyat')::numeric gibi tür dönüşümü uygulayın.
• Dil yapılandırmasını tutarsız kullanmak. İndeks turkish ile üretilip sorgu simple ile çalıştırılırsa eşleşme olmaz. İndeks ve sorgu aynı yapılandırmayı kullanmalıdır.
• tsvector sütununu uygulama tarafında elle güncellemek. GENERATED kullanılıyorsa elle yazmayın; tetikleyici kullanılıyorsa tutarlılığı test edin.
• Ağırlıkları sıralamada yok saymak. ts_rank ağırlıkları dikkate alır; ancak ts_rank_cd farklı davranır, ihtiyaca göre seçin.

Güvenlik ve Performans Notları

• SQL enjeksiyonu: tsquery ve JSONB yolları parametreli sorgularla verilmelidir. Yol (#>>) ve anahtar adları dinamik üretiliyorsa beyaz liste uygulayın; kullanıcıdan gelen anahtar adını doğrudan SQL'e gömmeyin.
• websearch_to_tsquery beklenmedik ifadeleri reddeder, ancak yine de girdi uzunluğunu sınırlayın. Çok uzun sorgular planlayıcıyı yorabilir.
• GIN indeksleri yazma maliyetini artırır. Yoğun yazma yapan tablolarda fastupdate ve gin_pending_list_limit ayarlarını izleyin.
• JSONB boyutu: Çok büyük JSONB değerleri TOAST'a taşınır; sık erişilen alanları ayrı sütunlara çıkarmak daha verimli olabilir.
• Sürüm uyumu: GENERATED ... STORED 12+, websearch_to_tsquery 11+ gerektirir. Daha eski sürümlerde tetikleyici ve to_tsquery ile eşdeğerini kurmanız gerekir.

Kapanış

JSONB ve FTS, PostgreSQL'de ayrı ayrı güçlü; birlikte kullanıldığında esnek şema ile metin aramayı tek veritabanında birleştirir. Kritik noktalar: doğru indeks türünü seçmek, dil yapılandırmasını indeks ve sorguda tutarlı tutmak, kullanıcı girdisini güvenli fonksiyonlarla sorguya çevirmek ve planı EXPLAIN ile doğrulamak. Bu ilkeler, sürümden bağımsız olarak uygulanabilir kalır.