Domain 2 of 4 · Chapter 11 of 12

Run vector similarity search and RAG on PostgreSQL

Unlock the complete study guide + 1,040 practice questions across 16 full exams.

Bundled into the existing Developing AI Cloud Solutions on Azure premium course — no separate purchase.

14-day money-back guarantee — no questions asked.

Included in this chapter:

  • Writing the retrieval query
  • Distance, not score
  • Getting the embedding into the column
  • Generating embeddings inside the database
  • Filtering: predicates, joins and the post-filter cost
  • Hybrid search: fusing lexical and vector rankings
  • Reranking the shortlist
  • The whole round trip: chunk, embed, retrieve, ground
  • Exam-pattern recognition

Choosing the distance operator the query will use

Decision axis<-> Euclidean (L2) distance<=> cosine distance<#> negative inner product
What Microsoft says it computesThe straight-line distance between two vectors in the n-dimensional spaceDerived from the cosine of the angle between two vectors; Azure OpenAI embeddings rely on cosine similarity to compare documents and a queryThe inner product of the two vectors, returned negated so that smaller still means nearer
Operator class the index must be built withvector_l2_opsvector_cosine_opsvector_ip_ops
Function form of the same computationl2_distance(vector, vector)cosine_distance(vector, vector)inner_product(vector, vector)
Reading the value as a similarityNo conversion is published; rank on the distance itself1 - (embedding <=> query)(embedding <#> query) * -1

Decision tree

Fewer rows than the LIMIT?the plan still looks healthyPost-filter thinningbigger candidate list, iterative scansExact tokens being missed?product codes, error numbersAdd a lexical halftsvector plus GIN, fuse with RRFRight chunk, wrong position?retrieved but ranked too lowRerank the shortlistfull vectors or azure_ai.rank()Check the embedding contractsame model deployment, same dimension,operator matches the operator classyesnoyesnoyesno
Cheat sheet

Cheat sheet

  • CREATE EXTENSION vector adds the vector type; the column dimension must match the model
  • Insert embeddings by binding the vector as a parameter
  • Stored and query embeddings must come from the same model to be comparable
  • Generate embeddings in-database via the azure_ai extension
  • pgvector exposes <-> (L2), <=> (cosine), and <#> (negative inner product) operators
  • Semantic retrieval is ORDER BY embedding <=> $query LIMIT k returning payload columns
  • The index opclass must match the query's distance operator
  • Add a WHERE predicate on metadata columns to restrict semantic retrieval
  • A very selective filter can under-return from an ANN index, so raise search effort
  • The RAG pattern retrieves the top-k nearest chunks with their citation metadata to ground the LLM
  • Hybrid search fuses PostgreSQL full-text with vector similarity for better recall
  • Retrieved candidates can be re-ranked before grounding to sharpen the top-k

Unlock with Premium — includes all practice exams and the complete study guide.

Also tested in

References

  1. Vector search in Azure Database for PostgreSQL flexible server (pgvector)
  2. Create a semantic search with Azure OpenAI in Azure Database for PostgreSQL
  3. PostgreSQL: CREATE INDEX (access methods and operator classes)
  4. Improve accuracy with advanced RAG architectures: chunking, hybrid search, reranking
  5. Choose an Azure service for vector search (capability matrix) Well-Architected
  6. PostgreSQL: CREATE CAST (explicit, assignment, and implicit cast contexts)
  7. Generate embeddings for vector search in Azure AI Search
  8. Understand the RAG pattern with Azure Database for PostgreSQL
  9. Generate vector embeddings with Azure OpenAI in Azure Database for PostgreSQL
  10. PostgreSQL: Introduction to full text search (tsvector, tsquery, the @@ operator)
  11. PostgreSQL: Controlling text search (ranking with ts_rank and ts_rank_cd)
  12. PostgreSQL: Preferred index types for text search (GIN versus GiST)
  13. AI functions in the azure_ai extension (preview), including azure_ai.rank()