AI-Powered Semantic Search for Product Catalog

Problem Description

An e-commerce platform is adding semantic search to their product catalog. They have approximately 500,000 products and plan to store 1536-dimensional dense vector embeddings generated by an external embedding model for each product. Users will search by meaning (e.g., "cozy winter jacket" should match relevant products even if those exact words don't appear in product titles).

Many search queries will filter by product category in addition to finding semantically similar results — for example, "show me the most similar products to this one, but only from the Outdoor category." Each category contains roughly 5–15% of total products, making the category filter fairly selective. The team wants good recall on these filtered searches.

The company is evaluating both self-hosted PostgreSQL deployments and managed cloud PostgreSQL options, and wants the implementation to work well in both environments with appropriate notes on any differences.

The database layer needs to be production-ready: good query performance at scale, memory-efficient storage where possible, and practical guidance on tuning for the expected query patterns.

Output Specification

Produce two files:

implementation.sql — A complete SQL script that sets up the schema for product embeddings, installs the necessary extension, creates the appropriate index type and configuration, and includes representative query examples showing how to perform semantic search (both unfiltered and category-filtered). Include comments explaining key choices.

deployment_notes.md — A markdown document covering: index configuration tuning parameters and how to adjust them for production scale; memory and runtime configuration relevant to this workload; and any considerations specific to managed cloud PostgreSQL environments.