{"id":8,"date":"2025-09-23T10:00:00","date_gmt":"2025-09-23T07:00:00","guid":{"rendered":"https:\/\/www.hasangultekin.com\/2025\/09\/23\/hybrid-search-postgresql-keywords\/"},"modified":"2025-09-23T10:00:00","modified_gmt":"2025-09-23T07:00:00","slug":"hybrid-search-postgresql-keywords","status":"publish","type":"post","link":"https:\/\/www.hasangultekin.com\/tr\/2025\/09\/23\/hybrid-search-postgresql-keywords\/","title":{"rendered":"PostgreSQL ile Hibrit Arama: Anahtar Kelimelerin H\u00e2l\u00e2 \u00d6nemli Oldu\u011fu Durumlar"},"content":{"rendered":"

Semantic search is helpful when a customer describes a problem in different words from the documentation. It can be less decisive when the important part of the question is an exact product code, a short error message, or a model number.<\/p>\n

A support system needs both kinds of evidence. I like PostgreSQL as a starting point because the application’s relational data and retrieval metadata can stay close together while the retrieval design is still evolving.<\/p>\n

Let different retrieval paths contribute candidates<\/h2>\n

A dense vector search asks which passages are close in embedding space. A lexical search asks which passages match the query’s terms under its text processing rules. Neither question is identical to \u201cwhich passage proves the answer?\u201d<\/p>\n

For a query containing a known SKU, I would also consider a dedicated exact-match path. Text search tokenization can split or normalize identifiers, so exact product lookup should not depend entirely on a natural-language search configuration.<\/p>\n

The pgvector documentation<\/a> describes combining vector retrieval with PostgreSQL full-text search and suggests rank fusion or a cross-encoder for combining results. I use those as candidate techniques to evaluate, rather than assuming that adding more stages must improve the result.<\/p>\n

Apply ownership and availability constraints early<\/h2>\n

Tenant, document visibility, publication state, and applicable market belong in candidate selection. Retrieving across every tenant and filtering afterward is both an authorization problem and a ranking problem.<\/p>\n

The same issue appears with approximate vector indexes. If restrictive filters leave too few eligible results, I investigate the query plan and recall behavior for the installed extension version. Raising a global candidate count is not a substitute for measuring the filtered workload.<\/p>\n

I keep an exact-search baseline on a manageable evaluation dataset. It helps distinguish losses introduced by approximate indexing from failures caused by embeddings, chunking, or the corpus itself.<\/p>\n

Combine rankings without pretending scores are equivalent<\/h2>\n

A lexical relevance score and a vector similarity score usually have different meanings and distributions. Adding their raw values with arbitrary weights can make the result sensitive to changes in one component.<\/p>\n

Rank-based fusion offers a useful alternative. The following pseudocode illustrates the idea; it is not a tuned production configuration:<\/p>\n

for each ranked result list:\n    for each candidate at rank r, starting at 1:\n        score[candidate.id] += 1 \/ (rank_constant + r)\n\nreturn candidates ordered by combined score<\/code><\/pre>\n
The rank constant controls how strongly the top positions dominate. I choose it through evaluation, along with the candidate pool sizes and any later reranking stage. Those parameters should be versioned with the retrieval configuration.<\/p>\n
Preserve source identity through the pipeline<\/h2>\n
A passage should retain its document ID, version, section, language, and access metadata. After fusion and reranking, the generator still needs to know where the evidence came from and which source version it represents.<\/p>\n
I also limit repeated passages from one document when they crowd out useful alternatives. That is a diversity decision with tradeoffs: some questions genuinely need several neighboring passages. A rigid one-passage rule can remove necessary context.<\/p>\n
PostgreSQL’s full-text search overview<\/a> explains how documents and queries are normalized for matching. The choice of language configuration deserves attention in a multilingual corpus, especially when product identifiers must remain intact.<\/p>\n
Keep the pipeline only as complex as the evidence justifies<\/h2>\n
I compare lexical retrieval, dense retrieval, their combination, and any reranking stage on the same questions. The review includes answerable cases, unsupported questions, exact identifiers, and different languages.<\/p>\n
A hybrid design earns its extra latency and maintenance cost when it reliably retrieves evidence the simpler alternatives miss. The useful output is a better-supported answer, not a more elaborate retrieval diagram.<\/p>\n
Updated 25 September 2026.<\/small><\/p>\n","protected":false},"excerpt":{"rendered":"
Tan\u0131mlay\u0131c\u0131lar\u0131, eri\u015fim filtrelerini ve s\u0131ralama kalitesi i\u00e7in \u00f6l\u00e7\u00fclebilir bir referans noktas\u0131n\u0131 koruyarak, anlamsal eri\u015fim ile s\u00f6zc\u00fcksel aramay\u0131 birle\u015ftirin.<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[151],"tags":[168,197,181],"class_list":["post-8","post","type-post","status-publish","format-standard","hentry","category-ai-engineering","tag-hybrid-search","tag-pgvector","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/www.hasangultekin.com\/tr\/wp-json\/wp\/v2\/posts\/8","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.hasangultekin.com\/tr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hasangultekin.com\/tr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hasangultekin.com\/tr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hasangultekin.com\/tr\/wp-json\/wp\/v2\/comments?post=8"}],"version-history":[{"count":0,"href":"https:\/\/www.hasangultekin.com\/tr\/wp-json\/wp\/v2\/posts\/8\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.hasangultekin.com\/tr\/wp-json\/wp\/v2\/media?parent=8"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hasangultekin.com\/tr\/wp-json\/wp\/v2\/categories?post=8"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hasangultekin.com\/tr\/wp-json\/wp\/v2\/tags?post=8"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}