Home / Hybrid search / Hybrid Search in SQL: Combining Full-Text and Vector Queries

Hybrid Search in SQL: Combining Full-Text and Vector Queries

Hybrid search in a SQL database runs a full-text keyword search and a vector similarity search over the same table, then merges the two ranked lists into one result, usually with Reciprocal Rank Fusion. The keyword side catches exact terms such as part numbers, while the vector side catches meaning and synonyms. In PostgreSQL this means combining full-text search with pgvector in two common table expressions and fusing the ranks in a final query. SQL Server, AlloyDB, and Oracle now offer their own versions. Purpose-built engines such as Weaviate do the same work in one call with a tunable weight. Two traps matter most in SQL: PostgreSQL’s built-in text ranking is not BM25, and filters on an approximate vector index can silently return too few rows.

What is hybrid search in a SQL database?

Hybrid search in a SQL database is a query that combines lexical full-text search and vector semantic search inside the database engine, returning a single ranked list. It lets a relational database serve search the way a dedicated search engine would.

The two halves use different indexes. Full-text search uses an inverted index, such as a GIN index on a tsvector column in PostgreSQL. Vector search uses an approximate nearest neighbor index, such as HNSW from pgvector or ScaNN in AlloyDB. Each half produces its own ranking, and a fusion step combines them.

The idea matches hybrid search in any system. What changes in SQL is where the work happens. Instead of calling a search API, you write a query that joins the two result sets, applies relational filters, and sorts by a fused score. The rows can be joined to orders, users, or prices in the same transaction.

That last point is the main appeal. Many teams already store their product catalog, tickets, or documents in a relational database. Adding search there avoids copying data into a second system. Whether that is the right choice depends on scale and workload. The reasons to combine the two searches come first.

Why does hybrid search matter in SQL databases?

Hybrid search matters in SQL databases because keyword search and vector search fail on different queries, and combining them covers both kinds of failure. A SQL table often holds exactly the mix of codes and prose that needs both.

Keyword search excels at exact tokens. A query for “SKU 44-1207” or an error code should match that string precisely. Vector search can blur such tokens, because embeddings capture meaning rather than spelling. Vector search, in turn, finds “laptop sleeve” when the row says “notebook case,” which keyword search misses.

Weaviate’s documentation gives a small example. A hybrid search for black canine ranks the black bear and the small black cat highly through the keyword match. It also lifts the brown dog, because the vector side links canine with dog. Neither search alone returns all three.

Hybrid search is also a common base for RAG. A language model answers better when retrieval finds both the exact policy number and the passages that explain it, as hybrid search for RAG explains. In SQL, the combined query follows a standard shape.

How does a hybrid search query work in SQL?

A hybrid search query in SQL works by running the keyword search and the vector search as two separate subqueries, ranking each, then joining them and summing a fusion score. Common table expressions keep the steps readable.

1. Keyword subquery: match the query against the full-text index, order by text rank, and keep the top N rows.
2. Vector subquery: order rows by distance to the query embedding, and keep the top N.
3. Rank: number each list with ROW_NUMBER() or RANK().
4. Fuse: full outer join the lists on the row ID and compute a combined score.
5. Return: sort by the combined score, apply a limit, and join any extra columns.

The fusion step is usually Reciprocal Rank Fusion, introduced by Cormack, Clarke, and Buettcher at SIGIR 2009. Each row scores 1 divided by (k plus its rank) in each list, and the scores are summed. The constant k is commonly 60. RRF needs only ranks, so it sidesteps the problem that text scores and vector distances use different scales. Reciprocal rank fusion covers the math.

A weighted sum of normalized scores is the main alternative. It keeps more information from each search but needs care with normalization, as score normalization explains. Either way, the pattern is easiest to see in a full query.

How do you write hybrid search in PostgreSQL with pgvector?

You write hybrid search in PostgreSQL by pairing a full-text query on a tsvector column with a pgvector distance query, then fusing their ranks in SQL. The pgvector README suggests exactly this, with Reciprocal Rank Fusion or a cross-encoder to combine results.

First, the table needs a text search column, an embedding column, and an index on each:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id        bigserial PRIMARY KEY,
  title     text,
  body      text,
  category  text,
  tsv       tsvector GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED,
  embedding vector(384)
);

CREATE INDEX ON documents USING gin (tsv);
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

The query then runs both searches and fuses them. The application passes the query text as $1 and its embedding, from the same model used for the rows, as $2:

WITH keyword AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(tsv, q) DESC) AS rank
  FROM documents, plainto_tsquery('english', $1) AS q
  WHERE tsv @@ q
  ORDER BY ts_rank_cd(tsv, q) DESC
  LIMIT 40
),
semantic AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $2) AS rank
  FROM documents
  ORDER BY embedding <=> $2
  LIMIT 40
)
SELECT d.id, d.title,
       COALESCE(1.0 / (60 + k.rank), 0) + COALESCE(1.0 / (60 + s.rank), 0) AS rrf_score
FROM keyword k
FULL OUTER JOIN semantic s ON k.id = s.id
JOIN documents d ON d.id = COALESCE(k.id, s.id)
ORDER BY rrf_score DESC
LIMIT 10;

The <=> operator is pgvector’s cosine distance; <-> is Euclidean and <#> is negative inner product. Use the operator that matches the model and the index operator class. Each CTE keeps 40 candidates, enough for fusion to find overlaps before the final limit of 10.

To weight one side more, multiply its term, for example 1.5 times the keyword term for catalogs full of part numbers. To filter, add the condition to both CTEs, such as WHERE category = 'manuals'. That filter interacts with the vector index in a way that needs care, which leads to the pitfalls. Several databases now package this pattern for you.

Which SQL databases support hybrid search natively?

SQL databases that support hybrid search natively include SQL Server and Azure SQL, Google AlloyDB, Oracle, and PostgreSQL through extensions, each with its own functions for the keyword side, the vector side, and fusion. “Native” ranges from a single function call to building blocks you fuse yourself.

Microsoft’s Azure SQL team describes the SQL Server approach directly. FREETEXTTABLE ranks text matches with BM25, VECTOR_DISTANCE() handles embeddings, and RRF is implemented manually because there is no built-in function. Google’s AlloyDB documentation offers both an ai.hybrid_search() function and a raw SQL route.

Oracle goes furthest toward one structure. Its hybrid vector index combines Oracle Text and vector index data structures into a single domain index. A query through DBMS_HYBRID_VECTOR.SEARCH can pass both a semantic search text and a keyword CONTAINS expression.

Features in this area change quickly, so check the current documentation for your version. Databases that support hybrid search compares SQL and non-SQL options side by side. Whichever engine you choose, a few mistakes recur.

What are the common pitfalls of hybrid search in SQL?

The common pitfalls of hybrid search in SQL are treating PostgreSQL text rank as BM25, losing rows when filters meet an approximate vector index, mixing incompatible scores, and embedding queries with a different model. Each one degrades results quietly, without an error.

• ts_rank is not BM25. PostgreSQL’s documentation states that its ranking functions consider term frequency, proximity, and document structure. It also states they do not use any global information. Without inverse document frequency, a common word counts as much as a rare one. Extensions such as pg_textsearch add true BM25 when ranking quality matters.
• Filters can starve the vector side. The pgvector README explains that with approximate indexes, filtering happens after the index scan. With the default hnsw.ef_search of 40, a filter matching 10% of rows returns only about 4 rows on average. pgvector 0.8.0 added iterative index scans, which keep scanning when too few rows survive.
• Raw scores do not mix. A text rank and a cosine distance live on different scales, and distance falls as similarity rises. Adding them directly gives meaningless orderings. Use ranks, as RRF does, or normalize first.
• Model drift. The query embedding must come from the same model as the stored embeddings. A model upgrade means re-embedding the table.
• Too few candidates. If each side keeps only 10 rows, fusion has little overlap to work with. Keep several times the final limit per side.

The filtering issue is not unique to PostgreSQL; it affects any approximate index where filters run after the search. Metadata filtering explains pre-filtering and how dedicated vector databases handle it. Pitfalls aside, the bigger question is where this workload should live.

Should hybrid search run in your OLTP database or a separate system?

Hybrid search should run in your OLTP database while data is modest and search traffic is light, and move to a separate system when load or relevance needs outgrow it. Both choices are common and both can be right.

Weaviate’s own view is balanced on the starting point. Its engineering blog says vector extensions make sense if you have already bet on relational or NoSQL systems, and for many teams that is the right place to start. It also argues that the hard part is operating on vectors, not storing them. HNSW solves a different problem than ACID transactions and row storage, with different memory and storage trade-offs.

A useful test is to ask what fails first. If search queries slow down checkout, isolate search on a replica or a separate engine. If relevance work keeps hitting limits such as missing BM25 or filter starvation, a search-first engine saves effort. If neither happens, the simplest architecture is the one you already run. Index freshness covers how to keep a separate search index in sync. The dedicated option looks different in practice.

How does hybrid search in Weaviate compare with hybrid search in SQL?

Hybrid search in Weaviate runs the keyword search, the vector search, and the fusion in one query, with a single alpha setting to weight them, while SQL needs the fusion written by hand. The underlying ideas are the same.

Weaviate’s keyword side is BM25F, a BM25 variant that handles multiple fields, so it uses corpus-wide statistics by design. The alpha parameter sets the balance: 0 is pure keyword, 1 is pure vector, and the default is 0.75. Two fusion methods are available. relativeScoreFusion, the default since version 1.24, normalizes each side’s scores to between 0 and 1 and sums them. rankedFusion uses rank-based scores of 1 divided by (rank plus 60), like RRF.

import weaviate
from weaviate.classes.query import Filter, HybridFusion, MetadataQuery

client = weaviate.connect_to_local()
docs = client.collections.use("Document")

response = docs.query.hybrid(
    query="replace filter on model X200",
    alpha=0.5,                                  # equal keyword and vector weight
    fusion_type=HybridFusion.RELATIVE_SCORE,
    filters=Filter.by_property("category").equal("manuals"),
    limit=10,
    return_metadata=MetadataQuery(score=True),
)
for obj in response.objects:
    print(round(obj.metadata.score, 3), obj.properties["title"])

client.close()

Weaviate’s documentation includes a worked example of why the fusion choice matters. With five documents, relative score fusion ranks first the one with a large lead in keyword score. Ranked fusion ranks a different document first, because ranks discard how big the gaps were. SQL implementations using plain RRF behave like ranked fusion.

The trade-off is the one in the architecture table. Weaviate removes the hand-written fusion, provides BM25 by default, and applies filters inside the search. SQL keeps everything in one database with transactional joins. How hybrid search works, balancing keyword and vector scores, relative score fusion, and the hybrid search tutorial cover the details.

Hybrid search in SQL runs a full-text search and a vector search over the same table and fuses their rankings, most often with Reciprocal Rank Fusion. PostgreSQL with pgvector, SQL Server, AlloyDB, and Oracle all support the pattern, with varying amounts of built-in help. The two traps to check are text ranking without BM25 statistics and filters that starve the approximate vector index. The practical step is to start in the database you already run, measure relevance on real queries, and move search to a dedicated engine such as Weaviate only when load or quality demands it.