{"id":150,"date":"2026-11-03T11:55:00","date_gmt":"2026-11-03T11:55:00","guid":{"rendered":"https:\/\/www.akifsen.com.tr\/blog\/?p=150"},"modified":"2026-11-03T11:55:00","modified_gmt":"2026-11-03T11:55:00","slug":"full-text-search-postgresql-like","status":"publish","type":"post","link":"https:\/\/www.akifsen.com.tr\/blog\/2026\/11\/03\/full-text-search-postgresql-like\/","title":{"rendered":"Tam Metin Arama \u0130\u00e7in LIKE &#8216;%kelime%&#8217; Yeterli De\u011fil"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Bir \u00fcr\u00fcn arama \u00f6zelli\u011fi \u015f\u00f6yle ba\u015flar:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM products\nWHERE name ILIKE '%' || :q || '%';<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Birka\u00e7 bin \u00fcr\u00fcnle h\u0131zl\u0131 \u00e7al\u0131\u015f\u0131r.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u00dcr\u00fcn say\u0131s\u0131 y\u00fcz binleri ge\u00e7ince arama yava\u015flar. Kullan\u0131c\u0131lar &#8220;kitaplar&#8221; yazd\u0131\u011f\u0131nda &#8220;kitap&#8221; ge\u00e7en \u00fcr\u00fcnleri bulamaz. &#8220;K\u0131rm\u0131z\u0131 elbise&#8221; aramas\u0131, ad\u0131nda &#8220;elbise k\u0131rm\u0131z\u0131&#8221; ge\u00e7en \u00fcr\u00fcn\u00fc ka\u00e7\u0131r\u0131r.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">LIKE&#8217;\u0131n s\u0131n\u0131rlar\u0131<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Ba\u015f\u0131nda <code>%<\/code> olan bir <code>LIKE<\/code> sorgusu normal bir B-tree index kullanamaz. Veritaban\u0131 her sat\u0131r\u0131 tek tek kontrol etmek zorundad\u0131r.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Kelime s\u0131ras\u0131n\u0131 ve kelime k\u00f6klerini anlamaz. &#8220;kitaplar&#8221;, &#8220;kitab\u0131&#8221;, &#8220;kitap\u00e7\u0131&#8221; ayr\u0131 kelimelerdir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Sonu\u00e7lar\u0131 alaka d\u00fczeyine g\u00f6re s\u0131ralayamaz. Ba\u015fl\u0131\u011f\u0131nda aranan kelime ge\u00e7en \u00fcr\u00fcn ile a\u00e7\u0131klaman\u0131n sonunda bir kez ge\u00e7en \u00fcr\u00fcn ayn\u0131 \u00f6nemdedir.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Trigram<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">K\u0131smi e\u015fle\u015fme ve yaz\u0131m hatalar\u0131na tolerans gerekiyorsa PostgreSQL&#8217;in <code>pg_trgm<\/code> eklentisi iyi bir ba\u015flang\u0131\u00e7:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE EXTENSION pg_trgm;\nCREATE INDEX idx_products_name_trgm\nON products USING GIN (name gin_trgm_ops);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Bu index ile <code>ILIKE '%kitap%'<\/code> sorgular\u0131 index kullanabilir hale gelir. Ayr\u0131ca benzerlik aramas\u0131 da yap\u0131labilir:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name, similarity(name, 'kitpa') AS score\nFROM products\nWHERE name % 'kitpa'\nORDER BY score DESC;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">&#8220;kitpa&#8221; yazan kullan\u0131c\u0131 &#8220;kitap&#8221; sonu\u00e7lar\u0131n\u0131 g\u00f6r\u00fcr. \u00dcr\u00fcn ad\u0131, ki\u015fi ad\u0131 ve \u015fehir ad\u0131 gibi k\u0131sa metinlerde olduk\u00e7a etkili.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Full-text search<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Uzun metinlerde ve kelime bazl\u0131 aramada PostgreSQL&#8217;in yerle\u015fik full-text search \u00f6zelli\u011fi daha uygun:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE articles ADD COLUMN tsv tsvector\nGENERATED ALWAYS AS (\n  setweight(to_tsvector('turkish', coalesce(title, '')), 'A') ||\n  setweight(to_tsvector('turkish', coalesce(body, '')), 'B')\n) STORED;\n\nCREATE INDEX idx_articles_tsv ON articles USING GIN (tsv);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Metin kelimelere ayr\u0131l\u0131r, gereksiz kelimeler \u00e7\u0131kar\u0131l\u0131r ve kelimeler k\u00f6klerine indirgenir. Ba\u015fl\u0131\u011fa daha y\u00fcksek a\u011f\u0131rl\u0131k verilir.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT title, ts_rank(tsv, q) AS rank\nFROM articles, websearch_to_tsquery('turkish', :q) q\nWHERE tsv @@ q\nORDER BY rank DESC\nLIMIT 20;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>websearch_to_tsquery<\/code>, kullan\u0131c\u0131n\u0131n yazd\u0131\u011f\u0131 metni t\u0131rnak ve eksi i\u015fareti gibi al\u0131\u015f\u0131k oldu\u011fu arama s\u00f6zdizimiyle yorumlar.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL&#8217;in T\u00fcrk\u00e7e dil deste\u011fi Snowball stemmer&#8217;a dayan\u0131yor. M\u00fckemmel de\u011fil, T\u00fcrk\u00e7e&#8217;nin eklemeli yap\u0131s\u0131nda baz\u0131 kelimeleri do\u011fru k\u00f6k\u00fcne indiremiyor. Ama <code>LIKE<\/code> ile kar\u015f\u0131la\u015ft\u0131r\u0131ld\u0131\u011f\u0131nda ciddi bir iyile\u015fme.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Ayr\u0131 bir arama motoru ne zaman?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Elasticsearch veya OpenSearch gibi bir arama motoru \u015fu durumlarda gerekli hale gelir:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Faceted search, yani kategori ve \u00f6zelliklere g\u00f6re sonu\u00e7 say\u0131lar\u0131<\/li>\n\n\n<li>Geli\u015fmi\u015f dil analizi, e\u015f anlaml\u0131lar, otomatik tamamlama<\/li>\n\n\n<li>\u00c7ok b\u00fcy\u00fck veri hacmi ve y\u00fcksek arama trafi\u011fi<\/li>\n\n\n<li>Alaka d\u00fczeyi \u00fczerinde ince ayar<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Ama ayr\u0131 bir arama motoru, ayr\u0131 bir altyap\u0131 ve veritaban\u0131 ile senkronizasyon demektir. Veri iki yerde tutulur ve aralar\u0131ndaki tutarl\u0131l\u0131k sa\u011flanmal\u0131d\u0131r. Bir \u00fcr\u00fcn silindi\u011finde arama sonu\u00e7lar\u0131ndan da kalkmal\u0131d\u0131r.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Benim \u00f6nerim, PostgreSQL&#8217;in kendi ara\u00e7lar\u0131yla ba\u015flamak. Trigram ve full-text search bir\u00e7ok uygulama i\u00e7in yeterli. Ger\u00e7ekten ihtiya\u00e7 ortaya \u00e7\u0131kt\u0131\u011f\u0131nda arama motoruna ge\u00e7mek, ba\u015ftan karma\u015f\u0131k bir altyap\u0131 kurmaktan daha az maliyetli.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Arama, kullan\u0131c\u0131lar\u0131n bir uygulamay\u0131 en do\u011frudan de\u011ferlendirdi\u011fi \u00f6zelliklerden biri.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u0130stedi\u011fi \u015feyi bulamayan kullan\u0131c\u0131, onun orada olmad\u0131\u011f\u0131n\u0131 d\u00fc\u015f\u00fcn\u00fcr.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>LIKE ile arama k\u00fc\u00e7\u00fck tablolarda \u00e7al\u0131\u015f\u0131r ama index kullanamaz, T\u00fcrk\u00e7e ekleri anlamaz ve sonu\u00e7lar\u0131 s\u0131ralayamaz. PostgreSQL full-text search, trigram ve ayr\u0131 arama motoru se\u00e7enekleri.<\/p>\n","protected":false},"author":1,"featured_media":149,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-150","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-veritabani"],"_links":{"self":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts\/150","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/comments?post=150"}],"version-history":[{"count":1,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts\/150\/revisions"}],"predecessor-version":[{"id":216,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts\/150\/revisions\/216"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/media\/149"}],"wp:attachment":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/media?parent=150"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/categories?post=150"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/tags?post=150"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}