PostgreSQL her yerde arama: tam metin, trigram, kısmi indeksler

İç araçlarda “her yerde arama” gerçekten ne demektir

Bir iç ekranda “her yerde arama” genelde şu anlama gelir: “Aklımdaki tam kaydı hızlıca bulmama yardım et, hatırlamıyorsam bile.” İnsanlar gezinmiyor. Doğrudan bir müşteri, bilet, fatura veya cihaz ile atlamak istiyorlar.

Bu yüzden yavaş arama yavaş sayfadan daha kötü hissettirir. Bir sayfa yüklemesi bir kez olur. Arama ise defalarca, çoğu zaman bir çağrı sırasında veya triage yaparken olur. Sonuçlar 2–3 saniye sürerse kullanıcı sorguyu değiştirir, backspace yapar, başka bir terim dener ve sonuçta daha fazla yük ve daha fazla hayal kırıklığı olur.

Tek bir arama kutusundan kullanıcılar şu davranışları bekler: kısmi eşlemeler ("alex" "Alexander" bulur), küçük yazım hatalarına tolerans ("microsfot" yine "Microsoft" bulur), makul “en iyi sonuç” sıralaması (tam ID'ler veya e-postalar üstte), hafif bir tazelik önceliği ve varsayılan olarak uygulanan filtreler (açık biletler, aktif müşteriler).

Zor kısım, tek bir girdinin genellikle birden fazla niyeti saklamasıdır. Bir temsilci bir bilet numarası yapıştırabilir, isim parçası yazabilir, bir e-posta arayabilir veya telefon numarası girebilir. Her niyet farklı bir strateji, farklı indeksler ve bazen farklı sıralama kuralı ister.

Bu yüzden indekslerle başlamayın. Önce kullanıcılarınızın gerçekten sahip olduğu birkaç arama niyetini listeleyin ve kimlik alanlarını (ID'ler, e-postalar) bulanık alanlardan (isimler, konu başlıkları) ve uzun metinden (notlar) ayırın.

Veriyi ve arama davranışlarını adlandırarak başlayın

Bir indeks seçmeden önce insanların gerçekten ne yazdığını not edin. “PostgreSQL her yerde arama” tek bir özellik gibi görünse de genellikle çok farklı aramaların karışımıdır.

İç araçlar “sert” tanımlayıcıları (sipariş ID'si, bilet numarası, fatura kodu) “yumuşak” metinle (müşteri adı, e-posta, notlar, etiketler) harmanlar. Bu gruplar PostgreSQL'de farklı davranır; hepsini aynı tutmak hızlıca yavaş sorgulara yol açar.

Sonra davranışları ayırın:

• Tam arama: biri TCK-104883 aradığında tek ve kesin bir sonuç bekler.
• Bulanık arama: biri john smth yazdığında isimler arasında hoşgörülü bir eşleme ve kısa bir liste bekler.
• Filtre odaklı arama: biri “Status = Open” ve “Assigned to = Me” seçtiğinde metin kutusu ikincildir.

Erken karar verin: sonuçların sıralanması (en iyi eşleşmeler önce) gerekiyor mu yoksa sadece filtreleme yeterli mi? Notlar ve uzun açıklamalar için sıralama önemlidir. ID'ler ve e-postalar için sıralama genellikle rastgele hisseder ve maliyeti artırır.

Kısa bir kontrol listesi genelde yeterlidir:

• Hangi alanlar her gün aranıyor?
• Hangi girdiler tam (ID, kodlar), hangi girdiler bulanık (isimler), hangileri uzun metin (notlar)?
• Hangi filtreler neredeyse her aramaya uygulanıyor?
• “En iyi eşleşme” sıralaması gerekli mi yoksa herhangi bir eşleşme kabul edilebilir mi?
• Tablo ne kadar büyüyecek: binler, yüzbinler, yoksa milyonlar?

Bu kararları baştan yazarsanız indeks seçimleri sonrasında tahmin olmaktan çıkar.

Temel: tam eşleşmeler ve neden ILIKE sıkıntı yaratır

Kolay kazançları önce kilitleyin. Birçok iç ekran için basit bir B-tree indeksi ID, sipariş numarası, e-posta ve dış referanslar gibi tam eşleşmelerde anında sonuç verir.

Eğer insanlar tam bir değer yapıştırıyorsa, sorgunuz gerçekten tam eşleşme olmalı. WHERE id = ... veya WHERE email = ... normal bir indeksle çok hızlı olabilir. E-posta için unique indeks hem hız hem veri kalitesi getirir.

Sorun, “her yerde arama”nın gizlice ILIKE haline dönüşmesiyle başlar. name ILIKE '%ann%' gibi bir sorgu başında joker karakter taşıdığı için PostgreSQL normal B-tree indeksini kullanamaz. Birçok satırı kontrol eder ve tablo büyüdükçe tahmin edilebilir biçimde yavaşlar.

Önek araması çalışabilir ama desenin başta sabitlenmesi gerekir: name ILIKE 'ann%'. Yine de ayrıntılar önemlidir (kolasyon, büyük/küçük harf işlemleri ve sorguladığınız ifadeyle aynı ifadeye indeksleyip indekslemediğiniz). UI'nız büyük/küçük harf duyarsız olmak zorundaysa yaygın bir yaklaşım lower(name) ile sorgulamak ve aynı ifadeye indeks oluşturmaktır.

Ayrıca “hızlı” için hedefleri kabul etmek yardımcı olur:

• Sıcak önbellekte veritabanı işi için yaklaşık 200 ms veya daha az
• Ağ ve rendering dahil 1 saniyenin altında uçtan uca
• Yaygın aramalar için görünür bir yüklenme durumu olmaması

Böyle hedeflerle, tam ve önek eşleşmelere devam edip edemeyeceğinizi ya da tam metin veya trigram indeks zamanının gelip gelmediğini karar vermek kolaylaşır.

Tam metin arama ne zaman doğru araçtır

Tam metin arama, insanlar doğal dil yazdığında ve sistemin doğru öğeleri bulmasını beklediği durumlarda en iyi seçenektir; sadece tam eşleşme değil. Bilet mesajları, iç notlar, uzun açıklamalar, bilgi tabanı makaleleri ve çağrı kayıtları buna örnektir.

Büyük kazanç sıralamadır. En iyi sonucu üstte göstererek uzun bir liste yerine kullanıcıya hızlıca yardımcı olur. İç araçlarda bu önemlidir: biri saniyeler içinde cevap görmek ister, 50 satırı tararken değil.

Yüksek seviyede tam metin arama üç parçadan oluşur:

• Bir tsvector (aranabilir metin, saklanmış veya üretilmiş)
• Bir tsquery (kullanıcının yazdığı metnin sorguya dönüştürülmüş hali)
• Bir dil yapılandırması (kelimelerin nasıl normalize edildiği)

Dil yapılandırması davranışı görünür kılan kısımdır. PostgreSQL yaygın stop-word’leri ("the" veya "and" gibi) çıkarır ve kök bulma uygular; böylece “pay”, “paid” ve “payment” eşleşebilir. Bu, notlar ve mesajlar için harikadır ama kısa yaygın bir kelime arandığında hiçbir sonuç alınmaması gibi sürprizlere yol açabilir.

Eşanlamlılar (synonyms) başka bir karar noktasıdır. Şirketiniz aynı şey için farklı kelimeler kullanıyorsa (örneğin “refund” vs “chargeback”) bunlar işe yarar, ama zaman içinde dikkat ister. Eşanlamlı listesi kısa ve destek/operasyonun gerçekten kullandığı terimlere dayalı olsun.

Pratik bir örnek: "can’t login after reset" araması, mesajda "cannot log in after password reset" yazan biletleri çekmeli. Farklı yazım olsa bile “ilgili bulma” davranışı tam metin aramanın işi ve genelde ILIKE ile arama motoru yapmaya çalışmaktan daha iyidir.

Trigram indekslerinin avantajı

Trigram indeksleri, kullanıcılar parçalar yazdığında, yazım hatası yaptığında veya sadece “şuna benzer bir şey” hatırladığında güçlü bir tercihtir. Kısa metin alanlarında tam metin aramanın çok katı kaldığı yerde parlıyor: kişi isimleri, şirket isimleri, bilet konuları, SKU’lar, sipariş numaraları ve ürün kodları.

Trigram 3 karakterlik parçadır. PostgreSQL iki dizeyi kaç trigram paylaştıklarına göre karşılaştırır. Bu yüzden "Jon Smth" ile "John Smith" eşleşebilir, ya da "ACM" ile "ACME"; sorgu bir kelimenin ortasını da bulabilir.

Eğer iş “doğru satırı bul” ise ve “belge hakkında bul” değilse, bu genellikle hoşgörülü bir “PostgreSQL her yerde arama” kutusu için en hızlı yoldur.

Tam metin aramayı nerede geçer

Tam metin arama uzun metin ve anlamla sıralama için iyidir, ama kısa alanlarda kısmi dizeleri ve küçük yazım hatalarını doğal olarak ele almaz. Trigram arama bu tür bulanıklık için tasarlanmıştır.

Yazma maliyetini makul tutun

Trigram indeksleri daha büyük olur ve yazmalarda ek yük getirir, bu yüzden seçici olun. İnsanların gerçekten aradığı sütunları indeksleyin:

• İsim, e-posta, şirket, kullanıcı adı
• Kısa tanımlayıcılar (SKU, kod, referans)
• Kısa başlık alanı (uzun not/yorum alanı değil)

Ekip arama kutusuna hangi alanları yazdığını doğru adlandırabiliyorsa trigram indekslemesini genelde küçük ve hızlı tutabilirsiniz.

İnsanların gerçekten kullandığı filtreler için kısmi indeksler

Bir “her yerde arama” kutusu genelde gizli varsayılanlara sahiptir. İnsanlar bir workspace içinde, aktif öğelerde arar ve silinmişleri hariç tutar. Bu filtreler neredeyse her istekte varsa, yaygın durumu yalnızca ona uygun satırları indeksleyerek hızlandırın.

Kısmi indeks, bir WHERE koşuluna sahip normal bir indekstir. PostgreSQL bunu daha küçük tutar çünkü sadece ilgilendiğiniz satırlar için giriş tutar. Bu genelde okunacak sayfa sayısını azaltır ve önbellek vuruş oranlarını iyileştirir.

Yaygın kısmi indeks hedefleri aktif satırlar (status = 'active'), soft delete (deleted_at IS NULL), tenant kapsamı ve “son” pencereleri (örneğin son 90 gün) gibi koşullardır.

Anahtar nokta UI ile eşlemek: ekran her zaman silinmiş satırları gizliyorsa sorgularınız da her zaman deleted_at IS NULL içermeli ve kısmi indeks aynı koşulu kullanmalıdır. is_deleted = false ile deleted_at IS NULL gibi küçük tutarsızlıklar planner'ın indeksi kullanmamasına neden olabilir.

Kısmi indeksler tam metin ve trigram indeksleriyle birlikte de çalışır. Örneğin, yalnızca silinmemiş satırlar için metin aramasını indekslemek indeks boyutunu kontrol altında tutar.

Takas: kısmi indeksler nadir sorgular için daha az yardımcıdır. Birisi ara sıra silinmiş kayıtlar veya tüm workspace'ler üzerinde arama yaparsa PostgreSQL daha yavaş bir plana dönerek tüm tabloyu tarayabilir. Bunu admin-e özgü bir yol ile ele alın veya nadir sorgu sıklaşırsa ayrı bir indeks ekleyin.

Aramayı gizem haline getirmeden yaklaşımları karıştırmak

Çoğu ekip, tek bir arama kutusu farklı niyetleri karşılamak zorunda olduğu için teknikleri karıştırır. Amaç, işlem sırasını net tutmak ki sonuçlar öngörülebilir olsun.

Basit bir öncelik sırası yardımcı olur; bunu ayrı sorgularla veya açık CASE mantığıyla tek bir sorguda uygulayın.

Öngörülebilir bir öncelik merdiveni

İlk sıkı eşleşme, sonra giderek gevşekleşin:

• Önce tam eşleşme (ID, e-posta, bilet numarası, SKU) — B-tree indekslerle
• Sonra önek eşleşme gerektiği yerde
• Ardından trigram eşleşme (isimler ve başlıklar için yazım hataları ve parçalar)
• En son uzun notlar, açıklamalar ve serbest içerik için tam metin arama

Aynı merdivene sadık kalırsanız kullanıcılar arama kutusunun ne anlama geldiğini öğrenir. “12345” bir bileti anında bulurken “refund policy”nin uzun metin araması yapmasının sistemin bozuk olduğu düşüncesini azaltır.

Önce filtre, sonra bulanıklaştırma

Bulanık arama tüm tabloyu düşünecek şekilde pahalılaşır. Aday kümesini, kullanıcıların gerçekten kullandığı filtrelerle (status, assigned team, tarih aralığı, hesap) daraltın, sonra trigram veya tam metin çalıştırın. Hızlı trigram indeksi bile milyonlarca satırı puanlamak zorunda kalırsa yavaş hissedilebilir.

Tek cümlelik bir kural yazmak, teknik olmayan ekip üyeleri için de anlaşılır olur: “Önce bilet numarasını tam eşleştiririz, sonra müşteri ismini yazım hatalarına toleranslı eşleştiririz, sonra notlarda arama yaparız.” Bu ortak tanım, neden bir satırın çıktığını açıklar.

Adım adım: bir yaklaşım seçin ve güvenle uygulayın

Hızlı bir “her yerde arama” kutusu küçük kararların bir toplamıdır. Önce bunları yazın, veritabanı işi basitleşir.

1. Tanımlayın: Tek bir kutu mu yoksa kutu + filtreler (status, owner, tarih aralığı) mı?
2. Alan başına eşleşme türünü seçin: ID'ler ve kodlar tam eşleşme ister. İsimler ve e-postalar genelde önek veya bulanık eşleşme ister. Uzun not ve açıklamalar doğal dil araması için tam metin uygundur.
3. Doğru indeksleri ekleyin ve kullanıldıklarını doğrulayın: indeksi oluşturun, sonra gerçek sorguyu EXPLAIN (ANALYZE, BUFFERS) ile kontrol edin.
4. Niyete uygun sıralama veya filtreleme ekleyin: kullanıcı “invoice 1042” yazdığında tam eşleşmeler yükselmeli. Yazımı hatalı bir isim girildiğinde similarity (benzerlik) sıralaması öne çıkmalı.
5. Gerçek sorgularla test edin: yazım hataları, çok kısa terimler ("al" gibi), uzun yapıştırılmış metin, boş giriş ve “sadece filtre” modu deneyin.

Güvenli gönderim için her seferinde bir şey değiştirin ve rollback'i kolay tutun. Büyük tablolarda yeni indeksler için CREATE INDEX CONCURRENTLY tercih edin ki yazmalar bloke edilmesin. Mümkünse özellik bayrağı arkasında yayınlayın ve gecikmeyi öncesi/sonrası karşılaştırın.

Pratik bir desen: önce tam eşleşme (hızlı ve kesin), sonra trigram (insani alanlar için yazım hatalarına tolerans), sonra uzun metin için tam metin arama.

Gerçekçi bir örnek: destek yönetici panelinde tek bir arama kutusu

Düşünün ki bir destek yönetici panelinde tek bir arama kutusu var; ekip bunun müşterileri, biletleri ve notları bulmasını bekliyor. Bu, “tek giriş, çok anlam” problemidir.

İlk kazanım niyeti görünür kılmaktır. Eğer sorgu bir e-posta veya telefon gibi görünüyorsa müşteri aramasına yönlendirin. Eğer bilet ID'si gibi görünüyorsa (ör. "TKT-10482"), doğrudan biletlere yönlendirin. Diğerleri bilet konusu ve notlar arasında metin aramasına düşsün.

Müşteri aramalarında trigram indeksleri genelde en iyi hissedendir. İsimler ve şirket dizeleri dağınıktır; insanlar parça yazar. Trigram indeksleri “jon smi” veya “acm” gibi aramaları hızlı ve hoşgörülü yapar.

Bilet notları için tam metin arama kullanın. Notlar gerçek cümlelerdir ve genelde alaka isteyen sonuçlar istersiniz; sadece “içerir” değil. Aynı anahtar kelimeyi içeren onlarca bileti sıralarken ranking işe yarar.

Filtreler birçok ekipin beklediğinden daha fazla önemlidir. Eğer temsilciler “open tickets” içinde yaşıyorsa kısmi indeksle yalnızca açık satırları kapsayın. Aynı şekilde “aktif müşteriler” için de yapın. Bu indeksleri küçük tutar ve ortak yolu hızlı yapar.

Çok kısa sorgular için kurallar koyun, aksi halde veritabanı gürültülü işler yapar:

• 1–2 karakter: son açık biletleri ve son güncellenen müşterileri göster
• 3+ karakter: müşteri alanlarında trigram ve bilet metinlerinde tam metin çalıştır
• Belirgin niyet yoksa: karışık bir liste gösterin ama her grubu sınırlayın (ör. 10 müşteri ve 10 bilet)

Aramayı yavaş veya kafa karıştırıcı yapan yaygın hatalar

Çoğu “arama neden yavaş?” hatası kendi kendine yapılır. Amaç her şeyi indekslemek değil, insanların gerçekten yaptığı işleri indekslemektir.

Yaygın tuzak birçok sütuna “her ihtimale karşı” indeks eklemektir. Okumalar iyileşebilir ama her insert/update şimdi ekstra iş yapar. Ticket, order, user gibi kayıtların gün boyunca değiştiği iç araçlarda yazma hızı önemlidir.

Başka bir hata, gerçekte isimler veya e-postalar için yazım-toleranslı bir arama gerekirken tam metin aramasına güvenmektir. Tam metin belgeler ve açıklamalar için iyidir; “Jon” vs “John” veya “gmail.con” vs “gmail.com” gibi hataları düzeltmez. Bu genelde trigram alanıdır.

Filtreler de sessizce planınızı bozabilir. Çoğu arama sabit bir filtreyle oluyorsa (status = 'open' veya org_id = 42), en iyi indeks o koşulu karşılayan kısmi indeks olabilir. Bunu unutursanız PostgreSQL beklediğinizden çok daha fazla satırı tarayabilir.

Yineleyen hatalar:

• Yazma maliyetini ölçmeden çok fazla indeks eklemek
• Tam metin aramasının yazım-toleranslı autocomplete gibi davranmasını beklemek
• Yaygın filtrelerin hangi indeksi kullanılacağını değiştirdiğini gözardı etmek
• Küçük, temiz verilerle test edip gerçek terim frekansını yok saymak
• Desteklenmeyen bir sütuna göre sıralama yapmak ve yavaş bir sort zorlamak

Örnek: bir destek ekranı biletleri konu, müşteri adı ve bilet numarasıyla arıyor, sonra latest_activity_at ile sıralıyor. Eğer filtrelenmiş küme (ör. açık biletler) için latest_activity_at indeksi yoksa sıralama, arama indeksinden aldığınız hızı silebilir.

Yayınlamadan önce hızlı kontroller

“Her yerde arama” özelliğini tamam demeden önce hangi davranışı vaat ettiğinize somut olun.

• İnsanlar tam bir tanımlayıcı ile kayıt mı bulmaya çalışıyor (bilet numarası, e-posta)?
• Yazım hatalarına tolerans bekliyorlar mı?
• Uzun notlardan veya açıklamalardan sıralı sonuç mı bekliyorlar?

Modları karıştırıyorsanız, hangisinin üstün olduğuna karar verin.

Ardından aramaların %80'ini yönlendiren 2–3 alanı belirleyin. Eğer aramaların %80'i e-posta, isim ve bilet ID'siyle oluyorsa önce bunları optimize edin, gerisini ikincil tutun.

Kısa bir yayın öncesi kontrol listesi:

• Alan başına ana eşleşme modu onaylandı mı (tam lookup, bulanık veya sıralı metin)?
• Kullanıcıların günlük uyguladığı filtreleri listelediniz mi ve indeksler bu kombinasyonlarla eşleşiyor mu?
• Çok kısa ve boş sorgular nasıl ele alınacak (ör. bulanık arama için 2–3 karakter gereksinimi; boşta “recent” gösterme)?
• Sıralama açıklanabilir mi: en yeni, en iyi metin eşleşmesi veya basit birleşik kural?

Son olarak, doğruluk yerine gerçekçi veri büyüklüğü ve zamanlama ile test edin. 1.000 satırla anında görünen bir sorgu 1.000.000 satırda yavaş olabilir.

Sonraki adımlar: planı hızlı bir arama ekranına dönüştürün

Arama kutusu ekip arama kurallarında anlaşma olduğu sürece hızlı kalır. Eşleşmenin ne demek olduğunu basit bir dille yazın (tam, önek, yazım-toleranslı), hangi alanların aranacağını ve filtrelerin sonuç kümesini nasıl değiştireceğini belirtin.

Gerçek aramalar içeren küçük bir test seti tutun ve bunu regresyon takımı gibi kullanın. 10–20 sorgu genelde yeterlidir: birkaç yaygın isim, birkaç kısmi e-posta, bir yazım hatası, uzun bir not parçası ve bir “sonuç yok” durumu. Değişikliklerden önce ve sonra çalıştırarak performansın bozulmadığından emin olun.

Eğer iç araçları AppMaster (appmaster.io) ile kuruyorsanız, bu arama kurallarını veri modeli ve iş mantığı ile birlikte tanımlamak UI davranışı ve veritabanı seçimlerinin gereksinimler değiştikçe sapmamasına yardımcı olur.