This post is a part of the AI-200: Developing AI Cloud Solutions on Azure  Exam Prep Hub.
This topic falls under these sections:
Develop AI solutions by using Azure data management services (25–30%)
   --> Develop AI solutions by using Azure Database for PostgreSQL
      --> Run vector similarity search, including storing embeddings, semantic retrieval, and implementing retrieval-augmented generation (RAG) patterns by using metadata filter

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Overview

Modern AI applications frequently need to retrieve information based on meaning rather than exact keyword matches. A user might ask:

“How can I reset my company laptop password?”

while the stored document might say:

“Instructions for recovering forgotten corporate device credentials.”

A traditional SQL search based on exact text may not recognize these as closely related. Vector similarity search solves this problem by representing text or other data as numerical vectors called embeddings and finding records whose vectors are mathematically similar to the vector representing the user’s query.

For the AI-200 exam, you should understand how to use Azure Database for PostgreSQL with the pgvector extension to:

• Store embeddings.
• Generate and query embeddings.
• Perform vector similarity searches.
• Choose an appropriate vector distance function.
• Use approximate nearest-neighbor indexes.
• Perform semantic retrieval.
• Apply metadata filters to vector searches.
• Build retrieval-augmented generation (RAG) solutions.
• Understand the performance implications of filtered vector searches.

Azure Database for PostgreSQL supports vector-store scenarios in which embeddings are generated by an embedding model and stored alongside the original application data. Vector search can then retrieve semantically similar content rather than relying exclusively on exact text matching.

1. Understanding Embeddings

An embedding is a numerical representation of data that captures semantic characteristics of the original information.

For example, an embedding model might transform:

"How do I reset my password?"

into a vector resembling:

[0.021, -0.184, 0.731, 0.092, ...]

The vector typically contains many dimensions. The exact number of dimensions depends on the embedding model.

Two pieces of content with similar meaning should generally produce vectors that are closer together in vector space.

For example:

"How do I reset my password?"

and

"I forgot my password. How can I recover it?"

should have embeddings that are relatively close to each other.

Conversely:

"What is the weather forecast?"

should produce an embedding that is considerably different.

Important exam concept

An embedding is not the same thing as the original text.

A typical database record might contain:

id
title
content
category
tenant_id
created_at
embedding

The text fields remain useful for displaying the retrieved content, while the embedding column is used for semantic similarity calculations.

2. What Is Semantic Retrieval?

Traditional database retrieval often asks:

“Which records contain these words?”

Semantic retrieval asks:

“Which records have meaning that is most similar to this query?”

This distinction is extremely important for AI applications.

Consider a knowledge base containing:

Document 1:
How to change your corporate password
Document 2:
How to configure your VPN connection
Document 3:
How to request a new laptop

A user asks:

"I can't remember my login credentials."

A semantic search can identify Document 1 even though the exact words “can’t remember my login credentials” might not occur in the document.

The basic process is:

User question
      |
      v
Embedding model
      |
      v
Query embedding
      |
      v
Vector similarity search
      |
      v
Most similar documents
      |
      v
Retrieved context

This is one of the fundamental building blocks of RAG applications.

3. Using pgvector in Azure Database for PostgreSQL

Azure Database for PostgreSQL can use the pgvector PostgreSQL extension to store and search vector embeddings.

The extension provides a vector data type and vector similarity operators.

A simplified table might look like:

CREATE TABLE documents
(
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    category TEXT,
    tenant_id UUID,
    embedding VECTOR(1536)
);

The number 1536 represents the dimensionality of the embedding.

The dimension must correspond to the output dimensions of the embedding model being used.

Important

You cannot arbitrarily store a 1536-dimensional embedding in a column defined as:

VECTOR(768)

The dimensions must be compatible.

For indexed vector columns, the vector dimensionality must be explicitly defined. Current pgvector documentation also distinguishes between the ability to store vectors and the ability to index them; indexed vector columns have a dimensionality limit. (GitHub)

4. Generating and Storing Embeddings

The database generally does not create the semantic embedding itself. An application typically sends text to an embedding model and receives the vector.

A common architecture is:

Source document
      |
      v
Chunk document
      |
      v
Embedding model
      |
      v
Embedding vector
      |
      v
Azure Database for PostgreSQL

For example, an application might use an embedding service to transform:

"Azure Database for PostgreSQL supports flexible compute options."

into a vector.

The application then stores both the text and its vector:

INSERT INTO documents
(
    title,
    content,
    category,
    tenant_id,
    embedding
)
VALUES
(
    'PostgreSQL Compute',
    'Azure Database for PostgreSQL supports flexible compute options.',
    'database',
    '...',
    '[0.021,-0.184,0.731,...]'
);

The exact application code used to generate the embedding depends on the embedding provider and SDK.

5. Vector Distance and Similarity

Vector search requires a way to determine how close two vectors are.

Common distance measures include:

• Cosine distance
• Euclidean/L2 distance
• Inner product

The appropriate metric depends on the embedding model and application requirements.

Cosine similarity

Cosine similarity measures the angle between two vectors.

It is commonly used for semantic text embeddings.

A related pgvector distance operator is:

<=>

For example:

SELECT
    id,
    title,
    content,
    embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

Here $1 represents the query embedding.

The rows with the smallest distance are the most similar.

Exam tip

Do not confuse distance with similarity.

For distance-based queries:

smaller distance = more similar

For a similarity score where larger is better:

larger similarity = more similar

The pgvector operators and index operator classes must correspond to the distance function being used. For example, cosine searches use vector_cosine_ops. (GitHub)

6. Performing a Basic Semantic Search

Suppose the user asks:

"How can I recover my forgotten password?"

The application first generates an embedding:

query_embedding

It then passes that vector to PostgreSQL.

A typical query is:

SELECT
    id,
    title,
    content,
    embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

Conceptually:

Query embedding
      |
      +---- compare ----> Document embedding 1
      |
      +---- compare ----> Document embedding 2
      |
      +---- compare ----> Document embedding 3
      |
      +---- compare ----> ...
      |
      v
Rank by distance
      |
      v
Return top K

The LIMIT value determines how many candidates are returned.

7. Exact vs. Approximate Vector Search

Without an approximate vector index, pgvector can perform an exact nearest-neighbor search.

This provides very high recall because the database compares the query against all applicable vectors.

However, searching a very large collection this way can become expensive.

For large datasets, approximate nearest-neighbor (ANN) indexes can significantly improve search performance by reducing the amount of data that must be examined.

Common pgvector index types include:

• HNSW
• IVFFlat
• DiskANN in supported Azure Database for PostgreSQL configurations.

HNSW generally provides a strong speed/recall tradeoff but requires more memory and typically takes longer to build than IVFFlat. IVFFlat generally uses less memory and builds faster, but its search quality/performance tradeoff differs. (GitHub)

8. HNSW

Hierarchical Navigable Small World (HNSW) is a graph-based approximate nearest-neighbor algorithm.

A simplified conceptual representation is:

             A -------- B
            / \        / \
           C   D ----- E   F
            \           /
             G -------- H

The index creates connections between vectors so that the search can navigate efficiently through the vector space.

A cosine HNSW index can be created with:

CREATE INDEX documents_embedding_idx
ON documents
USING hnsw (embedding vector_cosine_ops);

HNSW has several important characteristics:

• Strong query performance.
• Good speed/recall tradeoff.
• Higher memory requirements than IVFFlat.
• More expensive index construction.
• No training phase is required before creating the index.

HNSW parameters such as m and ef_construction influence index construction and recall/performance tradeoffs. (GitHub)

9. IVFFlat

IVFFlat uses clusters, or lists, to reduce the amount of the vector space searched.

A simplified representation is:

Cluster 1       Cluster 2       Cluster 3
  A B C           D E F           G H I
  J K             L M             N O

The query is compared against cluster centers to determine which lists are most likely to contain the nearest vectors.

An example index is:

CREATE INDEX documents_embedding_idx
ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

The lists parameter controls the number of clusters.

At query time, probes controls how many lists are searched.

Increasing the number of probes generally improves recall but increases search work.

Important exam distinction

IVFFlat requires a training-like clustering step when building the index.

Therefore, it is generally preferable to load representative data before creating an IVFFlat index.

HNSW does not require this training step.

10. Choosing Between HNSW and IVFFlat

A useful exam-oriented comparison is:

There is no universal “best” index.

The correct choice depends on:

• Dataset size.
• Query volume.
• Latency requirements.
• Recall requirements.
• Memory availability.
• Data-change patterns.
• Filtering requirements.

11. Metadata Filtering

Real-world AI applications rarely search the entire vector database.

They often need to restrict the search based on metadata.

For example:

tenant_id = 42
document_type = "policy"
language = "en"
department = "finance"

A vector search might therefore look like:

SELECT
    id,
    title,
    content,
    embedding <=> $1 AS distance
FROM documents
WHERE tenant_id = $2
  AND category = $3
ORDER BY embedding <=> $1
LIMIT 5;

This combines:

1. Semantic similarity.
2. Traditional relational filtering.

This is extremely important for enterprise RAG systems.

12. Why Metadata Filtering Matters

Suppose a company has documents for 10,000 customers.

A user from Customer A should not retrieve documents belonging to Customer B.

A metadata filter such as:

WHERE tenant_id = $2

can restrict the search to the correct tenant.

Other examples include:

WHERE department = 'Finance'

or:

WHERE document_type = 'Policy'

or:

WHERE language = 'en'

or:

WHERE created_at >= CURRENT_DATE - INTERVAL '1 year'

Metadata filters are therefore useful for:

• Multi-tenant applications.
• Security boundaries.
• Content categories.
• Geographic restrictions.
• Document types.
• Languages.
• Date ranges.
• Application-specific classifications.

13. Indexing Metadata Columns

A common mistake is to focus entirely on the vector index.

The metadata columns used in filters may also need appropriate PostgreSQL indexes.

For example:

CREATE INDEX documents_tenant_idx
ON documents (tenant_id);

Or:

CREATE INDEX documents_category_idx
ON documents (category);

For frequently combined filters, a multicolumn index can sometimes be useful:

CREATE INDEX documents_tenant_category_idx
ON documents (tenant_id, category);

The appropriate indexing strategy depends on:

• Filter selectivity.
• Query frequency.
• Cardinality.
• Data distribution.
• Other query predicates.

pgvector’s filtering guidance specifically notes that conventional PostgreSQL indexes on filter columns can be useful, especially when a condition matches a relatively small percentage of rows. (GitHub)

14. An Important Performance Consideration with ANN and Filters

One of the most important concepts for the AI-200 exam is that approximate vector search and metadata filtering interact in ways that can affect recall.

Consider:

SELECT *
FROM documents
WHERE category = 'finance'
ORDER BY embedding <=> $1
LIMIT 10;

With an approximate vector index, the vector index may identify a set of candidate vectors and the metadata condition may eliminate some of those candidates.

If the filter is highly selective, there might not be enough matching candidates in the initial ANN search.

The result can be fewer than the requested number of rows or lower recall.

Current pgvector versions provide iterative index scans to help with filtered approximate searches. Iterative scans can continue scanning the approximate index when filtering removes too many candidates, subject to configured limits. (GitHub)

For HNSW, for example:

SET hnsw.iterative_scan = strict_order;

or:

SET hnsw.iterative_scan = relaxed_order;

strict_order maintains exact ordering by distance.

relaxed_order can improve recall/performance characteristics but may return results that are slightly out of distance order.

15. Partial Vector Indexes

If an application frequently searches a small number of specific metadata values, a partial HNSW index can sometimes be useful.

For example:

CREATE INDEX documents_finance_embedding_idx
ON documents
USING hnsw (embedding vector_cosine_ops)
WHERE category = 'finance';

This creates an index containing only rows matching the condition.

This can be useful when:

• The filter value is highly predictable.
• There are relatively few filter values.
• The filtered subset is important enough to justify a specialized index.

However, creating a separate partial index for every possible metadata value is usually not practical.

For many distinct values, partitioning or other approaches may be more appropriate. (GitHub)

16. RAG: Retrieval-Augmented Generation

Retrieval-Augmented Generation (RAG) combines information retrieval with generative AI.

Instead of asking an LLM to answer a question solely from its trained knowledge, the application retrieves relevant information from a trusted data source and provides that information to the model as context.

A simplified RAG architecture is:

                 User Question
                       |
                       v
                Embedding Model
                       |
                       v
                 Query Embedding
                       |
                       v
             PostgreSQL Vector Search
                       |
             +---------+---------+
             |                   |
          Metadata             Similarity
           filters              search
             |                   |
             +---------+---------+
                       |
                       v
                Relevant Chunks
                       |
                       v
                  Prompt + Context
                       |
                       v
                     LLM
                       |
                       v
                  Final Answer

17. Why RAG Uses Embeddings

Suppose a user asks:

"What expenses can I claim when traveling for business?"

The system generates an embedding for the question.

It then searches a database containing chunks of company policy documents.

The vector search might return:

Travel Policy - Airfare
Travel Policy - Hotels
Travel Policy - Meals
Travel Policy - Ground Transportation

The application combines these retrieved chunks with the user’s question and sends them to the LLM.

The LLM can then generate an answer based on the retrieved company policies.

This reduces the need for the model to rely solely on its pretrained knowledge.

18. Document Chunking

A RAG system normally should not store an entire large document as a single embedding.

Instead, documents are typically divided into smaller chunks.

For example:

Document
   |
   +-- Chunk 1
   +-- Chunk 2
   +-- Chunk 3
   +-- Chunk 4

Each chunk receives its own embedding.

The database might therefore contain:

document_id
chunk_id
content
metadata
embedding

Chunking improves retrieval because the system can retrieve the specific portion of a document that is relevant to the question rather than retrieving an entire large document.

The ideal chunk size depends on:

• Document structure.
• Embedding model.
• Query characteristics.
• Context-window constraints.
• Desired retrieval precision.

19. Metadata in a RAG System

Metadata is particularly important in production RAG systems.

A chunk might contain:

document_id
chunk_id
tenant_id
department
document_type
language
security_level
created_at
source_url
content
embedding

The embedding provides semantic information.

The metadata provides structured information.

Together they allow sophisticated retrieval.

For example:

SELECT
    id,
    content,
    embedding <=> $1 AS distance
FROM document_chunks
WHERE tenant_id = $2
  AND department = 'Finance'
  AND language = 'en'
ORDER BY embedding <=> $1
LIMIT 10;

The query is effectively saying:

Find the 10 semantically closest English Finance documents that belong to this tenant.

20. Metadata Filtering and Security

Metadata filters can be important for application-level data isolation, but developers should not automatically assume that a SQL WHERE clause alone constitutes a complete security architecture.

For example:

WHERE tenant_id = $1

is useful only if the application reliably supplies the correct tenant identifier and users cannot bypass the application’s data-access logic.

For highly sensitive multi-tenant systems, database permissions, authentication, authorization, network security, and potentially PostgreSQL row-level security should also be considered.

Exam mindset

When a question asks how to ensure users retrieve only documents belonging to their organization, look for a solution that combines:

• Tenant metadata.
• Appropriate query filtering.
• Proper application/database authorization.

21. A Complete RAG Retrieval Query

A simplified RAG retrieval query might look like:

SELECT
    id,
    document_id,
    content,
    source_url,
    embedding <=> $1 AS distance
FROM document_chunks
WHERE tenant_id = $2
  AND document_type = 'policy'
  AND language = 'en'
ORDER BY embedding <=> $1
LIMIT 5;

The application then takes the five retrieved chunks and constructs a prompt such as:

Use the following company policy information to answer the user's question.
Context:
[Retrieved chunk 1]
[Retrieved chunk 2]
[Retrieved chunk 3]
[Retrieved chunk 4]
[Retrieved chunk 5]
User question:
[Question]
Answer using the provided context.

The LLM generates the final response.

22. Why Retrieval Quality Matters in RAG

An LLM can only use the context it receives.

If the retrieval system returns irrelevant information, the model may produce an incorrect or poorly supported response.

Therefore, RAG quality depends on several stages:

Document quality
       |
       v
Chunking strategy
       |
       v
Embedding quality
       |
       v
Vector index
       |
       v
Similarity search
       |
       v
Metadata filtering
       |
       v
Ranking/retrieval quality
       |
       v
Prompt construction
       |
       v
LLM response

Improving the LLM alone does not necessarily fix a poor retrieval system.

23. Hybrid Retrieval

Vector search is powerful, but semantic similarity is not always sufficient.

Some searches depend on exact terms such as:

• Product IDs.
• Invoice numbers.
• Error codes.
• Names.
• Legal references.
• SKU numbers.

For these scenarios, a combination of traditional keyword search and vector search can be valuable.

This is often called hybrid retrieval.

Conceptually:

                    Query
                      |
          +-----------+-----------+
          |                       |
          v                       v
   Keyword Search          Vector Search
          |                       |
          +-----------+-----------+
                      |
                      v
                Combined Ranking
                      |
                      v
                Retrieved Context

The appropriate retrieval approach depends on the nature of the data and queries.

24. Query Performance Considerations

Vector similarity search can be computationally expensive, especially when comparing a query vector with a very large number of vectors.

Important performance considerations include:

Use an appropriate ANN index

HNSW, IVFFlat, and supported DiskANN configurations can dramatically reduce the work required for large vector datasets.

Index metadata columns

If metadata filters are used frequently, appropriate PostgreSQL indexes on those columns can improve filtering.

Tune ANN parameters

For HNSW, parameters such as:

m
ef_construction
ef_search

affect index construction, memory, recall, and query performance.

For IVFFlat, important parameters include:

lists
probes

Monitor recall

Optimizing solely for latency can reduce retrieval quality.

A good RAG system needs an appropriate balance between:

Latency
Cost
Recall
Precision

25. Query Plans and Performance Testing

Do not guess about performance.

Use PostgreSQL tools such as:

EXPLAIN

and:

EXPLAIN ANALYZE

to understand query execution.

For example:

EXPLAIN ANALYZE
SELECT
    id,
    content
FROM document_chunks
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 10;

This can help determine whether the expected indexes are being used and where query time is being spent.

For vector workloads, performance testing should use realistic:

• Dataset sizes.
• Vector dimensions.
• Query distributions.
• Metadata selectivity.
• Concurrent workloads.
• Recall requirements.

26. Compute and Memory Considerations

Vector workloads can be more resource-intensive than ordinary transactional queries.

Azure Database for PostgreSQL Flexible Server offers compute tiers including:

• Burstable
• General Purpose
• Memory Optimized

The number of vCores and available memory can be scaled to match workload requirements. Storage performance can also be configured independently within the capabilities of the selected storage option.

For vector workloads, additional compute and memory may be needed for:

• Vector index construction.
• ANN searches.
• Concurrent queries.
• Large working sets.
• Sorting and ranking.
• Data ingestion.
• Embedding generation pipelines.

Simply increasing storage capacity does not automatically solve a CPU- or memory-bound vector workload.

27. Storage Considerations

Vector embeddings consume storage.

The storage requirement depends on:

• Number of vectors.
• Vector dimensionality.
• Vector data type.
• Number of metadata columns.
• Number and size of indexes.
• Number of document chunks.

As a rough conceptual example, if a vector has 1,536 dimensions and uses four-byte floating-point values, the raw vector values alone require approximately:

1,536 × 4 bytes
= 6,144 bytes

per vector, before considering PostgreSQL row overhead and indexes.

For millions of vectors, this can become substantial.

Therefore:

More documents
      +
More chunks
      +
Higher dimensions
      +
More indexes
      =
More storage and memory requirements

28. Choosing the Right Embedding Model

The embedding model is a major component of retrieval quality.

Important considerations include:

• Embedding dimensionality.
• Semantic quality.
• Language support.
• Domain suitability.
• Cost.
• Latency.
• Maximum input size.

The vector column’s dimensionality must match the model output.

For example:

embedding VECTOR(1536)

is appropriate only when the embedding values being stored have 1,536 dimensions.

If the embedding model changes to one producing a different dimensionality, the database schema and indexing strategy may need to change.

29. Common AI-200 Exam Traps

Trap 1: Confusing embeddings with similarity scores

An embedding is the vector representation.

A similarity or distance calculation compares two embeddings.

Trap 2: Assuming vector search requires an ANN index

It does not.

pgvector can perform exact nearest-neighbor searches without an ANN index.

ANN indexes are primarily used to improve performance at the cost of some recall or approximation. (GitHub)

Trap 3: Assuming HNSW and IVFFlat are identical

They are not.

Remember:

HNSW → graph-based
IVFFlat → clustered/inverted lists

HNSW generally requires more memory and has a stronger speed/recall tradeoff, while IVFFlat generally builds faster and uses less memory.

Trap 4: Forgetting the distance operator class

If using cosine distance, the index should use the appropriate cosine operator class:

vector_cosine_ops

The index and query need to use compatible distance semantics. (GitHub)

Trap 5: Ignoring metadata filters

A vector search that returns the most semantically similar documents is not necessarily sufficient for an enterprise application.

The search may also need:

WHERE tenant_id = ...

or:

WHERE department = ...

Trap 6: Assuming ANN filtering always returns K perfect matches

Approximate indexes can return fewer matching results when metadata filtering removes many candidates.

Iterative index scans can help compensate by allowing additional index scanning. (GitHub)

Trap 7: Using storage scaling to solve every performance problem

If the workload is CPU- or memory-bound, simply adding storage may not solve the problem.

Compute, memory, storage IOPS, throughput, indexes, query design, and connection behavior all need to be considered.

Trap 8: Assuming more retrieved documents are always better

Retrieving 100 irrelevant chunks can be worse than retrieving five highly relevant chunks.

RAG systems should balance:

Recall
Precision
Latency
Context size
Cost

30. AI-200 Quick Review

Before taking the exam, make sure you can explain the following:

Embeddings

• What an embedding is.
• Why embeddings enable semantic search.
• Why embedding dimensions matter.
• How embeddings are stored in PostgreSQL.

pgvector

• What the vector type does.
• How vector distance operators work.
• How to perform nearest-neighbor searches.
• How to create vector indexes.

ANN indexes

Know the differences between:

Exact search
HNSW
IVFFlat
DiskANN

Metadata filtering

Understand how to combine:

WHERE ...
ORDER BY embedding <=> query_vector
LIMIT ...

and why filtering can affect ANN search results.

RAG

Understand:

Question
   ↓
Embedding
   ↓
Vector search
   ↓
Metadata filtering
   ↓
Relevant chunks
   ↓
Prompt
   ↓
LLM
   ↓
Answer

Performance

Know how:

• Compute.
• Memory.
• Storage.
• Indexes.
• ANN parameters.
• Filter selectivity.
• Query plans.

can affect vector-search performance.

Practice Exam Questions

Question 1

An AI application stores support documents in Azure Database for PostgreSQL. Each document has a VECTOR(1536) embedding. A user submits a question, and the application generates a 1,536-dimensional query embedding.

Which SQL pattern should the application use to retrieve the most semantically similar documents when using cosine distance?

A.

SELECT *
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

B.

SELECT *
FROM documents
ORDER BY embedding = $1
LIMIT 5;

C.

SELECT *
FROM documents
WHERE embedding LIKE $1
LIMIT 5;

D.

SELECT *
FROM documents
ORDER BY embedding + $1
LIMIT 5;

Correct answer: A

Explanation

The pgvector <=> operator represents cosine distance. Ordering by this value in ascending order returns the vectors closest to the query vector.

The other operators do not perform cosine-distance vector similarity searches.

Question 2

A development team is building a RAG application. Documents belong to different customers, and each document contains a tenant_id column. A user from tenant 100 submits a question.

Which approach best prevents the vector retrieval query from returning documents belonging to other tenants?

A. Increase the HNSW ef_search value.

B. Add a tenant predicate to the retrieval query.

C. Increase the vector dimensionality.

D. Replace the vector index with an IVFFlat index.

Correct answer: B

Explanation

The retrieval query should include a metadata predicate such as:

WHERE tenant_id = 100

The vector index determines semantic proximity, while the metadata predicate restricts which records are eligible for retrieval.

Changing ANN parameters or vector dimensionality does not enforce tenant isolation.

In a production application, this should be combined with appropriate authorization and database security controls rather than relying solely on application behavior.

Question 3

A PostgreSQL developer needs an approximate nearest-neighbor index that generally provides strong query performance and recall but is willing to use more memory and spend more time building the index.

Which index is the best choice?

A. B-tree

B. IVFFlat

C. HNSW

D. Hash

Correct answer: C

Explanation

HNSW is a graph-based approximate nearest-neighbor algorithm. Compared with IVFFlat, it generally provides a stronger speed/recall tradeoff but requires more memory and has higher index-build costs.

B-tree and hash indexes are traditional PostgreSQL indexes and are not substitutes for an ANN vector index.

Question 4

An application uses an IVFFlat index for vector similarity searches. Which parameter determines how many inverted lists are examined during a search?

A. m

B. ef_construction

C. ef_search

D. probes

Correct answer: D

Explanation

For IVFFlat:

lists = number of index lists/clusters
probes = number of lists examined during a search

Increasing probes generally increases search work and can improve recall.

m, ef_construction, and ef_search are associated with HNSW.

Question 5

A RAG application stores each document as a single very large embedding. Retrieval frequently returns entire documents even though only small portions are relevant to the user’s question.

What is the best architectural improvement?

A. Split documents into smaller chunks and generate an embedding for each chunk.

B. Increase the number of PostgreSQL connections.

C. Remove all metadata from the documents.

D. Replace the embedding column with a Boolean column.

Correct answer: A

Explanation

Chunking allows the retrieval system to identify smaller, more relevant portions of documents.

A typical RAG pipeline is:

Document
   ↓
Chunks
   ↓
Embedding per chunk
   ↓
Vector search
   ↓
Relevant chunks

This generally provides more precise context to the generative model.

Question 6

A developer runs the following query against a large vector table:

SELECT *
FROM documents
WHERE category = 'finance'
ORDER BY embedding <=> $1
LIMIT 10;

The query uses an HNSW index, but sometimes returns only three rows even though thousands of finance documents exist.

What is a likely explanation?

A. PostgreSQL cannot use WHERE clauses with vector searches.

B. HNSW only supports integer vectors.

C. Approximate vector candidates can be filtered after the ANN scan, leaving fewer qualifying rows.

D. Cosine distance always returns exactly three rows.

Correct answer: C

Explanation

With approximate vector indexes, filtering can remove candidate rows after the ANN index has identified them. If the filter is selective, the initial candidate set may not contain enough qualifying rows.

Modern pgvector versions support iterative index scans that can continue scanning for additional candidates when necessary. (GitHub)

Question 7

A developer wants to perform a cosine-distance search using an HNSW index on a PostgreSQL vector column.

Which index definition is appropriate?

A.

CREATE INDEX idx
ON documents
USING hnsw (embedding vector_cosine_ops);

B.

CREATE INDEX idx
ON documents
USING hnsw (embedding vector_text_ops);

C.

CREATE INDEX idx
ON documents
USING btree (embedding vector_cosine_ops);

D.

CREATE INDEX idx
ON documents
USING hash (embedding vector_cosine_ops);

Correct answer: A

Explanation

For cosine-distance vector searches, the HNSW index should use:

vector_cosine_ops

The access method and operator class need to correspond to the vector search being performed. (GitHub)

Question 8

A RAG application needs to retrieve documents that are semantically similar to a question, but only from English Finance policy documents belonging to the current customer.

Which query best represents this requirement?

A.

SELECT *
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;

B.

SELECT *
FROM documents
WHERE language = 'en'
LIMIT 10;

C.

SELECT *
FROM documents
WHERE department = 'Finance'
LIMIT 10;

D.

SELECT *
FROM documents
WHERE tenant_id = $2
  AND language = 'en'
  AND department = 'Finance'
  AND document_type = 'policy'
ORDER BY embedding <=> $1
LIMIT 10;

Correct answer: D

Explanation

This query combines:

• Tenant filtering.
• Language filtering.
• Department filtering.
• Document-type filtering.
• Vector similarity ranking.

That is a typical metadata-filtered semantic retrieval query for a RAG application.

Question 9

A development team is deciding between HNSW and IVFFlat for a large vector dataset.

Which statement is correct?

A. IVFFlat always provides better recall and faster queries than HNSW.

B. HNSW generally uses more memory and takes longer to build, but provides a strong speed/recall tradeoff.

C. HNSW requires k-means training before the index can be created.

D. Neither HNSW nor IVFFlat supports approximate nearest-neighbor searches.

Correct answer: B

Explanation

HNSW generally requires more memory and has higher build costs than IVFFlat, but it often provides a better query speed/recall tradeoff.

IVFFlat uses clustering and has tunable lists and probes parameters.

HNSW does not require the training phase used by IVFFlat. (GitHub)

Question 10

A company has a RAG application with millions of document chunks. Vector searches are becoming slow, and the database shows high CPU utilization. The application currently performs exact nearest-neighbor searches without an approximate vector index.

What is the most appropriate first optimization to investigate?

A. Remove the embeddings from the database.

B. Convert all embeddings to strings.

C. Evaluate an appropriate approximate nearest-neighbor index such as HNSW or IVFFlat.

D. Increase the number of document chunks returned from 10 to 1,000.

Correct answer: C

Explanation

Exact nearest-neighbor searches can require comparisons against a large portion of the dataset. For sufficiently large vector collections, an ANN index such as HNSW or IVFFlat can substantially reduce search work.

The appropriate index should be selected and tuned based on the workload’s latency, recall, memory, and maintenance requirements.

Increasing the number of returned chunks would generally make the workload more expensive rather than solving the underlying problem.

Final Exam Takeaways

For this AI-200 topic, remember these relationships:

Embedding
    ↓
Numerical representation of semantic meaning

Vector similarity
    ↓
Measures how close two embeddings are

pgvector
    ↓
PostgreSQL extension providing vector storage and search

HNSW
    ↓
Graph-based ANN
    ↓
Strong speed/recall tradeoff
    ↓
Higher memory/build cost

IVFFlat
    ↓
Cluster/list-based ANN
    ↓
Faster/lower-memory index construction
    ↓
Tune with lists and probes

Metadata filtering
    ↓
Restricts which records participate in retrieval
    ↓
Important for tenant, category, language, security, etc.

RAG
    ↓
Question
    ↓
Query embedding
    ↓
Filtered vector search
    ↓
Relevant chunks
    ↓
Prompt context
    ↓
LLM
    ↓
Grounded response

The most important design principle is that vector similarity provides semantic relevance, while metadata provides structured constraints. A production RAG solution generally needs both. Also remember that approximate vector indexes introduce a speed-versus-recall tradeoff, and highly selective metadata filters can affect the number and quality of candidates returned.

Go to the AI-200 Exam Prep Hub main page