This page describes how to conduct full-text and vector hybrid searches inSpanner. Hybrid search combines the precision of keyword matching(full-text search, FTS) with the recall of semantic matching (vector search) toproduce highly relevant search results.

Spanner supports the following hybrid search patterns, which aredivided into three main categories:

Fusion search

Fusion search involves running FTS and vector search independently on the samedata corpus. It then merges the results to create a single, unified, and highlyrelevant ranked list.

While you can execute independent queries on the client side, hybrid search inSpanner provides the following advantages:

• Simplifies application logic by managing parallel requests and resultmerging on the server.
• Avoids transferring potentially large intermediate result sets to theclient.

You can use SQL to create fusion methods in Spanner.This section provides examples ofreciprocal rank fusionandrelative score fusion. However, we recommendevaluating different fusion strategies to determine which best suits yourapplication's requirements.

Rank-based fusion

Use rank-based fusion when the relevance scores from different retrieval methods(such as FTS scores and vector distances) are difficult to compare or normalizebecause they are measured in different spaces. This method uses the rank positionof each document from each retriever to generate a final score and ranking.

Reciprocal rank fusion (RRF) is a rank-based fusion function. The RRF scorefor a document is the sum of the reciprocals of its ranks from multipleretrievers is calculated as follows:

\[ score\ (d\epsilon D)\ =\ \sum\limits_{r\epsilon R}^{}(1\ /\ (\ 𝑘\ +\ ran{k}_{r}\ (𝑑)\ )\ )\ \]

Where:

• d is the document.
• R is the set of retrievers (FTS and vector search).
• k is a constant (often set to 60) used to moderate the influence of veryhigh-ranked documents.
• w is the rank of the document from retrieverr.

Implement RRF in a Spanner query

Vector metrics (such asAPPROX_DOT_PRODUCT)and text search scores (such asSCORE_NGRAMS)operate on incompatible scales. To resolve this, the following exampleimplements RRF to normalize data based on rank position rather than raw scores.

The query usesUNNEST(ARRAY(...) WITH OFFSET) to assign a rank to the top 100candidates from each method. It then calculates a standardized score using theinverse of those rankings and aggregates the results to return the top fivematches.

SELECTSUM(1/(60+rank))ASrrf_score,keyFROM((SELECTrank,xASkeyFROMUNNEST(ARRAY(SELECTkeyFROMhybrid_searchWHEREembeddingISNOTNULLORDERBYAPPROX_DOT_PRODUCT(@vector,embedding,OPTIONS=>JSON'{"num_leaves_to_search": 50}')DESCLIMIT100))ASxWITHOFFSETASrank)UNIONALL(SELECTrank,xASkeyFROMUNNEST(ARRAY(SELECTkeyFROMhybrid_searchWHERESEARCH_NGRAMS(text_tokens_ngrams,'foo')ORDERBYSCORE_NGRAMS(text_tokens_ngrams,'foo')DESCLIMIT100))ASxWITHOFFSETASrank))GROUPBYkeyORDERBYrrf_scoreDESCLIMIT5;

Score-based fusion

Score-based fusion is effective when the relevance scores from different methodsare comparable or you can normalize them, which might enable a more preciseranking that incorporates the actual relevance weight of each method.

Relative score fusion (RSF) is a score-based method that normalizes scoresfrom different methods relative to the highest and lowest scores within eachmethod, typically using theMIN() andMAX() functions. The RSF score of adocument retrieved by a set of retrievers is calculated as follows:

\[ score(d\epsilon D)\ =\ \sum\limits_{r\epsilon R}^{}({w}_{r}*(scor{e}_{r}(d)\ -\ mi{n}_{r})\ /\ (ma{x}_{r\ }-mi{n}_{r})\ ) \]

Where:

• d is the document.
• R is the set of retrievers (FTS and vector search).
• w is the weight assigned to a specific retriever.

Implement RSF in a Spanner query

To implement RSF, you must normalize the scores from the vector and FTS to acommon scale. The following example calculates the minimum and maximum scores inseparateWITH clauses to derive the normalization factors. It then combinesthe results using aFULL OUTER JOIN, summing the normalized scores from theapproximate nearest neighbor (ANN) search (converted fromcosine_distance) andthe FTS.

WITHannAS(SELECTkey,APPROX_COSINE_DISTANCE(@vector,embedding,OPTIONS=>JSON'{"num_leaves_to_search": 50}')AScosine_distance,FROMhybrid_searchWHEREembeddingISNOTNULLORDERBYcosine_distanceLIMIT100),ftsAS(SELECTkey,SCORE_NGRAMS(text_tokens_ngrams,'Green')ASscore,FROMhybrid_searchWHERESEARCH_NGRAMS(text_tokens_ngrams,'Green')ORDERBYscoreDESCLIMIT100),ann_minAS(SELECTMIN(1-cosine_distance)ASminFROMann),ann_maxAS(SELECTMAX(1-cosine_distance)ASmaxFROMann),fts_minAS(SELECTMIN(score)ASminFROMfts),fts_maxAS(SELECTMAX(score)ASmaxFROMfts)SELECTIFNULL(ann.key,fts.key),IFNULL(((1-ann.cosine_distance)-ann_min.min)/(ann_max.max-ann_min.min),0)+IFNULL((fts.score-fts_min.min)/(fts_max.max-fts_min.min),0)ASscoreFROMannFULLOUTERJOINftsONann.key=fts.keyCROSSJOINann_minCROSSJOINann_maxCROSSJOINfts_minCROSSJOINfts_maxORDERBYscoreDESCLIMIT5;

Filtered search

Filtered searches use FTS to create a filter that reduces the set of documentsconsidered for a k-nearest neighbors (KNN) vector search. You can optionally usea presort to limit the size of the result set.

Implement filtered search in a Spanner query

The example search in this section takes the following steps to narrow down thevector search space to the subset of data that match the keywords:

• UsesSEARCH (text_tokens, 'Green') to find rows where thetext_tokenscolumn contains the textGreen. The top 1000 rows are returned by aresort order defined by the search index.
• Uses a vector function,DOT_PRODUCT(@vector, embedding) to calculate thesimilarity between the query vector (@vector) and the stored document vector(embedding). It then sorts the results and returns the final top10 matches.

SELECTkeyFROM(SELECTkey,embeddingFROMhybrid_searchWHERESEARCH(text_tokens,'Green')ORDERBYpresortLIMIT1000)ORDERBYDOT_PRODUCT(@vector,embedding)DESCLIMIT10;

ML reranking

ML-based reranking is a computationally intensive but highly precise approach.It applies a machine learning model to a small candidate set that has beenreduced by FTS, vector search, or a combination of both. For moreinformation about Spanner Vertex AI integration, see theSpanner Vertex AI integration overview.

You can integrate ML reranking using the SpannerML.PREDICTfunction with a deployed Vertex AI model.

Implement ML-based reranking

1. Deploy a reranker model (such as fromHuggingFace) to a Vertex AI endpoint.

2. Create a SpannerMODEL objectthat points to the Vertex AI endpoint. For example, in thefollowingReranker model example:

   • text ARRAY<string(max)> is the documents.
   • text_pair ARRAY<string(max)> is the query text in the example.
   • score is the relevance scores assigned by the ML model for the inputpairs of texts.

   CREATEMODELRerankerINPUT(textARRAY<string(max)>,text_pairARRAY<string(max)>)OUTPUT(scoreFLOAT32)REMOTEOPTIONS(endpoint='...');

3. Use a subquery to retrieve the initial candidates (such as the top 100results from an ANN search) and pass them toML.PREDICT. The functionreturns a new score column for the final ordering.

   SELECTkeyFROMML.PREDICT(MODELReranker,(SELECTkey,text,"gift for 8-year-old"AStext_pairFROMhybrid_searchWHEREembeddingISNOTNULLORDERBYAPPROX_DOT_PRODUCT(@vector,embedding,options=>JSON'{"num_leaves_to_search": 50}')DESCLIMIT100))ORDERBYscoreDESCLIMIT3;

What's next

• Tailor your search engine with AI-powered hybrid search in Spanner.
• Learn more aboutfull-text search.