PostgreSQL Veritabanında Full Text Search

Dokümanın hazırlanmasındaki katkılarından dolayı Mustafa Tek arkadaşımıza teşekkür ederiz.

Öncelikle sorgulama yapmak için LOCATION isimli basit bir tablo oluşturuyorum ve içine kayıtlar giriyorum. Aşağıda görüldüğü gibi “ID”, “CITY” ve “COUNTRY” isimlerinde 3 adet sütunu mevcut.

Ben aramayı “CITY” ve “COUNTRY” sütunlarında yapacağım. Aslında herhangi ek bir sütun eklemeden ve ek bir iş yapmadan aşağıdaki örnek sorgularla doğrudan tüm metin araması yapılabilir.

Aşağıdaki sorgu ile “CITY” ve “COUNTRY” sütunlarını kapsayan bir arama yapıyorum ve içlerinde ‘Ankara’ kelimesini arıyorum.

select * from "LOCATION" where to_tsvector('simple',"CITY" || ' ' || "COUNTRY") @@ to_tsquery('simple','ANKARA')

Yukarıdaki sonuçta görüldüğü gibi sadece içinde ‘Ankara’ geçen kayıt geliyor. Sorgudaki arama kriterini ‘Turkey’ olarak değiştirip tekrar çalıştırıyorum.

select * from "LOCATION" where to_tsvector('simple',"CITY" || ' ' || "COUNTRY") @@ to_tsquery('simple','Turkey')

Yukarıdaki sonuçta görüldüğü gibi sadece içinde ‘Turkey’ geçen kayıtlar geliyor. Şimdi kriterimi 2 kelimeye çıkarıyorum ve ‘Barcelona&Spain’ yapıyorum.

select * from "LOCATION" where to_tsvector('simple',"CITY" || ' ' || "COUNTRY") @@ to_tsquery('simple','BARCELONA&spain')

Yukarıda görüldüğü gibi içinde ‘Barcelona’ ve ‘Spain’ geçen kayıt geldi. Yani burada bir AND sorgusu kullandım. AND sorgusu için ‘&’ karakterini kullanıyorum. Şimdi bunu OR sorgusuna çevirelim.

select * from "LOCATION" where to_tsvector('simple',"CITY" || ' ' || "COUNTRY") @@ to_tsquery('simple','barcelona|turkey')

Yukarıda görüldüğü gibi içinde ‘Barcelona’ veya ‘Turkey’ geçen kayıtlar geldi. Yani burada da bir OR sorgusu kullandım. OR sorgusu için ‘|’ karakterini kullanıyorum.

Eğer yukarıdaki sorgulara dikkat ederseniz her sorguda küçük ve büyük harfleri değiştirerek kullandım. Örnek olarak bi yerde ‘barcelona’ yazarken başka yerde ‘BARCELONA’ yazdım. Bu şekilde yazmamın sebebi burada kullandığımız tüm metin aramasının büyük küçük harf duyarlı olmadığını göstermektir.

Buraya kadar olan kısımda doğrudan sütunlarımızı kullanarak tüm metin araması yaptık. Eğer kayıt sayısız az ise bu şekilde sorgular atabilirsiniz. Fakat kayıt sayınız arttıça bu sorgularda performans sıkıntıları yaşarsınız. Bu sıkıntıları yaşamamak için bir sonraki bölümde TSVECTOR tipinde bir alan oluşturup sorguları o sütun üzerinden yapacağız.

Sorguları TSVECTOR Tipinde Bir Sütundan Yönetmek

Bu bölümde tüm metin aramalarımızda performans sıkıntısı yaşamamak için TSVECTOR tipinde yeni bir alan oluşturacağız ve aramaları oradan yapacağız.Bu sütunun tablodaki oluşabilecek değişikliklere göre güncel kalması için de trigger yazacağız. Böylece tabloda olabilecek Insert veya Update işlemlerinde yeni alanımız güncel kalacak.

İlk olarak tablomuza yeni alanımızı ekleyelim. Alanımızın adı “FULLTEXT” olsun. Aşağıdaki komutla alanı ekliyorum.

ALTER TABLE "LOCATION" ADD COLUMN "FULLTEXT" TSVECTOR

Yukarıda görüldüğü gibi yeni alanımız tablomuza eklendi fakat içi şu an boş. Aşağıdaki komutla “CITY” ve “COUNTRY” alanlarını TSVECTOR tipinde “FULLTEXT” alanına yazıyorum.

UPDATE "LOCATION" SET "FULLTEXT" = to_tsvector('simple',"CITY" || ' ' || "COUNTRY")

Yukarıda görüldüğü gibi “FULLTEXT” alanımızın içi artık doldu. Bundan sonra aramalarımızı bu alandan yapabiliriz. Aramalara geçmeden önce “FULLTEXT” alanımıza aşağıdaki komutla indeks ekliyoruz.

CREATE INDEX "GIN_LOCATION" ON "LOCATION" USING GIN("FULLTEXT")

İndeks ekleme işlemini de tamamladığımıza göre yeni alanımıza örnek sorgularımızı atabiliriz.

select * from "LOCATION" where "FULLTEXT" @@ to_tsquery('simple','Paris|Greece')

Yukarıda görüldüğü gibi içinde ‘Paris’ veya ‘Greece’ geçen kayıtlar geldi.

select * from "LOCATION" where "FULLTEXT" @@ to_tsquery('simple','ROMA&italy')

Bu sorguda da görüldüğü gibi içinde ‘Roma’ ve ‘Italy’ geçen kayıt geldi. Yeni eklediğimiz alandan sorgular yapabildiğimize göre son iş olarak bu alanın güncel kalmasını sağlamamız gerekiyor. Bunun için Insert ve Update işlemleri için tablomuza trigger ekleyeceğiz.

Aşağıdaki komutlarda önce triggerların çağıracağı fonksiyonu yazıyorum daha sonra da tabloma 2 adet trigger ekliyorum.

— Fonksiyonu Ekliyorum
CREATE OR REPLACE FUNCTION
func_location_fulltext() RETURNS trigger AS $location$
BEGIN
UPDATE “LOCATION” SET “FULLTEXT” = to_tsvector(‘simple’,”CITY” || ‘ ‘ || “COUNTRY”) WHERE “ID” = new.”ID”;
RETURN new;
END;
$location$ LANGUAGE plpgsql;
— Insert için Trigger Ekliyorum
CREATE TRIGGER trg_location_insert_fulltext
AFTER INSERT ON “LOCATION”
FOR EACH ROW EXECUTE PROCEDURE func_location_fulltext();
— Update için Trigger Ekliyorum
CREATE TRIGGER trg_location_update_fulltext
AFTER UPDATE of “CITY”,”COUNTRY” ON “LOCATION”
FOR EACH ROW EXECUTE PROCEDURE func_location_fulltext();

Görüldüğü gibi eklediğimiz kayıt için Trigger “FULLTEXT” alanını doldurdu. Yani artık Insert işlemlerinde güncelliğimizi koruyoruz. Şimdi bir de UPDATE işlemi gerçekleştirelim.

UPDATE "LOCATION" set "CITY" = 'G.Antep' where "ID" = 11

Update işlemi sonrasında da “FULLTEXT” alanımızın güncellendiğini görüyoruz. Yani artık Update işlemlerinde de güncelliğimizi koruyabiliyoruz. Buraya kadar yaptığımız işlemlerle artık tüm metin aramamızı yapar ve yönetir hale geldik.

Bir sonraki bölümde özellikle aramanızı Otomatik Tamamlama (Autocomplete) bir alanda kullanmak isterseniz kullanabileceğiniz kelimenin ilk harflerini girerek arama konusunda birkaç örnek vereceğim.

Kelimelerin İlk Harflerini Girerek Arama

Bu bölümde özellikle Autocomplete alanlarda işinize yarayacak bir özellik ile ilgili örnekler vereceğim. Mesela kullanıcı Autocomplete bir alana ‘Ank’ yazdığı anda ‘Ank’ ile başlayan kelimelerin geçtiği kayıtları bulmak isteyebilirsiniz. Bu durumda kelimelerin sonuna ‘:’ ekleyeceğiz.

Şimdi bununla ilgili birkaç örnek yapalım.

select * from "LOCATION" where "FULLTEXT" @@ to_tsquery('simple','tur:')

Yukarıdaki sorguda ‘tur’ ile başlayan kayıtları aradık ve sonuç olarak olarak ‘tur’ ile başlayan kelime içeren kayıtların geldiğini görüyoruz.

Başka bir örnek olarak;

select * from "LOCATION" where "FULLTEXT" @@ to_tsquery('simple','r:*')

Yukarıdaki sorguda ise ‘r’ harfi ile başlayan kelime içeren kayıtları aradım ve sonuç olarak russia, rostock ve roma olan kayıtların geldiğini görüyoruz. Bu şekilde kurduğumuz yapıyı artık bir Autocomplete alanda da rahatca kullanabiliriz.

Sonuç

Avantajları:

• to_tsvector kullanarak aramayı tek satır olarak hızlı bir şekilde tamamlayabiliriz.
• autocomplete alanlarda arama yapmak için kullanabiliriz, ve arama sonuçlarını çok hızlı elde edebiliyoruz.

Dezavantajları:

• Elasticsearch’ün yetenekli olduğu aggregation, tagging vb işlemler yapılamıyor.