PostgreSQL tam metin arama: Doğru yapılırsa hızlıdır (yavaş olduğu yanılgısını giderme)

• PostgreSQL’in yerleşik Full-Text Search(FTS) özelliğinin yavaş olduğuna dair bir algı var, ancak uygun optimizasyon yapıldığında son derece hızlı çalışabilir
• Neon’un blogunda Rust tabanlı pg_search uzantısı ile yerleşik FTS karşılaştırılıyor ve ikincisinin yavaş olduğu öne sürülüyor
• Ancak bu karşılaştırma büyük olasılıkla PostgreSQL FTS için gerekli olan temel optimizasyon adımları atlanmış halde yapıldı
• Bu yazı, yerleşik FTS yapılandırmasına yalnızca basit optimizasyonlar uygulanarak bile 50 kat performans artışı sağlanabileceğini sayılarla gösteriyor

Benchmark kurulumuna genel bakış

• 10 milyon log verisi içeren bir tablo temel alınarak test yapıldı

  CREATE TABLE benchmark_logs (  
      id SERIAL PRIMARY KEY,  
      message TEXT,  
      country VARCHAR(255),  
      severity INTEGER,  
      timestamp TIMESTAMP,  
      metadata JSONB  
  );  
  

• Sorunlu sorgu yapısı:

  SELECT country, COUNT(*)  
  FROM benchmark_logs  
  WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')  
  GROUP BY country  
  ORDER BY country;  
  

  • to_tsvector() sorgu içinde çalıştırılıyor → son derece verimsiz
  • GIN indeksi olsa bile düzgün şekilde kullanılamıyor

Test ortamı (varsayılan kurulumun kopyası)

• EC2 i7ie.xlarge instance, yerel NVMe SSD kullanıldı
• 4 vCPU, PostgreSQL 16(Docker) kullanıldı
• Başlıca PostgreSQL ayarları:

  -c shared_buffers=8GB  
  -c maintenance_work_mem=8GB  
  -c max_parallel_workers=4  
  -c max_worker_processes=4  
  

• Paralel işleme sınırı: max_parallel_workers_per_gather = 2 (Neon 8 kullanıyor)

Performans düşüşü nedeni 1: anlık tsvector hesaplama

• to_tsvector() sorgu içinde çalıştırıldığında:
• metin ayrıştırma, morfolojik analiz vb. her seferinde yeniden yapılır
• indekslerden hiç yararlanılamaz

• Çözüm: tsvector sütununu önceden oluşturup indekslemek

  • 1. tsvector sütunu ekleyin

  ALTER TABLE benchmark_logs ADD COLUMN message_tsvector tsvector;  
  

  • 2. Veriyi doldurun

    UPDATE benchmark_logs SET message_tsvector = to_tsvector('english', message);  
    

  • 3. İndeks oluşturun (fastupdate devre dışı)

    CREATE INDEX idx_gin_logs_message_tsvector  
    ON benchmark_logs USING GIN (message_tsvector)  
    WITH (fastupdate = off);  
    

  • 4. Sorguyu değiştirin

    SELECT country, COUNT(*)  
    FROM benchmark_logs  
    WHERE message_tsvector @@ to_tsquery('english', 'research')  
    GROUP BY country  
    ORDER BY country;  
    

Performans düşüşü nedeni 2: GIN indeksinde fastupdate=on ayarı

• fastupdate=on yazma performansı için faydalıdır, ancak arama performansını olumsuz etkiler
• Salt okunur veya arama odaklı veri kümelerinde fastupdate=off zorunludur
• İndeks daha küçük ve daha hızlı olur, ayrıca pending list işleme ihtiyacı ortadan kalkar

• Optimize edilmiş GIN indeksi oluşturma yöntemi

  CREATE INDEX idx_gin_logs_message_tsvector  
  ON benchmark_logs USING GIN (message_tsvector)  
  WITH (fastupdate = off);  
  

Performans artışı: 50 kattan fazla iyileşme

• Optimizasyon öncesi: yaklaşık 41,3 saniye (41.301 ms)
• Optimizasyon sonrası: yaklaşık 0,88 saniye (877 ms)
• Yaklaşık 50 kat performans artışı gösteriyor
• Daha az paralel işleme kullanılan ortamlarda da bu performans elde edilebiliyor

ts_rank performansı gerçekten yavaş olabilir

• ts_rank veya ts_rank_cd, tüm sonuçları değerlendirip ardından sıraladığı için görece yavaş olabilir
• Özellikle çok sayıda sonuç söz konusu olduğunda CPU/IO yükü büyür

Gelişmiş sıralama özelliği: VectorChord-BM25 uzantısı

• Sıralama doğruluğu ve hızının önemli olduğu durumlarda özel bir uzantı kullanmak daha etkili olabilir
• VectorChord-BM25, PostgreSQL için bir uzantıdır ve BM25 algoritması tabanlı sıralama değerlendirmesi sunar
• Elasticsearch’ten 3 kat daha hızlı olduğuna dair raporlar da bulunuyor

VectorChord-BM25’in avantajları

• BM25 algoritması: TF-IDF’den daha gelişmiş bir arama sıralama algoritması
• Özel indeks biçimi: Block WeakAnd gibi yüksek hızlı arama optimizasyonları
• bm25vector tipi sunar: tokenize edilmiş gösterimi saklar
• Hem arama doğruluğunu hem de hızı artırır

Sonuç: PostgreSQL’in yerleşik FTS’i de fazlasıyla hızlı olabilir

• tsvector sütunu ve uygun GIN indeksi (fastupdate=off) kullanıldığında, yerleşik FTS ile de çok hızlı arama yapılabilir
• Performans karşılaştırmaları optimize edilmiş bir temel üzerinden yapılmalıdır
• Gelişmiş sıralama özelliklerine ihtiyaç varsa VectorChord-BM25 gibi uzantı araçları değerlendirilebilir
• Ana mesaj: yavaş olan araç değil, sorun ayarlarda olabilir

[stadia]

Sayesinde sorgu ayarı yaptım.

[pcj9024]

Hacker News yorumları korkutucu... "On milyon? Şaka mı?"

[GN⁺]

Hacker News görüşleri

• pg_search'ün bakımcısı olarak, Postgres belgelerine göre hem Neon/ParadeDB yazısında hem de burada kullanılan stratejilerin geçerli alternatifler olarak sunulduğunu belirtiyorum

  • Postgres FTS'nin sorunu tek bir sorguyu optimize etmek değil, çok çeşitli gerçek dünya sorgularında Elastic düzeyinde performans sunmaktır
  • pg_search bu ikinci sorunu çözmek için tasarlandı ve benchmark'lar da bunu yansıtıyor
  • Neon/ParadeDB benchmark'ı toplam 12 sorgu içeriyor ve gerçekçi kullanım senaryolarında bu pek gerçekçi değil
  • pg_search, çeşitli "Elastic tarzı" sorgular ve Postgres tipleri için yalnızca basit index tanımlarıyla çalışır

• tsvector'ü gerçek zamanlı hesaplamak büyük bir hata

  • Postgres FTS'yi kişisel bir projede uyguladığımda belgeleri okudum ve yönergeleri takip ettim
  • Belgeler, temel optimize edilmemiş bir durumun nasıl oluşturulacağını ve sonra nasıl optimize edileceğini açıkça anlatıyor
  • Bu hatayı yapan kişinin ya belgeleri okumadığı ya da Postgres FTS'yi yanlış tanıtma niyetinde olduğu anlaşılıyor

• Her şeyi Postgres'in içine koyma eğilimini anlayamıyorum

• Postgres-native tam metin arama uygulamalarını daha fazla görmek hoşuma gidiyor

  • Alternatif çözümler (lucene/tantivy) değişmez segmentler için tasarlanmıştır; Postgres heap tablolarıyla birleştirildiğinde daha kötü bir çözüm olabilirler

• Açıklama planı olmadığı için neler olduğunu anlamak zor

  • Sorgu index kullandığında, gerçek zamanlı tsvector yeniden denetimi yalnızca eşleşen kayıtlar için uygulanır ve benchmark sorgusu LIMIT 10 kullandığı için yeniden denetim az olur
  • Sorgu koşulunun 2 gin index üzerinde şartları var; bu yüzden planlayıcı önce tüm eşleşmeleri yeniden denetliyor gibi görünüyor

• Birkaç yıl önce native FTS kullanmak istemiştim ama başaramadım

  • Saniyede binlerce ekleme olan bir tabloda, tam güncellemeler yavaşladı ve transaction zaman aşımına uğradı
  • Index ekledim ama ikinci index tamamlanınca sistemde zaman aşımı oluştu
  • Index'i yeniden kaldırmak zorunda kaldım ve gerçek FTS performansını test etme fırsatı bile bulamadım

• pg_search ve vchord_bm25 eklentileri için RPM/DEB paketleri hazırladım

  • Kendi benchmark'ını yapmak isteyenler için bağlantı veriyorum

• Birçok ekibin doğrudan Elasticsearch veya Meilisearch'e geçtiğini gördüm

  • Doğru kullanıldığında native PG FTS'den ciddi performans alınabilir
  • SQLite + FTS5 + Wasm kullanarak tarayıcıda benzer bir performans elde edilip edilemeyeceğini merak ediyorum

• 10 milyon kayıt oyuncak bir veri kümesi

  • Tüm Wikipedia ya da 2022 öncesi Reddit yorumları gibi büyük metin veri kümeleri benchmark için daha uygun olur

• İlk kez 2008 civarında pg tam metnini kullandım

  • Postgres tam metin aramasının sorunu fazla yavaş olması değil, yeterince esnek olmamasıdır
  • Basit arama eklemek için iyidir ama aramayı ince ayarlamak için yetersiz kalır
  • Solr ve Elasticsearch, karmaşık index ve arama işleme yapılandırmaları kurmanıza izin verir
  • Postgres bu özellikleri benimseyebilir ama şu anda hiçbirini sunmuyor
  • Postgres boşluklara göre bölme yapar; stopword ve stemming'i manuel olarak kullanabilirsiniz
  • Alan ağırlıklarına dayalı arama puanlaması yapılamaz
  • Alternatiflerle karşılaştırıldığında oyuncak bir sistemdir