Tutorial: Semantic similarity search for products

Example scenario

Suppose you have a DynamoDB table named itemtable that stores product information. Each record's business document (polardb_document) has the following structure:

{
  "item_id": {"S": "item_1001"},
  "title": {"S": "In-ear noise-canceling wireless earbuds"},
  "description": {"S": "In-ear Bluetooth earbuds with active noise cancellation, suitable for commuting and daily use"},
  "price": {"N": "79.9"}
}

• item_id: Product ID (DynamoDB partition key).

• title / description / price: Basic product information.

Step 1: Create the table and provision data

The following Python script uses the DynamoDB API to create the itemtable table and insert five test records. Replace the ENDPOINT_URL in the connection configuration with your DynamoDB-compatible access endpoint. See [doc link] for instructions on obtaining the endpoint.

#!/usr/bin/env python3
"""
Create the itemtable table and insert test data.
"""
import boto3
from botocore.exceptions import ClientError
# DynamoDB connection configuration
ENDPOINT_URL = "<your-endpoint_url>"
REGION = "<your-region>"
ACCESS_KEY = "<your-access-key>"
SECRET_KEY = "<your-secret-key>"
TABLE_NAME = "itemtable"
def create_dynamodb_client():
    return boto3.client(
        'dynamodb',
        endpoint_url=ENDPOINT_URL,
        region_name=REGION,
        aws_access_key_id=ACCESS_KEY,
        aws_secret_access_key=SECRET_KEY
    )
def create_table(client):
    try:
        client.create_table(
            TableName=TABLE_NAME,
            KeySchema=[{'AttributeName': 'item_id', 'KeyType': 'HASH'}],
            AttributeDefinitions=[{'AttributeName': 'item_id', 'AttributeType': 'S'}],
            BillingMode='PAY_PER_REQUEST'
        )
        waiter = client.get_waiter('table_exists')
        waiter.wait(TableName=TABLE_NAME, WaiterConfig={'Delay': 2, 'MaxAttempts': 60})
        print(f"Table {TABLE_NAME} created successfully.")
    except ClientError as e:
        if e.response['Error']['Code'] == 'ResourceInUseException':
            print(f"Table {TABLE_NAME} already exists. Skipping creation.")
        else:
            raise
def insert_test_data(client):
    items = [
        {'item_id': 'item_1001', 'title': 'In-ear noise-canceling wireless earbuds',
         'description': 'In-ear Bluetooth earbuds with active noise cancellation, suitable for commuting and daily use', 'price': 79.9},
        {'item_id': 'item_1002', 'title': 'Over-ear wireless headphones',
         'description': 'Over-ear Bluetooth wireless headphones designed for comfort, ideal for home and office use', 'price': 129.0},
        {'item_id': 'item_1003', 'title': 'Over-ear wired headphones',
         'description': 'Over-ear headphones that connect via a 3.5mm audio cable, better suited for desktop use or recording sessions', 'price': 109.0},
        {'item_id': 'item_1004', 'title': 'USB condenser microphone',
         'description': 'A condenser microphone that connects directly to a computer via USB, suitable for live streaming and voice conferences', 'price': 89.0},
        {'item_id': 'item_1005', 'title': 'Portable digital camera',
         'description': 'A lightweight and compact digital camera with optical zoom and image stabilization, perfect for travel and daily photography', 'price': 139.0},
    ]
    for idx, d in enumerate(items, 1):
        client.put_item(TableName=TABLE_NAME, Item={
            'item_id': {'S': d['item_id']},
            'title': {'S': d['title']},
            'description': {'S': d['description']},
            'price': {'N': str(d['price'])},
        })
        print(f"Inserted item {idx}: {d['item_id']}, {d['title']}")
def main():
    client = create_dynamodb_client()
    create_table(client)
    insert_test_data(client)
    resp = client.scan(TableName=TABLE_NAME)
    print(f"Verification complete. Total items: {len(resp.get('Items', []))}")
if __name__ == "__main__":
    main()

After the data is inserted, connect to PolarDB using psql or the Alibaba Cloud DMS console to proceed with the next steps.

Step 2: Create an HNSW index in PolarDB

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

SELECT * FROM dynamodb.itemtable LIMIT 5;

 item_id  |                                                                              polardb_document
-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 item_1001 | {"price": {"N": "79.9"}, "title": {"S": "In-ear noise-canceling wireless earbuds"}, "item_id": {"S": "item_1001"}, "description": {"S": "In-ear Bluetooth earbuds with active noise cancellation, suitable for commuting and daily use"}}
 item_1002 | {"price": {"N": "129.0"}, "title": {"S": "Over-ear wireless headphones"}, "item_id": {"S": "item_1002"}, "description": {"S": "Over-ear Bluetooth wireless headphones designed for comfort, ideal for home and office use"}}
 item_1003 | {"price": {"N": "109.0"}, "title": {"S": "Over-ear wired headphones"}, "item_id": {"S": "item_1003"}, "description": {"S": "Over-ear headphones that connect via a 3.5mm audio cable, better suited for desktop use or recording sessions"}}
 item_1004 | {"price": {"N": "89.0"}, "title": {"S": "USB condenser microphone"}, "item_id": {"S": "item_1004"}, "description": {"S": "A condenser microphone that connects directly to a computer via USB, suitable for live streaming and voice conferences"}}
 item_1005 | {"price": {"N": "139.0"}, "title": {"S": "Portable digital camera"}, "item_id": {"S": "item_1005"}, "description": {"S": "A lightweight and compact digital camera with optical zoom and image stabilization, perfect for travel and daily photography"}}
(5 rows)

-- Bind the model token (only needs to be run once)
SELECT polar_ai.AI_SetModelToken('_dashscope/text_embedding/text_embedding_v2', '<YOUR_API_KEY>');
-- Define the vector column as a stored generated column
ALTER TABLE dynamodb.itemtable
    ADD COLUMN embedding_vec VECTOR(1536) GENERATED ALWAYS AS
        (polar_ai.ai_text_embedding(polardb_document -> 'description' ->> 'S')) STORED;

CREATE INDEX idx_item_embedding_hnsw
ON dynamodb.itemtable
USING hnsw (embedding_vec vector_l2_ops)
WITH (m = 16, ef_construction = 64);

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'dynamodb'
  AND tablename  = 'itemtable';

     indexname       |                                                         indexdef
-------------------------+---------------------------------------------------------------------------------------------------------------------------
 itemtable_pkey          | CREATE UNIQUE INDEX itemtable_pkey ON dynamodb.itemtable USING btree (item_id)
 idx_item_embedding_hnsw | CREATE INDEX idx_item_embedding_hnsw ON dynamodb.itemtable USING hnsw (embedding_vec vector_l2_ops) WITH (m='16', ef_construction='64')
(2 rows)

Step 3: Perform a top-K semantic search

Suppose a user is viewing product item_1001 (In-ear noise-canceling wireless earbuds), and you want to find the two most semantically similar products based on its description. Run the following SQL query:

SET hnsw.ef_search = 100;
WITH target_item AS (
    SELECT embedding_vec
    FROM dynamodb.itemtable
    WHERE polardb_document -> 'item_id' ->> 'S' = 'item_1001'
)
SELECT
    polardb_document -> 'item_id'  ->> 'S' AS item_id,
    polardb_document -> 'title'    ->> 'S' AS title,
    embedding_vec <-> (SELECT embedding_vec FROM target_item) AS distance
FROM dynamodb.itemtable
WHERE polardb_document -> 'item_id' ->> 'S' <> 'item_1001'
ORDER BY distance
LIMIT 2;

Query logic explanation:

• The Common Table Expression (CTE) target_item retrieves the vector corresponding to the target product's description. The polar_ai.ai_text_embedding function automatically generates this vector from the description field.

• The main query excludes the target product itself. It uses the <-> operator to calculate the L2 distance and orders the results by distance in ascending order to return the two nearest neighbors.

• hnsw.ef_search controls the size of the candidate list at query time. Increasing its value can improve the recall rate.

Based on the test data in this tutorial, the two products semantically closest to 'In-ear noise-canceling wireless earbuds' should be 'Over-ear wireless headphones' and 'Over-ear wired headphones', as they all belong to the headphones category.

Result:

The query returns a result similar to the following:

 item_id  |            title           |     distance
-----------+----------------------------+------------------
 item_1002 | Over-ear wireless headphones | 0.69451931920655
 item_1003 | Over-ear wired headphones  | 0.965461934465811
(2 rows)

Summary

The PolarDB for PostgreSQL DynamoDB-compatible mode integrates the pgvector extension and the built-in polar_ai.ai_text_embedding model. This lets you maintain your existing DynamoDB API for write operations while dynamically generating vector columns from product descriptions in the underlying PostgreSQL table. You can then create approximate indexes, such as HNSW or IVFFlat, on these columns and directly use SQL for tasks like product similarity recommendations and semantic search. For more information about the capabilities of pgvector, see the official documentation.