Building a Semantic Product Search Service with MCP

Who this is for

This is for developers who already run PostgreSQL and want natural-language product search, exposed to an AI agent as an MCP tool. After reading, you should be able to follow the pipeline from embedding to vector query, and know which settings to check first.

Why semantic search

Keyword search misses intent. A query such as "comfortable running shoes for rainy weather" can match a product that never uses those words, if the product's meaning is close enough. Semantic search turns text into vectors and ranks products by similarity ^[1].

What you need

• An Azure OpenAI embedding deployment. The example uses text-embedding-3-small, which outputs 1536-dimensional vectors ^[1].
• PostgreSQL with the pgvector extension ^[1][2].
• Redis, optional, for caching ^[1].
• Environment variables for the endpoint and deployment name: PROJECT_ENDPOINT and EMBEDDING_DEPLOYMENT_NAME ^[1].

Step 1: generate embeddings

The embedding manager holds the main settings. The example uses a 1536-dimensional embedding, up to 8000 tokens per request, and a batch size of 100 ^[1]:

        self.embedding_dimension = 1536  # text-embedding-3-small dimension
        self.max_tokens = 8000  # Maximum tokens per request
        self.batch_size = 100  # Batch processing size

Cache misses go out in batches of 100, with a 0.1-second pause between batches. Cache entries use a SHA-256 key built from the deployment name and the text, and expire after 24 hours ^[1]. The example sets rate limits of 1000 requests and 150000 tokens per minute ^[1].

Two details to check before you run it. The snippet reads its settings with os.getenv but does not import os ^[1]. Add the import yourself. The endpoint and deployment name come from PROJECT_ENDPOINT and EMBEDDING_DEPLOYMENT_NAME ^[1].

Step 2: build the text for each product

The product embedder combines fields into one string with the template {product_name} {brand} {description} {category} {tags}, then collapses extra whitespace ^[1]:

        self.text_template = "{product_name} {brand} {description} {category} {tags}"

Vectors are written to retail.product_embeddings, with an upsert keyed on the product ID and embedding model ^[1]. By default, only products that have no embedding yet, or whose embedding is older than their updated_at, get regenerated. Set force_regenerate to rebuild everything ^[1].

Step 3: query by cosine distance

The query text is embedded first. The vector is sent to PostgreSQL as text in the form [x,y,...] and cast with ::vector ^[1]:

        embedding_vector = f"[{','.join(map(str, query_embedding))}]"

The main part of the query looks like this. It filters by store and active products, applies the threshold, orders by distance, and limits rows ^[1]:

WHERE pe.store_id = $2
  AND p.is_active = TRUE
  AND 1 - (pe.embedding <=> $1::vector) >= $3
ORDER BY pe.embedding <=> $1::vector
LIMIT $4

The semantic search tool requires query and store_id. The query can be 1 to 500 characters. The default limit is 20, the maximum is 100, and the default similarity threshold is 0.7 ^[1].

Step 4: hybrid search

Hybrid search adds a keyword score to the semantic score. Its defaults are 0.7 for semantic similarity and 0.3 for keyword matching ^[1]:

                "semantic_weight": {
                    "type": "number",
                    "description": "Weight for semantic similarity (0.0 to 1.0)",
                    "minimum": 0.0,
                    "maximum": 1.0,
                    "default": 0.7
                },

The keyword side uses PostgreSQL full-text search with to_tsvector and plainto_tsquery, and merges with the semantic results through a FULL OUTER JOIN ^[1].

Recommendations

The recommendation tool supports four types: similar_products, customer_based, trending, and cross_sell ^[1]. For customer-based results, the query averages the embeddings of what a customer bought in the last days_back days, which defaults to 90. It returns similar products that score at least 0.6 ^[1].

Performance: index and cache

The index example creates an HNSW index with vector_cosine_ops, m = 16, and ef_construction = 200 ^[1]:

CREATE INDEX CONCURRENTLY idx_product_embeddings_vector_optimized 
ON retail.product_embeddings 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);

It also gives example PostgreSQL values such as work_mem = '256MB' and shared_buffers = '512MB' ^[1]. These are starting points for your own hardware, not tuned results.

The Redis cache uses the default URL redis://localhost:6379. Its TTLs are 7 days for embeddings, 1 hour for search results, and 4 hours for recommendations ^[1]:

        self.embedding_ttl = timedelta(days=7)  # Embeddings cached for 1 week
        self.search_ttl = timedelta(hours=1)    # Search results cached for 1 hour
        self.recommendation_ttl = timedelta(hours=4)  # Recommendations cached for 4 hours

If Redis is not reachable, the cache manager logs a warning and keeps working without a cache ^[1].

The semantic side of hybrid search has a floor

In the hybrid query, the semantic part keeps only results with a similarity of at least 0.5. That floor is lower than the default 0.7 threshold of the standalone semantic tool ^[1]. Hybrid search therefore pulls in more candidates, and then the keyword and semantic scores are merged with their weights ^[1].

How the cache is split and cleared

The cache has three kinds of entry, each with its own lifetime: embeddings for 7 days, search results for 1 hour, and recommendations for 4 hours ^[1]. The cache manager also has an invalidate_store_cache method, which deletes cache keys matching a pattern built from the store ID ^[1]. If Redis is unavailable, the cache manager logs a warning and runs without caching ^[1].

Put input limits in the tool schema

The store_id may contain only letters, digits, underscores, and hyphens ^[1]. A query can be 1 to 500 characters, the result limit is 1 to 100, and the similarity threshold is a number from 0.0 to 1.0 ^[1]. When these limits sit in the schema, an agent can build valid arguments before the call, and mistakes show up earlier.

Common questions

Why does my search return nothing? The default similarity threshold is 0.7. Check the threshold first, then compare the scores of known good matches. The next lab covers testing and debugging, including embedding quality and relevance ^[1].

Can I paste the code and run it? Not as is. Add the missing os import, and fit the table names and schema to your own database ^[1].

Where do I read more about pgvector? Its documentation lives in the pgvector GitHub repository ^[2].

Adapted from the semantic search lab of Microsoft's open-source course *MCP for Beginners*^[1], released under the MIT License.