AI-200 practice: Run vector similarity search, including storing embeddings, semantic retrieval, and implementing retrieval-augmented generation (RAG) patterns by using metadata filter

Question 1

Your RAG application must retrieve the top-k most similar chunks, but only from documents belonging to the current tenant. Testing shows that running the vector similarity search across the whole table and filtering by tenant afterward returns too few relevant results when a tenant has few documents relative to the whole table.

What should you do?

Accepted Answer

Apply the tenant_id predicate in the same SQL query with the vector ORDER BY and LIMIT k so ranking occurs within the tenant subset

Answer

Increase the global top-k value so that after client-side tenant filtering, enough tenant-specific chunks remain for small tenants

Answer

Create a separate embeddings table for each tenant so every similarity query runs against an isolated table that never needs filtering

Answer

Download the full embeddings table into application memory and run cosine similarity plus tenant filtering entirely outside PostgreSQL queries

Question 2

You write a hybrid pgvector query finding the 5 nearest chunks to a query embedding, restricted to a specific document_id, and need it to use both the vector index and a filter efficiently: ```sql SELECT id, text, embedding <=> :query_vector AS distance FROM chunks WHERE document_id = :doc_id ORDER BY embedding <=> :query_vector LIMIT 5; ``` EXPLAIN ANALYZE shows the planner is performing a full sequential scan despite an hnsw index existing on embedding, even though document_id has very low selectivity (only a few rows match). What is the most likely explanation and fix?

Accepted Answer

PostgreSQL's hnsw vector index scan does not natively support arbitrary pre-filtering by another column; when the filter is highly selective, the planner may reasonably choose a sequential scan (filter first, then compute exact distances on the small remaining set) over probing the vector index and filtering afterward — adding a supporting B-tree index on document_id and analyzing table statistics can help the planner choose a better combined plan.

Answer

The hnsw index must be dropped and recreated with a WHERE document_id IS NOT NULL clause to support filtered queries.

Answer

pgvector does not support any form of filtered vector search; filtering and vector search must always be run as two entirely separate queries.

Answer

Change LIMIT 5 to LIMIT 5 OFFSET 0 to force index usage.

Question 3

Your RAG pipeline needs to retrieve the top-k most relevant document chunks from PostgreSQL, but only within documents belonging to the current user's tenant. What is the recommended pattern?

Accepted Answer

Vector query plus WHERE tenant_id filter

Answer

Fetch all rows then filter tenant in app

Answer

Query separate databases per tenant in turn

Answer

Encode tenant_id inside the embedding vector

Question 4

You need to implement retrieval-augmented generation where document chunks are filtered by both semantic similarity and a numeric 'publish_date' metadata range in Azure Database for PostgreSQL. Which query pattern is correct?

Accepted Answer

ORDER BY embedding <=> vector with date WHERE

Answer

Union two unrelated queries with no join key

Answer

Full scan sorted by publish_date only, no vector

Answer

Store dates as text and LIKE for range filters

Question 5

In a Retrieval-Augmented Generation (RAG) pattern using Azure Database for PostgreSQL, which SQL operator calculates cosine distance between vectors in pgvector?

Accepted Answer

<=>

Answer

<->

Answer

<#>

Answer

<~>

Question 6

When implementing a RAG pattern with Azure Database for PostgreSQL, you want to filter results based on a user's ID to ensure they only search their own embeddings. Where should this filter be applied for maximum performance?

Accepted Answer

WHERE clause with the vector distance predicate

Answer

App code after fetching all global ANN match rows

Answer

Staging table loaded before running vector search

Answer

Export embeddings to files then filter them offline

Question 7

You want hybrid retrieval in PostgreSQL. Keyword search with `ts_rank` and vector search with `<=>` each return a top-50 list, and their scores are on different scales. How should you combine them into one final ranking?

Accepted Answer

Rank each list separately (ROW_NUMBER() in two CTEs), then combine with Reciprocal Rank Fusion: score = Σ 1/(k + rank), with k around 60

Answer

Add ts_rank to the cosine distance and sort by the sum

Answer

Return only the rows that appear in both lists, in keyword order

Answer

Multiply ts_rank by the cosine distance and sort in ascending order

Question 8

A RAG step should send the model only chunks with cosine **similarity** of at least 0.80 to the query, and at most 8 of them. Which pgvector query does this?

Accepted Answer

SELECT id FROM chunks WHERE 1 - (embedding <=> $1) >= 0.80 ORDER BY embedding <=> $1 LIMIT 8

Answer

SELECT id FROM chunks WHERE embedding <=> $1 >= 0.80 ORDER BY embedding <=> $1 LIMIT 8

Answer

SELECT id FROM chunks WHERE embedding <-> $1 <= 0.80 ORDER BY embedding <-> $1 DESC LIMIT 8

Answer

SELECT id FROM chunks WHERE (embedding <#> $1) >= 0.80 LIMIT 8

Question 9

You want PostgreSQL to create the query embedding itself by calling your Azure OpenAI deployment inside the retrieval query, for example `ORDER BY embedding <=> azure_openai.create_embeddings('text-embedding-3-small', $1)::vector`. Which two setup steps are needed? Select two.

Accepted Answer

Allow-list AZURE_AI in the azure.extensions server parameter and run CREATE EXTENSION azure_ai

Accepted Answer

Configure the endpoint and credentials, for example SELECT azure_ai.set_setting('azure_openai.endpoint', ...) and azure_openai.subscription_key (or managed identity auth where supported)

Answer

Install pg_cron so that it can call Azure OpenAI on a schedule

Answer

Enable pgvector only, because it calls embedding models itself

Answer

Create a Cosmos DB account to host the embedding model

Question 10

Every RAG query filters on one of five known `doc_type` values. Searches for the rare types (each under 1% of rows) often return fewer than k results through the shared HNSW index. The server runs an older pgvector version without iterative scans. What is the most effective indexing approach?

Accepted Answer

Create a partial HNSW index for each doc_type, for example CREATE INDEX ... USING hnsw (embedding vector_cosine_ops) WHERE doc_type = 'policy'

Answer

Raise hnsw.ef_search to 10000 for every query

Answer

Add doc_type to the HNSW index as a second column

Answer

Filter by doc_type in the application after fetching the top 1000 rows

11 questions