The RAG system misses “ORA-00060” because the semantic embedding understands deadlocks but not the exact error code. Then pure keyword search misses the runbook because the page says “transaction cycle” instead of “deadlock.” This is the practical reason hybrid search exists: production users ask with both meaning and literals.

Short Version

Before buying a dedicated vector database or search platform, many teams should test hybrid retrieval inside PostgreSQL: full-text search for exact terms and lexical relevance, pgvector for semantic similarity, SQL filters for authorization, and a simple fusion step to combine candidates.

This is not the best architecture for every search product. It is a pragmatic starting point when the corpus already lives in Postgres, the team needs cheap hybrid RAG, and the operating model benefits from one database.

Outgrow it when relevance tuning, faceting, analyzers, typo tolerance, multilingual search, high query volume, or specialized vector features become central enough to justify OpenSearch, Qdrant, Weaviate, or another dedicated retrieval service.

Situation

Most internal RAG systems discover hybrid search through failure. Semantic retrieval works for concepts, lexical search works for identifiers, and production users need both in the same question.

The Problem

Pure vector search is bad at some things users care about:

  • Error codes.
  • Product SKUs.
  • Invoice numbers.
  • Names and acronyms.
  • API symbols.
  • Legal phrases.
  • Exact command output.

Pure keyword search is also bad at some things:

  • Synonyms.
  • Conceptual matches.
  • Paraphrases.
  • Questions that do not use the document’s wording.
  • Long support narratives.

Production RAG usually needs both. The mistake is assuming “hybrid search” requires a new platform immediately. PostgreSQL already has full-text search, ranking functions, indexes, filters, and pgvector. That combination can carry a serious first version if the team accepts its limits.

Core Technical Explanation

A simple chunk table can store both lexical and semantic representations:

CREATE TABLE document_chunks (
  id bigserial PRIMARY KEY,
  tenant_id bigint NOT NULL,
  document_id bigint NOT NULL,
  body text NOT NULL,
  search_vector tsvector NOT NULL,
  embedding vector(1536) NOT NULL,
  deleted_at timestamptz
);

CREATE INDEX document_chunks_fts_idx
  ON document_chunks USING gin (search_vector);

CREATE INDEX document_chunks_embedding_idx
  ON document_chunks USING hnsw (embedding vector_cosine_ops);

The lexical path uses tsvector and a text query. The semantic path uses vector distance. The hybrid path gets candidates from both, then combines them.

Do not assume full-text rank and vector distance are directly comparable. They are different scoring systems. A practical first implementation can use rank-based fusion: retrieve top candidates from each path, convert each result list into ranks, and reward documents that appear near the top of either or both lists.

Reciprocal Rank Fusion (RRF) is the standard formula for this kind of rank combination: for each document, sum 1 / (k + rank) across every ranked list it appears in, where k is a smoothing constant (Cormack, Clarke, and Büttcher’s original 2009 paper used k = 60, and most implementations still default to it). A document that ranks consistently well across both the lexical and vector lists outscores one that ranks first in a single list but is absent from the other — which is usually the behavior a hybrid search product wants. k is a tuning knob, not a fixed constant; validate it against the workload’s actual query set rather than assuming 60 is optimal for every corpus.

In Practice

Keep the architecture explicit:

flowchart TD
    Query[user query] --> Auth[tenant and entitlement filters]
    Auth --> Lex[Postgres full text candidates]
    Auth --> Vec[pgvector semantic candidates]
    Lex --> Fuse[rank fusion]
    Vec --> Fuse
    Fuse --> Rerank[optional rerank]
    Rerank --> Context[LLM context]

The retrieval service should run two bounded candidate queries:

  • Lexical query: top N by full-text rank under the tenant and lifecycle filters.
  • Vector query: top N by vector distance under the same filters.

Then merge by chunk ID or document ID. Keep the fusion code simple enough to inspect during incidents. Record which path contributed each final result.

For many internal RAG systems, this is enough: exact error codes come from full-text search, conceptual matches come from pgvector, and SQL filters enforce permissions.

Where It Breaks

Failure modeProduction symptomMitigation
Scores mixed directlyStrange ranking after combining pathsUse rank-based fusion or calibrated reranking
Filters differ between pathsUnauthorized or inconsistent candidatesShare one filter builder and test negative cases
Lexical query too narrowExact terms work but phrasing failsKeep vector path independent
Vector query too broadSemantic matches ignore identifiersKeep full-text path independent
Candidate count too smallFusion misses good documentsTune top N per path with query set
Postgres becomes search platformRelevance backlog outgrows DBA modelMove to OpenSearch or dedicated retrieval service

Security and Tenancy Notes

The lexical and vector paths must apply the same tenant, entitlement, lifecycle, and deletion filters. A common bug is securing the SQL vector query but letting the full-text query search a broader scope, or the reverse.

Log retrieval metadata, not raw sensitive chunks. For debugging, store chunk IDs, document IDs, score components, rank positions, tenant ID, and query class. Keep prompt text and retrieved body text under the same retention policy as the source documents.

If full-text search uses generated columns or triggers, ensure they update when documents are redacted, deleted, or moved between tenants.

Cost Notes

The cheap part is avoiding another stateful system. The expensive part is making Postgres do too many jobs after the workload grows.

Cost drivers include GIN index size, vector index size, write amplification during document updates, CPU for two candidate queries, and any reranker used after retrieval. Hybrid search can be cheaper than operating OpenSearch or a vector service, but only while query volume and relevance requirements stay within the team’s Postgres comfort zone.

Observability Notes

Track the two paths separately:

  • Lexical candidate count and latency.
  • Vector candidate count and latency.
  • Fusion overlap between lexical and vector results.
  • Final result source mix.
  • Empty-result rate.
  • Queries where exact tokens appear but lexical path contributed nothing.
  • Queries where vector path dominates but users click lexical results.
  • Slow plans for both candidate queries.

Keep a relevance test set with exact-code queries, synonym queries, long natural-language questions, and tenant-filtered negative cases.

Backup, Restore, and DR Notes

Hybrid search doubles the retrieval validation surface. After restore, confirm both GIN and vector indexes exist and are healthy. Confirm generated or stored tsvector values match restored text. Confirm embedding model versions match the active retrieval path.

If embeddings are rebuildable but tsvector is generated from source text, lexical search may recover faster than semantic search. That can be a useful degraded mode: lexical-only retrieval while vector indexes or embeddings rebuild.

When to Outgrow This Approach

Move beyond Postgres hybrid search when the problem becomes a search product rather than a database feature.

Strong signals:

  • Product teams need faceting, typo tolerance, stemming customization, synonyms, and relevance tooling.
  • Search traffic competes with OLTP latency.
  • Query logs require search-specific analytics.
  • Multiple corpora need different analyzers or ranking pipelines.
  • Vector retrieval needs specialized payload filtering, sparse vectors, or multi-stage search.
  • Relevance engineers need dashboards and explain tooling that Postgres does not provide.

At that point, OpenSearch may fit search-heavy workloads. Qdrant or Weaviate may fit retrieval-service workloads. The point is to earn the move with evidence.

Decision Checklist

  • Does the corpus already live in PostgreSQL?
  • Do users search for exact identifiers as well as concepts?
  • Can SQL filters express the authorization model?
  • Can the team maintain both GIN and vector indexes?
  • Is a simple fusion strategy acceptable for the first version?
  • Are candidate counts tuned with a real query set?
  • Can Postgres absorb two retrieval queries per user query?
  • Is there a relevance test set before launch?
  • Is lexical-only mode useful during vector rebuilds?
  • What signal tells the team to move to a dedicated search or vector platform?

What to Do Next

Problem: Teams assume hybrid search requires a new platform on day one, when pure vector search already misses exact identifiers and pure keyword search already misses paraphrases and synonyms.

Solution: Combine PostgreSQL full-text search and pgvector with rank-based fusion (RRF) as a first hybrid architecture — one source of truth, shared SQL filters, lexical recall, and semantic recall — and move to a dedicated search or vector platform only when relevance tuning, scale, or search operations outgrow what Postgres can do.

Proof: A relevance test set with exact-code queries, synonym queries, and tenant-filtered negative cases passes, and both the lexical and vector candidate queries apply identical authorization filters.

Action: This week, confirm the lexical and vector retrieval paths share one filter builder rather than two independently maintained ones, since that’s the most common place authorization drifts between paths.

Sources to Verify