Hybrid search

Background

• Dense search: Uses semantic context to understand the meaning behind a query.

• Sparse search: Emphasizes text matching to find results based on specific terms. This is equivalent to full-text search.

• Hybrid search: Combines the strengths of dense search and sparse search to capture both full context and specific keywords, delivering comprehensive search results.

Prepare data

1. Use a privileged account to create the extensions required for search.

   CREATE EXTENSION IF NOT EXISTS pg_jieba;
   CREATE EXTENSION IF NOT EXISTS rum;
   CREATE EXTENSION IF NOT EXISTS vector;
   CREATE EXTENSION IF NOT EXISTS polar_ai;

   The extensions provide the following features:

   • pg_jieba (Chinese word segmentation): Performs Chinese word segmentation.

   • rum (full-text search acceleration): Supports full-text search and relevance sorting.

   • vector (vector search): Supports vector search.

   • polar_ai: Lets you create models for text vectorization.

2. Create a table and insert test data.

   CREATE TABLE t_chunk(id serial, chunk text, embedding vector(1536), v tsvector);
   
   INSERT INTO t_chunk(chunk) VALUES('PolarDB is a next-generation cloud-native database developed by Alibaba Cloud. With a compute-storage decoupled architecture that combines software and hardware, PolarDB provides extreme elasticity, high performance, massive storage, and secure database services. It is 100% compatible with MySQL and PostgreSQL ecosystems, and highly compatible with Oracle syntax.'); 
   INSERT INTO t_chunk(chunk) VALUES('PolarDB editions: PolarDB for MySQL features multi-primary replication, multi-site disaster recovery, and HTAP capabilities. Transaction performance is up to 6x that of open source databases, and analytic performance is up to 400x that of open source databases. TCO is 50% lower than self-managed databases.'); 
   INSERT INTO t_chunk(chunk) VALUES('PolarDB-X provides high throughput, large storage, low latency, easy scalability, and ultra-high availability. PolarDB for PostgreSQL provides fast elasticity, high performance, massive storage, and secure services, and supports the Ganos multi-dimensional spatio-temporal engine and the open source PostGIS engine. PolarDB for PostgreSQL (Oracle-compatible) uses a compute-storage decoupled architecture that integrates software and hardware.');
   INSERT INTO t_chunk(chunk) VALUES('Tutorials: free cloud resources in a real cloud environment with immersive WYSIWYG experience. PolarDB for MySQL Serverless provides extreme elasticity with dynamic resource scaling and seamless scale-up in seconds. PolarDB for MySQL supports seamless failover without purchasing any resources.'); 
   INSERT INTO t_chunk(chunk) VALUES('PolarDB for MySQL IMCI accelerates complex queries and handles OLAP workloads efficiently. PolarDB for MySQL ePQ accelerates parallel queries and improves cluster resource utilization. PolarDB for PostgreSQL Serverless provides extreme elasticity with dynamic resource scaling.');
   INSERT INTO t_chunk(chunk) VALUES('PolarDB for PostgreSQL provides one-stop HTAP with real-time data synchronization between primary and analytic nodes. PolarDB-X supports transparent distribution with auto partitioning and distributed online DDL. The intelligent SQL conversion assistant helps migrate Oracle workloads to PolarDB, and RDS for MySQL workloads to PolarDB for MySQL.');
   INSERT INTO t_chunk(chunk) VALUES('Why Alibaba Cloud: global infrastructure, leading technology, stability, reliability, security, and compliance. Free trial available for all products.');
   INSERT INTO t_chunk(chunk) VALUES('Product updates, pricing, cost management, technical solutions, documentation, developer community, training and certification, free trial, enterprise services, migration services, and trust center.');

3. Generate vector data. You can create a custom model and call it to convert text into vectors. This example uses the text_embedding_v2 model provided by Alibaba Cloud Model Studio.

   Note

   Currently, only PolarDB for PostgreSQL Standard Edition clusters in the China (Beijing) region can call the built-in model text_embedding_v2. For more information, see How to quickly perform text vectorization.

   1. Bind an API key

      Before you call a built-in model for the first time, you must first go to Alibaba Cloud Model Studio to activate the service and obtain an API key. Then, execute the following SQL command to bind your API key to the specified model. A return value of t indicates success, and f indicates failure.

      SELECT polar_ai.AI_SetModelToken('_dashscope/text_embedding/text_embedding_v2', '<YOUR_API_KEY>');

   2. Embed text with SQL

      Call the AI_Text_Embedding function to perform text vectorization.

      -- Perform embedding
      UPDATE t_chunk SET embedding = polar_ai.ai_text_embedding(chunk);

4. Create the indexes required for search.

   • Create a vector index. This example uses L2 distance, which you can change as needed.

     CREATE INDEX ON t_chunk using hnsw(embedding vector_l2_ops);

   • Create a full-text index.

     UPDATE  t_chunk SET v = to_tsvector('jiebacfg', chunk);
     
     CREATE INDEX ON t_chunk USING rum (v rum_tsvector_ops);

Search

WITH t AS (
SELECT chunk, embedding <-> polar_ai.ai_text_embedding('What product editions does PolarDB offer')::vector(1536) as dist
FROM t_chunk
ORDER by dist ASC
limit 5 ),
t2 as (
  SELECT chunk, v <=> to_tsquery('english', 'PolarDB & PostgreSQL & quick') as rank
FROM t_chunk 
WHERE v @@ to_tsquery('english', 'PolarDB & PostgreSQL & quick')
ORDER by rank ASC
LIMIT 5
)
SELECT * FROM t
UNION ALL
SELECT * FROM t2;

-- Dense vector retrieval
WITH t1 as 
(
SELECT chunk, embedding <-> polar_ai.ai_text_embedding('What product editions does PolarDB offer')::vector(1536) as dist
FROM t_chunk
ORDER by dist ASC
limit 5
),
t2 as (
SELECT ROW_NUMBER() OVER (ORDER BY dist ASC) AS row_num,
chunk
FROM t1
),
-- Sparse vector retrieval
t3 as 
(
  SELECT chunk, v <=> to_tsquery('english', 'PolarDB & PostgreSQL & quick') as rank
  FROM t_chunk 
  WHERE v @@ to_tsquery('english', 'PolarDB & PostgreSQL & quick')
  ORDER by rank ASC
  LIMIT 5
),
t4 as (  
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS row_num,
chunk
FROM t3
),
-- Calculate RRF scores separately
t5 AS (
SELECT 1.0/(60+row_num) as score, chunk FROM t2
UNION ALL 
SELECT 1.0/(60+row_num), chunk FROM t4
)
-- Combine scores for merging
SELECT sum(score) as score, chunk
FROM t5
GROUP BY chunk
ORDER BY score DESC;

-- Dense vector retrieval
WITH t1 as 
(
SELECT chunk, embedding <-> polar_ai.ai_text_embedding('What product editions does PolarDB offer')::vector(1536) as dist
FROM t_chunk
ORDER by dist ASC
limit 5
),
t2 as (
SELECT ROW_NUMBER() OVER (ORDER BY dist ASC) AS row_num,
chunk
FROM t1
),
-- Sparse vector retrieval
t3 as 
(
  SELECT chunk, v <=> to_tsquery('english', 'PolarDB & PostgreSQL & quick') as rank
  FROM t_chunk 
  WHERE v @@ to_tsquery('english', 'PolarDB & PostgreSQL & quick')
  ORDER by rank ASC
  LIMIT 5
),
t4 as (  
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS row_num,
chunk
FROM t3
),
-- Calculate RRF scores separately with weights 0.8 and 0.2
t5 as (
SELECT (1.0/(60+row_num)) * 0.8 as score , chunk FROM t2
UNION ALL 
SELECT (1.0/(60+row_num)) * 0.2, chunk FROM t4
)
-- Combine scores for merging
SELECT sum(score) as score, chunk
FROM t5
GROUP BY chunk
ORDER BY score DESC;

SELECT chunk, embedding <-> polar_ai.ai_text_embedding('What product editions does PolarDB offer')::vector(1536) as dist
FROM t_chunk
ORDER by dist ASC
limit 5;

SELECT chunk, v <=> to_tsquery('english', 'PolarDB & PostgreSQL & quick') as rank
FROM t_chunk 
WHERE v @@ to_tsquery('english', 'PolarDB & PostgreSQL & quick')
ORDER by rank ASC
LIMIT 5;