Help Center/ Data Warehouse Service/ Best Practices/ Data for AI Converged Analysis/ Implementing a Simple Product Search and Recommendation System Using DWS Vector Computing

Updated on 2026-09-28 GMT+08:00

Implementing a Simple Product Search and Recommendation System Using DWS Vector Computing

With the rapid development of AI and large models, data processing requirements have shifted from traditional exact matching to more complex semantic understanding. For example, on an e-commerce platform, users may want to search for similar products by uploading an image or find the most relevant products through a text description. Traditional database systems perform well in processing structured data, but they cannot provide accurate search results when processing unstructured data, such as text, images, audio, and videos, as they cannot understand the meanings of such data.

To be specific, traditional databases rely on exact matching or field indexes for queries, which cannot meet the requirements of AI applications for fuzzy similarity search, such as text-to-image search, intelligent Q&A, and recommendation systems. To address this challenge and improve data processing efficiency and accuracy, DWS integrates the pgvector (0.8.0) plugin, which can be loaded in a pluggable manner to achieve in-database vector computing and retrieval. Users can implement vector retrieval in existing data warehouses without migrating data or reconstructing the application architecture. This effectively addresses the preceding challenge.

The typical scenarios are as follows:

Text-to-image search/Image-to-image search: Find the image most similar to the input image.
Intelligent Q&A/RAG: Find the document fragment most relevant to the user's question in the knowledge base.
Recommendation system: Find the product most similar to the user's interest vector.

This document uses a product search and recommendation system as an example to describe how to use vector computing of DWS to achieve semantic search and product recommendation.

Background

A product has fields such as the title, description, category, and price.
With the embedding capability of large models, the product description can be encoded into a 10-dimensional vector. (It is only used for demonstration. In practice, a 768-dimensional vector is commonly used.)
The keyword input by the user is also converted into a 10-dimensional vector.
Objective: Find the product that the user desires most through similarity search.

Basic Concepts

Before you start, learn the following concepts first.

**Table 1** Basic concepts
Concept	Description
Vector	A vector maps unstructured data such as text and images to numerical arrays with fixed dimensions (for example, [0.12, 0.34, -0.21, ...]) through mathematical methods. This allows computers to measure the semantic similarity between data through numerical calculations. For example, a three-dimensional vector [30, 175, 70] can be used to describe a person's age, height, and weight, allowing the computer to understand the semantic features.
Embedding (vectorization)	It refers to the process of converting unstructured data (such as product description text) into vectors, typically using LLMs (such as BERT and GPT). The vectors obtained from embedding retain the semantic information of the raw data.
Similarity measurement	It is a mathematical method for measuring how close two vectors are. Common measurements include Euclidean distance (L2 distance), inner product, and cosine similarity. The smaller the distance is between two vectors or the more similar they are to each other, the closer they are semantically. Euclidean distance (L2 distance): It calculates the straight-line distance between two vectors in space. Inner product: It is the product of the projections of two vectors in direction and magnitude. The result is a scalar. Cosine similarity: It compares the directions of two vectors.
Recall	It is the percentage of relevant records in the retrieval result out of all relevant records. For example, if there are 100 query-related records in the database and the system returns 80 relevant records after retrieval, the recall is 80% (80/100). The recall and query speed are typically inversely related. Exact search delivers a 100% recall, while approximate search provides a higher query speed at the expense of a lower recall.

Vector Computing

For details about the syntax of vector computing in DWS, see Vector Computing.

Vector Data Types

**Table 2** Vector data types
Data Type	Precision	Supported Dimension	Typical Use Case
vector	Single-precision floating point number, 4 bytes	≤ 16,000	Traditional embedding
halfvec	Half-precision floating point number (16 bits, 2 bytes per element)	≤ 16,000	High-dimensional embedding and memory-sensitive scenarios
bit	Binary vector (bit vector)	≤ 64,000	Binary hash, image/feature Boolean representation
sparsevec	Sparse vector	≤ 16,000, non-zero dimensions	Text TF-IDF sparse features and ultra-high-dimensional sparse embedding

Vector Distance/Similarity Operators

**Table 3** Vector distance/similarity operators
Operator	Distance/Similarity Type	Description
<->	Euclidean distance (L2)	Euclidean distance
<#>	(Negative) inner product	Inner product
<=>	Cosine similarity	Vector direction difference
<+>	L1 distance (Taxicab)	Sum of absolute differences of vector coordinates
<~>	Hamming distance	Number of positions at which the corresponding binary bits are different
<%>	Jaccard distance	Used for binary bit vector/set representation to measure the intersection/union set differences

Vector Index Types

**Table 4** Vector index types
Index Type	How It Works	Construction Cost	Query Speed/Recall	Main Optimization Parameter
IVFFlat	Divides vectors into lists (clusters/buckets). Only some lists are scanned during queries.	Fast construction and low memory usage	Medium query speed and low recall. The scan range is affected by the probes parameter.	lists (number of clusters) probes (number of scanned clusters) maxProbes (maximum number of scanned clusters)
HNSW	Accesses neighboring points through jumps based on a multi-layer navigable small world graph.	Slow construction and high memory usage	High query speed and recall. It is especially suitable for high-dimensional and low-latency scenarios.	m (maximum number of connections at each layer) ef_construction (number of construction candidates) ef_search (number of search candidates) max_scan_tuples (maximum number of scanned nodes) mem_scan_multiplier (dynamic memory expansion rate)

Operator Classes in the pgvector Extension

pgvector (PostgreSQL vector extension) provides three main operator classes for different distance measurements. The operator is typically specified in the statement for creating an index. The following is an example:

     CREATE INDEX ON products USING hnsw (embedding vector_l2_ops) 
WITH (m = 16, ef_construction = 200);

**Table 5** Operators
Operator Class	Distance Measurement	Operator	Mathematical Meaning	Use Case
vector_l2_ops	Euclidean distance (L2)	<->	Straight-line distance between two points	General scenarios, recommendation systems, and cluster analysis
vector_ip_ops	Inner product	<#>	Vector dot product, which measures direction consistency	Semantic search and information retrieval (especially text matching)
vector_cosine_ops	Cosine similarity	<=>	Cosine value of the vector angle, with the length ignored	Text similarity and sentence matching (most commonly used)

Prerequisites

The DWS cluster version must be 9.1.1.200 or later.
Before using vector computing, contact technical support to modify the cluster parameter feature_support_options and enable the enable_pgvector option.
The embedding vector of the product description has been obtained using an LLM (such as BERT or GPT). This document does not describe the vector generation process. Assume that the vector is ready.

Step 1: Install the pgvector Extension

Ensure that the GUC parameter feature_support_options is set to enable_pgvector. Contact technical support to set the parameter.
Enable vector computing in the database.
1

CREATE EXTENSION pgvector;
Run the following query. If pgvector is returned, the installation is successful.
1

SELECT extname FROM pg_extension WHERE extname = 'pgvector';

Step 2: Create a Product Table and Insert Data

Create a table to store product information and vector data.

768- or 1,024-dimensional vectors are commonly used in the industry, which involve a large amount of data. For demonstration purposes, this practice uses 10-dimensional vectors for simulation.

vector(10) indicates that the field stores 10-dimensional vectors. The vector dimension must be the same as the output dimension of the embedding model.

     DROP TABLE IF EXISTS products;
CREATE TABLE products (
    id bigserial PRIMARY KEY,
    title text,
    description text,
    price numeric,
    embedding vector(10)   -- 10-dimensional vector, generated from the product description using an embedding model
);
 
 
  

Insert product information and corresponding vectors into the product table.

     INSERT INTO products (title, description, price, embedding) VALUES
('Wireless Earbuds', 'Bluetooth wireless earbuds with charging case', 59.9, '[0.12, 0.34, -0.21, 0.15, -0.08, 0.33, 0.02, -0.41, 0.19, 0.07]'),
('Noise Cancelling Headphones', 'Over-ear headphones with active noise cancellation', 129.9, '[0.11, 0.36, -0.19, 0.17, -0.06, 0.31, 0.04, -0.39, 0.21, 0.05]'),
('Gaming Headset', 'Wired gaming headset with microphone', 79.9, '[0.10, 0.33, -0.25, 0.14, -0.03, 0.29, 0.01, -0.37, 0.18, 0.09]'),
('Smartphone Stand', 'Adjustable phone stand for desk use', 12.9, '[0.45, -0.12, 0.08, 0.48, -0.20, 0.11, 0.37, -0.05, 0.44, -0.15]'),
('USB-C Charger', 'Fast charging USB-C power adapter', 19.9, '[0.42, -0.15, 0.05, 0.46, -0.18, 0.08, 0.34, -0.07, 0.41, -0.12]'),
('Mechanical Keyboard', 'Mechanical keyboard with blue switches', 89.9, '[0.55, 0.02, -0.31, 0.50, 0.06, -0.28, 0.22, -0.44, 0.53, -0.09]'),
('Wireless Mouse', 'Ergonomic wireless mouse', 29.9, '[0.53, 0.01, -0.28, 0.51, 0.04, -0.25, 0.20, -0.42, 0.49, -0.07]'),
('Laptop Backpack', 'Water-resistant laptop backpack', 49.9, '[0.60, -0.05, -0.10, 0.58, -0.08, -0.15, 0.42, -0.02, 0.56, -0.13]'),
('4K Monitor', '27-inch 4K UHD computer monitor', 299.9, '[0.58, 0.04, -0.35, 0.55, 0.07, -0.32, 0.39, -0.10, 0.61, -0.18]'),
('Webcam', 'HD webcam for video conferencing', 39.9, '[0.52, -0.01, -0.20, 0.49, -0.03, 0.15, 0.36, -0.08, 0.47, -0.11]'),
('Bluetooth Speaker', 'Portable Bluetooth speaker with deep bass', 45.9, '[0.14, 0.30, -0.18, 0.13, -0.09, 0.28, 0.03, -0.35, 0.16, 0.01]'),
('Smart Watch', 'Fitness tracking smart watch', 99.9, '[0.20, 0.40, -0.22, 0.18, -0.05, 0.37, 0.09, -0.41, 0.23, 0.06]'),
('Fitness Tracker', 'Lightweight activity and sleep tracker', 49.9, '[0.22, 0.38, -0.24, 0.19, -0.07, 0.35, 0.07, -0.39, 0.25, 0.04]'),
('Tablet Stylus', 'Stylus pen for tablets', 25.9, '[0.48, -0.10, 0.12, 0.45, -0.17, 0.14, 0.40, -0.06, 0.50, -0.16]'),
('Laptop Cooling Pad', 'Cooling pad with dual fans', 34.9, '[0.57, -0.02, -0.15, 0.54, -0.04, -0.12, 0.46, -0.01, 0.59, -0.14]');
 
 
  

Step 3: Query Data

Query data by semantic similarity. Search for the products that are the most semantically similar to the vector of the search keyword input by the user. <-> is the L2 distance operator. A smaller value indicates a higher similarity. LIMIT 10 indicates that the top 10 products similar to the vector will be returned.

     SELECT id, title, price
FROM products
ORDER BY embedding <=> '[0.09, -0.05, 0.92, 0.11, -0.03, 0.85, 0.30, -0.50, 0.15, 0.02]'   -- Embedding vector (10-dimension) of the user's search keyword
LIMIT 10;

Click to enlarge

Execute a hybrid query. Based on the semantic similarity query, use traditional structured filters to achieve more accurate search. Filter results by price (price < 2,000) and then sort them by semantic similarity. This is suitable when you want to find the most similar products within a budget.

     SELECT id, title, price
FROM products
WHERE price < 2000                          -- Filter data by price.
ORDER BY embedding <=> '[0.09, -0.05, 0.92, 0.11, -0.03, 0.85, 0.30, -0.50, 0.15, 0.02]'  -- Sort the results by semantic similarity.
LIMIT 10;
 
 
  

Click to enlarge

Create a vector index to accelerate queries. If there is a large amount of data, you can create an ANN search index to improve vector query performance.

By default, DWS vector computing uses exact nearest neighbor (ENN) search, which provides 100% recall but delivers a slow query speed. If the data volume is large, you can create an HNSW or IVFFlat index to significantly improve the query speed at the cost of a slightly lower recall.

For details about index parameters, see Table 6. vector_l2_ops indicates the operator class used by the Euclidean distance (L2). For more information, see Table 5.

The operator class (for example, vector_l2_ops) specified during index creation must be the same as the distance operator (for example, <->) used in the query. If the query uses <=> (cosine similarity), use vector_cosine_ops when creating the index.

     -- Method 1: Create an HNSW index (which provides better query performance and is suitable for frequent queries).
CREATE INDEX ON products USING hnsw (embedding vector_l2_ops) 
WITH (m = 16, ef_construction = 200);

-- Method 2: Create an IVFFlat index (which provides faster construction and is suitable when there is a large amount of data).
CREATE INDEX ON products USING ivfflat (embedding vector_l2_ops) 
WITH (lists = 100);

**Table 6** **Index parameters**
Index Type	Parameter	Description	Recommended Value
HNSW	m	Maximum number of connections for each node at each layer of the graph. This parameter affects the index quality and memory usage.	16 (default). Increase the value to 48 if the data volume is large.
HNSW	ef_construction	Search width during index creation. A larger value indicates higher index quality but slower creation.	200 (default)
IVFFlat	lists	Number of inverted lists. A larger value indicates faster query but possibly a lower recall.	A value around sqrt (number of rows)

Query Result Analysis

Scenario 1: Product Search

The search keyword wireless audio headset input by a user is converted into a vector, and then a query is executed:

     SELECT id, title
FROM products
ORDER BY embedding <=> '[0.13, 0.35, -0.20, 0.16, -0.07, 0.32, 0.03, -0.38, 0.20, 0.04]'   -- 10-dimension embedding vector of the search keyword input by the user
LIMIT 10;

Compared with other product categories in the table, headphones, earbuds, and speakers are semantically closer to the search keyword input by the user. Even if the product title does not contain word "headset," headphones and earbuds are still displayed before other product categories in the search result. This shows the advantage of semantic search.

Scenario 2: Product Recommendation

A user is browsing products of the Noise Cancelling Headphones category. Products are recommended based on vector similarity.

     SELECT p2.title, p2.price
FROM products p1
JOIN products p2 ON p1.id <> p2.id          -- Exclude itself.
WHERE p1.title = 'Noise Cancelling Headphones'
ORDER BY p2.embedding <=> p1.embedding      -- Calculate the L2 distance between two product vectors. A smaller distance indicates a higher similarity.
LIMIT 10;
 
 
  

Earphones and speakers are more relevant to the Noise Cancelling Headphones category that the user is browsing, so they are displayed before other categories in the recommendation list.

Summary

By leveraging the vector computing capability provided by DWS, we have upgraded the traditional database system that focuses on structured queries to a unified data foundation that is capable of semantic understanding and similarity calculation. The system stores vectors and supports efficient similarity retrieval. It also supports semantic search, recommendation, and similar content matching through standard SQL statements, without requiring additional vector engines or complex data synchronization links.

With the HNSW and IVFFlat ANN indexes, vector search provides controllable performance and stable responses even when there is a large amount of data. In addition, vector search is deeply integrated with traditional structured queries, filter criteria, and join logic, enabling the smooth deployment of AI applications such as search recommendation, personalized analysis, and RAG on existing data warehouses and service systems.

This extended capability simplifies the system architecture while also significantly enhancing the data platform to support next-generation intelligent applications. This facilitates the evolution from data query to semantic computing and business decision-making support.

Parent Topic: Data for AI Converged Analysis

Previous topic: Building an RAG Framework Using DWS to Generate Industry Research Reports

Next topic: Database Management