PostgreSQL & pgvector

You will set up PostgreSQL with the pgvector extension for storing embeddings generated by hosted APIs (OpenAI text-embedding-ada-002, Gemini). Install pgvector via CREATE EXTENSION vector. Create an `embeddings` table with columns: id (UUID), content (TEXT), metadata (JSONB), embedding (vector(1536)) for OpenAI ada-002 dimensions. Insert sample embeddings by calling an embedding API and storing the resulting float arrays. Verify storage by querying the vector column and confirming dimension counts with vector_dims().

You will create and compare vector indexes for search performance. Build an HNSW index with CREATE INDEX ON embeddings USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64). Build an IVFFlat index with lists = 100 for comparison. Benchmark query times on 10K vectors: measure HNSW vs IVFFlat vs sequential scan using EXPLAIN ANALYZE. Tune HNSW parameters: increase ef_search at query time for better recall vs speed tradeoff. Document when to use HNSW (low latency) vs IVFFlat (lower memory).

You will implement similarity search using pgvector's distance operators. Write queries using <=> (cosine distance), <-> (L2 distance), and <#> (negative inner product) to find the top-K most similar vectors. Build a `search_similar()` Python function using asyncpg that accepts a query embedding and returns ranked results with similarity scores. Implement a distance threshold filter to exclude results below a minimum similarity. Compare search results across distance metrics on the same dataset to understand when each is appropriate.

You will implement hybrid search that combines vector similarity with metadata filtering. Create a GIN index on the metadata JSONB column for fast filtering. Write a query that filters by metadata (e.g., WHERE metadata->>'source' = 'docs' AND metadata->>'language' = 'en') before performing vector similarity search. Implement a `hybrid_search()` function that accepts both a query embedding and filter criteria, returning results sorted by similarity. Build a scoring function that combines vector similarity (0.7 weight) with full-text relevance (0.3 weight) using ts_rank.

You will build a SQLAlchemy model layer for pgvector operations. Create an `Embedding` ORM model using sqlalchemy-pgvector that maps the vector column to a Python list[float]. Build an `EmbeddingRepository` class with methods: `add_embedding(content, vector)`, `search(query_vector, top_k, filters)`, and `delete_by_source(source)`. Implement batch insertion using executemany for bulk loading embeddings. Configure the async engine with asyncpg and build FastAPI dependency injection for the repository.

You will build a complete semantic search API using FastAPI and pgvector. Create POST /search that accepts a query string, embeds it using OpenAI's text-embedding-ada-002 API via httpx, searches pgvector for similar documents, and returns ranked results with content, similarity score, and metadata. Add query parameters: top_k (default 10), min_score (default 0.7), and filter (optional JSON). Implement response caching with a cache key based on the query embedding hash. Add Prometheus metrics for search latency and result count.