Category: Databases

AI-200, azure, Azure AI, Databases, Microsoft Certification August 15, 2026

Implement vector indexing to enable similarity search (AI-200 Exam Prep)

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%)
   --> Integrate Azure Managed Redis in AI solutions
      --> Implement vector indexing to enable similarity search

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

Vector similarity search is a foundational capability for modern AI applications. It allows an application to retrieve data based on semantic similarity rather than requiring an exact keyword match.

For the AI-200: Developing AI Cloud Solutions on Azure exam, you should understand how Azure Managed Redis can be used as a low-latency vector database, how vectors are stored and indexed, the difference between FLAT and HNSW indexing, how distance metrics affect similarity calculations, and how vector indexes are queried.

Azure Managed Redis provides vector search through the RediSearch module. Vector data can be stored in Redis hashes or JSON documents and indexed for similarity searches.

1. What Is Vector Similarity Search?

Traditional database searches generally look for exact or textual matches.

For example:

"How do I reset my password?"

A keyword-based search might look for documents containing:

password
reset
credentials
account

Vector search takes a different approach.

The text is converted into an embedding, which is a numerical representation of the semantic meaning of the text.

For example:

			
"How do I reset my password?"
        ↓
Embedding model
        ↓
[0.021, -0.134, 0.087, ..., 0.442]

		

A document such as:

“Steps for recovering your account credentials”

may have an embedding that is mathematically close to the query embedding even though the document does not contain the exact phrase “reset my password.”

This allows vector search to find semantically related information.

2. What Is an Embedding?

An embedding is a high-dimensional numerical representation of data.

Embeddings can represent:

Text
Documents
Images
Products
Audio
Other types of content

The embedding model transforms the original content into a vector.

For example:

			
Document
   ↓
Embedding model
   ↓
[0.12, -0.04, 0.81, 0.23, ...]

		

The number of dimensions depends on the embedding model.

Important exam concept

The vectors being indexed and the query vectors must be compatible.

In particular, the vector index configuration must match the characteristics of the embedding model, including:

Vector dimensions
Distance metric
Vector representation/type

Using inconsistent embedding models can produce poor or invalid search results.

3. Azure Managed Redis as a Vector Database

Azure Managed Redis is primarily known for high-performance in-memory data operations, but it can also support vector workloads.

With the appropriate Redis functionality enabled, it can:

Store embeddings.
Create vector indexes.
Search vectors.
Return the nearest vectors.
Combine vector searches with metadata filtering.

This makes Azure Managed Redis useful for applications such as:

Semantic search
Retrieval-augmented generation (RAG)
Recommendation systems
Semantic caching
Conversational memory
Document retrieval
Similarity matching

The major advantage is low-latency access, particularly when vector search is being performed alongside other Redis-based application data.

4. RediSearch and Vector Indexing

Azure Managed Redis uses the RediSearch functionality to provide vector search.

For Azure Managed Redis vector search, RediSearch must be enabled when the Redis instance is created. It cannot simply be added later to an existing instance.

Current Azure Managed Redis documentation identifies RediSearch support for:

Memory Optimized
Balanced
Compute Optimized

The Flash Optimized tier does not support RediSearch. Azure Managed Redis vector workloads also require the Enterprise clustering policy.

Exam tip

If a scenario says:

“An existing Azure Managed Redis instance does not have RediSearch enabled. The application now needs vector similarity search.”

The important consideration is that the required module must be enabled during provisioning. You should not assume that the module can simply be installed onto an existing Azure Managed Redis instance.

5. Storing Vectors in Redis

Azure Managed Redis supports storing vector data in Redis data structures such as:

Hashes
JSON documents

Hashes

Hashes are useful when the application has relatively straightforward fields.

Conceptually:

			
document:123
    title       = "Azure AI"
    category    = "AI"
    embedding   = [ ... ]

JSON

JSON can be useful when the application has more complex or nested document structures.

Conceptually:

			
{
    "id": "document-123",
    "title": "Azure AI",
    "category": "AI",
    "embedding": [ ... ],
    "metadata": {
        "author": "Norm",
        "year": 2026
    }
}

		

The choice between hashes and JSON depends on the application’s data model and how the data will be accessed.

Microsoft’s current guidance specifically identifies both hashes and JSON as supported approaches for vector storage.

6. Why Metadata Matters

A vector should generally not exist by itself.

Applications often store metadata alongside the vector, such as:

Document ID
Document title
Category
Source URL
Timestamp
Tenant ID
Author
Security/access-control information

For example:

			
Document:
    id = 1001
    title = "Azure Container Apps"
    category = "Azure"
    tenant = "Contoso"
    embedding = [...]

		

Metadata enables filtered vector search.

For example:

Find the 5 documents most similar to this question, but only search documents belonging to the Azure category.

Or:

Find similar documents that the current user is authorized to access.

This becomes particularly important in multi-tenant and RAG applications.

7. Vector Indexing Strategies

The two important vector indexing strategies you should know for AI-200 are:

Index	Description	Typical use
FLAT	Exact/brute-force search	Smaller datasets or maximum accuracy
HNSW	Approximate nearest-neighbor graph	Larger datasets and lower latency

Understanding the trade-off between these approaches is important for the exam.

8. FLAT Index

A FLAT index performs an exhaustive comparison.

Conceptually:

			
Query vector
     |
     +---- Compare with Vector 1
     +---- Compare with Vector 2
     +---- Compare with Vector 3
     +---- Compare with Vector 4
     +---- ...
     +---- Compare with Vector N

		

Every candidate vector is evaluated.

Advantages

Exact search
High recall
Straightforward behavior
Useful for relatively small datasets

Disadvantages

More computationally expensive as the dataset grows
Latency can increase with the number of vectors

FLAT is therefore appropriate when exhaustive accuracy is more important than minimizing search computation.

9. HNSW Index

HNSW stands for Hierarchical Navigable Small World.

Instead of comparing the query against every vector, HNSW organizes vectors into a graph that allows the search to navigate toward likely nearest neighbors.

Conceptually:

                 Vector A
                /        \
           Vector B     Vector C
             /             \
        Vector D           Vector E
             \             /
                Vector F

The actual structure is considerably more sophisticated, but the important idea is that the index provides an efficient path toward nearby vectors.

Advantages

Fast similarity searches
Well suited to larger datasets
Reduces the amount of computation required
Supports approximate nearest-neighbor search

Disadvantages

Search is approximate rather than exhaustive
Indexing requires additional resources
There is a trade-off between search speed, recall, and resource consumption

Microsoft identifies HNSW as a common choice for larger datasets where lower latency is more important than exhaustive precision.

10. FLAT vs. HNSW

A useful way to remember the difference is:

FLAT = accuracy through exhaustive search

HNSW = speed through approximate search

For example:

Scenario A

You have 10,000 vectors and require exact results.

FLAT may be appropriate.

Scenario B

You have millions of vectors and require very low search latency.

HNSW is generally a better candidate.

The correct choice depends on:

Dataset size
Required latency
Accuracy/recall requirements
Available resources
Workload characteristics

11. Distance and Similarity Metrics

Once vectors are indexed, Redis needs a way to determine how close two vectors are.

Common metrics include:

Cosine

Cosine similarity measures the angle between vectors.

It is commonly used for text embeddings.

Conceptually:

			
Vector A
   ↘
    angle
   ↗
Vector B

		

The smaller the angular difference, the more semantically similar the vectors generally are.

Euclidean / L2

Euclidean distance measures the straight-line distance between vectors.

			
A ●----------------● B
       distance

A smaller distance indicates greater similarity.

Inner Product

Inner product, also called dot product in many contexts, can be used for similarity/ranking depending on how embeddings are generated and normalized.

Azure Managed Redis vector search supports metrics including:

L2
COSINE
IP

The appropriate metric depends on the embedding model and how its vectors are represented.

12. KNN Search

A common vector-search operation is K-nearest neighbors (KNN).

Suppose the application asks:

“Which five documents are most similar to this question?”

The application sets:

K = 5

The vector search returns the five nearest vectors according to the selected similarity/distance metric.

Conceptually:

			
Query
  |
  +-- Result 1  ← most similar
  +-- Result 2
  +-- Result 3
  +-- Result 4
  +-- Result 5

		

KNN is especially useful in:

Semantic search
Recommendation systems
RAG
Similarity matching

Azure Managed Redis supports KNN and vector range queries.

13. Approximate Nearest Neighbor Search

ANN, or approximate nearest neighbor search, attempts to find vectors that are very close to the query without necessarily exhaustively comparing every vector.

This can dramatically reduce search latency and computational requirements.

The trade-off is:

You may sacrifice some recall for significantly better performance.

HNSW is an example of an indexing strategy commonly used to enable efficient approximate nearest-neighbor searches.

14. Vector Index Configuration

When creating a vector index, think about the following characteristics:

1. Data structure

Will the vectors be stored in:

Hashes?
JSON documents?

2. Vector field

Which property contains the embedding?

For example:

embedding

3. Vector dimensions

The index must accommodate the dimensionality of the embeddings.

4. Distance metric

Choose the appropriate metric, such as:

			
COSINE
L2
IP

5. Index algorithm

Choose between:

			
FLAT
HNSW

6. Metadata fields

Determine which fields need to support filtering.

15. Example Conceptual Data Model

Consider a RAG application containing technical documentation.

A Redis record might conceptually look like:

			
document:1001
title:
"Azure Container Apps"
category:
"Containers"
source:
"https://example.com/container-apps"
tenant:
"Contoso"
embedding:
[0.012, -0.081, 0.224, ...]

		

The application can then:

Receive a user’s question.
Generate an embedding for the question.
Submit the query vector to Redis.
Search the vector index.
Retrieve the closest documents.
Apply metadata/security filtering.
Send the retrieved content to the LLM.
Generate a grounded response.

16. Vector Search and RAG

Vector indexing is especially important for Retrieval-Augmented Generation (RAG).

A typical RAG pipeline looks like this:

                DOCUMENT INGESTION
                       |
                       v
                 Split documents
                       |
                       v
                 Generate embeddings
                       |
                       v
             Store vectors + metadata
                       |
                       v
                Create vector index
                       |
                       |
             USER QUERY
                  |
                  v
           Generate query embedding
                  |
                  v
          Vector similarity search
                  |
                  v
          Apply metadata/security filters
                  |
                  v
             Retrieve top K
                  |
                  v
          Add retrieved context
                  |
                  v
                   LLM
                  |
                  v
              Final response

The vector database does not generate the final natural-language response.

Its role is primarily retrieval.

17. Why Metadata Filtering Is Important in RAG

Suppose a company has documents belonging to multiple departments:

			
HR
Finance
Engineering
Legal

A user asks:

“What is our reimbursement policy?”

A pure vector search could potentially retrieve semantically relevant documents from multiple departments.

Instead, the application can use metadata:

department = "Finance"

or, more importantly:

tenant_id = current_user.tenant_id

and possibly:

access_level <= current_user.access_level

This helps ensure that retrieval is both relevant and appropriately scoped.

For RAG, metadata can also provide information needed to identify the source of retrieved content.

18. Hybrid Search

Vector search does not necessarily need to operate alone.

Azure Managed Redis can combine vector search with other search/filter capabilities, including:

Numeric filters
Text filters
Geospatial filters
Prefix matching
Fuzzy matching
Boolean conditions

This enables hybrid retrieval.

For example:

Find products semantically similar to this product, but only return products where category = 'laptop' and price < 1500.

The vector component handles semantic similarity while the metadata/filter component constrains the candidate results.

19. Choosing FLAT or HNSW

For the exam, think about the decision this way:

Choose FLAT when:

The dataset is relatively small.
Exact similarity results are important.
Exhaustive comparison is acceptable.
Search latency is less critical.

Choose HNSW when:

The dataset is large.
Low latency is important.
Approximate results are acceptable.
High-throughput vector search is required.

Do not assume that HNSW is always better. It is a trade-off.

20. Important Exam Considerations

When answering AI-200 questions involving Azure Managed Redis vector indexing, pay attention to these details.

RediSearch must be available

Vector search depends on the RediSearch functionality.

Vector indexing is different from ordinary Redis keys

A Redis key/value operation retrieves a known key. Vector indexing enables similarity-based retrieval.

HNSW is approximate

It is designed to improve search performance and reduce computation compared with exhaustive search.

FLAT is exhaustive

It compares the query against the indexed vectors rather than navigating an approximate graph.

Metadata is valuable

Metadata enables filtering and allows applications to associate retrieved vectors with meaningful application information.

Embedding compatibility matters

The query embedding and indexed embeddings need to be compatible with the index configuration.

Vector search is not generation

Redis retrieves relevant information. An LLM can subsequently use that information to generate a response in a RAG architecture.

21. Common Exam Traps

Trap 1: “HNSW always provides exact results”

Incorrect.

HNSW is an approximate nearest-neighbor approach.

Trap 2: “FLAT is always the best option”

Incorrect.

FLAT can become computationally expensive as the number of vectors increases.

Trap 3: “Vector search replaces metadata filtering”

Incorrect.

Vector similarity determines semantic closeness. Metadata filters can constrain the search to the appropriate subset.

Trap 4: “The vector database generates the answer”

Incorrect.

The vector database retrieves relevant information. An LLM can use that retrieved information to generate the final response.

Trap 5: “Any embedding can be searched against any vector index”

Incorrect.

The embedding dimensions, representation, and similarity configuration need to be compatible.

Trap 6: “RediSearch can always be enabled later”

Incorrect for Azure Managed Redis provisioning.

Current Azure Managed Redis guidance states that required modules such as RediSearch need to be enabled when the instance is created.

22. AI-200 Exam Takeaways

Remember these concepts:

Concept	What to remember
Embedding	Numerical representation of semantic meaning
Vector	High-dimensional numerical representation
Vector index	Makes similarity searches efficient
RediSearch	Provides vector search capabilities
FLAT	Exact/exhaustive search
HNSW	Approximate nearest-neighbor search
KNN	Retrieves the K most similar vectors
ANN	Faster approximate similarity search
COSINE	Common metric for text embeddings
L2	Euclidean distance
IP	Inner-product similarity
Metadata	Enables filtering and contextual information
RAG	Retrieve relevant content before LLM generation
Hash	Redis structure suitable for vector + fields
JSON	Redis structure suitable for structured/nested vector records

Practice Exam Questions

Question 1

An AI application uses Azure Managed Redis to store 2 million document embeddings. The application requires very low-latency similarity searches and can tolerate a small reduction in recall in exchange for improved performance.

Which vector indexing strategy is most appropriate?

A. FLAT

B. HNSW

C. Hash-only retrieval

D. Key-based lookup

Answer: B

Explanation

HNSW is designed for approximate nearest-neighbor searches and is generally appropriate for larger datasets where low latency is important. It avoids exhaustive comparison with every vector and therefore can substantially reduce search work.

FLAT performs exhaustive searches and can become increasingly expensive as the number of vectors grows. A hash-only retrieval or normal key lookup cannot perform semantic vector similarity search.

Question 2

A development team has 5,000 product embeddings and requires exhaustive similarity comparisons because search accuracy is more important than minimizing computational cost.

Which indexing strategy should the team consider?

A. HNSW

B. FLAT

C. Boolean indexing

D. Prefix indexing

Answer: B

Explanation

FLAT performs an exhaustive comparison of the query vector against the indexed vectors. It is appropriate when the dataset is relatively small or when exhaustive accuracy is preferred.

HNSW is designed for approximate nearest-neighbor searches and trades some recall for performance.

Question 3

An application generates an embedding for a user’s question and wants to retrieve the five most semantically similar documents from Azure Managed Redis.

Which concept describes this operation?

A. Cache invalidation

B. Key-based lookup

C. K-nearest neighbors

D. Transaction processing

Answer: C

Explanation

K-nearest neighbors (KNN) retrieves the top K vectors that are closest to the query vector according to the configured similarity/distance metric.

With K = 5, the application requests the five nearest vectors.

Question 4

An organization stores document embeddings in Azure Managed Redis. Each document also contains a tenantId field. A RAG application must ensure that users retrieve documents only from their own tenant.

What is the primary purpose of the tenantId metadata?

A. Increasing the dimensionality of embeddings

B. Changing the embedding model

C. Replacing the vector index

D. Restricting vector retrieval to the appropriate tenant

Answer: D

Explanation

Metadata such as tenantId can be used to filter vector-search results so that retrieval is restricted to the appropriate tenant.

This is particularly important in multitenant AI and RAG applications where semantic similarity alone does not provide an authorization boundary.

Question 5

A team creates an Azure Managed Redis instance and later decides that it needs vector search. The instance was created without the required RediSearch functionality.

What should the team understand?

A. RediSearch must be enabled during instance provisioning

B. Vector search automatically becomes available when the first vector is stored

C. FLAT indexing eliminates the need for RediSearch

D. KNN automatically installs the required module

Answer: A

Explanation

Azure Managed Redis vector search requires RediSearch, and current Azure Managed Redis guidance states that the module must be enabled when the instance is created. Modules cannot simply be added to an existing instance afterward.

Question 6

An application uses text embeddings generated by an embedding model. Which consideration is most important when configuring the vector index?

A. The Redis key must contain the user’s password

B. The vector index must be compatible with the embedding dimensions and similarity configuration

C. Every embedding must be stored as plain text

D. The application must use FLAT regardless of dataset size

Answer: B

Explanation

The vector index needs to be configured consistently with the embeddings being generated. In particular, vector dimensions and the selected similarity metric need to be compatible with the embedding model and its vector representation.

Using an incompatible vector configuration can cause errors or poor search results.

Question 7

A RAG application retrieves documents from Azure Managed Redis using vector similarity search. What should happen after relevant documents are retrieved?

A. Redis automatically writes the final natural-language answer

B. The vector index generates a new embedding for every retrieved document

C. The retrieved content can be supplied to an LLM as grounding/context

D. The vectors are converted into relational database tables

Answer: C

Explanation

In a RAG architecture, vector search is the retrieval stage.

The application retrieves relevant content and supplies it as context to an LLM. The LLM then uses that context to generate the response.

The vector database does not itself generate the final natural-language answer.

Question 8

A team wants to find products semantically similar to a user’s query but only within the Laptops category.

Which approach best satisfies this requirement?

A. Perform only an exact key lookup

B. Delete all vectors outside the Laptops category

C. Use only the product title as the vector

D. Combine vector similarity search with a metadata filter

Answer: D

Explanation

Vector similarity identifies semantically similar products, while the metadata filter restricts results to the required category.

This is an example of combining vector retrieval with structured filtering.

Question 9

Which statement best describes the primary difference between FLAT and HNSW vector indexes?

A. FLAT performs exhaustive comparison, while HNSW uses an approximate graph-based approach

B. FLAT stores JSON while HNSW stores hashes

C. FLAT supports text only while HNSW supports vectors only

D. FLAT is used for metadata and HNSW is used for authentication

Answer: A

Explanation

The fundamental distinction is the search strategy.

FLAT performs exhaustive comparisons, while HNSW uses a graph-based approximate nearest-neighbor approach designed to improve search performance at scale.

The distinction is not based on whether the data is stored as hashes or JSON.

Question 10

An application uses Azure Managed Redis for vector similarity search. Which combination represents a valid vector-search design?

A. Store only Redis keys and perform exact string comparisons

B. Store embeddings, create a vector index, and query using a compatible similarity metric

C. Store embeddings only in application memory and use Redis for authentication

D. Store embeddings as passwords and use expiration to determine similarity

Answer: B

Explanation

A vector-search implementation requires embeddings to be stored, a compatible vector index to be created, and queries to use an appropriate similarity/distance configuration.

The other choices describe unrelated Redis capabilities and do not implement vector similarity search.

Final Exam Review

For “Implement vector indexing to enable similarity search”, the most important mental model is:

                 CONTENT
                    |
                    v
             Embedding model
                    |
                    v
              Vector embedding
                    |
                    v
       +-------------------------+
       |     Azure Managed       |
       |         Redis           |
       |                         |
       | Vector + metadata       |
       |         ↓               |
       |    Vector index         |
       |    /         \          |
       | FLAT          HNSW      |
       +-------------------------+
                    ^
                    |
             Query embedding
                    |
                    v
             Similarity search
                    |
                    v
              Top-K results
                    |
                    v
             RAG / Application

If you remember only a handful of things for the exam, remember these:

RediSearch provides vector-search capabilities in Azure Managed Redis.
FLAT = exhaustive/exact search.
HNSW = approximate nearest-neighbor search optimized for performance.
KNN returns the top K similar vectors.
Cosine, L2, and inner product are important similarity/distance metrics.
Vectors should be compatible with the embedding model and index configuration.
Store metadata alongside vectors when applications need filtering or source information.
Vector search retrieves information; an LLM can use that information for RAG generation.
Vector search requires appropriate Redis provisioning, including RediSearch and supported configuration.
The right index is determined by dataset size, latency requirements, accuracy/recall requirements, and resource considerations.

Go to the AI-200 Exam Prep Hub main page

AI-200, azure, Databases, Microsoft Certification August 15, 2026

Implement indexing strategies, including optimizing query latency and reducing pgvector compute overhead (AI-200 Exam Prep)

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
      --> Implement indexing strategies, including optimizing query latency and reducing pgvector compute overhead

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

Azure Database for PostgreSQL is a managed PostgreSQL service that can support both traditional relational workloads and AI workloads involving vector embeddings. For AI-200, developers should understand how to design indexes and tune queries so that applications can retrieve data efficiently while minimizing CPU, memory, I/O, and overall compute consumption.

This topic has two closely related areas:

Traditional PostgreSQL indexing and query optimization
pgvector indexing and vector-search optimization

The key objective is not simply to “add indexes.” An index can dramatically improve read performance, but indexes also consume storage and require additional work when rows are inserted, updated, or deleted. A good design balances query latency, workload characteristics, storage, and maintenance overhead.

For vector workloads, there is an additional tradeoff: approximate nearest-neighbor (ANN) indexes can substantially reduce the amount of computation required for similarity searches, but they can trade some recall for performance.

1. Why Indexing Matters

Consider a table containing several million documents:

			
CREATE TABLE documents
(
    id          BIGINT PRIMARY KEY,
    tenant_id   BIGINT,
    category    VARCHAR(100),
    title       TEXT,
    content     TEXT,
    created_at  TIMESTAMPTZ
);

		

Suppose the application frequently executes:

			
SELECT *
FROM documents
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 20;

		

Without an appropriate index, PostgreSQL may need to scan a large portion of the table and then sort the results.

An index such as:

			
CREATE INDEX ix_documents_tenant_created
ON documents (tenant_id, created_at DESC);

can allow PostgreSQL to locate the relevant rows much more efficiently.

The important exam concept is:

Indexes are designed around query patterns, not simply around individual columns.

2. Common PostgreSQL Index Types

PostgreSQL supports several index types, each designed for different access patterns.

B-tree

B-tree is the default and most commonly used index type.

It is appropriate for:

equality comparisons
range comparisons
sorting
ORDER BY
many JOIN conditions
MIN() and MAX() patterns in appropriate circumstances

Examples:

			
CREATE INDEX ix_customer_email
ON customers (email);

and:

			
CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date);

B-tree indexes are generally the first choice for conventional relational queries.

Azure’s autonomous tuning functionality currently provides recommendations for B-tree indexes for conventional query workloads.

Hash

Hash indexes are designed primarily for equality comparisons.

For example:

WHERE customer_id = 1001

However, B-tree indexes are generally more broadly useful because they support both equality and range operations.

GIN

GIN indexes are useful for data structures containing multiple values, such as:

arrays
JSONB
full-text-search-related workloads

For example, if a JSONB column is frequently searched by contained values, a GIN index may be appropriate.

GiST

GiST is a generalized indexing framework used for several specialized data types and search scenarios.

It can be useful for:

geometric data
range types
specialized extensions

It is also relevant to some vector-search scenarios in the broader PostgreSQL ecosystem, although the AI-200 pgvector focus is primarily on ANN index strategies such as IVFFlat, HNSW, and DiskANN.

3. Index Columns Based on Query Patterns

A common mistake is creating an index on every column that appears in a WHERE clause.

Instead, examine the actual query workload.

Suppose the application frequently executes:

			
SELECT *
FROM orders
WHERE customer_id = 100
  AND order_date >= '2026-01-01'
ORDER BY order_date DESC;

		

A composite index can be considerably more useful than separate indexes:

			
CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date DESC);

This allows PostgreSQL to efficiently narrow the rows by customer_id and then use the index ordering for order_date.

4. Composite Index Column Order Matters

Consider:

			
CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date);

This index is particularly useful for queries such as:

WHERE customer_id = 100

and:

			
WHERE customer_id = 100
  AND order_date >= '2026-01-01'

But it is not necessarily an efficient substitute for an index beginning with order_date when the query only searches by:

WHERE order_date >= '2026-01-01'

This is commonly referred to as the leftmost-prefix principle for B-tree indexes.

Exam takeaway

When designing a composite index, think about:

the most selective/useful leading predicates
equality predicates
range predicates
sorting requirements
the actual workload

Do not assume that the order of columns in an index is interchangeable.

5. Avoid Excessive Indexing

Indexes improve reads but aren’t free.

Every additional index can result in:

additional storage consumption
additional memory pressure
additional write overhead
longer INSERT operations
longer UPDATE operations
longer DELETE operations
additional maintenance

For example, if a table has:

100 million rows

and five large indexes, maintaining those indexes can become a significant part of the workload.

Therefore:

Create indexes that provide measurable value to important queries.

Do not blindly index every column.

Azure Database for PostgreSQL’s autonomous tuning capability can identify potentially useful indexes and also identify duplicate or unused indexes. It can additionally recommend statistics or vacuum-related actions when appropriate.

6. Use EXPLAIN to Understand Query Performance

One of the most important PostgreSQL performance tools is:

EXPLAIN

For example:

			
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 100;

To actually execute the query and obtain runtime information:

			
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 100;

EXPLAIN ANALYZE is especially valuable because it provides actual execution statistics rather than merely the optimizer’s estimated plan.

You might discover that PostgreSQL is performing:

Seq Scan

instead of:

Index Scan

That doesn’t automatically mean the database is wrong.

For a query returning a large percentage of a table, a sequential scan can actually be cheaper than using an index.

Important exam principle

The presence of an index does not guarantee that PostgreSQL will use it.

The query planner chooses the execution strategy it estimates will be cheapest.

7. Keep Statistics Current

PostgreSQL’s optimizer relies on statistics to estimate:

number of rows
data distribution
selectivity
expected query costs

If statistics are stale, PostgreSQL may select a poor execution plan.

ANALYZE updates table statistics:

ANALYZE documents;

For example, after significant changes to a table, current statistics can help the optimizer make better decisions.

Azure Database for PostgreSQL autonomous tuning can identify tables that lack appropriate statistics and recommend ANALYZE when applicable.

8. Query Design Can Matter More Than Adding an Index

Consider:

			
SELECT *
FROM orders;

If the application only needs 10 rows, retrieving the entire table is inefficient regardless of indexing.

Instead:

			
SELECT id, customer_id, order_date
FROM orders
WHERE customer_id = 100
ORDER BY order_date DESC
LIMIT 10;

		

This reduces:

rows processed
data transferred
memory consumption
network traffic
application processing

Azure’s query-performance guidance similarly emphasizes filtering data at the database rather than retrieving large datasets and filtering them in application code.

9. Parameterize Queries

Applications should generally use parameterized queries rather than constructing SQL dynamically.

Instead of building:

			
SELECT *
FROM customers
WHERE email = 'someone@example.com';

into a SQL string dynamically, use a parameterized command supported by the application’s PostgreSQL SDK or driver.

Benefits include:

improved security
reduced SQL injection risk
better query reuse
more predictable application behavior

Query parameterization is also specifically identified as a useful optimization technique in Azure PostgreSQL query-performance guidance.

10. Understand pgvector

For AI applications, PostgreSQL can be extended with pgvector.

pgvector provides support for storing and searching vector embeddings.

A typical table might look like:

			
CREATE TABLE documents
(
    id          BIGSERIAL PRIMARY KEY,
    content     TEXT,
    embedding   vector(1536)
);

		

The vector might represent:

a document
a paragraph
an image
a product
a customer profile
a question
another AI-generated representation

The vector’s dimensions must correspond to the embedding model’s output.

11. Exact Vector Search

Without a vector index, pgvector performs an exact nearest-neighbor search.

For example:

			
SELECT id, content
FROM documents
ORDER BY embedding <=> '[...]'
LIMIT 5;

The database calculates the distance between the query vector and stored vectors.

This provides excellent recall because the database evaluates the candidates directly, but it becomes increasingly expensive as the number of vectors grows.

For a table containing millions of embeddings, comparing the query against every vector can consume substantial:

CPU
memory
I/O
execution time

Microsoft’s PostgreSQL guidance describes unindexed vector search as exact search and explains that ANN indexes trade some recall for improved execution performance.

12. Approximate Nearest-Neighbor Search

Approximate nearest-neighbor, or ANN, indexing reduces the amount of data that must be examined.

Instead of asking:

“Which vector is closest among every vector?”

the system uses an index to identify a smaller set of promising candidates.

This can dramatically reduce search latency and compute requirements.

The tradeoff is:

ANN improves performance at the potential cost of recall.

For AI applications, this is often an excellent tradeoff.

13. IVFFlat

IVFFlat stands for Inverted File with Flat Compression.

It divides vectors into groups or lists based on clustering.

A query then searches selected lists rather than the entire dataset.

A simplified example:

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

The lists parameter controls the number of clusters/lists.

During querying, ivfflat.probes controls how many lists are searched.

For example:

SET ivfflat.probes = 10;

Increasing probes generally improves recall but requires more computation and can increase latency.

Microsoft recommends starting points for lists and probes based on dataset size, but these are starting points rather than universal values. They should be benchmarked against the actual workload.

IVFFlat characteristics

Characteristic	IVFFlat
Index type	ANN
Build speed	Relatively fast
Memory use	Lower than HNSW
Training	Requires clustering/training
Query tuning	`probes`
Main tradeoff	Speed vs. recall

A particularly important point is that IVFFlat works best when the index is created after the initial dataset has been loaded, because its clustering depends on the data distribution.

14. HNSW

HNSW stands for Hierarchical Navigable Small World.

It creates a multilayer graph structure that allows the search to navigate toward likely nearest neighbors.

Example:

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

HNSW has two important build-time parameters:

			
m
ef_construction

m controls the maximum number of connections per layer.

ef_construction controls the size of the candidate list used during index construction.

At query time, HNSW uses:

ef_search

For example:

SET hnsw.ef_search = 100;

Increasing ef_search generally considers more candidates and can improve recall at the expense of additional computation and latency.

HNSW characteristics

Characteristic	HNSW
Index type	ANN
Query performance	Generally strong
Memory consumption	Higher than IVFFlat
Build cost	Higher than IVFFlat
Training step	None
Query tuning	`ef_search`
Build tuning	`m`, `ef_construction`

One important advantage is that HNSW does not require a separate training phase, so it can be created even when the table is empty.

15. DiskANN

Azure Database for PostgreSQL Flexible Server also supports DiskANN for vector search.

DiskANN is designed for scalable approximate nearest-neighbor search and is particularly useful for very large vector datasets.

Microsoft describes DiskANN as offering a strong balance of:

high recall
high queries per second
low latency
large-scale vector search

DiskANN is supported on Azure Database for PostgreSQL Flexible Server.

Important DiskANN parameters include:

max_neighbors
l_value_ib
l_value_is

For example:

			
CREATE INDEX documents_embedding_diskann_idx
ON documents
USING diskann (embedding vector_cosine_ops);

DiskANN can be an important option when workloads become very large and vector-search scalability becomes a primary concern.

16. Choosing Between IVFFlat, HNSW, and DiskANN

A useful exam-oriented comparison is:

Requirement	Potential choice
Faster index creation and lower memory	IVFFlat
Strong speed/recall tradeoff	HNSW
Large-scale vector workloads on Flexible Server	DiskANN
Need an index before data is loaded	HNSW or DiskANN
Need tunable candidate/list searching	IVFFlat/HNSW/DiskANN
Exact search required	No ANN index

The choice should be based on:

dataset size
insertion/update pattern
acceptable latency
required recall
available memory
index build time
query volume
workload growth

There is no universally “best” vector index.

17. Choose the Correct Distance Metric

pgvector supports different distance calculations.

Common operators include:

Operator	Distance/similarity
`<=>`	Cosine distance
`<->`	L2/Euclidean distance
`<#>`	Negative inner product

The index must use the corresponding operator class.

For cosine distance:

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

The query should use the cosine-distance operator:

			
SELECT id, content
FROM documents
ORDER BY embedding <=> '[...]'
LIMIT 10;

For L2 distance:

			
CREATE INDEX documents_embedding_l2_idx
ON documents
USING hnsw (embedding vector_l2_ops);

and:

ORDER BY embedding <-> '[...]'

For inner product:

			
CREATE INDEX documents_embedding_ip_idx
ON documents
USING hnsw (embedding vector_ip_ops);

and:

ORDER BY embedding <#> '[...]'

The index operator class and query operator need to correspond for PostgreSQL to use the appropriate vector index.

18. Why the Distance Metric Matters

Suppose an embedding model is designed for cosine similarity.

Using the wrong distance metric can produce different rankings.

Therefore, developers should understand the relationship:

			
Embedding model
       ↓
Desired similarity measurement
       ↓
pgvector operator
       ↓
Vector index operator class

		

For example:

			
Cosine
   ↓
<=>
   ↓
vector_cosine_ops

		

This relationship is highly testable in scenario-based questions.

19. Reduce pgvector Compute Overhead

A central objective of vector optimization is reducing how much work the database must perform.

Several techniques can help.

Technique 1: Use ANN indexes

Instead of comparing against every vector:

			
Exact search
1,000,000 vectors
↓
Potentially evaluate 1,000,000 candidates

ANN can narrow the candidate set:

			
ANN search
1,000,000 vectors
↓
Index identifies promising candidates
↓
Evaluate a much smaller candidate set

		

This can substantially reduce CPU and latency.

Technique 2: Tune search parameters

For IVFFlat:

SET ivfflat.probes = 10;

For HNSW:

SET hnsw.ef_search = 100;

Higher values generally increase search work.

Therefore:

Don’t automatically maximize these parameters.

Instead, benchmark the smallest values that achieve the required recall and latency.

Technique 3: Return fewer results

If the application only needs five documents:

LIMIT 5

is preferable to:

LIMIT 10000

when the larger result set isn’t required.

This can reduce downstream processing and data transfer.

Technique 4: Filter before or alongside vector retrieval where appropriate

AI applications frequently combine semantic similarity with metadata.

For example:

			
SELECT id, content
FROM documents
WHERE tenant_id = 42
  AND category = 'finance'
ORDER BY embedding <=> '[...]'
LIMIT 10;

		

This can be much more useful than searching the entire database.

However, vector filtering requires careful index/data-layout design. A vector index alone does not automatically make every metadata-filtered vector query efficient.

20. Partial Indexes for Filtered Vector Workloads

A partial index can be useful when only a subset of records participates in a workload.

For example:

			
CREATE INDEX premium_documents_vector_idx
ON documents
USING hnsw (embedding vector_cosine_ops)
WHERE tier = 'premium';

Now the index contains only rows satisfying:

tier = 'premium'

This can reduce index size and potentially reduce search work for that workload.

However, the query must include the appropriate predicate:

			
WHERE tier = 'premium'
ORDER BY embedding <=> '[...]'
LIMIT 10;

Partial indexes are particularly useful when a workload repeatedly targets a well-defined subset of data. Microsoft provides partial-index examples for pgvector workloads.

21. Vector Dimensions and Indexing Limits

A particularly important implementation detail is that vector columns used for indexing need explicitly defined dimensions.

For example:

embedding vector(1536)

is indexable.

But:

embedding vector

does not provide a fixed dimension for the index.

Microsoft’s current PostgreSQL guidance also states that indexed vectors are limited to 2,000 dimensions for the relevant IVFFlat and HNSW index types. Vectors with more dimensions can be stored, but they cannot be indexed using those index types. Dimensionality reduction can be considered when appropriate.

Exam trap

A question may present:

embedding vector(3072)

and ask why an HNSW or IVFFlat index cannot be created.

The important issue is the index dimension limit, not that PostgreSQL cannot store the vector.

22. Load Data Before Creating an IVFFlat Index

IVFFlat uses clustering to organize vectors into lists.

Consequently, the data distribution matters.

A common approach is:

			
Create table
Load embeddings
Create IVFFlat index
Tune probes
Benchmark

		

rather than:

			
Create table
Create IVFFlat index
Load all data

Microsoft recommends loading data before creating the vector index when possible because index creation is faster and the resulting layout is more optimal.

23. HNSW Does Not Require Training

This is an important contrast.

IVFFlat

			
Data
 ↓
Clustering/training
 ↓
Lists

		

HNSW

			
Data
 ↓
Graph construction

HNSW doesn’t have the same training requirement as IVFFlat and can therefore be created on an empty table.

This difference is a common source of exam questions.

24. Index Build Memory

Vector indexes can be expensive to build.

PostgreSQL’s:

maintenance_work_mem

can affect index construction.

For large vector indexes, having sufficient memory can significantly improve index-build performance.

For example:

SET maintenance_work_mem = '8GB';

should only be used when the server has sufficient resources and the setting is appropriate for the workload.

Azure documentation specifically discusses increasing maintenance_work_mem to speed DiskANN index creation and recommends scaling resources appropriately rather than blindly allocating excessive memory.

25. Connection Pooling

Query performance isn’t only about indexes.

AI applications can generate large numbers of short-lived database connections.

Creating connections repeatedly can consume resources and add latency.

Azure Database for PostgreSQL Flexible Server supports built-in PgBouncer connection pooling.

A connection pool allows many application operations to reuse a smaller number of database connections.

This is especially useful for:

serverless applications
high-concurrency APIs
AI inference applications
applications generating many short-lived requests

Azure guidance specifically recommends considering connection pooling when applications create many short-lived connections or maintain many mostly idle connections.

26. Monitor Query Performance

When optimizing a query, don’t rely on intuition alone.

A useful process is:

			
Identify slow query
       ↓
Examine workload
       ↓
EXPLAIN / EXPLAIN ANALYZE
       ↓
Inspect execution plan
       ↓
Identify bottleneck
       ↓
Change index/query/configuration
       ↓
Benchmark again

		

Azure Database for PostgreSQL provides Query Store functionality that can help identify expensive queries and compare workload performance over time.

27. Understand Sequential Scans

Seeing:

Seq Scan

in an execution plan isn’t automatically a problem.

Suppose a table contains:

1,000 rows

and the query needs:

800 rows

Using an index may actually be more expensive than scanning the table.

But if a table contains:

100,000,000 rows

and the query needs:

10 rows

an appropriate index could provide a huge performance advantage.

Therefore:

The correct question is not “Does the query use an index?” but “Is the chosen execution plan efficient for this workload?”

28. Avoid Indexes That Don’t Match the Query

Suppose you create:

			
CREATE INDEX ix_products_category
ON products(category);

but the application primarily queries:

WHERE product_name = 'Laptop'

The index isn’t useful for that predicate.

Likewise, creating a cosine vector index doesn’t make a query using L2 distance automatically use that index.

The index must correspond to the query’s access pattern.

29. Data Layout Matters

For AI workloads, data layout can significantly affect performance.

A document table might contain:

			
id
tenant_id
document_type
created_at
content
embedding

		

The developer should consider:

how frequently each column is filtered
how frequently vector searches are performed
tenant isolation
metadata filtering
vector dimensions
number of vectors
update frequency
index size
workload growth

For example, a multi-tenant application may benefit from organizing indexes and queries around tenant_id rather than treating all tenants as one undifferentiated search space.

30. Exact vs. Approximate Search

This distinction is critical for AI-200.

Feature	Exact Search	ANN Search
Recall	Perfect	Potentially lower
CPU cost	Higher	Lower
Latency	Higher at scale	Lower at scale
Index required	No	Yes
Best for	Small datasets/high recall	Large datasets/low latency
Examples	Sequential vector comparison	IVFFlat/HNSW/DiskANN

The choice depends on application requirements.

If absolute recall is more important than latency, exact search may be appropriate.

If an application must search millions of embeddings with low latency, ANN is usually more appropriate.

31. Practical Optimization Strategy

A strong approach for an AI application is:

Step 1 — Understand the workload

Determine:

number of vectors
vector dimensions
queries per second
expected latency
required recall
update frequency
filtering requirements

Step 2 — Start with correct query semantics

Choose:

distance metric
pgvector operator
corresponding operator class

Step 3 — Benchmark exact search

This establishes a baseline.

Step 4 — Select an ANN index

Evaluate:

IVFFlat
HNSW
DiskANN where supported

Step 5 — Tune search parameters

For example:

			
IVFFlat → probes
HNSW → ef_search
DiskANN → l_value_is

Step 6 — Measure recall and latency

Don’t optimize only for speed.

Measure both:

			
Latency
+
Recall
+
CPU
+
Memory

		

Step 7 — Optimize metadata filtering

Consider:

conventional indexes
composite indexes
partial indexes
appropriate data layout

Step 8 — Monitor continuously

Workloads change.

An index that works well today may not be optimal after the dataset grows by 10×.

32. Key AI-200 Exam Takeaways

Remember these concepts:

B-tree is the default PostgreSQL index and is appropriate for many relational queries.
Composite index column order matters.
Indexes improve reads but add storage and write/maintenance overhead.
EXPLAIN shows the optimizer’s plan.
EXPLAIN ANALYZE executes the query and provides actual runtime information.
Keep PostgreSQL statistics current.
PostgreSQL does not have to use an index simply because one exists.
pgvector supports exact vector search without an ANN index.
ANN indexes trade some recall for performance.
IVFFlat uses lists/clustering and is generally faster to build and less memory-intensive than HNSW.
HNSW generally provides a strong speed/recall tradeoff but uses more memory and takes longer to build.
DiskANN is available for Azure Database for PostgreSQL Flexible Server and is designed for highly scalable ANN workloads.
IVFFlat uses probes to control how many lists are searched.
HNSW uses ef_search to control the search candidate list.
HNSW uses m and ef_construction during index construction.
The vector query operator must correspond to the vector index’s operator class.
<=> is cosine distance.
<-> is L2 distance.
<#> is negative inner product.
Indexed vectors need explicitly defined dimensions.
Relevant IVFFlat/HNSW vector indexes have a 2,000-dimension indexing limit.
Load data before creating an IVFFlat index when possible.
HNSW does not require a training phase.
Partial indexes can be useful for frequently queried subsets.
maintenance_work_mem can affect vector index build performance.
Connection pooling can reduce connection overhead.
Benchmark before and after optimization rather than assuming an index is beneficial.

Practice Exam Questions

Question 1

An Azure Database for PostgreSQL application frequently executes the following query:

			
SELECT *
FROM orders
WHERE customer_id = 100
  AND order_date >= '2026-01-01'
ORDER BY order_date DESC;

		

Which index is most appropriate for this query pattern?

			
CREATE INDEX ix_orders_date
ON orders(order_date);

			
CREATE INDEX ix_orders_customer
ON orders(customer_id);

			
CREATE INDEX ix_orders_customer_date
ON orders(customer_id, order_date DESC);

			
CREATE INDEX ix_orders_date_customer
ON orders(order_date DESC, customer_id);

Answer: C

Explanation:
The query first filters on customer_id, then applies a range condition and ordering on order_date. A composite B-tree index beginning with customer_id and followed by order_date aligns well with this access pattern. The ordering of columns in a composite index matters. An index beginning with order_date is generally less useful for the equality predicate on customer_id.

Question 2

A developer creates an HNSW index for a vector column and wants to increase the number of candidate vectors considered during each vector search. Which parameter should the developer adjust?

A. hnsw.ef_search

B. maintenance_work_mem

C. ivfflat.probes

D. hnsw.m

Answer: A

Explanation:
hnsw.ef_search controls the size of the dynamic candidate list used during HNSW search. Increasing it generally improves recall but increases search work and can increase latency. hnsw.m affects graph construction, while ivfflat.probes applies to IVFFlat.

Question 3

A development team has 5 million document embeddings and currently performs exact vector similarity searches. CPU utilization is high and query latency is unacceptable. The application can tolerate a small reduction in recall in exchange for substantially better performance.

What should the team consider?

A. Remove the vector column.

B. Replace PostgreSQL with a B-tree index on the embedding.

C. Increase the number of columns returned by the query.

D. Create an approximate nearest-neighbor vector index.

Answer: D

Explanation:
ANN indexes such as IVFFlat, HNSW, and DiskANN can reduce the amount of vector-search computation by narrowing the candidate set. They trade some recall for improved execution performance. A conventional B-tree index is not a substitute for a vector ANN index.

Question 4

A developer creates the following index:

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

Which query is aligned with this index?

			
SELECT *
FROM documents
ORDER BY embedding <-> '[...]'
LIMIT 10;

			
SELECT *
FROM documents
ORDER BY embedding <=> '[...]'
LIMIT 10;

			
SELECT *
FROM documents
ORDER BY embedding <#> '[...]'
LIMIT 10;

			
SELECT *
FROM documents
ORDER BY embedding = '[...]'
LIMIT 10;

Answer: B

Explanation:
vector_cosine_ops corresponds to cosine distance, which uses the <=> operator. <-> represents L2 distance, while <#> represents negative inner product. The index’s operator class and the query’s distance operator must correspond for the vector index to be used appropriately.

Question 5

A developer is creating an IVFFlat index on a large collection of embeddings. The developer wants the index’s clustering to reflect the actual distribution of the data.

Which approach is generally recommended?

A. Create the index before inserting any data.

B. Create the index and then delete half of the data.

C. Load the data before creating the IVFFlat index.

D. Disable all PostgreSQL statistics before creating the index.

Answer: C

Explanation:
IVFFlat uses clustering to organize vectors into lists. When possible, loading the data before creating the index allows the index to be built using the actual data distribution and generally results in a faster and more optimal index build.

Question 6

An application has a vector column defined as:

embedding vector(3072)

The developer attempts to create an IVFFlat index and receives an error indicating that the vector has too many dimensions for the index.

What is the most likely reason?

A. IVFFlat supports only integer vectors.

B. Vector indexes cannot contain more than 2,000 dimensions.

C. PostgreSQL cannot store vectors larger than 1,536 dimensions.

D. IVFFlat requires vectors to use the text data type.

Answer: B

Explanation:
The current Azure Database for PostgreSQL guidance states that IVFFlat and HNSW indexes can index vectors with up to 2,000 dimensions. Vectors with more than 2,000 dimensions can be stored but cannot be indexed using those index types. Dimensionality reduction can be considered when appropriate.

Question 7

An application frequently searches only premium documents:

			
WHERE tier = 'premium'
ORDER BY embedding <=> '[...]'
LIMIT 10;

The table contains a very large number of documents, but only a small percentage are premium.

Which strategy could reduce the size of the vector index and optimize this specific workload?

A. Create a partial vector index containing only premium documents.

B. Remove the tier predicate from the query.

C. Create an index on an unrelated timestamp column.

D. Store embeddings as JSON instead of vectors.

Answer: A

Explanation:
A partial index can contain only rows satisfying a specified predicate, such as:

WHERE tier = 'premium'

This can make the index smaller and potentially reduce the amount of data involved in searches targeting that subset. The query needs to include the appropriate predicate for the partial index to be applicable.

Question 8

A PostgreSQL developer sees the following execution plan:

Seq Scan on orders

The developer concludes that the database is performing poorly because an index exists on the queried column.

Which statement is most accurate?

A. PostgreSQL always uses an index when one exists.

B. A sequential scan always indicates an incorrectly designed index.

C. PostgreSQL may choose a sequential scan when it estimates that scanning the table is cheaper.

D. Sequential scans can occur only when statistics are disabled.

Answer: C

Explanation:
PostgreSQL’s optimizer chooses the execution plan it estimates will have the lowest cost. If a query retrieves a large percentage of a table, a sequential scan can be more efficient than using an index. Therefore, the existence of an index does not guarantee that PostgreSQL will use it.

Question 9

An AI application uses HNSW vector search. The team wants to improve recall but observes that increasing the search parameter also increases CPU consumption and latency.

Which explanation is most accurate?

A. Increasing the HNSW search candidate list generally causes more vectors/candidates to be considered.

B. Increasing ef_search disables the vector index.

C. Increasing ef_search converts HNSW into a B-tree index.

D. Increasing ef_search reduces the number of candidates examined.

Answer: A

Explanation:
hnsw.ef_search controls the dynamic candidate list used during HNSW searches. Increasing it can improve recall because more candidates are considered, but this increases search work and may increase latency and resource consumption.

Question 10

A high-volume AI API frequently creates short-lived PostgreSQL connections for individual vector-search requests. CPU and connection overhead are becoming significant.

What is the most appropriate optimization?

A. Create a new database connection for every SQL statement.

B. Disable all indexes.

C. Increase the number of vector dimensions.

D. Use connection pooling, such as PgBouncer, to reuse database connections.

Answer: D

Explanation:
Connection creation and management can become expensive when applications generate many short-lived connections. Connection pooling allows application requests to reuse database connections, reducing connection overhead. Azure Database for PostgreSQL Flexible Server provides built-in PgBouncer functionality that can be considered for this scenario.

Final Exam Review

For this topic, think in terms of four layers of optimization:

			
1. Query design
       ↓
2. Traditional PostgreSQL indexes
       ↓
3. pgvector ANN indexes
       ↓
4. Runtime/configuration tuning

		

A strong AI-200 developer should be able to look at a workload and reason through questions such as:

What is the query actually doing?

Which columns are being filtered, joined, or sorted?

Would a B-tree, composite, or partial index help?

Is exact vector search still appropriate at this scale?

Should I use IVFFlat, HNSW, or DiskANN?

Which distance metric and operator class are required?

Can I reduce the candidate set without sacrificing too much recall?

Are statistics current?

Is connection overhead contributing to latency?

What does EXPLAIN ANALYZE actually show?

The central lesson is that performance optimization is a measurement and tradeoff exercise. The goal isn’t to maximize the number of indexes or blindly tune every parameter. The goal is to achieve the required latency, recall, throughput, and resource consumption for the application’s actual workload.

Go to the AI-200 Exam Prep Hub main page

AI-200, azure, Databases, Microsoft Certification, NoSQL, SQL August 15, 2026

Implement connection optimization to improve throughput and minimize latency (AI-200 Exam Prep)

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
      --> Implement connection optimization to improve throughput and minimize latency

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

Connection management is an important part of application performance when working with Azure Database for PostgreSQL. An application can have well-designed SQL, appropriate indexes, and sufficient compute resources and still experience poor performance if it creates too many database connections, repeatedly establishes short-lived connections, or communicates with the database across a high-latency network path.

For the AI-200 exam, the key idea is:

Optimize how applications establish, reuse, and manage PostgreSQL connections before simply increasing the database’s connection limit.

Connection optimization involves several complementary strategies:

Use connection pooling.
Reuse established connections rather than repeatedly creating them.
Avoid excessive concurrent connections.
Place applications and databases appropriately within Azure.
Use private networking where appropriate.
Configure connection and pool sizes based on workload.
Use appropriate timeout and retry behavior.
Monitor connection utilization and resource consumption.
Understand how Azure’s built-in PgBouncer works.
Design serverless applications carefully because they can create connection bursts.

Azure Database for PostgreSQL Flexible Server provides built-in PgBouncer to help with connection pooling. Azure’s current guidance specifically recommends using PgBouncer rather than simply increasing max_connections when more connection capacity is needed.

1. Why Database Connections Affect Performance

A PostgreSQL connection is not free.

When an application establishes a connection, PostgreSQL must perform connection setup, authentication, session initialization, and resource allocation. PostgreSQL uses a process-based architecture, so maintaining large numbers of connections consumes server resources.

This becomes particularly important for applications that repeatedly perform operations such as:

Open connection.
Execute one query.
Close connection.
Repeat thousands of times.

The database may spend substantial resources managing connections rather than processing useful database work.

Azure specifically notes that large numbers of connections can increase CPU utilization and contribute to problems such as memory pressure, disk contention, and lock contention. Short-lived connections are particularly problematic because connection establishment and termination occur frequently.

Connection overhead

Conceptually:

			
Application
    |
    | Establish connection
    v
PostgreSQL
    |
    | Authenticate / initialize session
    |
    | Execute query
    |
    | Return results
    |
    | Close connection
    v
Application

		

If this happens for every operation, the overhead can become significant.

A better architecture is:

			
Application
    |
    v
Connection Pool
    |
    +---- Existing PostgreSQL connection
    |
    +---- Existing PostgreSQL connection
    |
    +---- Existing PostgreSQL connection
    |
    v
Azure Database for PostgreSQL

		

The application obtains an existing connection, uses it, and returns it to the pool.

2. Connection Pooling

Connection pooling is one of the most important concepts for this exam topic.

A connection pool maintains a collection of already-established database connections.

Instead of creating a new connection for every database operation, an application:

Requests a connection from the pool.
Uses the connection.
Completes the transaction or operation.
Returns the connection to the pool.

The connection remains available for reuse.

Without pooling

			
Request 1 → Create connection → Query → Close
Request 2 → Create connection → Query → Close
Request 3 → Create connection → Query → Close
Request 4 → Create connection → Query → Close

With pooling

			
Request 1 ─┐
Request 2 ─┤
Request 3 ─┼→ Connection Pool → Reusable DB connections
Request 4 ─┘

This reduces connection establishment overhead and can significantly improve throughput for workloads containing many small or short-lived operations.

3. Client-Side Connection Pooling

There are two important approaches to pooling:

Client-side/application pooling
Server-side pooling with PgBouncer

Client-side pooling is implemented by the application framework or PostgreSQL driver.

For example, a web application might maintain a pool containing a limited number of PostgreSQL connections.

Suppose an application receives 500 simultaneous HTTP requests.

It does not necessarily need 500 PostgreSQL connections.

Instead:

			
500 application requests
          |
          v
   Connection Pool
          |
          +---- Connection 1
          +---- Connection 2
          +---- Connection 3
          ...
          +---- Connection 20

		

Requests can share the available database connections as they become available.

Benefits

Client-side pooling can:

Reduce connection establishment overhead.
Reduce authentication overhead.
Reduce database resource consumption.
Improve application throughput.
Reduce latency for short database operations.
Protect the database from excessive connection creation.

A particularly important point for the exam is that pool size should not simply be set equal to the maximum number of application requests.

A pool containing thousands of connections can itself become a performance problem.

4. Azure Database for PostgreSQL Built-In PgBouncer

Azure Database for PostgreSQL Flexible Server provides built-in PgBouncer as an optional connection-pooling solution.

PgBouncer is a lightweight connection pooler positioned between the application and PostgreSQL.

Conceptually:

			
Application
    |
    | Many client connections
    v
+----------------+
|   PgBouncer    |
| Connection Pool|
+----------------+
    |
    | Fewer PostgreSQL connections
    v
PostgreSQL Server

		

This allows many client connections to be handled without requiring an equivalent number of active PostgreSQL server connections.

Azure’s built-in PgBouncer is available for General Purpose and Memory Optimized compute tiers and can be used with public or private networking.

5. PgBouncer Port 6432

When using the built-in PgBouncer service, applications connect through port:

6432

The standard PostgreSQL connection uses:

5432

So a conceptual connection configuration is:

			
Direct PostgreSQL:
server.postgres.database.azure.com:5432
Through PgBouncer:
server.postgres.database.azure.com:6432

Azure’s current documentation states that PgBouncer uses port 6432 and the same hostname as the PostgreSQL server.

Exam tip

If a question asks how to route an Azure Database for PostgreSQL application through the built-in PgBouncer service, port 6432 is an important detail to recognize.

6. PgBouncer Transaction Pooling

The built-in PgBouncer configuration uses transaction pooling by default.

In transaction pooling, a PostgreSQL server connection is assigned to a client for the duration of a transaction.

After the transaction completes, the server connection can be reused by another client.

Conceptually:

			
Client A
   |
   | BEGIN
   | SQL
   | SQL
   | COMMIT
   |
   v
Connection returned to pool
Client B
   |
   | BEGIN
   | SQL
   | COMMIT
   |
   v
Same server connection can be reused

		

This is highly effective for applications with many concurrent clients but relatively short transactions.

Azure’s current PgBouncer configuration documentation identifies transaction as the default pgbouncer.pool_mode.

7. PgBouncer Client Connections vs. PostgreSQL Connections

This distinction is especially important for exam questions.

Suppose an application has:

5,000 client connections

That does not mean PostgreSQL must execute 5,000 database sessions simultaneously.

PgBouncer can accept many client connections while maintaining a smaller number of actual PostgreSQL server connections.

The pooler can queue clients while database connections are busy.

Therefore:

Increasing the number of client connections does not automatically increase the number of PostgreSQL connections actually executing work.

Azure documents separate PgBouncer settings for client connections and server-side pool size, including pgbouncer.max_client_conn and pgbouncer.default_pool_size.

8. Do Not Simply Increase `max_connections`

A common mistake is to encounter:

FATAL: sorry, too many clients already.

and respond by increasing PostgreSQL’s max_connections dramatically.

This is generally not the preferred solution.

Every PostgreSQL connection consumes resources, whether it is actively executing a query or sitting idle.

Increasing max_connections can therefore make the underlying resource problem worse.

Azure recommends using PgBouncer instead when additional connection capacity is required and specifically recommends conservative pooling values followed by monitoring.

Better approach

Instead of:

			
More connections
       ↓
Increase max_connections
       ↓
More memory/resource consumption

		

Prefer:

			
Many application requests
       ↓
Connection pooling
       ↓
Controlled number of database connections
       ↓
Better resource utilization

		

9. Choosing an Appropriate Pool Size

A connection pool should be sized based on:

Application concurrency.
Query duration.
Transaction duration.
Database compute capacity.
CPU utilization.
Memory availability.
Workload characteristics.
Number of application instances.

A larger pool isn’t automatically better.

Consider:

Pool = 10 connections

If queries are short and the database is adequately sized, this may be sufficient.

Increasing the pool to:

Pool = 500 connections

could actually make performance worse if those connections compete for CPU, memory, locks, or I/O.

Azure’s current guidance recommends conservative PgBouncer values and monitoring resource utilization and application performance rather than blindly maximizing connection counts.

10. Connection Pooling in Scaled-Out Applications

This becomes particularly important in cloud applications.

Imagine an application running on 20 instances.

If every instance creates a pool of 50 connections:

			
20 application instances
×
50 connections each
=
1,000 potential connections

		

If the application scales to 100 instances:

100 × 50 = 5,000 connections

This can unexpectedly overwhelm the database.

Therefore, pool sizing must consider the total number of application instances, not just the pool size configured in one instance.

Exam scenario

If an Azure application automatically scales from 5 instances to 50 instances, a fixed connection pool size can multiply database connections dramatically.

The correct response is often to:

Reduce per-instance pool sizes.
Use connection pooling appropriately.
Use PgBouncer when appropriate.
Monitor total database connections.
Avoid simply raising max_connections.

11. Serverless Applications and Connection Bursts

Serverless applications require special attention.

Azure Functions and similar platforms can scale out rapidly.

For example:

			
Normal:
5 function instances
× 10 DB connections
= 50 connections

During a traffic spike:

			
100 function instances
× 10 DB connections
= 1,000 connections

This can create a connection storm.

Recommended design

Use:

Connection pooling where appropriate.
Conservative pool sizes.
PgBouncer when appropriate.
Efficient transaction design.
Connection reuse.
Appropriate application scaling limits.
Monitoring and alerting.

The goal is to allow application scalability without allowing database connections to grow uncontrollably.

12. Connection Churn

Connection churn refers to repeatedly opening and closing database connections.

High connection churn can be especially harmful when connections are short-lived.

For example:

			
Open → Query → Close
Open → Query → Close
Open → Query → Close
Open → Query → Close
...

		

The database spends resources repeatedly creating and destroying connections.

Instead:

			
Create pool
     ↓
Reuse connection
     ↓
Execute transaction
     ↓
Return connection
     ↓
Reuse connection

		

Azure specifically identifies frequent short-duration connections as a source of performance degradation.

Key exam concept

If the question describes:

Many short-lived connections
High connection counts
High CPU associated with connection activity
Connection establishment overhead
Web applications with many concurrent requests

Think:

Connection pooling

13. Application Location Matters

Connection optimization isn’t limited to the database itself.

Network distance affects latency.

An application running in one Azure region while its database is in another region introduces network latency for every database interaction.

For example:

			
Application
    |
    | Long network path
    v
PostgreSQL

		

is generally less desirable than:

			
Application
    |
    | Short network path
    v
PostgreSQL

		

Azure recommends considering client and network characteristics, including where clients are located and whether requests cross regions or availability zones.

General principle

Place latency-sensitive application components close to the database.

This is particularly important for applications that perform many sequential database operations.

14. Availability Zones and Latency

Azure Database for PostgreSQL Flexible Server supports deployment within availability zones and zone-redundant high availability.

For latency-sensitive applications, the placement of the application relative to the database should be considered.

However, don’t confuse high availability with performance optimization.

Zone-redundant HA primarily provides resilience by maintaining a standby in another availability zone. It is not a mechanism for making ordinary queries faster.

A test question might present:

An application requires low latency but also requires zone-redundant HA.

The appropriate design should balance:

Application location.
Primary database location.
Availability-zone architecture.
Required resilience.
Network latency.

15. Private Networking

Azure Database for PostgreSQL Flexible Server supports:

Private access through virtual network integration.
Public access with allowed IP addresses.
Public access plus private endpoints in supported configurations.

For applications hosted in Azure, private networking can provide a secure network path and can be part of an overall architecture designed for predictable connectivity.

With private access, Azure resources communicate with the PostgreSQL server through private IP addresses within the virtual network architecture.

Important distinction

Do not assume:

“Private networking automatically makes every query faster.”

Network latency depends on architecture and physical/network topology.

The more useful exam principle is:

Use an appropriate network topology and avoid unnecessary network distance or cross-region traffic.

16. DNS and Connection Reliability

Applications should use the PostgreSQL server’s fully qualified domain name (FQDN) rather than hard-coded IP addresses.

This is especially important because managed services can change underlying infrastructure.

A connection string should conceptually look like:

			
Host=myserver.postgres.database.azure.com
Port=5432
Database=mydatabase
User Id=...
Password=...
SSL Mode=Require

		

rather than relying on a fixed IP address.

Using the service hostname allows Azure to manage underlying infrastructure changes without requiring application code to change.

17. TLS and Connection Overhead

Azure Database for PostgreSQL uses TLS/SSL for data in transit, with TLS 1.2 and later supported.

Encryption is an important security requirement, but TLS also introduces some connection-handshake overhead.

This is another reason connection pooling is valuable.

Instead of repeatedly paying connection-establishment costs:

			
TLS handshake
Authentication
Session initialization
Query
Close

		

the application can establish connections and reuse them.

Thus, pooling can improve performance while allowing secure TLS connections to remain in use.

18. Connection Timeouts

Connection optimization also involves appropriate timeout settings.

A connection timeout controls how long an application waits while establishing a connection.

A command/query timeout controls how long an operation is allowed to execute.

These are different concepts.

Connection timeout

Can I connect to PostgreSQL?

Command timeout

How long should I allow this query to execute?

Pool wait timeout

How long should I wait for a connection from the pool?

Understanding these distinctions is useful when diagnosing latency.

A long connection timeout does not make a connection faster. It merely allows the application to wait longer before failing.

19. Retries and Transient Failures

Cloud applications should be designed to tolerate transient failures.

For example:

			
Application
     |
     | Connection attempt
     X
Transient network failure
     |
     v
Retry with appropriate backoff

		

Retries should be:

Limited.
Controlled.
Appropriate for the operation.
Implemented with exponential backoff where appropriate.
Combined with connection pooling.

Avoid retry storms

If thousands of application requests all fail simultaneously and immediately retry:

			
Failure
 ↓
1,000 retries
 ↓
Database/network overload
 ↓
More failures
 ↓
1,000 more retries

		

This can make an outage worse.

A better approach uses controlled retries and backoff.

20. Connection Pooling and Transactions

Application code should release pooled connections promptly.

A common pattern is:

			
Acquire connection
    ↓
Begin transaction
    ↓
Execute operations
    ↓
Commit / Rollback
    ↓
Release connection

		

Avoid holding a database connection while performing unrelated work.

For example, this is inefficient:

			
Acquire DB connection
       ↓
Call external AI service
       ↓
Wait 10 seconds
       ↓
Perform database query
       ↓
Release connection

		

The connection is unavailable to other requests while the application waits.

A better approach is:

			
Call AI service
       ↓
Receive result
       ↓
Acquire DB connection
       ↓
Perform database transaction
       ↓
Release connection

		

This maximizes connection reuse.

21. Avoid Long-Running Transactions

Long transactions can reduce the effectiveness of connection pooling.

If a transaction remains open for an extended period, its database connection remains occupied.

For example:

			
Connection Pool
   |
   +-- Connection 1 → long transaction
   +-- Connection 2 → available
   +-- Connection 3 → available
   +-- Connection 4 → available

		

As more connections become tied up in long-running transactions, other requests may have to wait.

Therefore:

Keep transactions as short as practical.

This is particularly important in high-concurrency applications.

22. PgBouncer Configuration to Know

Several PgBouncer settings are useful to recognize for the AI-200 exam.

Setting	Purpose
`pgbouncer.enabled`	Enables built-in PgBouncer
`pgbouncer.pool_mode`	Controls when server connections can be reused
`pgbouncer.default_pool_size`	Number of server connections allowed per user/database pool
`pgbouncer.max_client_conn`	Maximum number of client connections
`pgbouncer.min_pool_size`	Maintains a minimum number of server connections
`pgbouncer.query_wait_timeout`	Maximum time a query can wait for execution assignment
`pgbouncer.server_idle_timeout`	Controls how long an idle server connection remains before being dropped
`pgbouncer.max_prepared_statements`	Controls protocol-level prepared statement tracking in supported pooling modes

Current Azure documentation lists transaction pooling as the default pool mode, a default default_pool_size of 50, and a default max_client_conn of 5,000. These are service configuration defaults and should not be interpreted as universal recommendations for every workload.

23. Monitoring Connections

Connection optimization should be based on measurement rather than guesswork.

Useful things to monitor include:

Active connections.
Idle connections.
Connection creation rate.
Connection wait time.
CPU utilization.
Memory utilization.
Query duration.
Transaction duration.
Storage I/O.
Application response time.
Pool utilization.
PgBouncer metrics.

Azure Database for PostgreSQL provides monitoring and alerting capabilities, including host metrics and slow-query logging.

Built-in PgBouncer can also expose metrics for active connections, idle connections, pooled connections, and connection pools when the appropriate PgBouncer diagnostics settings are enabled.

24. Diagnosing Connection-Related Performance Problems

When an application is slow, don’t immediately assume the SQL query is the problem.

A useful troubleshooting sequence is:

Step 1: Check application latency

Determine whether the delay occurs:

Before database access.
While waiting for a connection.
During query execution.
While receiving results.

Step 2: Check connection counts

Look for:

Excessive connections.
Rapid connection growth.
Many idle connections.
Connection-limit errors.

Step 3: Check connection churn

Determine whether the application repeatedly creates and destroys connections.

Step 4: Check pool configuration

Look at:

Pool size.
Maximum pool size.
Pool wait time.
Connection lifetime.
Number of application instances.

Step 5: Check database resources

Look at:

CPU.
Memory.
Storage.
IOPS.
Query performance.

Step 6: Check network topology

Determine whether traffic crosses:

Regions.
Availability zones.
Unnecessary network boundaries.

Step 7: Optimize the actual workload

Only after understanding the bottleneck should you consider:

Query optimization.
Index changes.
Compute scaling.
Storage changes.
Architecture changes.

25. Connection Optimization Strategy

A practical strategy for Azure Database for PostgreSQL is:

                    Application
                         |
                         v
                Application Pool
                         |
                         v
                  PgBouncer
                         |
                         v
             Azure PostgreSQL
                         |
              +----------+----------+
              |                     |
            CPU                   Storage

Then optimize each layer:

Application

Reuse connections.
Avoid connection churn.
Keep transactions short.
Configure reasonable pool sizes.
Avoid holding connections while performing unrelated work.

Pooling

Use client-side pooling where appropriate.
Use Azure’s built-in PgBouncer when appropriate.
Understand transaction pooling.
Monitor pool utilization.

Network

Place applications close to the database.
Avoid unnecessary cross-region communication.
Use appropriate private networking.
Use the database FQDN.

Database

Don’t blindly increase max_connections.
Scale compute when CPU/memory is genuinely the bottleneck.
Optimize expensive queries.
Monitor resource utilization.

26. Common AI-200 Exam Traps

Trap 1: “Increase `max_connections`“

Usually not the best first answer.

Think: connection pooling.

Trap 2: “Create a connection for every request”

Usually inefficient.

Think: reuse connections through pooling.

Trap 3: “Use the largest possible pool”

Incorrect.

Think: appropriately sized pool based on workload and database capacity.

Trap 4: “PgBouncer increases database processing capacity”

Not exactly.

PgBouncer improves connection management and allows many clients to share a smaller number of database connections. It does not magically increase the CPU or query-processing capacity of PostgreSQL.

Trap 5: “More connections always means more throughput”

False.

Too many connections can cause contention and resource pressure.

Trap 6: “Private networking automatically reduces latency”

Not necessarily.

Private networking provides an appropriate secure connectivity architecture, but actual latency depends on network topology and location.

Trap 7: “Connection timeout controls query execution time”

False.

Connection timeout and query/command timeout address different stages of database interaction.

Trap 8: “Connection pooling eliminates the need to optimize SQL”

False.

Pooling solves connection-management overhead. Poor SQL can still consume substantial CPU, memory, I/O, and locks.

27. Key Takeaways for the AI-200 Exam

Remember these principles:

Connection establishment has a cost.
Connection pooling reduces connection churn.
Reuse connections rather than repeatedly creating them.
Don’t equate application concurrency with database connection count.
Avoid blindly increasing max_connections.
Azure Database for PostgreSQL Flexible Server provides built-in PgBouncer.
The built-in PgBouncer endpoint uses port 6432.
Transaction pooling is the default PgBouncer pool mode.
Pool size should be based on workload and database capacity.
Scaled-out applications multiply connection counts.
Serverless applications can cause connection bursts.
Keep transactions short.
Don’t hold connections while waiting on unrelated operations.
Keep latency-sensitive applications geographically and architecturally close to the database.
Monitor connection counts, CPU, memory, latency, and pool utilization.
Use retries carefully to avoid retry storms.
Use the database FQDN rather than hard-coded IP addresses.
Connection pooling complements—not replaces—query and database optimization.

Practice Exam Questions

Question 1

An AI-powered web application uses Azure Database for PostgreSQL. During periods of high traffic, the application creates thousands of short-lived database connections. CPU utilization on the PostgreSQL server increases significantly even though the queries themselves are relatively simple.

What should you implement first?

A. Connection pooling
B. Increase the PostgreSQL max_connections setting substantially
C. Disable TLS for database connections
D. Move the database to a larger storage account

Answer: A

Explanation:
Connection establishment and termination consume database resources. Connection pooling allows established connections to be reused, reducing connection churn and improving throughput. Increasing max_connections can increase resource consumption rather than solve the underlying problem.

Question 2

An application uses Azure Database for PostgreSQL Flexible Server and Azure’s built-in PgBouncer. The application must connect through the PgBouncer endpoint rather than directly to PostgreSQL.

Which port should the application use?

A. 443
B. 5432
C. 8080
D. 6432

Answer: D

Explanation:
The standard PostgreSQL endpoint uses port 5432. Azure’s built-in PgBouncer service uses port 6432. The application can use the PostgreSQL server hostname while changing the port to 6432.

Question 3

A web application is deployed across 30 instances. Each instance maintains a connection pool with a maximum of 100 PostgreSQL connections. During scaling events, the database experiences connection pressure.

What is the most likely cause?

A. PostgreSQL automatically duplicates every database row
B. TLS encryption prevents connection reuse
C. PgBouncer automatically disables indexes
D. The application-level pool size is multiplied across application instances

Answer: D

Explanation:
Connection pools are generally maintained per application instance. Thirty instances with a potential 100 connections each could create as many as 3,000 application-side connections. Pool sizing must therefore consider the total number of instances.

Question 4

An application frequently opens a PostgreSQL connection, executes one short query, and immediately closes the connection. The pattern occurs thousands of times per minute.

Which change is most likely to improve throughput?

A. Increase the number of database connections created per request
B. Increase storage capacity
C. Disable connection authentication
D. Reuse connections through a connection pool

Answer: D

Explanation:
The workload exhibits high connection churn. Connection pooling allows existing connections to be reused, avoiding repeated connection establishment and teardown.

Question 5

A development team encounters the following error on an Azure Database for PostgreSQL server:

FATAL: sorry, too many clients already.

The team wants to support more application clients without unnecessarily increasing the number of active PostgreSQL server connections.

What should they consider?

A. Azure Database for PostgreSQL built-in PgBouncer
B. Increasing the number of database indexes
C. Disabling SSL/TLS
D. Converting all queries to stored procedures

Answer: A

Explanation:
PgBouncer can accept many client connections while managing a smaller pool of PostgreSQL server connections. Azure recommends PgBouncer as a connection-management solution rather than simply increasing max_connections.

Question 6

An application acquires a PostgreSQL connection from its pool and then calls an external AI service that takes 15 seconds to respond. The application keeps the database connection checked out during those 15 seconds.

What is the primary concern?

A. PostgreSQL automatically deletes the connection
B. The connection remains occupied unnecessarily and reduces pool availability
C. The database will automatically increase its CPU capacity
D. The AI service will execute the PostgreSQL transaction

Answer: B

Explanation:
A pooled connection should generally be held only while database work is being performed. Holding connections during unrelated long-running operations reduces the number of connections available to other requests and can increase latency.

Question 7

An AI application has its compute resources in one Azure region and its Azure Database for PostgreSQL server in a distant region. The application performs many sequential database calls, and network latency is a major contributor to response time.

Which architectural change is most likely to reduce network latency?

A. Increase max_connections
B. Increase the PostgreSQL database password length
C. Place latency-sensitive application and database resources closer together
D. Increase the connection pool to several thousand connections

Answer: C

Explanation:
Reducing network distance can reduce round-trip latency for database operations. Increasing connection counts does not solve geographic network latency and may introduce additional resource contention. Azure explicitly identifies client location and cross-region traffic as factors in PostgreSQL performance.

Question 8

Which statement best describes transaction pooling in PgBouncer?

A. A PostgreSQL server connection can be reused after a client’s transaction completes
B. Every client permanently receives its own PostgreSQL server process
C. Every SQL statement requires a new physical database server
D. All application clients must share one PostgreSQL connection

Answer: A

Explanation:
In transaction pooling, a server-side PostgreSQL connection is associated with a client for the duration of a transaction and can subsequently be reused. Azure’s built-in PgBouncer uses transaction pooling by default.

Question 9

An administrator wants to improve PostgreSQL performance and notices that the database has a very high max_connections value. Many of the connections become active simultaneously during traffic spikes.

What is the primary concern with simply increasing max_connections further?

A. It automatically disables connection pooling
B. It prevents PostgreSQL from using indexes
C. It forces all queries to become distributed queries
D. More connections can increase memory and other resource consumption and cause performance problems

Answer: D

Explanation:
Each PostgreSQL connection consumes resources. A high number of active connections can increase memory and CPU pressure and contribute to contention. Azure specifically advises against simply increasing max_connections and recommends connection pooling such as PgBouncer when additional connection capacity is needed.

Question 10

A serverless AI application experiences sudden traffic spikes. Each newly created application instance establishes several PostgreSQL connections immediately. During scale-out events, the database reaches its connection limit.

Which design change is most appropriate?

A. Configure every serverless instance to create more connections
B. Use controlled connection pooling and carefully manage per-instance connection limits
C. Remove all database indexes
D. Increase query timeouts so connections remain open longer

Answer: B

Explanation:
Serverless scale-out can multiply connection counts quickly. Controlled pooling and conservative per-instance connection limits help prevent connection storms. PgBouncer can also be considered when appropriate. Increasing the number of connections per instance would make the problem worse.

Final Exam Perspective

For this AI-200 objective, think of connection optimization as a resource-management problem rather than simply a database configuration problem.

When you see an exam scenario involving:

Many clients + short-lived connections + high latency + connection errors

your thought process should be:

			
Are connections being reused?
        ↓
Is connection pooling configured?
        ↓
Is the pool appropriately sized?
        ↓
Would PgBouncer help?
        ↓
Are too many application instances creating connections?
        ↓
Is the application close enough to PostgreSQL?
        ↓
Are transactions short?
        ↓
Are CPU, memory, and query performance actually the bottleneck?

		

The most important rule to remember is:

Don’t solve connection pressure by blindly adding more database connections. Control and reuse connections, keep transactions efficient, minimize unnecessary network latency, and scale the database only when monitoring demonstrates that database resources—not connection management—are the actual bottleneck.

This distinction is especially important for AI workloads because AI applications frequently combine highly concurrent APIs, serverless processing, vector/database operations, and external AI-service calls. Efficient connection management helps keep the database available for the work that actually matters.

Go to the AI-200 Exam Prep Hub main page

AI-200, azure, Databases, Microsoft Certification August 15, 2026

Configure compute, memory, and storage resources to support vector workloads (AI-200 Exam Prep)

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
      --> Configure compute, memory, and storage resources to support vector workloads

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

Azure Database for PostgreSQL is well suited to AI applications that store relational data alongside vector embeddings. With the pgvector extension, PostgreSQL can store embeddings and perform vector similarity searches directly alongside application data and metadata.

However, vector workloads can be substantially different from traditional transactional workloads. AI applications may perform:

High-dimensional vector comparisons
Approximate nearest-neighbor (ANN) searches
Large vector index builds
Metadata filtering combined with vector searches
Concurrent similarity searches
Embedding ingestion and updates
Large scans or index maintenance operations

These workloads can place significant demands on CPU, memory, storage I/O, and storage capacity.

For the AI-200 exam, it is important to understand that optimizing a vector workload is not simply a matter of creating a vector index. The underlying Azure Database for PostgreSQL compute and storage configuration must also be capable of supporting the workload.

1. Understand the Relationship Between Compute, Memory, and Storage

A useful way to think about PostgreSQL performance is:

Compute → CPU and memory

Storage → capacity, IOPS, throughput, and latency

Workload → determines which resources become bottlenecks

Azure Database for PostgreSQL Flexible Server provides three primary compute tiers:

Compute tier	Typical purpose
Burstable	Development, testing, and workloads with intermittent or low CPU requirements
General Purpose	Production workloads requiring predictable compute and memory
Memory Optimized	Workloads requiring substantial memory relative to CPU

The available compute configurations vary by hardware generation and SKU. General Purpose provides approximately 4 GiB of memory per vCore, while Memory Optimized configurations provide substantially more memory per vCore.

For sustained vector workloads, General Purpose or Memory Optimized is generally more appropriate than Burstable because vector search and index construction can produce sustained CPU and memory demand.

2. Why CPU Matters for Vector Workloads

Vector similarity search involves mathematical operations over potentially thousands of numerical dimensions.

For example, a semantic search application might generate a query embedding:

[0.018, -0.273, 0.491, ...]

and compare it with thousands or millions of stored embeddings.

Depending on the search strategy, PostgreSQL may need to perform substantial computation to determine which vectors are closest to the query vector.

CPU becomes especially important when:

Queries perform exact vector searches.
ANN indexes are being built.
Many users execute vector searches concurrently.
Queries combine vector similarity with metadata filtering.
Embeddings are being generated and inserted at high volume.
Index maintenance is occurring while the application is serving queries.

A useful rule for the exam is:

If CPU is consistently saturated, increasing storage performance alone will not solve the problem.

Likewise, increasing the number of vCores does not automatically solve every performance problem. If the workload is storage-bound or memory-bound, additional CPU may provide little benefit.

3. Choosing the Compute Tier

Burstable

Burstable compute is designed for workloads that spend significant periods below their baseline CPU capacity and occasionally need additional CPU.

It is useful for:

Development environments
Testing
Proof-of-concept AI applications
Low-volume applications
Intermittent workloads

Burstable instances use CPU credits. If CPU demand remains high for an extended period, credits can be depleted, limiting the usefulness of this tier for sustained workloads.

Exam consideration

If a question describes a production AI application performing continuous vector searches with high concurrency, do not automatically select Burstable simply because it is less expensive.

4. General Purpose Compute

General Purpose provides a balance between CPU, memory, and predictable performance.

It is typically appropriate for:

Production AI applications
Moderate-to-high concurrency
Applications combining relational and vector workloads
RAG applications
Semantic search applications
Applications with sustained CPU requirements

For many production vector applications, General Purpose is a sensible starting point.

You should then monitor actual CPU, memory, storage I/O, and query performance before deciding whether to scale further.

5. Memory Optimized Compute

Memory Optimized configurations provide more memory per vCore than General Purpose.

Memory becomes especially important for vector workloads because vector indexes and working data can consume substantial amounts of memory.

Memory Optimized compute can be appropriate when:

Vector indexes are large.
Index construction requires substantial working memory.
Queries process large amounts of data.
The workload experiences memory pressure.
PostgreSQL benefits from caching more frequently accessed data.
Large concurrent queries need additional working memory.

The important exam concept is:

Choose Memory Optimized when memory—not simply CPU—is the limiting resource.

Adding CPU to a memory-constrained workload may not solve the underlying problem.

6. Why Memory Is Important for pgvector

Vector workloads can be memory-intensive for several reasons.

Consider a vector with 1,536 dimensions stored using 32-bit floating-point values.

The raw vector data requires approximately:

1,536 × 4 bytes = 6,144 bytes

or about 6 KB per vector, before accounting for row, table, index, and PostgreSQL storage overhead.

A million such vectors therefore represents several gigabytes of raw vector values before indexes and other data are considered.

The actual memory requirements depend on:

Number of vectors
Vector dimensionality
Data types
Index type
Number of concurrent queries
Query execution requirements
PostgreSQL configuration
Metadata and relational columns

This is why vector database sizing should not be based solely on the number of rows.

7. Storage Capacity Is Different From Storage Performance

One of the most important concepts for the exam is that storage capacity and storage performance are different things.

Storage capacity determines how much data can be stored.

Storage performance involves:

IOPS
Throughput
Latency

For example:

A database may have enough storage capacity but still have insufficient IOPS to handle its workload efficiently.

Azure Database for PostgreSQL uses its provisioned storage for database files, temporary files, transaction logs, and PostgreSQL server logs. Storage configuration also affects available I/O performance.

8. IOPS

IOPS means input/output operations per second.

IOPS is especially important for workloads that perform many relatively small reads and writes.

Examples include:

Transaction processing
Random index lookups
Concurrent queries
Embedding inserts
Index maintenance
Metadata lookups

A vector workload that performs many concurrent searches can generate significant storage activity, particularly when data or indexes cannot be efficiently served from memory.

9. Storage Throughput

Storage throughput describes how much data can be transferred per unit of time, generally measured in MB/s.

Throughput becomes important for operations such as:

Large table scans
Large index builds
Bulk loading
Backup and restore operations
ETL operations
Large data movement

For example, increasing IOPS may not solve a workload that is primarily moving large amounts of data and is constrained by throughput.

Think of the distinction this way:

IOPS = how many I/O operations

Throughput = how much data

Latency = how quickly an individual I/O operation completes

These concepts are related but are not interchangeable.

10. Storage Latency

Latency is the amount of time required to complete an individual I/O operation.

For interactive AI applications, low latency can be extremely important.

For example, suppose an application performs:

Receive a user’s question.
Generate an embedding.
Search the vector database.
Retrieve metadata.
Send context to an AI model.
Generate a response.

If the vector database takes too long to respond, it increases the overall response time experienced by the user.

Storage latency can therefore become part of the end-to-end latency of a RAG or semantic-search application.

11. Premium SSD and Premium SSD v2

Azure Database for PostgreSQL supports different storage options, including Premium SSD and Premium SSD v2.

Premium SSD provides provisioned storage with performance characteristics tied in part to disk size.

Premium SSD v2 provides more granular control over storage performance, allowing IOPS and throughput to be configured more independently of storage capacity.

This makes Premium SSD v2 particularly useful when an application needs high storage performance without necessarily requiring a correspondingly large amount of storage.

For example, consider an application that requires:

500 GB of actual data
High concurrent vector-search activity
High IOPS
Low latency

With traditional storage models, increasing storage capacity may be one way to obtain more performance.

With Premium SSD v2, performance can be tuned more directly through IOPS and throughput.

12. Storage Capacity Can Affect Performance

For Premium SSD, the provisioned disk size influences the baseline performance available from the disk.

Therefore:

Do not think of storage size as merely a capacity decision.

It can also affect performance.

However, increasing storage capacity solely to improve performance should not be the first optimization strategy.

First determine whether the bottleneck is actually storage performance.

Azure recommends considering compute and storage together because the compute SKU can itself impose limits on the I/O performance that the database can use.

13. Compute and Storage Must Be Balanced

Consider this example:

A PostgreSQL server is configured with storage capable of delivering 80,000 IOPS.

However, the selected compute configuration can drive only a much smaller number of IOPS.

The database cannot magically consume the full 80,000 IOPS.

The effective performance is limited by the bottleneck in the overall architecture.

This leads to an important principle:

The highest configured limit is not necessarily the actual achievable performance.

You need sufficient:

CPU
Memory
Storage IOPS
Storage throughput
Network capacity

to support the workload.

14. Vector Indexes Increase Resource Requirements

The choice of vector index has significant implications for resource consumption.

Current Azure Database for PostgreSQL pgvector documentation describes three supported vector index approaches:

IVFFlat
HNSW
DiskANN

These indexes have different performance and resource characteristics.

15. IVFFlat

IVFFlat uses an inverted-file approach that divides vectors into lists.

The number of lists influences how the vector data is organized.

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

Increasing the number of probes generally increases recall but also increases the amount of work required by the query.

Resource characteristics

IVFFlat generally:

Builds faster than HNSW.
Uses less memory during index construction than HNSW.
Provides approximate nearest-neighbor search.
Requires tuning of lists and probes.
Benefits from having representative data available when the index is built.

A major exam point is that IVFFlat generally has lower memory requirements than HNSW.

16. HNSW

HNSW creates a graph structure that connects vectors to neighboring vectors.

It is designed for approximate nearest-neighbor searches and generally provides a strong speed-versus-recall tradeoff.

HNSW:

Usually provides better query performance than IVFFlat for many workloads.
Requires more memory to build than IVFFlat.
Takes longer to build.
Does not require the same training step as IVFFlat.
Can be created before data is loaded.

HNSW has configurable parameters including:

m
ef_construction
ef_search

The default m is 16 and the default ef_construction is 64 in the current documented configuration. Query-time ef_search controls the size of the candidate list considered during search.

Resource implications

Increasing HNSW construction parameters can increase resource requirements.

Therefore:

A larger, more complex HNSW index may require more memory and compute resources.

This is one reason Memory Optimized compute can be useful for demanding vector workloads.

17. DiskANN

DiskANN is another approximate nearest-neighbor algorithm supported in Azure Database for PostgreSQL Flexible Server.

It is designed for scalable vector search and can provide a strong balance between recall, query performance, and index construction characteristics.

DiskANN can be particularly relevant for large-scale vector workloads.

Current Azure documentation also describes support for high-dimensional embeddings with newer DiskANN capabilities, including dimensions beyond the traditional 2,000-dimension indexing limit associated with HNSW and IVFFlat.

For the exam, the key point is not to memorize every DiskANN parameter. Instead, understand that index selection affects compute, memory, storage, query latency, and recall.

18. Vector Dimensions Affect Resource Requirements

Vector dimensionality has a direct impact on storage requirements.

Suppose an application stores:

			
1,000,000 vectors
1,536 dimensions
4 bytes per dimension

Raw vector storage is approximately:

			
1,000,000 × 1,536 × 4
= 6,144,000,000 bytes

or approximately 6.14 GB of raw vector values.

The actual database footprint will be larger because it also includes:

PostgreSQL row overhead
Table storage
Vector indexes
Metadata
Transaction logs
Temporary data
Other indexes
Database system overhead

Consequently:

Higher-dimensional embeddings increase both storage requirements and the amount of computation required for vector operations.

19. Dimension Limits and Indexing

A particularly important pgvector consideration is that the vector column should have a defined dimensionality when creating an index.

For example:

embedding vector(1536)

is indexable.

A generic declaration such as:

embedding vector

does not provide the dimensionality required for creating the traditional vector indexes.

Current documentation states that IVFFlat and HNSW indexing supports vectors up to 2,000 dimensions. Vectors above that size can be stored, but those index types cannot directly index them.

This can influence architecture decisions when selecting an embedding model.

20. PostgreSQL Memory Configuration

PostgreSQL has several memory-related configuration settings.

One particularly important parameter for maintenance operations is:

maintenance_work_mem

It controls memory available for operations such as:

Index creation
VACUUM
Certain maintenance operations

For vector workloads, this can matter significantly during large index builds.

However, simply setting maintenance_work_mem to an extremely large value is dangerous.

If multiple maintenance operations run concurrently, the total memory consumption can become substantial.

Azure documentation specifically warns that overly aggressive maintenance_work_mem settings can contribute to out-of-memory conditions.

Exam principle

More memory allocated to a PostgreSQL operation can improve performance, but the setting must be balanced against total available server memory and concurrency.

21. Index Creation Can Be Resource Intensive

Creating a vector index over millions of embeddings can require significant:

CPU
Memory
Storage I/O
Time

This is particularly true for HNSW.

For large data sets, it can be beneficial to:

Load the data.
Validate the data.
Create the vector index.
Test the index.
Tune query parameters.

Current Azure guidance recommends loading data before creating vector indexes when possible because index creation can be faster and the resulting layout can be more optimal.

22. Don’t Confuse Query Performance With Index-Build Performance

A configuration optimized for fast index creation is not necessarily the same configuration optimized for low query latency.

For example:

IVFFlat generally requires less memory during construction.
HNSW generally consumes more memory during construction but can provide better query performance.
DiskANN has its own performance and storage characteristics.

Therefore, evaluate both:

Build-time performance

and

Query-time performance

when selecting an indexing strategy.

23. Scaling Compute

Azure Database for PostgreSQL Flexible Server supports vertical scaling.

You can change:

Compute tier
Compute SKU
vCores
Memory

Compute and storage can be scaled independently.

Scale compute when:

CPU utilization is consistently high.
Queries are CPU-bound.
Memory pressure is present and a larger SKU provides more memory.
Concurrent vector searches are overwhelming the server.
Index construction requires more compute capacity.

24. Scale Memory When Memory Is the Bottleneck

Suppose monitoring shows:

CPU = 45%
Storage I/O = 40%
Available memory = very low
Query latency = high

Adding more CPU may not help much.

A better strategy may be to move to a larger compute SKU or Memory Optimized tier to increase available memory.

This is a classic exam scenario:

Identify the bottleneck before selecting the resource to scale.

25. Scale Storage When Capacity Is the Bottleneck

Storage should be increased when the database is approaching its capacity limit.

Azure Database for PostgreSQL storage can be scaled upward, but storage cannot generally be reduced after provisioning.

Storage growth planning should account for:

Base relational data
Vector embeddings
Vector indexes
PostgreSQL indexes
Temporary space
Transaction logs
Future data growth

Storage autogrow can also be used to automatically increase storage when conditions warrant it.

26. Scale Storage Performance When I/O Is the Bottleneck

Consider a server where:

CPU = 35%
Memory = healthy
Storage capacity = 40%
Storage I/O = consistently near its limit
Query latency = high

Adding more vCores may not solve the problem.

Instead, investigate:

Storage IOPS
Storage throughput
Storage latency
Storage type
Compute/storage I/O limits

Premium SSD v2 can be particularly useful when the workload needs higher IOPS or throughput without simply increasing capacity.

27. Connection Pooling Matters

AI applications can generate large numbers of concurrent requests.

Opening a new PostgreSQL connection for every request can create unnecessary overhead and increase pressure on:

CPU
Memory
Connection limits
Network resources

Connection pooling allows applications to reuse database connections.

For high-volume AI applications, connection pooling can therefore improve scalability and reduce connection-management overhead.

This is particularly important when an application receives many simultaneous semantic-search requests.

28. Combine Vector Search With Metadata Filtering

AI applications commonly need queries such as:

“Find the most semantically similar documents, but only from the customer’s region and only from documents created within the last year.”

That means the database may need to perform:

Vector similarity search.
Metadata filtering.
Sorting/ranking.
Result retrieval.

Indexes on frequently filtered relational columns can therefore be important even though the workload is primarily a vector workload.

For example:

			
CREATE INDEX idx_documents_tenant
ON documents (tenant_id);

and:

			
CREATE INDEX idx_documents_created
ON documents (created_at);

The exact indexing strategy should be based on actual query patterns.

29. Partitioning Can Help Large Workloads

Partitioning can be useful when data naturally divides into logical groups.

Possible partitioning strategies include:

Tenant
Geography
Date
Business unit
Data lifecycle

For example:

			
documents_2025
documents_2026
documents_2027

Partitioning can reduce the amount of data that must be considered for some queries.

However:

Partitioning is not automatically a vector-search optimization.

It should be used when the data model and query patterns make partition pruning useful.

30. Monitor Before You Scale

One of the strongest principles for AI-200 is:

Measure first, then optimize.

Important metrics and observations include:

Compute

CPU utilization
Memory utilization
CPU credits for Burstable instances

Storage

Storage used
Storage percentage
I/O percentage
IOPS
Throughput
Latency

Azure exposes storage-related metrics such as storage limit, storage percentage, storage used, and I/O percentage for monitoring.

PostgreSQL

Also examine:

Query duration
Slow queries
Connections
Locks
Cache behavior
Index usage
Autovacuum activity

Vector workload

Measure:

Vector query latency
Queries per second
Recall
Index build time
Index size
Candidate-search parameters
CPU utilization during vector searches

31. A Practical Resource-Sizing Process

A good process for configuring a PostgreSQL vector workload is:

Step 1: Estimate the data volume

Determine:

Number of records
Number of vectors
Vector dimensions
Expected growth

Step 2: Estimate vector storage

Calculate approximate raw vector size:

number of vectors × dimensions × bytes per dimension

Then add overhead for tables and indexes.

Step 3: Identify the workload

Determine whether the workload is primarily:

Read-heavy
Write-heavy
Search-heavy
Batch-oriented
High-concurrency
Mixed

Step 4: Select compute

Choose among:

Burstable
General Purpose
Memory Optimized

based on sustained CPU and memory requirements.

Step 5: Select storage

Consider:

Capacity
IOPS
Throughput
Latency
Growth
Cost

Step 6: Select the vector index

Evaluate:

IVFFlat
HNSW
DiskANN

based on:

Dataset size
Recall requirements
Query latency
Memory availability
Build time
Update frequency

Step 7: Load and index

When practical:

Load the data.
Create the vector index.
Validate query plans.
Benchmark vector queries.

Step 8: Monitor

Measure the workload under realistic concurrency.

Step 9: Scale the actual bottleneck

Do not blindly increase vCores or storage.

32. Common Exam Scenarios

Scenario 1: CPU is consistently high

Problem: Vector searches are CPU-intensive.

Likely solution: Increase compute capacity or move to a more appropriate compute tier.

Scenario 2: Memory is exhausted during HNSW index creation

Problem: HNSW requires substantial memory during construction.

Likely solution: Increase available memory and review index construction parameters.

Scenario 3: Storage I/O is saturated

Problem: CPU and memory are healthy, but storage I/O is near its limit.

Likely solution: Increase storage performance, such as IOPS/throughput, or use a more appropriate storage configuration.

Scenario 4: Storage capacity is nearly full

Problem: The database is approaching its provisioned capacity.

Likely solution: Increase storage capacity and/or enable an appropriate storage autogrow strategy.

Scenario 5: The workload is low-volume and intermittent

Problem: The application spends most of its time idle.

Likely solution: Burstable compute may be appropriate.

Scenario 6: High-concurrency production vector search

Problem: The application performs sustained vector searches with many simultaneous users.

Likely solution: General Purpose or Memory Optimized compute is generally more appropriate than Burstable, depending on whether CPU or memory is the dominant constraint.

33. Key AI-200 Exam Takeaways

Remember these relationships:

Requirement	Resource to investigate
Sustained CPU pressure	Compute/vCores
Memory pressure	Larger compute SKU / Memory Optimized
Storage capacity shortage	Storage size
High I/O operations	IOPS
Large data transfers	Throughput
Slow individual disk operations	Storage latency
Large HNSW index construction	Memory + CPU + storage
Low-volume intermittent workload	Burstable
Sustained production workload	General Purpose or Memory Optimized
High vector-search concurrency	Compute + memory + storage
High-dimensional embeddings	More storage and computational resources
Vector index build taking too long	Compute, memory, storage, and index strategy
Query latency too high	Identify whether CPU, memory, storage, index, or query plan is responsible

The central lesson is:

Vector database performance is an end-to-end resource problem.

Choosing the correct compute tier, providing sufficient memory, selecting appropriate storage performance, and choosing an appropriate vector index must all work together.

Practice Exam Questions

Question 1

An AI application uses Azure Database for PostgreSQL Flexible Server to perform thousands of vector similarity searches per minute. CPU utilization remains consistently above 90%, while memory and storage I/O remain well within acceptable limits.

What should you investigate first?

A. Increase storage capacity

B. Enable storage autogrow

C. Increase compute capacity

D. Increase storage throughput

Answer: C

Explanation: The evidence indicates that CPU is the bottleneck. Increasing storage capacity or throughput will not address a CPU-bound workload. Increasing the compute capacity can provide additional CPU resources. The key exam skill is identifying the actual resource bottleneck before scaling.

Question 2

A development application uses Azure Database for PostgreSQL for occasional vector searches. The database is idle most of the time but occasionally experiences short periods of increased CPU utilization.

Which compute tier is potentially the most appropriate?

A. Burstable

B. Memory Optimized

C. Ultra-high-memory General Purpose

D. Dedicated high-IOPS compute

Answer: A

Explanation: Burstable compute is designed for workloads that are normally below their baseline CPU capacity but occasionally need additional CPU. It can be appropriate for development and testing workloads with intermittent demand. It is generally less suitable for sustained production workloads.

Question 3

A production application creates a large HNSW vector index. Index creation frequently causes memory pressure and sometimes fails because the server runs out of memory.

Which action is most directly relevant?

A. Reduce storage capacity

B. Move to a larger-memory compute configuration

C. Enable storage autogrow

D. Reduce the number of PostgreSQL connections to zero

Answer: B

Explanation: HNSW index construction can require substantial memory. A larger compute configuration, particularly a Memory Optimized configuration when appropriate, provides additional memory. Storage autogrow addresses capacity rather than RAM availability.

Question 4

An Azure Database for PostgreSQL server has sufficient CPU and memory, but storage I/O utilization is consistently near its maximum and vector query latency is increasing.

What should the administrator investigate?

A. Increasing the number of embedding dimensions

B. Reducing available storage

C. Moving to Burstable compute

D. Increasing storage IOPS or otherwise improving storage performance

Answer: D

Explanation: The evidence indicates a storage I/O bottleneck. Storage performance can be addressed by evaluating IOPS, throughput, latency, and the selected storage configuration. Premium SSD v2 can provide more granular control over IOPS and throughput.

Question 5

Which statement best describes the relationship between storage capacity and storage performance in Azure Database for PostgreSQL?

A. Storage capacity and IOPS are always completely independent

B. Storage capacity can influence available storage performance, depending on the storage type

C. Storage capacity determines CPU utilization

D. Storage capacity has no relationship to database performance

Answer: B

Explanation: Storage capacity and storage performance are distinct concepts, but they are not always completely independent. With Premium SSD, provisioned disk size affects baseline performance characteristics. Premium SSD v2 provides more independent control over IOPS and throughput.

Question 6

A company wants to run a sustained, high-concurrency production RAG application using Azure Database for PostgreSQL. The workload continuously performs vector searches and requires predictable performance.

Which compute option is generally more appropriate than Burstable?

A. A development-sized Burstable instance

B. A smaller Burstable instance with CPU credits

C. A server with minimal memory

D. General Purpose or Memory Optimized compute, based on the workload’s bottleneck

Answer: D

Explanation: Sustained production workloads generally require predictable compute capacity. General Purpose provides a balanced configuration, while Memory Optimized is appropriate when memory requirements are especially high. Burstable is primarily intended for workloads with intermittent CPU requirements.

Question 7

A PostgreSQL vector workload has healthy CPU utilization but extremely low available memory during large vector-index operations. Which resource is the most important to evaluate?

A. Memory

B. Storage capacity only

C. Network bandwidth only

D. CPU credits

Answer: A

Explanation: The observed bottleneck is memory. Increasing CPU alone does not necessarily resolve memory pressure. A larger compute SKU or Memory Optimized tier can provide additional memory.

Question 8

A team needs to support a vector workload that requires high IOPS but does not require a large amount of additional storage capacity. Which storage option is particularly useful to investigate?

A. Burstable compute

B. Standard database backups

C. Premium SSD v2

D. Increasing PostgreSQL connection limits

Answer: C

Explanation: Premium SSD v2 allows IOPS and throughput to be configured more independently from storage capacity, making it useful when a workload needs substantial storage performance without simply provisioning a very large disk.

Question 9

An organization is selecting between IVFFlat and HNSW for a vector workload. The team has limited memory available and wants faster index construction, while accepting a potentially less favorable query speed/recall tradeoff.

Which index is generally the better starting point?

A. HNSW

B. A standard B-tree index on the vector column

C. No index under any circumstances

D. IVFFlat

Answer: D

Explanation: IVFFlat generally builds faster and uses less memory than HNSW. HNSW generally offers a better speed/recall tradeoff but requires more memory and takes longer to build. The appropriate choice ultimately depends on workload requirements and benchmarking.

Question 10

An AI application stores one million embeddings, each containing 1,536 dimensions using 4-byte floating-point values. Which statement is most accurate?

A. The raw vector values alone require approximately 6.14 GB before database and index overhead

B. The vectors require exactly 1.536 GB regardless of data type

C. Vector dimensionality has no effect on storage requirements

D. The vector index will always be smaller than the raw vector data

Answer: A

Explanation: The approximate raw vector storage is:

			
1,000,000 × 1,536 × 4 bytes
= 6,144,000,000 bytes

or approximately 6.14 GB. Actual database storage requirements will be larger because PostgreSQL must also store row overhead, metadata, indexes, transaction-related data, and other database structures. Higher-dimensional embeddings therefore increase both storage and computational requirements.

Final Exam Review

For AI-200, remember the following chain:

Vector workload → identify bottleneck → choose appropriate compute → provide sufficient memory → select storage capacity and performance → select vector index → benchmark → monitor → scale

The most important distinctions are:

CPU handles computational work.
Memory supports working data, caching, and resource-intensive operations such as vector-index construction.
Storage capacity determines how much data can be stored.
IOPS measures the number of storage operations that can be performed.
Throughput measures the volume of data transferred.
Latency measures how quickly individual I/O operations complete.
Compute and storage limits interact, so optimizing one layer does not guarantee equivalent end-to-end performance.
HNSW generally consumes more memory and takes longer to build than IVFFlat, but can provide a better speed/recall tradeoff.
Premium SSD v2 is useful when granular IOPS and throughput control is valuable.
Memory Optimized is appropriate when memory is the dominant resource requirement.
Burstable is best suited to intermittent or low-baseline CPU workloads rather than sustained, high-concurrency production vector workloads.
Always identify the bottleneck before scaling.

The exam is likely to test these concepts through scenarios rather than simply asking you to memorize resource definitions. When presented with a performance problem, first determine whether the evidence points to CPU, memory, storage capacity, IOPS, throughput, latency, query design, or vector-index configuration. Then select the resource or optimization that addresses that specific bottleneck.

Go to the AI-200 Exam Prep Hub main page

AI-200, Databases, Microsoft Certification, NoSQL August 15, 2026August 15, 2026

Model schemas and implement indexing strategies, including designing tables and choosing appropriate data types (AI-200 Exam Prep)

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
      --> Model schemas and implement indexing strategies, including designing tables and choosing appropriate data types

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

Azure Database for PostgreSQL is a fully managed PostgreSQL service that provides the capabilities of the PostgreSQL relational database engine while Azure manages much of the underlying infrastructure.

For the AI-200 exam, developers need to understand how to design an effective PostgreSQL schema and choose appropriate indexing strategies. These decisions directly affect:

Query performance
Storage requirements
Insert and update performance
Data integrity
Scalability
Application responsiveness
Resource consumption
AI and vector-search workloads

Two fundamental decisions are involved:

How should the data be modeled?
How should the database be indexed to efficiently retrieve that data?

A good schema and indexing strategy should be based on the application’s actual workload rather than simply creating an index on every column.

1. Understanding Relational Schema Design

A relational schema defines how information is organized into:

Tables
Columns
Data types
Primary keys
Foreign keys
Constraints
Indexes
Relationships

For example, an AI-powered customer-support application might store information in tables such as:

			
CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

		

And:

			
CREATE TABLE support_tickets (
    ticket_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    subject TEXT NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ticket_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

		

This design separates customer information from ticket information while establishing a relationship between them.

2. Choose Data Types Carefully

One of the most important schema-design decisions is choosing the appropriate data type for each column.

PostgreSQL provides many native data types, including numeric, character, date/time, Boolean, JSON, UUID, array, and other specialized types. (PostgreSQL)

The general principle is:

Choose the smallest appropriate type that accurately represents the data and its required operations.

Avoid automatically storing everything as TEXT.

2.1 Integer Types

PostgreSQL provides several integer types.

Type	Size	Typical use
`smallint`	2 bytes	Small numeric ranges
`integer`	4 bytes	General-purpose integers
`bigint`	8 bytes	Large identifiers or numeric values

For example:

customer_id BIGINT

may be appropriate when a system could eventually contain billions of records.

An integer may be sufficient when the expected range is much smaller.

Exam consideration

If a value can exceed the range of integer, use bigint.

Don’t select bigint merely because “bigger is better.” Larger types can increase storage requirements and potentially affect index size.

3. Exact Versus Approximate Numeric Values

PostgreSQL provides exact numeric types such as:

			
numeric
decimal

and approximate floating-point types such as:

			
real
double precision

numeric and decimal are appropriate when exact decimal arithmetic is important, such as financial amounts. PostgreSQL documents numeric/decimal as exact numeric types, while real and double precision are approximate floating-point types. (PostgreSQL)

For example:

price NUMERIC(10,2)

is preferable to:

price DOUBLE PRECISION

when representing currency.

Exam tip

If the question involves money, financial calculations, or exact decimal precision, think:

NUMERIC / DECIMAL

If approximate scientific or engineering calculations are acceptable, floating-point types may be appropriate.

4. Character Data Types

Common character types include:

			
text
varchar(n)
char(n)

For most variable-length textual application data, text or appropriately sized varchar is generally suitable.

For example:

description TEXT

could be appropriate for a support-ticket description.

A fixed-width char(n) should generally be reserved for situations where fixed-width semantics are actually useful.

Important distinction

A developer shouldn’t use varchar(100) simply because the database “requires” a length. PostgreSQL’s text type can be used for unrestricted variable-length strings.

If a maximum length is a business rule, however, enforcing that rule through a constraint can be appropriate.

5. Date and Time Types

PostgreSQL supports several date/time types, including:

date
time
timestamp
timestamp with time zone
interval

PostgreSQL uses timestamptz as an abbreviation for timestamp with time zone. (PostgreSQL)

For distributed cloud applications, timestamps frequently need to represent an absolute point in time.

For example:

created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP

is often preferable to:

created_at TIMESTAMP

when the application operates across multiple time zones.

Exam tip

If the requirement is:

“Store the instant an event occurred regardless of the user’s time zone.”

Think:

TIMESTAMPTZ

If the requirement is specifically a calendar date without a time component:

DATE

6. Boolean Values

Use:

BOOLEAN

for true/false information.

Example:

is_active BOOLEAN NOT NULL DEFAULT TRUE

Don’t store values such as:

			
"Y"
"N"

or:

			
"true"
"false"

as text unless there is a specific interoperability requirement.

Native types communicate intent more clearly and allow PostgreSQL to enforce appropriate semantics.

7. UUIDs

PostgreSQL has a native uuid type for universally unique identifiers. A UUID is a 128-bit value and can be useful in distributed applications where identifiers need to be generated independently across systems. (PostgreSQL)

For example:

			
CREATE TABLE documents (
    document_id UUID PRIMARY KEY,
    title TEXT NOT NULL
);

UUIDs can be particularly useful when:

Multiple systems generate identifiers.
Records are created independently by distributed services.
Exposing sequential database IDs externally is undesirable.
Globally unique identifiers are required.

However, UUIDs aren’t automatically better than integer keys. Sequential numeric identifiers can be smaller and may have favorable index characteristics.

8. JSON and JSONB

PostgreSQL supports both:

			
json
jsonb

json stores JSON text, while jsonb stores decomposed binary JSON data and provides indexing capabilities useful for querying JSON content. (PostgreSQL)

For applications that need to frequently query JSON attributes, jsonb is often the more useful choice.

For example:

			
CREATE TABLE documents (
    document_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    metadata JSONB
);

A document might contain:

			
{
    "language": "en",
    "category": "technical",
    "source": "internal"
}

		

This can be useful when an AI application has semi-structured metadata that doesn’t justify creating a separate relational column for every possible attribute.

Important design consideration

Don’t use JSONB as an excuse to abandon relational modeling.

If an attribute is:

frequently queried,
important to business logic,
highly structured,
relational in nature,

a normal relational column may be more appropriate.

9. Primary Keys

Every major entity should generally have a clearly defined primary key.

Example:

			
CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name TEXT NOT NULL
);

A primary key provides:

Entity identification
Uniqueness
A target for foreign-key relationships
An important access path for queries

PostgreSQL automatically creates a unique index to enforce a primary-key constraint.

Exam tip

Don’t create a separate duplicate index on a primary-key column unless there is a specific reason.

For example, creating:

			
CREATE INDEX idx_products_product_id
ON products(product_id);

after declaring:

product_id BIGINT PRIMARY KEY

would normally be redundant.

10. Foreign Keys and Relationships

Foreign keys maintain relationships between tables.

For example:

			
CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

		

This establishes:

			
Customer
   |
   +----< Orders

A foreign-key constraint protects referential integrity.

However, developers should also consider indexing foreign-key columns when they are frequently used for:

Joins
Filtering
Parent/child lookups
Deletes or updates involving referenced rows

A foreign-key constraint itself does not automatically create an index on the referencing column.

11. What Is an Index?

An index is a separate data structure that allows PostgreSQL to locate rows more efficiently than scanning the entire table.

Without an appropriate index, PostgreSQL may need to perform a sequential scan:

			
Read row 1
Read row 2
Read row 3
...
Read row 1,000,000

		

An index can allow PostgreSQL to locate relevant rows much more efficiently.

For example:

			
CREATE INDEX idx_customers_email
ON customers(email);

Now a query such as:

			
SELECT *
FROM customers
WHERE email = 'user@example.com';

has an index available for locating the matching row.

PostgreSQL emphasizes that indexes can significantly improve retrieval performance but also introduce system overhead, so they should be used sensibly. (PostgreSQL)

12. The Cost of Indexes

Indexes aren’t free.

An index consumes:

Disk space
Memory/cache resources
CPU during maintenance
Time during INSERT
Time during UPDATE
Time during DELETE

When a row changes, PostgreSQL may also need to update associated indexes.

Therefore:

More indexes do not automatically mean better performance.

For example, creating ten indexes on a heavily written table may significantly increase write overhead.

A good indexing strategy balances:

Read performance

against

Write and storage overhead.

13. B-tree Indexes

The default PostgreSQL index type is the B-tree.

For example:

			
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

B-tree indexes are particularly useful for:

Equality comparisons
Range comparisons
Sorting
ORDER BY
Many common join operations

For example:

WHERE customer_id = 100

or:

WHERE order_date >= '2026-01-01'

or:

ORDER BY order_date

are common candidates for B-tree indexes.

14. Indexing Columns Used in WHERE Clauses

Consider:

			
SELECT *
FROM orders
WHERE customer_id = 12345;

If this query is executed frequently against a large table, an index on customer_id may be beneficial:

			
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

The key question isn’t:

“Can I index this column?”

Almost any column can be indexed.

The better question is:

“Does an index on this column improve an important query enough to justify its maintenance cost?”

15. Selectivity Matters

Index usefulness depends partly on selectivity.

Selectivity describes how effectively a predicate narrows the number of rows that must be examined.

Suppose a table contains 10 million orders.

A query:

WHERE customer_id = 98765

might return only 20 rows.

That is highly selective.

An index is potentially very useful.

Now consider:

WHERE status = 'Active'

if 9.5 million of the 10 million rows have status = 'Active'.

The predicate is not very selective.

An index might provide little benefit, depending on the workload and query plan.

Exam principle

Don’t assume that every frequently filtered column should automatically have an index.

Consider:

Number of distinct values
Number of rows returned
Query frequency
Table size
Query execution plan

16. Composite Indexes

A composite, or multicolumn, index contains multiple columns.

For example:

			
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

This can be useful for queries such as:

			
SELECT *
FROM orders
WHERE customer_id = 100
  AND order_date >= '2026-01-01';

The order of columns in a composite B-tree index matters.

PostgreSQL generally gets the greatest benefit from constraints on the leading/leftmost columns of a multicolumn B-tree index. (PostgreSQL)

Therefore:

(customer_id, order_date)

and:

(order_date, customer_id)

are not interchangeable from an optimization perspective.

17. Choosing Column Order in Composite Indexes

Suppose the application frequently runs:

			
WHERE customer_id = ?
AND order_date >= ?

An index such as:

			
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

is a natural candidate.

The equality predicate on customer_id comes first, followed by the range condition on order_date.

A useful general pattern is:

Equality conditions first, followed by range/order columns, when that matches the workload.

But don’t treat this as an absolute rule. The optimizer and actual query workload matter.

18. Indexes for ORDER BY

Indexes can also help eliminate or reduce the cost of sorting.

For example:

			
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

can potentially support queries involving:

			
WHERE customer_id = 100
ORDER BY order_date;

PostgreSQL B-tree indexes naturally support ordered scans, and index ordering can also be explicitly configured when specialized ordering requirements exist. (PostgreSQL)

19. Unique Indexes

A unique index ensures that duplicate values aren’t allowed.

For example:

			
CREATE UNIQUE INDEX idx_customers_email
ON customers(email);

This can enforce uniqueness for email addresses.

Alternatively, define the business rule directly through a constraint:

email TEXT UNIQUE

The latter is often clearer when uniqueness is part of the table’s logical model.

20. Partial Indexes

A partial index indexes only rows satisfying a condition.

For example:

			
CREATE INDEX idx_open_tickets
ON support_tickets(customer_id)
WHERE status = 'Open';

This can be particularly useful when:

Only a subset of rows is frequently queried.
The qualifying subset is relatively small.
The predicate is stable and matches important queries.

A query such as:

			
SELECT *
FROM support_tickets
WHERE status = 'Open'
  AND customer_id = 100;

may benefit from the partial index.

Why partial indexes can help

Instead of indexing millions of rows:

10 million total rows

the index may contain only:

500,000 open tickets

That can reduce index size and maintenance overhead.

21. Expression Indexes

PostgreSQL can index the result of an expression rather than simply a column.

For example:

			
CREATE INDEX idx_users_lower_email
ON users (LOWER(email));

This can support queries such as:

			
SELECT *
FROM users
WHERE LOWER(email) = 'user@example.com';

Without a matching expression index, applying a function to the indexed column may prevent PostgreSQL from using an ordinary index on email as effectively.

Exam concept

If a query consistently searches on:

LOWER(column)

consider whether an expression index on:

LOWER(column)

is appropriate.

22. Covering Indexes and INCLUDE

PostgreSQL supports indexes that include additional non-key columns.

For example:

			
CREATE INDEX idx_orders_customer
ON orders(customer_id)
INCLUDE (order_date, total_amount);

The key column is:

customer_id

while:

			
order_date
total_amount

are included payload columns.

This can sometimes allow PostgreSQL to satisfy a query directly from the index through an index-only scan, reducing the need to access the table.

However, this should be used selectively because included columns increase index size.

23. GIN, GiST, and BRIN

Although B-tree is the default and most common index type, PostgreSQL provides several index types.

Important types include:

Index	Typical uses
B-tree	Equality, ranges, ordering
Hash	Equality comparisons
GIN	Multivalued data, JSONB, arrays, full-text-related use cases
GiST	Specialized data types, geometric/search operations
BRIN	Very large tables where values correlate with physical row order

For AI-200, don’t memorize these as isolated facts. Understand why a developer would choose a particular index.

24. BRIN Indexes

A BRIN, or Block Range Index, is useful when column values have a strong correlation with the physical order of rows.

A classic example is a huge table containing time-series data where rows are generally inserted in chronological order.

For example:

			
CREATE INDEX idx_events_created_brin
ON events USING BRIN(created_at);

A BRIN index is much smaller than a traditional B-tree index in suitable scenarios.

However, it is not a universal replacement for B-tree.

Exam clue

If you see:

Extremely large table
Naturally ordered data
Time-series-like workload
Strong correlation between physical order and column values

consider:

BRIN

25. GIN Indexes and JSONB

GIN indexes are commonly associated with data containing multiple values within a row, including JSONB and arrays.

For example:

			
CREATE INDEX idx_documents_metadata
ON documents USING GIN(metadata);

This can support queries that search within JSONB content.

For AI applications, this can be useful when documents contain metadata such as:

			
{
    "department": "finance",
    "language": "en",
    "document_type": "policy"
}

		

and queries need to filter based on those attributes.

26. Schema Design for AI Applications

AI applications frequently combine traditional relational data with:

Documents
Metadata
Embeddings
User information
Conversation history
Processing status
Model information
Timestamps

A relational schema might look like:

			
CREATE TABLE documents (
    document_id UUID PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    metadata JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

		

For a vector-enabled application, an embedding column may also be added using an appropriate vector extension/type.

For example, conceptually:

			
documents
---------------------------------
document_id
title
content
metadata
embedding
created_at

		

The exact vector implementation and indexing strategy depend on the PostgreSQL extension and AI workload being used.

27. Don’t Confuse Relational Indexes with Vector Indexes

This is particularly important for AI-200.

A traditional B-tree index is designed for operations such as:

WHERE customer_id = 123

or:

ORDER BY created_at

It is not a general-purpose solution for high-dimensional vector similarity searches.

Vector workloads may use specialized vector indexing mechanisms, such as those provided by pgvector or other supported vector extensions.

For example, Azure Database for PostgreSQL supports vector-search technologies and associated specialized indexes for AI workloads.

The important conceptual distinction is:

			
Traditional relational search
        ↓
B-tree / GIN / GiST / BRIN

versus:

			
Vector similarity search
        ↓
Vector-aware indexing

This distinction becomes especially important when studying the AI-200 PostgreSQL vector-search objectives.

28. Don’t Over-Index

One of the most common database design mistakes is creating indexes without considering the workload.

Imagine:

			
CREATE TABLE transactions (
    transaction_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    merchant_id BIGINT,
    amount NUMERIC(12,2),
    status TEXT,
    transaction_date TIMESTAMPTZ
);

		

It might be tempting to create five indexes:

			
customer_id
merchant_id
amount
status
transaction_date

		

But that may not be optimal.

Suppose the application primarily runs:

			
WHERE customer_id = ?
AND transaction_date >= ?

A composite index might be much more valuable:

			
CREATE INDEX idx_transactions_customer_date
ON transactions(customer_id, transaction_date);

The actual workload should drive the decision.

29. Indexes and Write Performance

Suppose a table has:

			
table
indexes

Every insert potentially requires maintenance of those indexes.

Therefore:

			
More indexes
      ↓
Potentially faster reads
      ↓
But slower writes + more storage

		

The goal is not maximum indexing.

The goal is:

The right indexes for the application’s important queries.

30. Use Query Plans to Validate Indexing Decisions

Don’t create an index and assume it is being used.

Use PostgreSQL query-plan tools such as:

EXPLAIN

and:

EXPLAIN ANALYZE

For example:

			
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 100;

The query plan can help determine whether PostgreSQL is performing:

Sequential scans
Index scans
Bitmap index scans
Index-only scans
Joins
Sorts
Other operations

The goal is to understand why a query performs the way it does.

31. Statistics Matter

PostgreSQL’s query optimizer relies on statistics about the data distribution.

If statistics are outdated, PostgreSQL may choose a poor execution plan.

For example, the optimizer might estimate:

Expected rows: 100

when the query actually returns:

2,000,000 rows

That can lead to an inappropriate plan.

Keeping table statistics current is therefore an important part of performance tuning.

Azure Database for PostgreSQL’s performance guidance specifically emphasizes examining query plans, query behavior, index usage, and statistics when diagnosing performance problems.

32. Query Store and Indexing

Azure Database for PostgreSQL Flexible Server provides Query Store capabilities for tracking query performance over time.

Query Store can help identify:

Long-running queries
Resource-intensive queries
Query execution frequency
Changes in query performance
Wait statistics
Potential tuning opportunities

Query Store stores its information in the azure_sys database.

This makes Query Store particularly useful when deciding:

“Which queries actually need optimization?”

rather than guessing based on the schema alone.

33. Autonomous Tuning

Azure Database for PostgreSQL Flexible Server also provides autonomous tuning capabilities.

It can analyze workload information and provide recommendations such as:

Creating potentially beneficial indexes
Removing duplicate indexes
Removing unused indexes
Analyzing tables with missing or outdated statistics
Vacuuming bloated tables

The important exam concept is that automated recommendations should still be evaluated in the context of the application’s workload.

34. A Practical Indexing Process

A good indexing workflow looks like this:

Step 1: Understand the workload

Identify:

Frequently executed queries
Important user-facing queries
Expensive queries
Joins
Filters
Sorts
Aggregations

Step 2: Examine query plans

Use:

EXPLAIN

and:

EXPLAIN ANALYZE

Step 3: Identify bottlenecks

Determine whether the problem involves:

Sequential scans
Poor join strategies
Missing indexes
Sorting
Excessive I/O
Outdated statistics
Poor query design

Step 4: Create the appropriate index

Choose among:

B-tree
Composite index
Partial index
Expression index
GIN
GiST
BRIN
Specialized vector indexes

Step 5: Test the change

Compare:

			
Before
   ↓
Query performance
   ↓
Create index
   ↓
Query performance
   ↓
After

		

Step 6: Monitor production behavior

A theoretically useful index may not provide sufficient real-world benefit.

Azure Query Store can be useful for measuring the effect of changes over time.

35. Common AI-200 Exam Traps

Trap 1: “Index every column”

Incorrect.

Indexes consume storage and introduce write-maintenance overhead.

Trap 2: “Use B-tree for everything”

Incorrect.

B-tree is the default and is excellent for many relational queries, but specialized workloads may require other index types.

Trap 3: “A foreign key automatically creates an index”

Incorrect.

A foreign-key constraint maintains referential integrity, but the referencing column does not automatically receive an index simply because the foreign key exists.

Trap 4: “A primary key needs another index”

Usually incorrect.

The primary-key constraint already creates a unique index.

Trap 5: “Composite index column order doesn’t matter”

Incorrect.

For B-tree indexes, leading columns matter significantly. (PostgreSQL)

Trap 6: “More indexes always improve performance”

Incorrect.

Indexes can improve reads but increase storage and write-maintenance costs.

Trap 7: “Use floating point for currency”

Generally incorrect.

Use an exact numeric type such as:

NUMERIC

when exact decimal arithmetic is required.

Trap 8: “Store all structured data as JSON”

Incorrect.

JSONB is valuable for semi-structured data, but strongly structured and frequently queried attributes may belong in relational columns.

Trap 9: “A relational index is automatically a vector index”

Incorrect.

Vector similarity searches require vector-aware approaches.

36. Quick Reference: Data Type Selection

Requirement	Good candidate
Small integer	`smallint`
General integer	`integer`
Very large integer	`bigint`
Exact decimal	`numeric` / `decimal`
Approximate decimal	`real` / `double precision`
Variable text	`text` / `varchar`
Calendar date	`date`
Absolute timestamp	`timestamptz`
True/false	`boolean`
Globally unique identifier	`uuid`
Semi-structured JSON	`jsonb`
Binary data	`bytea`

37. Quick Reference: Index Selection

Requirement	Potential index
Equality/range queries	B-tree
Sorting	B-tree
Composite filtering	Multicolumn B-tree
Frequently queried subset	Partial index
Function-based searches	Expression index
JSONB/array containment	GIN
Specialized data structures	GiST
Very large, physically correlated data	BRIN
Vector similarity	Vector-specific index

The actual choice should always be validated against the workload and execution plan.

38. Key Takeaways for the AI-200 Exam

For this topic, remember these principles:

Choose data types based on the data and required operations.
Use numeric/decimal when exact decimal arithmetic is required.
Use timestamptz when an absolute point in time must be represented across time zones.
Use uuid when globally unique identifiers are useful for a distributed system.
Use jsonb for queryable semi-structured JSON data.
Define primary keys to uniquely identify entities.
Foreign-key columns may need indexes for joins and related access patterns.
B-tree is the default choice for many equality, range, and ordering queries.
Composite-index column order matters.
Partial indexes can efficiently target frequently queried subsets.
Expression indexes can help when queries consistently apply functions to columns.
GIN, GiST, and BRIN serve specialized workloads.
Vector similarity searches require vector-aware indexing.
Every index has a maintenance and storage cost.
Use query plans and workload telemetry to validate indexing decisions.
Query Store can help identify expensive queries and evaluate performance changes.
Don’t optimize based solely on intuition—measure the workload.

10 Practice Exam Questions

Question 1

A financial application stores transaction amounts in Azure Database for PostgreSQL. The application must perform exact calculations involving dollars and cents.

Which data type should you use for the transaction amount?

A. DOUBLE PRECISION
B. NUMERIC(12,2)
C. REAL
D. VARCHAR(20)

Answer: B

Explanation

NUMERIC is an exact numeric type and is appropriate when exact decimal calculations are required, such as financial amounts. REAL and DOUBLE PRECISION are approximate floating-point types and can introduce rounding behavior that is undesirable for financial calculations.

Question 2

An application frequently executes this query:

			
SELECT *
FROM orders
WHERE customer_id = @customer_id
  AND order_date >= @start_date;

The table contains millions of rows.

Which index is the most appropriate starting point?

			
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

			
CREATE INDEX idx_orders_date_customer
ON orders(order_date, customer_id);

			
CREATE INDEX idx_orders_customer
ON orders(customer_id);

			
CREATE INDEX idx_orders_date
ON orders(order_date);

Answer: A

Explanation

The query filters by equality on customer_id and then applies a range condition to order_date. A composite B-tree index beginning with customer_id and followed by order_date is a strong candidate for this workload.

The important concept is that the order of columns in a composite index matters.

Question 3

A PostgreSQL table contains 50 million event records. Records are inserted approximately in chronological order. Queries frequently retrieve events based on a range of timestamps.

Which index type could be particularly appropriate if the timestamp values have a strong correlation with physical row order?

A. GIN
B. Hash
C. BRIN
D. Expression B-tree

Answer: C

Explanation

BRIN indexes are designed for very large tables where indexed values have a useful correlation with the physical order of rows. Time-series data that is inserted chronologically is a classic example.

Question 4

A developer creates this table:

			
CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    name TEXT NOT NULL
);

The developer then proposes creating another standard index on customer_id.

What is the best response?

A. Create the index because primary keys cannot be indexed.
B. Create the index because primary keys only enforce uniqueness.
C. Create the index because primary-key lookups always require two indexes.
D. The additional index is normally unnecessary because the primary key already has a unique index.

Answer: D

Explanation

A PostgreSQL primary-key constraint is backed by a unique index. Creating another identical index on the same column would normally be redundant and would consume additional storage and maintenance resources.

Question 5

An application stores document metadata in a PostgreSQL jsonb column:

metadata JSONB

The application frequently searches within the JSON documents for matching attributes.

Which index type is commonly appropriate for this workload?

A. GIN
B. BRIN
C. Hash
D. B-tree on the table’s primary key

Answer: A

Explanation

GIN indexes are well suited to indexing composite or multivalued data and are commonly used with jsonb data. They can make searches involving JSONB contents much more efficient.

Question 6

An application frequently executes:

			
SELECT *
FROM users
WHERE LOWER(email) = 'user@example.com';

There is a normal B-tree index on:

email

but the query still isn’t benefiting from the index as expected.

Which approach could directly support this search pattern?

A. Create a BRIN index on email.
B. Create a GIN index on the primary key.
C. Create an expression index on LOWER(email).
D. Convert email to BIGINT.

Answer: C

Explanation

The query applies LOWER() to the column. An expression index can index the result of that expression:

			
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

This allows PostgreSQL to efficiently support queries using the same expression.

Question 7

A developer is designing a global AI application and wants identifiers that can be generated independently by multiple distributed application instances without coordinating a central sequence.

Which data type is the best fit?

A. SMALLINT
B. UUID
C. REAL
D. DATE

Answer: B

Explanation

PostgreSQL’s native UUID type provides 128-bit universally unique identifiers. UUIDs are particularly useful when identifiers need to be generated independently across distributed systems.

Question 8

A developer wants to improve application performance and proposes creating indexes on every column in a frequently updated table.

Which statement best describes the problem with this approach?

A. PostgreSQL supports only one index per table.
B. Indexes cannot be created on columns used in updates.
C. Indexes can improve reads but increase storage and write-maintenance overhead.
D. PostgreSQL automatically deletes indexes that are not used.

Answer: C

Explanation

Indexes can significantly improve read performance, but they aren’t free. Inserts, updates, and deletes may require corresponding index maintenance. Excessive indexing can therefore increase write overhead and storage consumption.

Question 9

A support system has 20 million tickets, but only 200,000 are currently open. Most application queries retrieve open tickets by customer.

Which indexing strategy could reduce index size while targeting the important workload?

A. Create a partial index containing only open tickets.
B. Create an index on every column in the table.
C. Create a BRIN index on the ticket description.
D. Store the ticket status as JSON.

Answer: A

Explanation

A partial index can index only rows satisfying a predicate:

			
CREATE INDEX idx_open_tickets_customer
ON support_tickets(customer_id)
WHERE status = 'Open';

Because the application primarily queries open tickets, this can provide a smaller, workload-focused index.

Question 10

An AI application stores text embeddings in Azure Database for PostgreSQL and needs to perform nearest-neighbor similarity searches.

Which statement is correct?

A. A standard B-tree index is always sufficient for high-dimensional vector similarity searches.
B. A primary-key index automatically provides vector similarity search.
C. A BRIN index should always be used for embeddings.
D. A vector-aware indexing mechanism should be used for vector similarity workloads.

Answer: D

Explanation

Traditional relational indexes such as B-tree are designed for conventional relational operations such as equality, range filtering, and ordering. Vector similarity search requires vector-aware data types, operators, and indexing mechanisms supported by the chosen PostgreSQL vector solution.

Final Exam Perspective

The most important mindset for this AI-200 topic is to think of database design as a workload-driven optimization problem.

When presented with a scenario, ask:

What data am I storing?

Then:

What is the correct data type?

Then:

How will the application access the data?

Then:

What index best supports those access patterns?

And finally:

Does the index actually improve the workload enough to justify its cost?

That sequence is much more valuable for the exam than simply memorizing lists of PostgreSQL data types and index types.

For Azure Database for PostgreSQL specifically, Query Store and related performance tooling can help move that decision from guesswork to evidence by identifying expensive queries and allowing performance to be compared before and after changes.

Go to the AI-200 Exam Prep Hub main page

AI-200, azure, Databases, Microsoft Certification August 15, 2026

Connect and query Azure Database for PostgreSQL by using SDKs (AI-200 Exam Prep)

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
      --> Connect and query Azure Database for PostgreSQL by using SDKs

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

Azure Database for PostgreSQL is a fully managed PostgreSQL service that provides a familiar PostgreSQL database engine while Azure manages much of the underlying infrastructure, availability, maintenance, and scaling.

For AI-200, developers need to understand how applications connect to Azure Database for PostgreSQL and how they use programming-language client libraries to execute SQL statements.

The key idea is:

Your application normally connects to Azure Database for PostgreSQL through a PostgreSQL client library/driver, establishes a secure connection, executes parameterized SQL commands, processes the results, and properly manages connections and transactions.

Azure Database for PostgreSQL supports commonly used PostgreSQL client interfaces including:

Python — psycopg
C#/.NET — Npgsql
Java — JDBC
Node.js — pg
Go — PostgreSQL drivers such as pgx or pq
PHP — php-pgsql
Ruby — pg
C/C++ — PostgreSQL client libraries
ODBC — psqlODBC

These are PostgreSQL client libraries rather than an Azure-specific database SDK. (Microsoft Learn)

1. Understand the Connection Architecture

A typical application architecture looks like this:

			
Application
    |
    | PostgreSQL client library
    | (Npgsql, psycopg, JDBC, pg, etc.)
    v
Secure connection
    |
    | TLS
    v
Azure Database for PostgreSQL
    |
    v
PostgreSQL database
    |
    +-- Tables
    +-- Views
    +-- Indexes
    +-- Functions
    +-- Extensions

		

The application is responsible for using a PostgreSQL-compatible client library. Azure provides the managed PostgreSQL server.

For example:

			
C# application
      |
      v
   Npgsql
      |
      v
Azure Database for PostgreSQL

		

or:

			
Python application
      |
      v
   psycopg
      |
      v
Azure Database for PostgreSQL

		

This distinction is important for the exam.

Azure SDKs are commonly used to manage Azure resources and services.

PostgreSQL client libraries are used to communicate with the PostgreSQL database itself.

2. Obtain the Connection Information

An application generally needs:

Server hostname
Database name
Port
Username
Authentication information
TLS/SSL configuration

The standard PostgreSQL port is:

An Azure Database for PostgreSQL server typically has a hostname similar to:

myserver.postgres.database.azure.com

A connection string might look conceptually like:

			
host=myserver.postgres.database.azure.com
port=5432
dbname=mydatabase
user=myuser
password=<secret>
sslmode=require

		

The exact connection-string syntax varies by client library.

Azure’s current guidance shows PostgreSQL connections using TLS and port 5432. (Microsoft Learn)

3. Secure Connections with TLS

Applications should connect to Azure Database for PostgreSQL using encrypted connections.

Azure Database for PostgreSQL supports TLS 1.2 and TLS 1.3 and rejects TLS 1.0 and 1.1. (Microsoft Learn)

For example, a connection string can include:

sslmode=require

This tells the client to use an encrypted connection.

More stringent certificate validation can be configured using settings such as:

sslmode=verify-ca

or:

sslmode=verify-full

verify-full provides stronger validation because it verifies both the certificate chain and the server hostname.

Exam tip

If a question describes:

“The application must communicate with PostgreSQL securely.”

Look for TLS/SSL configuration rather than simply changing the database port.

Changing the port does not provide encryption.

4. Authentication Options

Applications can authenticate to Azure Database for PostgreSQL in several ways.

Common approaches include:

PostgreSQL authentication

The application supplies a PostgreSQL username and password.

Conceptually:

			
Application
    |
    | username + password
    v
PostgreSQL

		

This is straightforward but requires careful secret management.

Microsoft Entra authentication

Applications can also authenticate using Microsoft Entra identities.

This allows applications to obtain an access token rather than embedding a PostgreSQL password in application code.

Azure supports both system-assigned and user-assigned managed identities for authentication to Azure Database for PostgreSQL. (Microsoft Learn)

A managed-identity architecture can look like:

			
Azure App Service / VM / Function / Container
             |
             | Managed identity
             v
       Microsoft Entra ID
             |
             | Access token
             v
Azure Database for PostgreSQL

		

This can eliminate the need to store a database password in the application.

Exam tip

If a question says:

“The application is hosted in Azure and should access PostgreSQL without storing credentials.”

The likely direction is Microsoft Entra authentication with a managed identity, assuming the relevant service and database configuration support it.

5. Network Connectivity Matters

Successful SDK code does not guarantee a successful connection.

The application must also have network access to the PostgreSQL server.

Azure Database for PostgreSQL Flexible Server supports two primary networking approaches:

Public access, where allowed IP addresses are controlled through firewall rules
Private access, using virtual network integration

(Microsoft Learn)

Therefore, when troubleshooting a connection, consider:

			
Application
   |
   +--> DNS resolution
   |
   +--> Network routing
   |
   +--> Firewall / network rules
   |
   +--> TLS
   |
   +--> Authentication
   |
   +--> Database authorization
   |
   v
PostgreSQL

		

A connection failure does not necessarily mean the SDK code is incorrect.

6. Python and `psycopg`

For Python applications, psycopg is a current PostgreSQL client library.

The basic pattern is:

			
import psycopg
conn = psycopg.connect(
    "host=myserver.postgres.database.azure.com "
    "port=5432 "
    "dbname=mydatabase "
    "user=myuser "
    "password=<password> "
    "sslmode=require"
)
cursor = conn.cursor()
cursor.execute(
    "SELECT id, name FROM products WHERE category = %s",
    ("AI",)
)
rows = cursor.fetchall()
for row in rows:
    print(row)
cursor.close()
conn.close()

		

The important concepts are:

Create a connection.
Create a cursor.
Execute SQL.
Retrieve results.
Commit changes when appropriate.
Close resources.

Microsoft’s current Python guidance uses psycopg and demonstrates parameterized SQL through cursor.execute(). (Microsoft Learn)

7. Parameterized Queries

One of the most important development practices is to avoid constructing SQL by concatenating user input.

Avoid:

			
name = request.args["name"]
sql = "SELECT * FROM products WHERE name = '" + name + "'"
cursor.execute(sql)

This can expose the application to SQL injection.

Instead, use parameters:

			
cursor.execute(
    "SELECT * FROM products WHERE name = %s",
    (name,)
)

The database driver handles the parameter separately from the SQL statement.

Why this matters

Parameterized queries provide:

Better security
Safer handling of user input
Cleaner code
Better separation between SQL and data

Exam clue

If the question says:

“The application accepts user-provided values and must prevent SQL injection.”

The answer should generally involve parameterized queries, not string concatenation.

8. C#/.NET and Npgsql

For .NET applications, Npgsql is the commonly recommended PostgreSQL ADO.NET data provider.

(Microsoft Learn)

Install it using:

dotnet add package Npgsql

A basic example is:

			
using Npgsql;
var connectionString =
    "Host=myserver.postgres.database.azure.com;" +
    "Port=5432;" +
    "Database=mydatabase;" +
    "Username=myuser;" +
    "Password=<password>;" +
    "SSL Mode=Require;";
await using var connection =
    new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var command =
    new NpgsqlCommand(
        "SELECT id, name FROM products WHERE category = @category",
        connection);
command.Parameters.AddWithValue("category", "AI");
await using var reader =
    await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine(
        $"{reader.GetInt32(0)} - {reader.GetString(1)}");
}

		

Notice the use of:

@category

instead of concatenating a value into the SQL string.

9. JDBC for Java Applications

Java applications commonly use the PostgreSQL JDBC driver.

A conceptual example is:

			
String url =
    "jdbc:postgresql://myserver.postgres.database.azure.com:5432/mydatabase"
    + "?sslmode=require";
Connection connection =
    DriverManager.getConnection(
        url,
        username,
        password);
PreparedStatement statement =
    connection.prepareStatement(
        "SELECT id, name FROM products WHERE category = ?");
statement.setString(1, "AI");
ResultSet results = statement.executeQuery();
while (results.next()) {
    System.out.println(results.getString("name"));
}

		

The important pattern is:

			
Connection
    ↓
PreparedStatement
    ↓
Parameters
    ↓
executeQuery()
    ↓
ResultSet

		

Exam tip

If you see:

PreparedStatement

think:

Parameterized SQL and protection against SQL injection.

10. Node.js and the `pg` Package

Node.js applications can use the PostgreSQL pg package.

Conceptually:

			
const { Client } = require("pg");
const client = new Client({
    host: "myserver.postgres.database.azure.com",
    port: 5432,
    database: "mydatabase",
    user: "myuser",
    password: "<password>",
    ssl: true
});
await client.connect();
const result = await client.query(
    "SELECT id, name FROM products WHERE category = $1",
    ["AI"]
);
console.log(result.rows);
await client.end();

		

Notice that PostgreSQL parameters use placeholders such as:

			
$1
$2
$3

rather than constructing SQL dynamically.

11. Querying Data

Applications can use the client library to execute standard PostgreSQL SQL.

For example:

			
SELECT id, name, price
FROM products
WHERE category = 'AI'
ORDER BY price DESC;

The client library sends the SQL statement to PostgreSQL and returns the results to the application.

A typical workflow is:

			
Build SQL
   ↓
Bind parameters
   ↓
Execute command
   ↓
Database processes query
   ↓
Return rows
   ↓
Application processes rows

		

12. Executing INSERT, UPDATE, and DELETE

SDK/client libraries aren’t limited to SELECT.

They can execute data modification statements.

INSERT

			
INSERT INTO products (name, category, price)
VALUES ($1, $2, $3);

UPDATE

			
UPDATE products
SET price = $1
WHERE id = $2;

DELETE

			
DELETE FROM products
WHERE id = $1;

Applications must properly handle transactions for operations where multiple changes need to succeed or fail together.

13. Transactions

A transaction groups multiple database operations into a logical unit.

For example:

			
BEGIN
   |
   +--> INSERT order
   |
   +--> INSERT order item
   |
   +--> UPDATE inventory
   |
COMMIT

		

If something fails:

			
BEGIN
   |
   +--> INSERT order
   |
   +--> INSERT order item
   |
   +--> ERROR
   |
ROLLBACK

		

This provides atomicity.

Typical transaction pattern

			
with psycopg.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        cursor.execute(
            "INSERT INTO orders(customer_id) VALUES (%s)",
            (customer_id,)
        )
        cursor.execute(
            "UPDATE inventory SET quantity = quantity - %s "
            "WHERE product_id = %s",
            (quantity, product_id)
        )

		

If an exception occurs within the transaction context, the transaction can be rolled back rather than leaving partially applied changes.

14. Connection Pooling

Opening a new database connection for every request can be inefficient.

Consider a web API receiving 1,000 requests:

			
Request 1 → Open connection → Query → Close
Request 2 → Open connection → Query → Close
Request 3 → Open connection → Query → Close
...

This creates unnecessary connection overhead.

A connection pool instead maintains a set of reusable connections:

                Connection Pool
              +------------------+
Request ----->| Connection 1     |
Request ----->| Connection 2     |
Request ----->| Connection 3     |
Request ----->| Connection 4     |
              +------------------+

The application:

Requests a connection.
Uses it.
Returns it to the pool.

Benefits

Connection pooling can:

Reduce connection establishment overhead
Improve application performance
Handle concurrent workloads more efficiently
Reduce unnecessary database connection churn

Important distinction

A connection pool is not the same thing as a database transaction.

A pool manages reusable connections.

A transaction manages the atomicity of database operations.

15. Asynchronous Database Operations

Modern applications often use asynchronous database operations.

For example, .NET applications can use:

await connection.OpenAsync();

and:

await command.ExecuteReaderAsync();

This helps applications avoid blocking a thread while waiting for database I/O.

This can be particularly important for:

Web APIs
Serverless applications
High-concurrency applications
AI applications processing many requests

16. Handling Query Results

A database query may return:

Zero rows
One row
Many rows

Applications should not assume that a result always exists.

For example:

			
SELECT id, name
FROM products
WHERE id = $1;

The application should handle the case where no matching product exists.

For multiple rows, the application generally iterates over a cursor, reader, or result set.

17. Avoid Retrieving More Data Than Necessary

A common application mistake is:

			
SELECT *
FROM products;

when the application only needs two columns.

Prefer:

			
SELECT id, name
FROM products;

Similarly, use filtering:

			
SELECT id, name
FROM products
WHERE category = $1;

rather than retrieving an entire table and filtering the results in application code.

This reduces:

Data transferred over the network
Application memory usage
Database processing in some scenarios
Unnecessary work

18. Use the Database to Perform Database Work

Suppose an application needs the average product price.

Avoid:

			
Retrieve every product
       ↓
Send all products to application
       ↓
Calculate average in application

		

Prefer:

			
SELECT AVG(price)
FROM products;

The database is optimized to perform database operations.

Other useful SQL operations include:

			
COUNT()
SUM()
AVG()
MIN()
MAX()
GROUP BY
ORDER BY
JOIN

		

This is particularly relevant to AI applications because unnecessarily moving large datasets into application memory can become expensive and slow.

19. Stored Procedures and Functions

PostgreSQL supports database-side functions and procedures.

An application can invoke them through its client library.

For example:

SELECT calculate_customer_score($1);

This can be useful when business or database logic is intentionally centralized in PostgreSQL.

However, don’t automatically move all application logic into database functions.

Consider:

Maintainability
Performance
Security
Deployment complexity
Transaction requirements
Whether the logic belongs in the database or application

20. Connection Lifecycle

A reliable application should carefully manage database resources.

The general lifecycle is:

			
Create/acquire connection
          ↓
Open connection
          ↓
Create command/cursor
          ↓
Execute SQL
          ↓
Process results
          ↓
Commit or rollback
          ↓
Close/release resources

		

Using language-supported resource-management features is preferable.

For example, C# uses:

await using

and Python can use:

with

This reduces the chance of leaking connections or other resources.

21. Secrets Should Not Be Hard-Coded

Avoid:

password = "MySuperSecretPassword123!"

inside application source code.

Instead, use a secure configuration mechanism.

For Azure applications, a common architecture is:

			
Application
     |
     v
Managed Identity
     |
     v
Azure Key Vault
     |
     v
Database credentials/secrets

		

Or, when using Microsoft Entra authentication, eliminate the need for a database password where appropriate.

This is especially important in production AI applications because database credentials can provide access to sensitive business information.

22. Common Connection Problems

When an application cannot connect, troubleshoot systematically.

Problem 1: Incorrect hostname

Verify the server’s fully qualified domain name.

For example:

myserver.postgres.database.azure.com

Problem 2: Firewall restriction

With public access, the application’s source IP must be allowed by the server’s firewall configuration.

Problem 3: Private networking

If the server uses private access, the application must have appropriate connectivity to the virtual network.

Problem 4: Authentication failure

Verify:

Username
Password or token
Authentication method
Database permissions

Problem 5: TLS configuration

Verify the client supports the required TLS configuration and that the connection string is configured appropriately.

Problem 6: Wrong database

The server may be reachable, but the requested database may not exist or the user may not have access.

23. Connection Failure vs. Authorization Failure

This distinction is important for troubleshooting questions.

Connection failure

The application cannot establish a connection to PostgreSQL.

Possible causes:

			
DNS
Firewall
Network
Port
TLS
Server availability

		

Authentication failure

The server is reachable, but the credentials or authentication mechanism are invalid.

			
"Who are you?"
       ↓
Authentication

Authorization failure

The user successfully authenticated but doesn’t have permission to perform the requested operation.

			
"Who are you?"
       ↓
Authentication
       ↓
"What are you allowed to do?"
       ↓
Authorization

		

A question that says:

“The application successfully connects but receives a permission-denied error when querying a table.”

should lead you toward database permissions, not firewall configuration.

24. SDK/Client Library Selection

A useful AI-200 mental model is:

Application language	PostgreSQL client
Python	`psycopg`
C#/.NET	Npgsql
Java	JDBC PostgreSQL driver
Node.js	`pg`
Ruby	`pg`
PHP	`php-pgsql`
Go	PostgreSQL driver such as `pgx`
C	`libpq`

Azure’s current connection-library guidance lists these types of client interfaces for Azure Database for PostgreSQL Flexible Server. (Microsoft Learn)

Remember:

The client library communicates with PostgreSQL; it isn’t primarily an Azure resource-management SDK.

25. AI Application Considerations

This topic becomes especially important in AI applications.

A typical AI application might look like:

			
User
  |
  v
AI application
  |
  +--> Azure OpenAI
  |
  +--> Azure Database for PostgreSQL
  |       |
  |       +--> Application data
  |       +--> Embeddings
  |       +--> Vector indexes
  |
  +--> Azure Storage

		

The application may use PostgreSQL for:

Relational application data
Conversation history
User information
AI-generated metadata
Document metadata
Embeddings
Vector search

The SDK/client library provides the application with the database connection needed to execute SQL and, when configured, vector-related PostgreSQL operations.

26. Key Exam Takeaways

For AI-200, remember these relationships:

Connection

			
Application
    ↓
PostgreSQL client library
    ↓
TLS connection
    ↓
Azure Database for PostgreSQL

		

Python

psycopg

.NET

Npgsql

Java

JDBC

Node.js

pg

Security

			
TLS
+
secure credential management
+
Microsoft Entra authentication where appropriate
+
managed identities where appropriate

		

Query security

			
Parameterized queries
        ↓
Avoid SQL injection

Performance

			
Connection pooling
+
asynchronous I/O
+
efficient SQL
+
retrieve only required data

		

Transactions

			
BEGIN
   ↓
Multiple operations
   ↓
COMMIT
or
ROLLBACK

		

Troubleshooting

			
Network
  ↓
TLS
  ↓
Authentication
  ↓
Authorization
  ↓
SQL/query behavior

		

Practice Exam Questions

Question 1

A Python application hosted in Azure must connect to Azure Database for PostgreSQL and execute parameterized SQL queries. Which client library should the developer use?

A. psycopg
B. azure-storage-blob
C. azure-cosmos
D. redis-py

Answer: A

Explanation

psycopg is a PostgreSQL client library for Python. It provides the functionality required to establish PostgreSQL connections and execute SQL statements.

The other libraries target different Azure services or technologies:

azure-storage-blob — Azure Blob Storage
azure-cosmos — Azure Cosmos DB
redis-py — Redis

The important distinction is that Azure Database for PostgreSQL is accessed using a PostgreSQL client library.

Question 2

A web application accepts a product name from users and uses that value in a PostgreSQL query. Which approach provides the best protection against SQL injection?

A. Use a parameterized query and bind the product name as a parameter.

B. Encode the product name using Base64 before concatenating it into the SQL statement.

C. Store the product name in an Azure Storage blob before executing the query.

D. Disable TLS for the database connection.

Answer: A

Explanation

Parameterized queries separate SQL code from user-supplied values.

For example:

			
cursor.execute(
    "SELECT * FROM products WHERE name = %s",
    (product_name,)
)

The value is treated as data rather than executable SQL.

Base64 encoding does not prevent SQL injection, and neither Blob Storage nor TLS configuration solves SQL injection.

Question 3

An application is deployed using Azure Database for PostgreSQL with public network access. The application receives a connection timeout. The database server is running and the connection string contains the correct hostname. What should the developer investigate first?

A. Whether the SQL query uses a parameterized statement

B. Whether the database table has an index

C. Whether the application’s source IP address is allowed by the PostgreSQL firewall rules

D. Whether the application has enough memory to process query results

Answer: C

Explanation

With public access, Azure Database for PostgreSQL uses firewall rules to control allowed client IP addresses.

A timeout before a database connection is established points toward network connectivity rather than SQL query construction or database indexing.

The troubleshooting sequence should include:

			
DNS
→ Network
→ Firewall
→ TLS
→ Authentication
→ Authorization
→ Query

		

Question 4

A .NET application needs to connect to Azure Database for PostgreSQL and execute SQL statements. Which library is the appropriate PostgreSQL client?

A. Azure.Storage.Blobs

B. Azure.Messaging.ServiceBus

C. Microsoft.Data.SqlClient

D. Npgsql

Answer: D

Explanation

Npgsql is the PostgreSQL data provider for .NET and is used to connect to PostgreSQL databases and execute PostgreSQL SQL statements.

Microsoft.Data.SqlClient is designed for SQL Server/Azure SQL rather than PostgreSQL.

Question 5

An application performs five related database operations. If the third operation fails, none of the previous operations should remain committed. Which database capability should the developer use?

A. A transaction

B. A connection string

C. A firewall rule

D. A connection pool

Answer: A

Explanation

A transaction allows multiple operations to be treated as a single logical unit.

For example:

			
BEGIN
  Operation 1
  Operation 2
  Operation 3 ← failure
ROLLBACK

		

The rollback prevents earlier operations in the transaction from remaining committed.

A connection pool manages reusable connections; it does not provide transaction semantics.

Question 6

A high-traffic web API opens a new PostgreSQL connection for every HTTP request and closes it immediately after the query. The application experiences unnecessary connection overhead. What should the developer consider?

A. Disable TLS

B. Use connection pooling

C. Replace PostgreSQL with Blob Storage

D. Increase the database query timeout

Answer: B

Explanation

Connection pooling allows the application to reuse established database connections instead of repeatedly creating and destroying them.

This can reduce connection-establishment overhead and improve performance for applications handling many requests.

Question 7

An Azure-hosted application needs to access Azure Database for PostgreSQL without storing a database password in application source code. Which authentication approach is most appropriate when supported by the application’s hosting environment and database configuration?

A. Hard-code the administrator password in the application

B. Store the password in a source-code configuration file

C. Use Microsoft Entra authentication with a managed identity

D. Disable authentication on the PostgreSQL server

Answer: C

Explanation

Managed identities allow Azure resources to authenticate to supported services without developers embedding credentials in application code.

Azure Database for PostgreSQL supports Microsoft Entra authentication and managed identities. (Microsoft Learn)

Hard-coding credentials is insecure, and disabling authentication is not an appropriate solution.

Question 8

A Java application needs to execute the following query using a user-provided value:

			
SELECT *
FROM documents
WHERE category = ?

Which Java API should the developer use to safely bind the value?

A. PreparedStatement

B. StringBuilder

C. System.out

D. FileOutputStream

Answer: A

Explanation

PreparedStatement is designed for parameterized SQL.

The application can bind the parameter rather than concatenate user input into the SQL string.

For example:

			
PreparedStatement statement =
    connection.prepareStatement(
        "SELECT * FROM documents WHERE category = ?");
statement.setString(1, category);

This is safer than dynamically constructing SQL with user input.

Question 9

An application successfully establishes a connection to Azure Database for PostgreSQL. However, when it attempts to query a table, PostgreSQL returns a permission-denied error. Which area should the developer investigate?

A. DNS resolution

B. Azure Storage firewall rules

C. Database authorization and user permissions

D. PostgreSQL server hostname

Answer: C

Explanation

The application has already successfully connected, so basic network connectivity and server resolution are working.

A permission-denied error after connection generally indicates an authorization problem.

The developer should investigate:

Database user
Role membership
Table permissions
Schema permissions
Required privileges

This is different from authentication, which establishes who the user is.

Question 10

An application retrieves only the name and category of a product. Which query is generally preferable when those are the only required values?

			
SELECT *
FROM products;

			
SELECT *
FROM products
WHERE id = $1;

			
SELECT name, category
FROM products
WHERE id = $1;

			
SELECT *
FROM products
ORDER BY name;

Answer: C

Explanation

The application only needs name and category, so the query should retrieve only those columns and filter to the required row.

			
SELECT name, category
FROM products
WHERE id = $1;

This minimizes unnecessary data retrieval and uses a parameterized value.

The other queries retrieve unnecessary columns or, in some cases, unnecessary rows.

Final AI-200 Study Summary

For this topic, the most important thing to remember is that Azure Database for PostgreSQL is PostgreSQL, so applications generally communicate with it through standard PostgreSQL client libraries.

The core exam concepts can be condensed to:

Concept	Remember
Python	`psycopg`
.NET	Npgsql
Java	JDBC
Node.js	`pg`
Default PostgreSQL port	5432
Transport security	TLS
Query security	Parameterized queries
Multiple related operations	Transactions
High-volume connections	Connection pooling
Azure credential-free authentication	Managed identity + Microsoft Entra authentication
Public networking	Firewall rules / allowed IPs
Private networking	VNet/private connectivity
Authentication	Establishes identity
Authorization	Determines permissions
Query results	Process through cursor/reader/result set
Resource management	Close/release connections and cursors
Performance	Efficient SQL, limited columns/rows, pooling, appropriate async operations

The exam is especially likely to test whether you can distinguish the database client library, authentication, networking, authorization, query security, and connection management. Those concepts are easy to mix together, so keeping those boundaries clear is valuable.

Go to the AI-200 Exam Prep Hub main page

AI-200, azure, Azure AI, Cloud computing, Databases, Microsoft Certification, NoSQL August 15, 2026August 15, 2026

Store and retrieve embeddings and execute vector similarity search for semantic retrieval (AI-200 Exam Prep)

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 Cosmos DB for NoSQL
      --> Store and retrieve embeddings and execute vector similarity search for semantic retrieval

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 simply matching exact words.

For example, suppose a user asks:

“What options are available for taking my dog on vacation?”

A traditional keyword search might look for documents containing the words dog, vacation, or travel. A semantic search system can instead identify documents discussing pet-friendly hotels, even if those documents never use the exact words in the user’s question.

This is accomplished using vector embeddings and vector similarity search.

Azure Cosmos DB for NoSQL provides integrated vector storage, indexing, and search capabilities. Applications can store embeddings directly alongside their source documents and use the VectorDistance() system function to find documents whose vectors are closest to a query vector.

For the AI-200 exam, you should understand:

What embeddings are
How embeddings are generated
How embeddings are stored in Cosmos DB
Vector embedding policies
Vector indexing policies
flat, quantizedFlat, and diskANN
The VectorDistance() function
k-nearest-neighbor (kNN) searches
Semantic retrieval
Filtering vector searches
Why TOP N is important
How vector search fits into RAG applications
Important vector-search limitations

1. What Is a Vector Embedding?

A vector embedding is a numerical representation of information.

An embedding model converts content such as:

Text
Documents
Images
Audio
Other supported data

into an array of numerical values.

For example, a simplified embedding might look like:

[0.12, -0.43, 0.87, 0.21, -0.09]

Real-world embedding models generally produce vectors with many more dimensions.

The important concept is that the position of an embedding in a high-dimensional mathematical space represents characteristics of the original content.

Content with similar meanings tends to have vectors that are close together.

For example:

"How can I travel with my dog?"

might be semantically close to:

"Hotels that allow pets"

even though the two sentences don’t contain the same words.

2. Embeddings Are Generated Outside Cosmos DB

Azure Cosmos DB stores and searches embeddings, but the embedding itself is typically generated by an embedding model.

For example, an application might use an embedding API such as an Azure OpenAI embedding model.

The general workflow is:

			
Source content
      |
      v
Embedding model
      |
      v
Vector embedding
      |
      v
Azure Cosmos DB

		

For a search request:

			
User query
      |
      v
Embedding model
      |
      v
Query embedding
      |
      v
Cosmos DB vector search
      |
      v
Most semantically similar documents

		

The stored document embedding and query embedding need to be compatible. In practice, applications should generate both using the same embedding model or a compatible embedding space.

3. Storing Embeddings in Cosmos DB

One of the major advantages of the integrated vector capabilities in Azure Cosmos DB for NoSQL is that the embedding can be stored alongside the original document.

For example:

			
{
    "id": "doc001",
    "category": "travel",
    "title": "Pet-Friendly Hotels",
    "content": "Hotels that welcome dogs and cats...",
    "embedding": [
        0.123,
        -0.456,
        0.789,
        0.234
    ]
}

		

The application therefore doesn’t need to maintain a completely separate database containing the vector and another database containing the associated document.

The vector and its source data can be colocated.

This is particularly useful for AI applications because the application can retrieve both:

The similarity result
The original content needed to answer the user’s question

from the same Cosmos DB item.

4. What Is Semantic Retrieval?

Semantic retrieval means finding information based on its meaning rather than simply matching keywords.

Consider these two documents:

Document A

“Our resort provides accommodations for guests traveling with pets.”

Document B

“Our resort has a swimming pool and fitness center.”

A user searches:

“Where can I stay with my dog?”

Document A is likely to have a much closer semantic relationship to the query.

A vector search system identifies that relationship by comparing embeddings.

The basic process is:

Generate embeddings for documents.
Store the embeddings with the documents.
Generate an embedding for the user’s query.
Compare the query vector with document vectors.
Rank documents according to similarity.
Return the most relevant documents.

This is the foundation of many retrieval-augmented generation (RAG) applications.

5. Vector Search in Azure Cosmos DB

Azure Cosmos DB for NoSQL provides vector search capabilities through:

Vector embedding policies
Vector indexing policies
The VectorDistance() system function

Vector indexes improve vector-search efficiency by reducing latency and RU consumption compared with an unindexed/full-scan approach.

At a conceptual level:

                  Azure Cosmos DB
              +---------------------+
              |                     |
Document ---> | Original content    |
              |                     |
Embedding --> | Vector embedding    |
              |                     |
              | Vector index        |
              |                     |
              +----------+----------+
                         ^
                         |
                  VectorDistance()
                         |
                  Query embedding

6. Vector Embedding Policies

A vector embedding policy describes the vector properties that Cosmos DB should treat as embeddings.

The policy can specify characteristics such as:

The vector property path
Number of dimensions
Distance function
Data type

The policy establishes how Cosmos DB should interpret the vector data.

A simplified conceptual configuration might look like:

			
{
    "vectorEmbeddings": [
        {
            "path": "/embedding",
            "dataType": "float32",
            "dimensions": 1536,
            "distanceFunction": "cosine"
        }
    ]
}

		

The exact configuration supported depends on the current Cosmos DB capabilities and account configuration, but the important exam concept is:

The vector embedding policy describes the characteristics of the vector data.

Don’t confuse this with the vector indexing policy.

7. Vector Indexing Policies

The vector indexing policy determines how Cosmos DB indexes the vectors for vector search.

Azure Cosmos DB for NoSQL currently provides three primary vector index types:

Index	General purpose
`flat`	Exact/brute-force vector search
`quantizedFlat`	Quantized vector search for smaller/scoped workloads
`diskANN`	Efficient approximate vector search for larger workloads

Choosing the appropriate index is an important architectural decision.

8. The `flat` Vector Index

The flat index performs a brute-force comparison of vectors.

Its major advantage is accuracy.

A flat search can provide exact nearest-neighbor results.

However, it has a maximum vector dimensionality of 505 dimensions, which makes it unsuitable for many modern high-dimensional embedding models.

It can be appropriate for relatively small vector datasets or situations where exact recall is particularly important.

Key exam concept

Flat = exact/brute-force search.

9. The `quantizedFlat` Vector Index

quantizedFlat compresses vectors before storing them in the vector index.

This can provide:

Lower latency
Higher throughput
Lower RU consumption

compared with an ordinary flat index.

The trade-off is that quantization can result in some loss of accuracy.

quantizedFlat supports vectors up to 4,096 dimensions.

Microsoft currently describes quantizedFlat as particularly appropriate for smaller or more narrowly scoped searches, with 50,000 vectors or fewer in the search scope being a useful general guideline—not an absolute limit. Actual workloads should be benchmarked.

Key exam concept

quantizedFlat = compressed/brute-force search with improved efficiency and a possible small accuracy trade-off.

10. The `diskANN` Vector Index

diskANN is designed for efficient approximate vector search, particularly for larger workloads.

It can provide:

Low latency
High throughput
Efficient RU consumption
High retrieval accuracy

It supports vectors up to 4,096 dimensions.

Microsoft describes DiskANN as generally the most performant option when the search scope exceeds approximately 50,000 vectors, although actual workload testing remains important.

Key exam concept

diskANN = approximate vector search optimized for larger datasets/search scopes.

11. Vector Index Comparison

For exam preparation, remember the following:

Characteristic	`flat`	`quantizedFlat`	`diskANN`
Search type	Exact/brute force	Quantized brute force	Approximate
Maximum dimensions	505	4,096	4,096
Accuracy	Exact	Slight possible loss	High, configurable trade-offs
Large datasets	Poor fit	Better for smaller/scoped data	Excellent
Latency at scale	Higher	Moderate	Lower
RU efficiency at scale	Lower	Better	Better
Typical use	Small/exact searches	Smaller/scoped searches	Large-scale vector search

12. Important Requirement: Vector Index Configuration

A vector index must be configured for the vector property that will be searched.

For example:

			
"vectorIndexes": [
    {
        "path": "/embedding",
        "type": "diskANN"
    }
]

		

The vector embedding policy and vector index work together.

A useful way to remember the distinction is:

Embedding policy = What is my vector?

Vector index = How should I search my vector?

13. Performing Vector Similarity Search

The primary Cosmos DB function used for vector similarity search is:

VectorDistance()

A basic query might look like:

			
SELECT TOP 10
    c.title,
    VectorDistance(c.embedding, @queryVector) AS SimilarityScore
FROM c
ORDER BY VectorDistance(c.embedding, @queryVector)

		

This query:

Takes the query vector.
Compares it with c.embedding.
Calculates a vector distance.
Sorts the results.
Returns the top 10 results.

Microsoft specifically recommends using TOP N for vector searches because returning unnecessary results increases RU consumption and latency.

14. Understanding `VectorDistance()`

The function conceptually compares:

			
Document vector
        |
        v
VectorDistance()
        ^
        |
Query vector

		

The result represents the distance between the vectors.

The exact interpretation depends on the configured distance function.

Common distance concepts include:

Cosine
Euclidean
Dot product

The application should use the distance function appropriate for the embedding model and workload.

15. Why Distance Matters

Suppose the query embedding is:

Q = [0.2, 0.3, 0.5]

and the database contains:

			
A = [0.2, 0.3, 0.5]
B = [0.8, 0.1, 0.2]
C = [-0.4, 0.7, 0.1]

The vector closest to the query is likely the most semantically similar.

The search engine can therefore rank results:

			
Document A
Document B
Document C

The application doesn’t have to know the meaning represented by every dimension.

The embedding model and vector-distance calculation handle that mathematical representation.

16. Always Use `TOP N`

A particularly important exam and practical-development point is:

Use TOP N with vector searches.

For example:

			
SELECT TOP 5
    c.id,
    c.title,
    VectorDistance(c.embedding, @queryVector) AS score
FROM c
ORDER BY VectorDistance(c.embedding, @queryVector)

		

If the application only needs the five most relevant documents, there’s little reason to retrieve thousands of results.

Returning unnecessary results can increase:

RU consumption
Latency
Network traffic
Application processing

Microsoft explicitly recommends TOP N for vector searches.

17. Filtering Vector Searches

Vector search can also be combined with traditional query filtering.

For example:

			
SELECT TOP 10
    c.title,
    c.category,
    VectorDistance(c.embedding, @queryVector) AS score
FROM c
WHERE c.category = "travel"
ORDER BY VectorDistance(c.embedding, @queryVector)

		

This means:

Find the most semantically similar documents within the travel category.

This is extremely useful in real applications.

Examples include:

Search products within a specific department.
Search documents belonging to a specific tenant.
Search hotel information within a particular region.
Search only documents that a user is authorized to access.

Azure Cosmos DB supports combining vector search with other query filtering capabilities.

18. Vector Search and Partitioning

Azure Cosmos DB applications should always consider partitioning.

For example, a multi-tenant application might have:

			
{
    "id": "doc123",
    "tenantId": "tenantA",
    "title": "Company policy",
    "embedding": [...]
}

		

A query could restrict retrieval to a particular tenant:

			
SELECT TOP 10
    c.title,
    VectorDistance(c.embedding, @queryVector) AS score
FROM c
WHERE c.tenantId = @tenantId
ORDER BY VectorDistance(c.embedding, @queryVector)

		

This can narrow the search scope and can be important for both performance and data isolation.

19. Semantic Search vs. Keyword Search

It is important to understand the difference.

Keyword search

A keyword search primarily asks:

Does this document contain the requested word or phrase?

For example:

"automobile"

might fail to find a document that only says:

"car"

Semantic search

Semantic search asks:

Which documents are mathematically closest in meaning to this query?

Therefore:

"automobile"

may retrieve documents discussing:

			
cars
vehicles
motor vehicles
transportation

depending on how the embedding model represents the concepts.

20. Hybrid Search

Vector search doesn’t have to replace traditional search.

Many AI applications use hybrid search, combining:

Keyword/full-text search
Vector similarity
Metadata filtering

For example:

			
User query
     |
     +--------------------+
     |                    |
     v                    v
Keyword search       Vector search
     |                    |
     +---------+----------+
               |
               v
       Combined ranking
               |
               v
        Relevant results

		

This can provide better retrieval than relying exclusively on either keyword or vector search.

For example, vector search is good at identifying semantic similarity, while keyword search can be valuable when an exact product ID, name, or technical term matters.

21. Vector Search and RAG

One of the most important practical applications of vector search is Retrieval-Augmented Generation (RAG).

A simplified RAG architecture looks like this:

              DOCUMENT INGESTION
                     |
                     v
             Generate embeddings
                     |
                     v
             Azure Cosmos DB
          +----------------------+
          | Documents            |
          | Embeddings            |
          | Vector index          |
          +----------------------+

                     ^
                     |
              Vector retrieval
                     |
                     |
User question --> Generate embedding
                     |
                     v
             Vector similarity search
                     |
                     v
              Relevant documents
                     |
                     v
                  LLM
                     |
                     v
              Generated answer

The vector database is responsible for retrieving relevant information.

The LLM is responsible for generating the final response using that retrieved information.

This distinction is important.

Vector search retrieves information; the LLM generates the response.

22. Keeping Embeddings Synchronized

Suppose the source document changes:

			
Original document
       |
       v
Embedding A

The document is updated:

			
Updated document
       |
       v
Embedding A  <-- stale!

The embedding may no longer accurately represent the document.

Therefore, applications should have a mechanism to regenerate embeddings when source content changes.

Azure Cosmos DB’s change feed can be used as part of an architecture that detects changes and triggers embedding regeneration. The current AI-200 training material specifically includes change-feed processing for keeping embeddings synchronized.

A common architecture is:

			
Document updated
      |
      v
Cosmos DB change feed
      |
      v
Processing component
      |
      v
Generate new embedding
      |
      v
Update Cosmos DB item

		

23. Vector Index Limitations You Should Know

Several limitations are particularly relevant for the AI-200 exam.

Maximum dimensions

Current limits include:

flat: 505 dimensions
quantizedFlat: 4,096 dimensions
diskANN: 4,096 dimensions

Minimum vectors for quantizedFlat and diskANN

quantizedFlat and diskANN require at least 1,000 vectors for indexed vector searching. If fewer than 1,000 vectors are present, a full scan can be performed instead.

Shared throughput

Vector indexing and search currently aren’t supported on accounts using shared throughput.

Vector policy changes

Vector embedding and vector indexing policy settings aren’t simply modified in place. Depending on the specific configuration, the existing policy/index must be removed and recreated, or a new container may be required.

Vector search cannot simply be disabled

Once vector indexing and search are enabled on a container, it cannot simply be disabled.

24. Common Exam Traps

Trap 1: Confusing embeddings with indexes

An embedding is the numerical representation of content.

An index is the structure used to efficiently search those vectors.

Trap 2: Thinking Cosmos DB generates the embedding

Cosmos DB stores and searches embeddings.

An embedding model, such as an embedding API, generates the embedding.

Trap 3: Assuming `diskANN` is exact

diskANN is an approximate nearest-neighbor approach.

It is designed to provide excellent performance while maintaining high retrieval quality.

Trap 4: Assuming `quantizedFlat` is exact

Quantization can introduce a small loss of accuracy.

Trap 5: Forgetting `TOP N`

A vector search should generally use TOP N to avoid unnecessarily expensive retrieval.

Trap 6: Using `flat` for a 1,536-dimensional embedding

The current flat limit is 505 dimensions.

A 1,536-dimensional embedding requires a vector index type supporting that dimensionality, such as quantizedFlat or diskANN.

Trap 7: Treating vector search as keyword search

Vector search is based on semantic similarity, not exact text matching.

25. Exam-Focused Summary

For AI-200, remember this chain:

			
Source data
     |
     v
Embedding model
     |
     v
Vector embedding
     |
     v
Cosmos DB document
     |
     v
Vector embedding policy
     |
     v
Vector index
     |
     v
VectorDistance()
     |
     v
TOP N results
     |
     v
Semantic retrieval

		

The most important concepts are:

Concept	Remember
Embedding	Numerical representation of content
Vector store	Stores and retrieves embeddings
Vector embedding policy	Defines characteristics of vectors
Vector index	Makes vector searches more efficient
`flat`	Exact/brute-force; max 505 dimensions
`quantizedFlat`	Quantized; max 4,096 dimensions
`diskANN`	Approximate, efficient large-scale search; max 4,096 dimensions
`VectorDistance()`	Performs vector distance calculation
`TOP N`	Limits results and helps control RU/latency
Semantic search	Finds content by meaning
Metadata filtering	Narrows the search space
Hybrid search	Combines lexical and vector retrieval
RAG	Uses retrieved context to augment LLM generation
Change feed	Can trigger embedding refresh when data changes

Practice Exam Questions

Question 1

An AI application stores product descriptions in Azure Cosmos DB for NoSQL. The application needs to find products that are semantically similar to a user’s natural-language query.

What should the application do?

A. Store the product descriptions as strings and use CONTAINS() exclusively.

B. Generate embeddings for the product descriptions and store the vectors with the documents.

C. Convert each product description to a partition key.

D. Store each word as a separate Cosmos DB item.

Answer: B

Explanation:
Semantic retrieval requires converting content into vector embeddings. The embeddings can then be stored alongside the original documents in Cosmos DB and compared with a query embedding. Keyword functions such as CONTAINS() don’t provide semantic similarity.

Question 2

An application uses a 1,536-dimensional embedding model and needs an efficient vector index for a large production dataset.

Which vector index type is the most appropriate choice?

A. flat

B. hash

C. range

D. diskANN

Answer: D

Explanation:
diskANN supports vectors up to 4,096 dimensions and is designed for efficient approximate vector search at larger scales. flat is limited to 505 dimensions and therefore cannot index a 1,536-dimensional vector.

Question 3

An application needs the five most semantically similar documents to a query vector.

Which query pattern should be used?

			
SELECT *
FROM c
ORDER BY VectorDistance(c.embedding, @queryVector)

			
SELECT TOP 5 *
FROM c
ORDER BY c.embedding

			
SELECT TOP 5 *
FROM c
ORDER BY VectorDistance(c.embedding, @queryVector)

			
SELECT *
FROM c
WHERE c.embedding = @queryVector

Answer: C

Explanation:
VectorDistance() calculates the distance between the stored embedding and query vector. TOP 5 limits the results to the five most relevant documents and helps avoid unnecessary RU consumption and latency.

Question 4

Which statement best describes the purpose of a vector embedding?

A. It is a Cosmos DB authentication token.

B. It is the partition key automatically generated by Cosmos DB.

C. It is a numerical representation of the semantic characteristics of content.

D. It is an index containing document metadata.

Answer: C

Explanation:
An embedding is a numerical representation generated by an embedding model. Semantically related content tends to produce vectors that are close together in vector space.

Question 5

A company has a relatively small vector search workload and wants to use a vector index that compresses vectors to improve efficiency while accepting a possible small loss in accuracy.

Which index should it consider?

A. flat

B. quantizedFlat

C. diskANN

D. NoSQL range indexing

Answer: B

Explanation:
quantizedFlat compresses vectors before indexing. This can improve latency, throughput, and RU efficiency compared with flat, at the potential cost of some accuracy. It is particularly suited to smaller or more narrowly scoped searches.

Question 6

An application has documents containing both an embedding and a category property. It needs to find the most semantically similar documents, but only within the "finance" category.

Which approach is appropriate?

A. Perform a vector search without filtering and discard non-finance results afterward.

B. Store each category in a separate Cosmos DB account.

C. Use VectorDistance() together with a WHERE filter for the category.

D. Replace the embeddings with category names.

Answer: C

Explanation:
Vector search can be combined with traditional Cosmos DB query filters. The application can use a WHERE clause to restrict the search to documents matching the required metadata.

Question 7

A developer changes the text of a document but continues using the embedding that was generated from the old version.

What is the primary problem?

A. The partition key automatically changes.

B. The vector index is deleted.

C. The document becomes unreadable.

D. The embedding may no longer accurately represent the document.

Answer: D

Explanation:
An embedding represents the content used to generate it. If the source content changes substantially, the old embedding can become stale. Applications can use mechanisms such as the Cosmos DB change feed to detect changes and trigger embedding regeneration.

Question 8

Which statement correctly describes the flat vector index in Azure Cosmos DB for NoSQL?

A. It performs exact/brute-force vector search and supports vectors up to 505 dimensions.

B. It performs approximate DiskANN search and supports 4,096 dimensions.

C. It compresses vectors and always produces approximate results.

D. It is used only for keyword searches.

Answer: A

Explanation:
The flat index performs brute-force vector search and can provide exact nearest-neighbor results. Its current maximum vector dimensionality is 505.

Question 9

An AI application uses vector search as part of a RAG architecture.

What is the primary purpose of the vector search portion of the architecture?

A. Generate the final natural-language response.

B. Retrieve content that is semantically relevant to the user’s query.

C. Train the large language model.

D. Replace the embedding model.

Answer: B

Explanation:
Vector search retrieves relevant information based on semantic similarity. The retrieved content can then be supplied to an LLM as context for generating the final answer. Vector retrieval and LLM generation are separate responsibilities.

Question 10

A developer creates a vector search query that returns every matching document instead of limiting the result set. The application only needs the top 10 results.

What should the developer change?

A. Remove the vector index.

B. Increase the embedding dimensionality.

C. Add a TOP 10 clause to the query.

D. Replace VectorDistance() with CONTAINS().

Answer: C

Explanation:
Vector searches should generally use TOP N to limit the number of returned results. Returning more results than the application needs can increase RU consumption and latency.

Final Exam Takeaways

If you remember only a handful of things from this topic, remember these:

Embeddings represent the semantic characteristics of content numerically.
An embedding model generates the embedding; Cosmos DB stores and searches it.
Embeddings can be stored alongside the original Cosmos DB document.
VectorDistance() is the key function for vector similarity searches.
Use TOP N when performing vector retrieval.
flat provides exact/brute-force search but is limited to 505 dimensions.
quantizedFlat provides a more efficient quantized approach for smaller/scoped searches.
diskANN is designed for efficient approximate search at larger scales and supports up to 4,096 dimensions.
Vector search can be combined with metadata filters and hybrid search.
Vector retrieval is a fundamental building block for RAG applications.
When source content changes, embeddings may need to be regenerated.
For AI-200 scenario questions, pay close attention to the dataset size, vector dimensionality, accuracy requirements, RU consumption, and latency requirements when selecting a vector index.

Go to the AI-200 Exam Prep Hub main page

AI-200, azure, Databases, Microsoft Certification, NoSQL August 15, 2026

Optimize query performance and Request Units (RUs) consumption by using indexing policies and consistency levels (AI-200 Exam Prep)

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 Cosmos DB for NoSQL
      --> Optimize query performance and Request Units (RUs) consumption by using indexing policies and consistency levels

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

Azure Cosmos DB for NoSQL is designed to provide globally distributed, low-latency access to JSON data at scale. A key part of developing efficient Cosmos DB solutions is understanding how queries consume Request Units (RUs) and how indexing policies and consistency levels affect query performance, throughput, latency, and cost.

For the AI-200 exam, you should understand how to:

Explain what RUs represent.
Identify factors that increase or decrease RU consumption.
Understand how indexes improve query performance.
Configure indexing policies.
Include or exclude property paths from indexing.
Understand range and composite indexes.
Recognize when a query is likely to require a full scan.
Understand the relationship between partition keys and query performance.
Understand the five Cosmos DB consistency levels.
Choose an appropriate consistency level based on application requirements.
Understand how consistency affects read throughput.
Use query metrics to investigate expensive queries.

A useful way to think about optimization is:

Efficient Cosmos DB queries minimize the amount of data that must be examined and returned while using an indexing and consistency strategy appropriate for the application’s requirements.

1. Understanding Request Units (RUs)

Azure Cosmos DB uses Request Units (RUs) as a normalized measure of the resources required to perform database operations.

Instead of pricing or throttling individual operations according to CPU time, disk operations, memory, and other implementation details, Cosmos DB abstracts those resources into RUs.

For example, operations such as:

Creating an item
Reading an item
Updating an item
Deleting an item
Running a query

consume RUs.

The amount of RU consumption depends on the work required to perform the operation.

Important exam concept

The number of items returned is not the only factor determining RU consumption.

A query can return a small number of items while still consuming significant RUs if Cosmos DB has to examine a large amount of data.

Conversely, an efficiently indexed query may examine a relatively small amount of data and consume fewer RUs.

2. What Determines RU Consumption?

Several factors influence the RU charge of a request.

Common factors include:

Size of the items being read or written
Number of items involved
Number of properties being indexed
Query complexity
Whether indexes can be used efficiently
Whether the query is single-partition or cross-partition
Number of partitions involved
Amount of data returned
Consistency level
Type of operation

For example, consider:

			
SELECT *
FROM c
WHERE c.customerId = "C1001"

If customerId is efficiently indexed and the query can target the appropriate partition, the query can be relatively inexpensive.

A query such as:

			
SELECT *
FROM c
WHERE c.description = "some value"

may be considerably more expensive if the query requires examining many partitions or cannot efficiently use an appropriate index.

3. Why Indexing Matters

Azure Cosmos DB for NoSQL automatically indexes properties by default.

This means developers generally don’t have to create indexes manually before executing common queries.

The default indexing policy indexes every property of every item, using range indexes for string and numeric values.

This default behavior provides good general-purpose query performance.

However, an application may benefit from a custom indexing policy.

For example, suppose documents contain:

			
{
    "id": "1001",
    "customerId": "C1001",
    "name": "Norm",
    "description": "...",
    "largeMetadata": {
        "property1": "...",
        "property2": "...",
        "property3": "..."
    }
}

		

If the application frequently queries:

WHERE c.customerId = "C1001"

but never queries largeMetadata, indexing every property may provide little benefit while increasing index storage and indexing work.

A custom indexing policy can exclude paths that aren’t needed for queries.

4. Indexing and Write Costs

Indexes aren’t free.

When an item is created or modified, Cosmos DB must maintain the indexes associated with that item.

Therefore, extensive indexing can increase:

Write RU consumption
Index storage
Index maintenance work

This creates an important optimization tradeoff:

Strategy	Potential benefit	Potential cost
Index many properties	Better query flexibility	More index storage and write overhead
Index fewer properties	Lower indexing overhead	Some queries may require scans
Use composite indexes	Efficient supported multi-property queries	Additional index maintenance
Use default policy	Simple and broadly effective	May index properties the application never queries

The goal isn’t to minimize indexes at all costs.

The goal is to index the paths required by the application’s query workload.

5. Indexing Modes

Azure Cosmos DB for NoSQL supports indexing modes that determine how indexes are maintained.

The important mode for normal querying is:

Consistent

The index is updated synchronously as items are created, updated, or deleted.

This provides predictable query behavior and is the normal indexing mode for queryable containers.

A container can also have indexing disabled by setting the indexing mode to none.

This can be useful for workloads where secondary indexing isn’t needed, such as certain key-value-style scenarios or some bulk-loading scenarios.

However, queries against a container without the necessary indexes may require scans and can therefore consume significantly more RUs.

6. Included and Excluded Paths

One of the most important ways to customize an indexing policy is through included paths and excluded paths.

An indexing policy can essentially answer:

Which JSON properties should Cosmos DB index?

For example:

			
{
    "indexingMode": "consistent",
    "includedPaths": [
        {
            "path": "/*"
        }
    ],
    "excludedPaths": [
        {
            "path": "/largeMetadata/*"
        }
    ]
}

		

This approach indexes the document generally while excluding a portion that isn’t queried.

A useful rule is:

Exclude properties that don’t need to participate in queries, especially large or frequently changing properties, when doing so is appropriate for the workload.

The indexing-policy documentation recommends using an include-root/exclude-specific-path strategy when you want new properties added to the data model to be indexed automatically unless explicitly excluded.

7. The Partition Key Is Critical to Query Performance

Indexing alone does not guarantee an inexpensive query.

The partition key is also extremely important.

Consider a container partitioned by:

/customerId

A query such as:

			
SELECT *
FROM c
WHERE c.customerId = "C1001"

can potentially be targeted to a single logical partition.

Compare that with:

			
SELECT *
FROM c
WHERE c.city = "Orlando"

If city isn’t the partition key, Cosmos DB may need to execute the query across multiple partitions.

This is called a cross-partition query.

Cross-partition queries can consume more RUs because multiple partitions may need to participate.

Exam takeaway

When analyzing a query, don’t ask only:

“Is the property indexed?”

Also ask:

“Can the query be directed to the appropriate partition?”

A well-designed partition key and appropriate indexing policy work together.

8. Partition Key Indexing

There is an important detail that can appear in exam questions.

A partition key property isn’t automatically indexed merely because it is the partition key.

If the partition key isn’t /id, it should generally be included in the indexing policy when queries filter on it. Otherwise, queries using that property can be forced into full scans, increasing RU consumption.

For example, if the partition key is:

/customerId

and the application frequently queries:

WHERE c.customerId = "C1001"

the indexing policy should support that path.

9. Types of Indexes

Azure Cosmos DB supports several index types.

For AI-200, you should understand at least the major concepts surrounding:

Range indexes
Composite indexes
Spatial indexes
Vector indexes

The most important indexes for traditional query optimization are range and composite indexes.

10. Range Indexes

Range indexes are based on an ordered structure and can support many common query operations.

They can support operations such as:

>=

<=

as well as certain ORDER BY, JOIN, and string-function scenarios.

For example:

			
SELECT *
FROM c
WHERE c.price > 100

can benefit from an appropriate range index on price.

Similarly:

			
SELECT *
FROM c
ORDER BY c.price

requires a range index on the ordered property.

11. Composite Indexes

A composite index indexes multiple properties together.

Composite indexes are particularly useful for queries involving multiple properties and certain combinations of filtering and sorting.

For example:

			
SELECT *
FROM c
WHERE c.category = "AI"
ORDER BY c.timestamp DESC

may benefit from an appropriate composite index involving:

			
/category
/timestamp

The order of properties in a composite index matters.

For example, these are not necessarily interchangeable:

(category ASC, timestamp DESC)

and:

(timestamp DESC, category ASC)

The appropriate ordering depends on the query workload.

Exam tip

If a question describes a query using multiple properties with filtering and/or ordering, think:

Could a composite index make this query more efficient?

12. Index Utilization

Cosmos DB’s query engine can use indexes in different ways.

The query engine can perform operations ranging from highly efficient index seeks to full scans.

Generally, the progression is:

Index seek
Precise index scan
Expanded index scan
Full index scan
Full scan

An index seek is particularly efficient because the query engine can identify the relevant index entries without examining the entire dataset.

A full scan is considerably more expensive because Cosmos DB must inspect the underlying data rather than efficiently locating matching records through an appropriate index.

13. Why `SELECT *` Can Cost More

The amount of data returned affects RU consumption.

Consider:

			
SELECT *
FROM c
WHERE c.customerId = "C1001"

versus:

			
SELECT c.id, c.name
FROM c
WHERE c.customerId = "C1001"

The second query may consume fewer RUs because it returns less data.

This leads to an important optimization principle:

Return only the properties your application needs.

Avoid retrieving large documents when only a few properties are required.

14. Avoid Unnecessary Cross-Partition Queries

Suppose a container has:

Partition key: /customerId

This query can potentially target a partition:

			
SELECT c.id, c.name
FROM c
WHERE c.customerId = "C1001"

But this query may involve many partitions:

			
SELECT c.id, c.name
FROM c
WHERE c.status = "Active"

If status isn’t the partition key, Cosmos DB may need to query multiple partitions.

Cross-partition queries aren’t inherently bad.

They are sometimes necessary.

The important point is:

Don’t accidentally create expensive cross-partition queries when the application can supply the partition key.

15. Measuring Query RU Consumption

The Cosmos DB SDKs provide information about the RU charge associated with operations.

For example, application code can inspect the response from a query and determine how many RUs were consumed.

This is valuable because optimization should be based on actual workload measurements rather than assumptions.

When troubleshooting an expensive query, examine:

RU charge
Query execution time
Number of returned documents
Index utilization
Number of partitions involved
Query predicates
Requested properties
Partition-key usage

16. Index Transformation

Changing an indexing policy can cause Cosmos DB to perform an index transformation.

For example, adding an indexed path requires Cosmos DB to build the new index for existing data.

Index transformation is asynchronous and consumes RUs. Queries begin using a newly added indexed path after the index transformation has completed.

This is important operationally.

If you replace one index with another, a good strategy is generally:

Add the new index.
Wait for the transformation to complete.
Verify the workload.
Remove the old index if it is no longer required.

Removing an indexed path takes effect immediately, so removing an index before the replacement is ready can temporarily cause queries to fall back to scans.

17. Understanding Consistency Levels

Indexing affects how efficiently data can be located.

Consistency affects what version of the data a read is allowed to return.

Azure Cosmos DB provides five consistency levels, ordered from strongest to weakest:

Strong
Bounded staleness
Session
Consistent prefix
Eventual

Choosing the consistency level is a business and application decision.

You should not automatically select the strongest consistency level.

18. Strong Consistency

Strong consistency guarantees that reads return the latest committed version of the data.

This provides the strongest read guarantee.

The tradeoff is that strong consistency can increase write latency and reduce availability in some globally distributed scenarios because replicas must satisfy the stronger synchronization requirements.

Appropriate scenarios

Strong consistency may be appropriate for scenarios where stale data is unacceptable, such as:

Certain financial transactions
Critical inventory decisions
Applications requiring immediate globally consistent reads

Exam clue

If a question says:

“The application must always read the most recently committed value.”

Think:

Strong consistency.

19. Bounded Staleness

Bounded staleness guarantees that reads aren’t allowed to become older than a configured limit based on:

Time
Number of versions/operations

This is useful when the application can tolerate a controlled amount of replication lag but needs a stronger guarantee than eventual consistency.

For example:

“Data can be up to a few seconds old, but never older than that.”

This points toward bounded staleness.

Bounded staleness is particularly relevant to globally distributed applications that need near-strong consistency without the full cost of strong consistency.

20. Session Consistency

Session consistency is commonly useful for interactive applications.

It provides guarantees such as:

Read-your-writes
Monotonic reads
Monotonic writes

In practical terms, a user who writes data should be able to read that data within the same session.

For example:

User updates their profile.
User immediately refreshes the profile.
The application should see the user’s update.

Session consistency is often a good balance between strong consistency and scalability.

21. Consistent Prefix

Consistent prefix guarantees that reads see writes in the order they occurred, without observing them out of sequence.

The application may not immediately see every write, but it won’t see writes in an inconsistent order.

For example, suppose writes occur in this order:

A → B → C → D

A reader might see:

			
A
A, B
A, B, C
A, B, C, D

but shouldn’t see:

A, C

while missing B.

22. Eventual Consistency

Eventual consistency provides the weakest consistency guarantee.

Different replicas may temporarily return different values, but replicas eventually converge.

The major advantages include:

Lower coordination requirements
High availability
Good performance
Lower latency in many distributed scenarios

Eventual consistency may be appropriate for:

Social feeds
Recommendation systems
Analytics dashboards
Non-critical status information
Content where temporary staleness is acceptable

23. Consistency and Read Throughput

Consistency isn’t simply about correctness.

It can also affect read throughput.

For strong and bounded staleness consistency, reads are performed against two replicas in a four-replica set to satisfy the consistency guarantees.

Session, consistent prefix, and eventual consistency use single-replica reads.

Consequently, for the same number of provisioned RUs, strong and bounded staleness consistency provide approximately half the read throughput of the weaker consistency levels.

This is a very important AI-200 exam concept.

Remember:

Stronger consistency can consume more read capacity.

Therefore, if an application does not require strong consistency, relaxing the consistency requirement can improve read scalability.

24. Consistency Does Not Change Write RU Charges

For the same type of write operation, write RU consumption is generally identical across consistency levels.

However, stronger consistency can have other performance implications, particularly around replication and latency.

Therefore, don’t confuse:

Consistency → read behavior and read throughput

with:

Indexing → query efficiency and index maintenance

Both affect application performance, but in different ways.

25. Choosing the Right Consistency Level

A useful decision framework is:

Requirement	Recommended consideration
Must always see the latest committed value	Strong
Can tolerate a precisely bounded amount of staleness	Bounded staleness
Users need read-your-writes behavior	Session
Writes must appear in order but can be delayed	Consistent prefix
Temporary inconsistency is acceptable	Eventual

The key is to choose the weakest consistency level that still satisfies the application’s requirements.

This can improve scalability and reduce unnecessary coordination.

26. Combining Indexing and Consistency Optimization

Indexing and consistency should be considered separately.

Suppose an application has an expensive query.

You might investigate:

Indexing

Is the filtered property indexed?
Is an appropriate range index available?
Is a composite index appropriate?
Is the partition key included in the indexing policy?
Is the query performing a full scan?
Are unnecessary properties being indexed?

Query design

Is the partition key supplied?
Is the query unnecessarily cross-partition?
Is SELECT * returning unnecessary data?
Can the query be simplified?

Consistency

Does the application actually require strong consistency?
Could session consistency satisfy the requirement?
Could eventual consistency satisfy the requirement?

This distinction is important:

Don’t try to solve every RU problem by changing the indexing policy.

Likewise:

Don’t weaken consistency when the application actually requires stronger guarantees.

27. A Practical Optimization Example

Imagine an AI-powered customer-support application.

The container contains millions of support conversations.

The partition key is:

/customerId

The application runs:

			
SELECT *
FROM c
WHERE c.customerId = "C1001"
AND c.status = "Open"
ORDER BY c.createdDate DESC

		

Several optimization questions should be considered.

Question 1: Can the query target a partition?

Yes.

It specifies:

customerId = C1001

which is the partition key.

Question 2: Are the relevant properties indexed?

The query uses:

			
customerId
status
createdDate

The indexing policy should support the query.

Question 3: Would a composite index help?

Potentially.

The query combines filtering and sorting across multiple properties, so a composite index may be appropriate depending on the exact query workload and index requirements.

Question 4: Does the application need every property?

Perhaps not.

Instead of:

SELECT *

the application could retrieve only:

SELECT c.id, c.status, c.createdDate, c.subject

Question 5: Does the application need strong consistency?

If the support application can tolerate some temporary staleness, a weaker consistency level may provide better read scalability.

This illustrates an important principle:

Query performance is usually the result of several design decisions working together.

28. Common AI-200 Exam Traps

Trap 1: “Indexes always reduce RU consumption.”

Not necessarily.

Indexes can reduce the amount of data that must be examined for queries, but maintaining indexes also adds write and storage overhead.

Trap 2: “The partition key automatically makes the property indexed.”

Not necessarily.

The partition key should be considered separately from the indexing policy. A partition key property should be included in the indexing policy when queries need to efficiently filter on it.

Trap 3: “Strong consistency is always better.”

Strong consistency provides stronger guarantees, but it can reduce read throughput and increase latency/availability tradeoffs.

Choose it only when required.

Trap 4: “Eventual consistency means data is permanently inconsistent.”

No.

Eventual consistency means replicas may temporarily disagree, but they eventually converge.

Trap 5: “A query returning one item must be inexpensive.”

Not necessarily.

Cosmos DB may have to examine many items or partitions to discover that single matching item.

Trap 6: “Cross-partition queries are always wrong.”

No.

Cross-partition queries are sometimes necessary.

The goal is to avoid unnecessary cross-partition queries and design the partition key appropriately for the workload.

Trap 7: “Removing an index is harmless.”

Removing an index can cause queries that depended on it to fall back to less efficient execution, potentially increasing RU consumption.

29. AI-200 Exam Quick Reference

Concept	Remember
RU	Normalized unit of Cosmos DB resource consumption
Index	Helps locate matching data efficiently
Default indexing	Automatically indexes properties by default
Custom indexing	Can include/exclude paths
Range index	Equality, range, ordering, and other supported operations
Composite index	Multiple-property query patterns
Full scan	Potentially expensive; examines underlying data broadly
Partition key	Determines data distribution and can enable targeted queries
Cross-partition query	May require querying multiple partitions
`SELECT *`	Can return more data and increase RU consumption
Strong consistency	Latest committed value
Bounded staleness	Controlled maximum staleness
Session	Read-your-writes and session guarantees
Consistent prefix	Writes observed in order
Eventual	Temporary inconsistency allowed
Strong/bounded read throughput	Lower than weaker levels for same RU allocation
Index transformation	Asynchronous and consumes RUs
Best practice	Choose indexes and consistency based on workload requirements

Practice Exam Questions

Question 1

An application stores customer records in Azure Cosmos DB for NoSQL. The container is partitioned by /customerId. The application frequently executes the following query:

			
SELECT *
FROM c
WHERE c.customerId = "C1005"

The developer wants to minimize RU consumption.

Which approach is most appropriate?

A. Add a spatial index to the customerId property.

B. Disable indexing so the query engine can scan the container faster.

C. Change the consistency level to Strong regardless of the application’s requirements.

D. Ensure the customerId path is appropriately indexed and provide the partition key value when executing the query.

Answer: D

Explanation

The query uses the partition key, allowing Cosmos DB to target the appropriate logical partition. The property should also be appropriately indexed when queries filter on it. This combination can significantly improve query efficiency.

Disabling indexing would generally make query execution less efficient. Spatial indexes are intended for geospatial data, not customer identifiers. Strong consistency does not inherently optimize this query.

Question 2

A globally distributed application displays product recommendations. Recommendations can be temporarily stale as long as replicas eventually converge.

Which consistency level is generally the most appropriate?

A. Strong

B. Bounded staleness

C. Session

D. Eventual

Answer: D

Explanation

The application explicitly permits temporary staleness and does not require read-your-writes or strict ordering guarantees. Eventual consistency is therefore appropriate.

Strong consistency provides stronger guarantees than necessary. Bounded staleness provides a specific staleness guarantee that isn’t required by the scenario. Session consistency would provide stronger session-level guarantees than needed.

Question 3

A Cosmos DB container contains documents with hundreds of properties. An application queries only /customerId, /status, and /createdDate. Many large metadata properties are never queried.

The development team wants to reduce indexing overhead and index storage.

What should they consider?

A. Enable strong consistency.

B. Customize the indexing policy to exclude properties that don’t need to be queried.

C. Remove the partition key.

D. Replace all range indexes with spatial indexes.

Answer: B

Explanation

A custom indexing policy can exclude properties that don’t participate in queries. This can reduce index size and indexing maintenance overhead.

Changing consistency doesn’t address unnecessary indexes. Removing the partition key is not an appropriate optimization, and spatial indexes aren’t appropriate for ordinary scalar properties such as customer IDs and status values.

Question 4

An application requires that a user immediately see an item after the user creates it, but the application does not require globally strong consistency for every user.

Which consistency level is generally the best fit?

A. Eventual

B. Consistent prefix

C. Session

D. Strong

Answer: C

Explanation

Session consistency provides read-your-writes behavior and is well suited to interactive applications where a user expects to see their own changes.

Eventual consistency doesn’t provide the same session guarantees. Consistent prefix guarantees write ordering but doesn’t provide the same read-your-writes behavior. Strong consistency is stronger than necessary for the stated requirement.

Question 5

A query returns only one document but consumes a surprisingly large number of RUs. The query doesn’t specify the partition key and runs against a container with many physical partitions.

What is the most likely explanation?

A. Cosmos DB charges a fixed RU amount for every returned document.

B. The query must always use a spatial index.

C. The query may be executing across multiple partitions and examining significant amounts of data before finding the matching document.

D. Returning one document always requires Strong consistency.

Answer: C

Explanation

The number of returned documents isn’t the only determinant of RU consumption. A cross-partition query can require Cosmos DB to examine multiple partitions, potentially consuming significant RUs even if only one document ultimately matches.

There is no fixed RU charge per returned document, spatial indexing is unrelated, and consistency doesn’t automatically become Strong because one document is returned.

Question 6

A query uses:

			
SELECT *
FROM c
WHERE c.category = "AI"
ORDER BY c.timestamp DESC

The application frequently executes this query and wants to optimize its performance.

Which index type should the developer investigate first?

A. Composite index

B. Spatial index

C. Vector index

D. No index; ORDER BY queries cannot use indexes

Answer: A

Explanation

The query uses multiple properties in filtering and ordering. A composite index can be useful for query patterns involving multiple properties and sorting.

Spatial indexes are designed for geospatial operations. Vector indexes are designed for vector search. Cosmos DB can use indexes for ORDER BY operations.

Question 7

An application currently uses Strong consistency. Performance testing shows that read throughput is insufficient. The application requirements state that users only need read-your-writes behavior within their own sessions.

What should the developer consider?

A. Add a spatial index.

B. Change the partition key to /id without analyzing the workload.

C. Disable all indexes.

D. Use Session consistency if it satisfies the application’s requirements.

Answer: D

Explanation

Session consistency provides read-your-writes behavior and other session-level guarantees while avoiding the stronger coordination requirements of Strong consistency.

Changing the partition key or disabling indexes doesn’t directly address the stated consistency requirement. Spatial indexing is unrelated.

Question 8

A developer removes an indexed path from a Cosmos DB indexing policy because the property is no longer queried. An existing query unexpectedly begins consuming substantially more RUs.

What is the most likely explanation?

A. Removing an indexed path causes all writes to become strongly consistent.

B. The query may no longer be able to use the removed index and may fall back to a less efficient scan.

C. Removing an index automatically converts the container into a different API.

D. Cosmos DB stops supporting partitioning when an index is removed.

Answer: B

Explanation

When an indexed path is removed, queries that relied on that index may no longer be able to use it and can fall back to a full scan or another less efficient execution strategy. This can substantially increase RU consumption.

The other options describe behaviors that don’t occur as a result of removing an indexed path.

Question 9

A company wants to ensure that reads never return a value older than a configured amount of time or number of updates, but it doesn’t require Strong consistency.

Which consistency level should the developer select?

A. Eventual

B. Session

C. Bounded staleness

D. Consistent prefix

Answer: C

Explanation

Bounded staleness is specifically designed for scenarios where the application can tolerate a controlled amount of staleness based on time or the number of versions/operations.

Eventual consistency provides no such bounded staleness guarantee. Session consistency focuses on session-level guarantees, while consistent prefix guarantees write ordering rather than a specific staleness bound.

Question 10

A Cosmos DB account has a workload dominated by read operations. The application doesn’t require Strong or Bounded Staleness consistency. The team wants to maximize read throughput for the same provisioned RU capacity.

Which approach is most appropriate?

A. Use Session, Consistent Prefix, or Eventual consistency according to the application’s requirements.

B. Increase indexing on every possible property.

C. Change every query to SELECT *.

D. Use Strong consistency for all queries.

Answer: A

Explanation

Strong and Bounded Staleness consistency use more replicas for reads and therefore provide approximately half the read throughput of Session, Consistent Prefix, and Eventual consistency for the same RU allocation.

If the application doesn’t require the stronger guarantees, using an appropriate weaker consistency level can improve read scalability.

Increasing indexes can help particular queries but doesn’t address the consistency-related read-throughput issue. SELECT * can actually increase data returned and RU consumption, while Strong consistency would move in the opposite direction from the desired optimization.

Final Exam Takeaways

For AI-200, the most important concepts to remember are:

RUs represent the resources consumed by Cosmos DB operations.
Indexes can make queries substantially more efficient, but maintaining indexes has a cost.
The default indexing policy indexes properties automatically.
Custom indexing policies can include or exclude property paths.
Range indexes support many common equality, range, and ordering operations.
Composite indexes are important for appropriate multi-property query patterns.
A partition-key-aware query is generally more efficient than an unnecessary cross-partition query.
The partition key should be considered separately from indexing.
Returning unnecessary data, such as with SELECT *, can increase RU consumption.
Strong consistency provides the strongest read guarantee but has performance and availability tradeoffs.
Bounded staleness provides a controlled staleness guarantee.
Session consistency provides important read-your-writes behavior for interactive applications.
Consistent prefix preserves write ordering.
Eventual consistency provides the weakest guarantees but can maximize scalability and availability.
Strong and bounded staleness provide lower read throughput for the same RU allocation than Session, Consistent Prefix, and Eventual consistency.
Index transformations consume RUs and occur asynchronously.
When optimizing Cosmos DB, consider the combination of partitioning, indexing, query design, returned data, and consistency—not any one factor in isolation.

Go to the AI-200 Exam Prep Hub main page

azure, Databases, Microsoft Certification August 15, 2026

Connect to Azure Cosmos DB for NoSQL by using the SDK and run queries (AI-200 Exam Prep)

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 Cosmos DB for NoSQL
      --> Connect to Azure Cosmos DB for NoSQL by using the SDK and run queries

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

Azure Cosmos DB for NoSQL is a globally distributed, fully managed NoSQL database service designed for applications that require flexible schemas, low-latency access, and elastic scalability.

For the AI-200 exam, developers should understand how to:

Connect applications to Azure Cosmos DB for NoSQL.
Use the Azure Cosmos DB SDK.
Authenticate securely.
Create and access databases and containers.
Define partition keys.
Insert, read, update, and delete items.
Construct SQL queries for Cosmos DB.
Execute queries through the SDK.
Work with query parameters.
Understand partition-aware querying.
Process query results efficiently.
Recognize common performance considerations.

The key concept is that Azure Cosmos DB for NoSQL exposes a SQL-like query language, while the SDK provides the programming interface through which an application connects to the service and executes those queries.

1. Understanding the Azure Cosmos DB for NoSQL Data Model

Before connecting with an SDK, it is important to understand the hierarchy used by Azure Cosmos DB.

The basic structure is:

Cosmos DB account → Database → Container → Item

Cosmos DB account

The account is the top-level Azure resource. It provides the endpoint through which applications communicate with Cosmos DB.

An account contains one or more databases.

Database

A database provides a logical grouping of containers.

For example:

			
AI200CosmosAccount
    └── CustomerDatabase

Container

A container is where items are stored.

A container is roughly analogous to a table in a relational database, although it is much more flexible because Cosmos DB items can have different structures.

			
CustomerDatabase
    ├── Customers
    ├── Orders
    └── Products

A container also defines the partition key path, which is extremely important for scalability and query performance.

Item

An item is a JSON document.

For example:

			
{
    "id": "customer-1001",
    "customerName": "John Smith",
    "country": "US",
    "email": "john@example.com",
    "loyaltyLevel": "Gold"
}

		

Unlike a relational table, another item in the same container could contain additional properties.

2. Connecting to Azure Cosmos DB

An application needs two primary pieces of information to connect to a Cosmos DB account:

The Cosmos DB account endpoint.
A supported authentication mechanism.

A typical endpoint looks conceptually like:

https://<account-name>.documents.azure.com:443/

The SDK uses this endpoint to communicate with the Cosmos DB service.

3. Authentication

Authentication is an important exam topic because applications should avoid embedding long-lived credentials directly in source code.

Several authentication approaches are available, including:

Microsoft Entra ID-based authentication.
Managed identities.
Account keys.
Connection strings.

For production Azure applications, Microsoft Entra ID with managed identity is generally preferable when supported by the application’s architecture because credentials do not need to be stored in application configuration or source code.

For example, an application running on an Azure service can use its managed identity to authenticate to Cosmos DB.

The conceptual flow is:

			
Application
     │
     │ Managed identity
     ▼
Microsoft Entra ID
     │
     │ Token
     ▼
Azure Cosmos DB

		

Exam point

If a question asks for the most secure way for an Azure-hosted application to authenticate to Cosmos DB without storing credentials, look for an answer involving:

Microsoft Entra ID + managed identity + appropriate Cosmos DB data-plane permissions.

4. Using the Azure Cosmos DB SDK

Microsoft provides SDKs for several programming languages, including:

.NET
Java
JavaScript/TypeScript
Python

The SDK provides classes and methods for interacting with Cosmos DB.

For example, a .NET application can use the Azure Cosmos DB SDK package.

A simplified connection looks like:

			
var client = new CosmosClient(
    endpoint,
    credential);

The CosmosClient represents the client connection to the Cosmos DB account.

Applications can then access databases and containers through the client.

Conceptually:

			
CosmosClient
     │
     └── Database
           │
           └── Container
                 │
                 ├── Create item
                 ├── Read item
                 ├── Replace item
                 ├── Delete item
                 └── Query items

		

5. Reuse the CosmosClient

A common application-design mistake is creating a new CosmosClient for every database operation.

Instead, applications should generally create and reuse a single CosmosClient instance for the lifetime of the application.

For example:

			
private static CosmosClient client = new CosmosClient(
    endpoint,
    credential);

The SDK manages connections internally.

Creating clients repeatedly can cause unnecessary connection overhead and negatively affect performance.

Exam tip

If a question presents code that creates a new CosmosClient for every request, consider whether the question is testing your knowledge of client reuse.

Reuse the client rather than repeatedly creating new instances.

6. Accessing a Database

Once the client has been created, the application can obtain a reference to a database.

For example:

Database database = client.GetDatabase("CustomerDatabase");

This does not necessarily mean that the database has been created.

It obtains a client-side reference to the database.

If the database needs to be created, the SDK provides methods such as:

			
DatabaseResponse response =
    await client.CreateDatabaseIfNotExistsAsync("CustomerDatabase");

The CreateIfNotExists pattern is useful when an application should create the resource only when necessary.

7. Accessing a Container

After obtaining a database reference, the application can access a container:

			
Container container =
    database.GetContainer("Customers");

As with GetDatabase(), obtaining a container reference does not mean that the container has been created.

A container can be created when necessary:

			
ContainerResponse response =
    await database.CreateContainerIfNotExistsAsync(
        "Customers",
        "/country");

The second parameter specifies the partition key path.

In this example:

/country

is the partition key path.

8. Partition Keys

Partitioning is fundamental to Cosmos DB.

A container distributes its items across physical partitions based on the configured partition key.

For example:

			
{
    "id": "customer-1001",
    "country": "US",
    "name": "John Smith"
}

		

If /country is the partition key path, the value:

US

determines the logical partition to which the item belongs.

A good partition key should generally provide:

High cardinality.
Even distribution.
Sufficient request-volume distribution.
Values that match common access patterns.

Why this matters for queries

If a query includes the partition key value, Cosmos DB can often limit the query to the relevant partition rather than querying every partition.

This is called a single-partition query or targeted query, depending on the scenario.

A query that does not provide a partition key value may require a cross-partition query.

9. Creating Items

Items are JSON documents.

A .NET application can create an item using the SDK:

			
var customer = new
{
    id = "customer-1001",
    country = "US",
    name = "John Smith",
    loyaltyLevel = "Gold"
};
ItemResponse<dynamic> response =
    await container.CreateItemAsync(
        customer,
        new PartitionKey("US"));

		

The partition key value supplied to the SDK should correspond to the item’s partition key.

For a container partitioned on:

/country

the request should specify:

new PartitionKey("US")

10. Reading an Item

When the application’s partition key and item ID are known, the SDK can directly retrieve an item.

For example:

			
ItemResponse<Customer> response =
    await container.ReadItemAsync<Customer>(
        "customer-1001",
        new PartitionKey("US"));

This is generally much more efficient than querying for the item because Cosmos DB can directly address the item using its ID and partition key.

Important distinction

Consider these two operations:

ReadItem(id, partitionKey)

versus:

SELECT * FROM c WHERE c.id = "customer-1001"

The point read supplies both the item ID and partition key and is the preferred operation when those values are known.

Exam tip

If a question asks how to retrieve one known item as efficiently as possible, look for:

Point read using the item’s ID and partition key.

11. Updating Items

The SDK supports updating existing items.

Depending on the required behavior, developers can use operations such as:

Replace
Upsert
Patch

Replace

Replace generally replaces the entire item.

Upsert

Upsert means:

Update the item if it exists; otherwise create it.

For example:

			
await container.UpsertItemAsync(
    customer,
    new PartitionKey("US"));

Patch

Patch modifies selected properties without requiring the application to replace the entire document.

For example, an application might update only:

loyaltyLevel

rather than sending the entire customer document.

This can reduce the amount of data transmitted and simplify partial updates.

12. Deleting Items

An item can be deleted using its ID and partition key:

			
await container.DeleteItemAsync<Customer>(
    "customer-1001",
    new PartitionKey("US"));

Again, knowing both the ID and partition key allows Cosmos DB to directly identify the item.

13. Querying Azure Cosmos DB for NoSQL

Cosmos DB for NoSQL uses a SQL-like query language.

A simple query is:

SELECT * FROM c

The c represents each item being queried.

For example:

			
SELECT *
FROM c
WHERE c.country = "US"

This returns items whose country property is US.

14. Selecting Specific Properties

Applications don’t always need the entire document.

Instead of:

			
SELECT *
FROM c

you can select specific properties:

			
SELECT
    c.id,
    c.name,
    c.email
FROM c

		

This can reduce the amount of data returned to the application.

It can also make the application’s intent clearer.

15. Filtering Results

The WHERE clause filters documents.

For example:

			
SELECT *
FROM c
WHERE c.loyaltyLevel = "Gold"

Multiple conditions can be combined:

			
SELECT *
FROM c
WHERE c.country = "US"
AND c.loyaltyLevel = "Gold"

Other operators include:

			
=
!=
<
>
<=
>=
AND
OR

		

16. Parameterized Queries

Applications should avoid constructing queries by concatenating user input into SQL strings.

For example, this pattern should be avoided:

			
string query =
    "SELECT * FROM c WHERE c.name = '" + userName + "'";

Instead, use parameterized queries.

For example:

			
var query = new QueryDefinition(
    "SELECT * FROM c WHERE c.name = @name")
    .WithParameter("@name", userName);

This approach:

Separates query structure from values.
Helps prevent injection-style problems.
Makes query reuse easier.
Provides cleaner application code.

17. Executing a Query

The SDK provides query APIs that allow the application to execute a QueryDefinition.

For example:

			
var query = new QueryDefinition(
    "SELECT * FROM c WHERE c.country = @country")
    .WithParameter("@country", "US");
using FeedIterator<Customer> iterator =
    container.GetItemQueryIterator<Customer>(query);
while (iterator.HasMoreResults)
{
    FeedResponse<Customer> response =
        await iterator.ReadNextAsync();
    foreach (Customer customer in response)
    {
        Console.WriteLine(customer.name);
    }
}

		

This demonstrates an important concept:

Cosmos DB queries can return results in multiple pages.

18. FeedIterator and Pagination

A query may return more data than can reasonably be delivered in one response.

The SDK therefore exposes query results through an iterator.

Conceptually:

			
Query
  │
  ▼
Page 1
  │
  ▼
Page 2
  │
  ▼
Page 3
  │
  ▼
...

		

The application checks:

iterator.HasMoreResults

and retrieves each page using:

await iterator.ReadNextAsync()

This is important for scalability.

Exam tip

If a question asks how to process a potentially large Cosmos DB query result set, look for an answer involving:

FeedIterator / paginated results rather than loading the entire result set into memory.

19. Cross-Partition Queries

Suppose a container uses:

/country

as its partition key.

A query such as:

			
SELECT *
FROM c
WHERE c.country = "US"

provides a partition key value.

This allows Cosmos DB to target the appropriate partition.

However, a query such as:

			
SELECT *
FROM c
WHERE c.loyaltyLevel = "Gold"

does not specify the partition key.

The service may therefore need to query multiple partitions.

This is a cross-partition query.

Cross-partition queries are not inherently wrong. They are sometimes necessary.

However, they can require more resources and incur higher request charges than targeted queries.

20. Supplying a Partition Key to a Query

The SDK can provide the partition key value separately from the query itself.

For example:

			
var query = new QueryDefinition(
    "SELECT * FROM c WHERE c.loyaltyLevel = @level")
    .WithParameter("@level", "Gold");
var requestOptions = new QueryRequestOptions
{
    PartitionKey = new PartitionKey("US")
};
using FeedIterator<Customer> iterator =
    container.GetItemQueryIterator<Customer>(
        query,
        requestOptions: requestOptions);

		

The application is effectively telling Cosmos DB:

Search only the US partition.

This can significantly improve query efficiency when the access pattern permits it.

21. Query Performance and Request Units

Azure Cosmos DB measures database operations using Request Units (RUs).

The RU charge depends on factors such as:

The operation being performed.
The amount of data processed.
The complexity of the query.
Indexing.
Number of partitions involved.
Number of documents examined.
Amount of data returned.

A query that scans many partitions can consume substantially more RUs than a targeted query.

Applications should therefore design queries and partition keys together.

22. Indexing

Cosmos DB automatically indexes properties by default in many common configurations.

Indexes help Cosmos DB efficiently locate matching documents.

However, indexing every property isn’t always optimal for every workload.

Applications with specialized workloads may need to configure indexing policies to balance:

Query performance.
Write performance.
Storage.
RU consumption.

For the AI-200 exam, understand the relationship:

			
More/appropriate indexing
        ↓
Efficient queries
        ↓
Potentially lower query cost

		

But indexing isn’t a substitute for good partition-key design.

23. Querying Arrays and Nested Properties

Cosmos DB documents can contain nested objects and arrays.

For example:

			
{
    "id": "1001",
    "customer": {
        "name": "John",
        "country": "US"
    },
    "orders": [
        {
            "id": "O100",
            "total": 125
        },
        {
            "id": "O101",
            "total": 200
        }
    ]
}

		

A nested property can be accessed using dot notation:

			
SELECT c.customer.name
FROM c

Cosmos DB also supports array operations.

For example, the ARRAY_CONTAINS function can determine whether an array contains a particular value.

The ability to query nested JSON is one of the significant advantages of the NoSQL model.

24. Query Functions

Azure Cosmos DB for NoSQL supports many built-in functions.

Examples include functions for:

Strings.
Arrays.
Mathematical calculations.
Date/time operations.
Type checking.
Spatial data.

For example:

			
SELECT *
FROM c
WHERE CONTAINS(c.name, "Smith")

Another example:

			
SELECT *
FROM c
WHERE ARRAY_CONTAINS(c.tags, "AI")

The important exam concept is not memorizing every function but understanding that Cosmos DB’s query language provides rich querying capabilities against JSON documents.

25. Querying With `ORDER BY`

Results can be sorted using ORDER BY.

For example:

			
SELECT
    c.id,
    c.name,
    c.total
FROM c
ORDER BY c.total DESC

		

This returns the highest totals first.

Queries can also use OFFSET and LIMIT patterns for controlled result sets.

26. Querying With Aggregates

Cosmos DB supports aggregate functions such as:

			
COUNT
SUM
AVG
MIN
MAX

		

For example:

			
SELECT VALUE COUNT(1)
FROM c
WHERE c.country = "US"

The VALUE keyword is useful when the desired result is the scalar value rather than an object containing a property.

27. Querying With `SELECT VALUE`

Consider:

			
SELECT c.name
FROM c

This returns objects such as:

			
{
    "name": "John"
}

Using:

			
SELECT VALUE c.name
FROM c

returns the values directly:

			
"John"
"Mary"
"Robert"

This distinction can appear in exam questions.

28. Querying With Continuation Tokens

Cosmos DB can return a continuation token when a query result spans multiple pages.

The application can use the continuation token to continue retrieving results.

This is particularly useful for:

Large result sets.
Pagination.
Resuming queries.
Avoiding the need to retrieve everything at once.

The SDK’s iterator abstraction commonly handles this pagination process for the application.

29. Point Reads vs. Queries

One of the most important distinctions to understand is:

Requirement	Preferred operation
Retrieve a known item by ID and partition key	Point read
Find items matching conditions	Query
Retrieve multiple items from a partition	Query
Modify one known item	Replace/Patch
Create or update an item	Upsert
Remove one known item	Delete

Example

If you know:

			
id = customer-1001
country = US

use:

ReadItem(id, partitionKey)

rather than:

SELECT * FROM c WHERE c.id = "customer-1001"

The point read is designed specifically for this scenario.

30. Common Exam Traps

Trap 1: Confusing the database with the container

A database contains containers.

A container contains items.

Trap 2: Treating Cosmos DB like a relational database

Cosmos DB for NoSQL stores JSON documents and uses containers rather than relational tables.

Trap 3: Forgetting the partition key

The partition key is central to Cosmos DB scalability and query performance.

Trap 4: Using a query for a known item

If both the item ID and partition key are known, use a point read.

Trap 5: Creating a CosmosClient for every request

The client should generally be reused.

Trap 6: Building queries with string concatenation

Use parameterized queries with QueryDefinition.

Trap 7: Assuming every query is single-partition

A query that doesn’t target a partition may become a cross-partition query.

Trap 8: Loading all results into memory

Use the SDK’s iterator/pagination model to process potentially large result sets incrementally.

31. AI-200 Exam Takeaways

For this topic, make sure you can explain the following without referring to documentation:

Cosmos DB hierarchy
- Account → Database → Container → Item.
CosmosClient
- Establishes the SDK connection to the Cosmos DB account.
- Should generally be reused.
Authentication
- Understand account keys versus Microsoft Entra ID and managed identity.
Partition keys
- Determine logical data distribution.
- Are critical to scalability and query performance.
Point reads
- Use item ID + partition key when both are known.
Queries
- Use Cosmos DB’s SQL-like query language.
Parameterized queries
- Use QueryDefinition and parameters rather than string concatenation.
Cross-partition queries
- Can occur when a query isn’t targeted to a specific partition.
FeedIterator
- Used to process paginated query results.
Request Units
- Measure Cosmos DB resource consumption.
Indexing
- Supports efficient queries and can affect RU consumption.
CRUD operations
- Create, read, update, upsert, patch, and delete items through the SDK.

Practice Exam Questions

Question 1

An application running in Azure needs to connect to Azure Cosmos DB for NoSQL. The organization requires that no database credentials be stored in application configuration.

Which authentication approach should the developer prefer?

A. Store the Cosmos DB account key in the application’s source code.

B. Store the Cosmos DB connection string in an environment variable.

C. Use a managed identity with Microsoft Entra ID authentication and appropriate Cosmos DB permissions.

D. Create a new Cosmos DB account key whenever the application starts.

Answer: C

Explanation:
A managed identity allows an Azure-hosted application to authenticate without storing long-lived credentials in application code or configuration. The identity must have the appropriate permissions to access Cosmos DB. Hard-coded keys and connection strings introduce credential-management risks.

Question 2

A container uses /customerId as its partition key. An application needs to retrieve a specific item, and it already knows both the item’s id and customerId.

Which operation should the application use?

A. A point read using the item ID and partition key.

B. A cross-partition SQL query.

C. A query using ORDER BY.

D. A query using GROUP BY.

Answer: A

Explanation:
When the item ID and partition key are known, a point read is the appropriate operation. It directly addresses the item instead of executing a query across documents.

Question 3

A developer needs to allow users to search for customers by name. The name is supplied by the user at runtime.

Which approach should the developer use?

A. Concatenate the user input into the SQL string.

B. Encode the user’s input as Base64 and concatenate it into the SQL string.

C. Create a separate container for each possible customer name.

D. Use a parameterized QueryDefinition.

Answer: D

Explanation:
A parameterized query separates query structure from user-supplied values. The Cosmos DB SDK supports parameters through QueryDefinition.WithParameter(). This is preferable to dynamically concatenating user input into query strings.

Question 4

A Cosmos DB container is partitioned by /region. An application executes:

			
SELECT *
FROM c
WHERE c.productCategory = "AI"

The query does not specify a region.

What should the developer understand about this query?

A. It automatically becomes a point read.

B. It may require a cross-partition query.

C. It can only return one document.

D. Cosmos DB automatically changes the partition key for the query.

Answer: B

Explanation:
Because the query does not restrict the /region partition key, Cosmos DB may need to query multiple partitions. Cross-partition queries are supported, but they can consume more resources than targeted queries.

Question 5

An application executes a query that can return hundreds of thousands of documents. The developer wants to avoid loading all results into memory simultaneously.

Which SDK approach is most appropriate?

A. Use a FeedIterator and process the results page by page.

B. Convert the query into a point read.

C. Increase the item’s partition key value.

D. Retrieve the entire result set using a single string response.

Answer: A

Explanation:
Cosmos DB queries can return results in multiple pages. The SDK’s FeedIterator allows an application to retrieve and process each page incrementally, which is more appropriate for large result sets.

Question 6

An application repeatedly creates a new CosmosClient object every time it performs a database operation.

What should the developer do?

A. Create a new client for every item.

B. Create two clients for every request to provide redundancy.

C. Reuse a CosmosClient instance for the lifetime of the application.

D. Replace the SDK with direct HTTP calls for every operation.

Answer: C

Explanation:
CosmosClient is designed to be reused. Creating clients repeatedly introduces unnecessary connection-management overhead and can negatively affect application performance.

Question 7

A container uses /country as its partition key. An application frequently retrieves customers when both their customer ID and country are known.

Which design provides the most direct access to an individual customer?

A. Store all customers in a single partition.

B. Use a point read with the customer ID and country as the partition key value.

C. Run a cross-partition query for every customer.

D. Use ORDER BY country before retrieving the customer.

Answer: B

Explanation:
A point read using the item ID and partition key can directly locate an item. This is preferable to running a query when the application’s access pattern already provides both values.

Question 8

A developer wants to update only the status property of a large Cosmos DB document rather than replacing the entire document.

Which operation is most appropriate?

A. CreateItem

B. ReadItem

C. DeleteItem

D. Patch

Answer: D

Explanation:
Patch is designed for modifying specific properties or paths within an existing item without requiring the entire document to be replaced.

Question 9

A Cosmos DB application performs a query that searches across many physical partitions. The query consumes significantly more Request Units than a similar query that targets a single partition.

What is the most likely explanation?

A. Cross-partition queries can require work across multiple partitions.

B. Cosmos DB charges a fixed number of RUs for every query regardless of its scope.

C. Point reads always consume more RUs than cross-partition queries.

D. Partition keys have no relationship to query performance.

Answer: A

Explanation:
Queries that span multiple partitions may require Cosmos DB to perform work across those partitions, which can increase resource consumption. Designing partition keys around application access patterns can help reduce unnecessary cross-partition queries.

Question 10

A developer wants a query to return only customer names as scalar values instead of objects such as:

			
{
    "name": "John Smith"
}

Which query should the developer use?

			
SELECT *
FROM c

			
SELECT c
FROM c

			
SELECT VALUE c.name
FROM c

			
SELECT OBJECT(c.name)
FROM c

Answer: C

Explanation:
SELECT VALUE returns the selected expression directly rather than wrapping it in a JSON object. Therefore:

			
SELECT VALUE c.name
FROM c

returns scalar values such as:

			
"John Smith"
"Mary Jones"

rather than objects containing a name property.

Final Exam Reminder

For AI-200, don’t think of Cosmos DB simply as “a NoSQL database that I can query.” Think about the relationship between data modeling, partitioning, SDK operations, queries, and performance.

The most important decision pattern is:

Know the item ID + partition key? → Point read.
Need to find items based on criteria? → Query.
Know the partition key? → Target the partition when possible.
Large result set? → Process pages with the SDK iterator.
User-supplied values? → Parameterize the query.
Azure-hosted application without stored credentials? → Prefer managed identity/Entra ID where supported.

Go to the AI-200 Exam Prep Hub main page

AI-200, azure, Cloud computing, Databases, NoSQL August 8, 2026August 15, 2026

Implement a change feed processor to detect and handle new or updated items (AI-200 Exam Prep)

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 Cosmos DB for NoSQL
      --> Implement a change feed processor to detect and handle new or updated items

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

Azure Cosmos DB for NoSQL provides a change feed that records changes made to items in a container. Applications can consume this feed to react to data changes without repeatedly querying the entire container.

For the AI-200 exam, an important implementation pattern is the change feed processor. It provides a push-based mechanism for detecting changes and delivering them to application code for processing.

A change feed processor is particularly useful when an application needs to perform an action whenever items are created or updated, such as:

Processing newly submitted documents
Generating embeddings for newly created content
Updating a search index
Synchronizing data with another system
Running AI processing when new data arrives
Performing analytics or enrichment
Triggering downstream business workflows
Maintaining materialized or derived data

The change feed processor also handles important operational concerns such as checkpointing, load balancing, lease management, and recovery.

1. What Is the Azure Cosmos DB Change Feed?

The change feed is a persistent record of changes to items in an Azure Cosmos DB container.

Conceptually, it looks like this:

			
Application
    |
    | Creates/updates items
    v
Azure Cosmos DB Container
    |
    | Change feed
    v
Change Feed Processor
    |
    +--> Process new item
    +--> Generate embedding
    +--> Update search index
    +--> Call downstream service
    +--> Store derived data

		

Instead of repeatedly asking:

“Which items have changed since the last time I checked?”

the application can consume the change feed and process changes incrementally.

This makes the change feed especially useful for event-driven and near-real-time architectures.

2. Latest Version Change Feed Mode

For the AI-200 scenario involving detection of new or updated items, the default latest version change feed mode is particularly important.

In latest version mode:

Creates appear in the change feed.
Updates appear in the change feed.
Deletes do not appear.
If an item is changed multiple times before it is read, the feed provides the latest version rather than every intermediate version.

For example:

			
Item created
     |
     v
Status = "Pending"
     |
     v
Status = "Processing"
     |
     v
Status = "Completed"

		

If these changes occur before the consumer reads the feed, latest-version mode may expose the current version rather than every intermediate state.

Therefore, latest-version mode is appropriate when the application cares about the current state of changed items, rather than every individual mutation.

Important exam distinction

If an application must detect deletes or process every intermediate version, latest-version mode isn’t sufficient.

Azure Cosmos DB also supports all versions and deletes mode, which captures creates, updates, and deletes. That mode has additional requirements, including continuous backup, and is available for Azure Cosmos DB for NoSQL.

3. What Is a Change Feed Processor?

The change feed processor is a higher-level mechanism for consuming the Azure Cosmos DB change feed.

It uses a push model.

Rather than requiring your application to repeatedly pull batches and manage continuation state itself, the processor:

Reads changes from the monitored container.
Determines which changes need to be processed.
Delivers batches of changes to your application code.
Maintains processing state using a lease container.
Distributes work among multiple processor instances.
Recovers work when an instance fails.

The change feed processor is currently provided through the Azure Cosmos DB .NET V3 and Java V4 SDKs. Python and Node.js applications can consume the change feed using the pull model rather than the change feed processor library.

4. The Four Components of a Change Feed Processor

A key AI-200 concept is understanding the four major components.

4.1 Monitored Container

The monitored container is the Azure Cosmos DB container whose changes you want to process.

For example:

			
Database: AIApplication
Container: Documents
Partition key: /customerId

The processor monitors Documents.

When items are created or updated, those changes become available through the change feed.

4.2 Lease Container

The lease container stores the state used by the change feed processor to coordinate processing.

This is extremely important.

The lease container allows multiple processor instances to share the workload without processing the same lease simultaneously.

Conceptually:

                 Lease Container
                /       |       \
               /        |        \
              v         v         v
          Lease 1    Lease 2    Lease 3
             |          |          |
             v          v          v
          Worker A   Worker B   Worker C

The leases represent ownership and progress for portions of the change feed.

The lease container can be in the same Cosmos DB account as the monitored container or in a separate account.

Exam tip

If a question asks:

What component maintains the state of change feed processing?

The answer is generally:

The lease container.

5. Compute Instances

A compute instance hosts the change feed processor.

Examples include:

Azure Kubernetes Service pods
Azure App Service instances
Azure Virtual Machines
Long-running application processes
Hosted background services

For example:

			
AKS Cluster
Pod 1 --> Change Feed Processor
Pod 2 --> Change Feed Processor
Pod 3 --> Change Feed Processor

Each processor instance must have a unique instance name.

The processor distributes leases among the available instances.

6. The Delegate

The delegate is your application code that processes the changes.

For example, suppose an AI application stores documents in Cosmos DB.

When a document changes, the delegate might:

Extract the text.
Generate an embedding.
Store the embedding.
Update a vector index.
Record processing status.

Conceptually:

			
Cosmos DB Change
       |
       v
Change Feed Processor
       |
       v
Delegate
       |
       +--> Extract text
       |
       +--> Generate embedding
       |
       +--> Store embedding
       |
       +--> Update AI search data

		

The delegate is therefore where the application’s business logic lives.

7. How the Processing Lifecycle Works

The basic lifecycle is:

			
Read change feed
       |
       v
Are there changes?
   /          \
 No            Yes
 |              |
 v              v
Wait          Send batch
 |              |
 +------<-------+
                |
                v
        Delegate succeeds?
           /        \
         No          Yes
         |            |
         v            v
     Retry from    Update
     checkpoint    lease

		

More precisely, the processor:

Reads the change feed.
Waits if no changes are available.
Sends a batch of changes to the delegate.
Waits for successful processing.
Updates the lease with the latest successfully processed position.
Continues processing.

The checkpoint is therefore advanced after successful processing.

8. Why the Change Feed Processor Uses At-Least-Once Processing

One of the most important concepts for the exam is that the change feed processor provides an at-least-once delivery guarantee.

Suppose the processor reads:

			
Change A
Change B
Change C

and passes them to your delegate.

If the delegate fails before the checkpoint is successfully updated, the processor can process those changes again.

Therefore:

			
Change A
Change B
Change C
      |
      v
   Process
      |
      X Failure
      |
      v
Retry
      |
      v
Change A
Change B
Change C

		

This means your application should generally be idempotent.

9. Why Idempotency Matters

An idempotent operation can safely be executed more than once without producing an incorrect final result.

For example, suppose the change feed processor receives:

			
{
    "id": "document-123",
    "status": "completed"
}

Your processing logic might update a downstream record:

document-123 -> completed

If the same change is processed twice, the final state remains:

document-123 -> completed

That is preferable to an operation such as:

balance = balance + 100

where processing the same event twice could incorrectly add the amount twice.

Exam rule

Design change feed handlers assuming a change may be delivered more than once.

10. Lease-Based Load Distribution

The change feed processor can distribute processing across multiple instances.

For example:

			
Change Feed
------------------------------------------------
Partition Range 1
Partition Range 2
Partition Range 3
Partition Range 4
------------------------------------------------
        |          |          |          |
        v          v          v          v
     Worker 1   Worker 2   Worker 3   Worker 4

		

The lease container coordinates ownership of these workloads.

If one worker fails, its leases can eventually be acquired by another worker.

This provides fault tolerance without requiring the developer to manually coordinate workers.

11. Scaling the Change Feed Processor

Suppose you initially have:

Worker 1

and later add:

			
Worker 2
Worker 3

The change feed processor can redistribute leases among the workers.

Conceptually:

			
Before:
Worker 1
 ├── Lease 1
 ├── Lease 2
 ├── Lease 3
 └── Lease 4
After scaling:
Worker 1
 ├── Lease 1
 └── Lease 2
Worker 2
 └── Lease 3
Worker 3
 └── Lease 4

		

This allows processing to be parallelized.

However, simply adding instances does not mean that processing becomes infinitely parallel.

The available workload is constrained by the number of leases/partition ranges.

The number of processor instances should not exceed the number of available leases for meaningful distribution.

12. Partitioning and Change Feed Processing

Azure Cosmos DB containers are partitioned using a partition key.

For example:

			
Container: Documents
Partition key: /customerId

The change feed processor works with the underlying partition ranges.

Each range can be processed independently, allowing parallel processing.

This is one reason that selecting an appropriate partition key remains important even when using the change feed.

A poor partition key can create an uneven workload.

13. Starting Position

An important implementation detail is the processor’s starting position.

When a change feed processor is initialized for the first time, its starting point determines which changes it processes.

In latest-version mode, you can configure the processor to start from a specified time or from the beginning of the container’s lifetime.

For example:

			
Container history
|
|---- Change A
|---- Change B
|---- Change C
|---- Change D
|---- Change E
|
             ^
             |
       Start processor

		

If configured to begin at Change A, the processor can process the historical changes.

If configured to start from the current point, older changes aren’t processed.

Important

The starting-position configuration is used when initializing the processor. Once the lease container has established the processor’s state, changing the starting configuration doesn’t reset the existing checkpoint.

14. Change Feed Processor vs. Pull Model

There are two major approaches to consuming the change feed.

Feature	Change Feed Processor	Pull Model
Processing style	Push	Pull
Checkpoint management	Lease container	Application-managed continuation
Load balancing	Built in	Application responsibility
Error/retry infrastructure	Built in	Application responsibility
.NET support	Yes	Yes
Java support	Yes	Yes
Python	Not through processor library	Yes
Node.js	Not through processor library	Yes

The change feed processor is generally easier when you want Azure Cosmos DB to manage the mechanics of distributing work and maintaining processing state.

15. Change Feed Processor vs. Azure Functions Trigger

Another important distinction is between the change feed processor and the Azure Functions trigger for Cosmos DB.

Both can be used to build event-driven applications.

For example:

			
Cosmos DB
   |
   +----> Change Feed Processor
   |
   +----> Azure Functions Trigger

		

The change feed processor is useful when you need more direct control over a long-running processing application.

The Azure Functions trigger is useful when you want a serverless implementation.

The Azure Functions trigger also uses a lease container to maintain processing state.

16. Handling Processing Failures

Suppose your delegate encounters an exception:

			
Batch
  |
  v
Delegate
  |
  X Exception

		

The processor doesn’t simply assume the batch succeeded.

Because the checkpoint hasn’t advanced successfully, the processor can retry the batch.

This behavior produces the at-least-once guarantee.

Important design consideration

If a particular item consistently causes processing to fail, the processor can repeatedly encounter the same problem.

A robust application should therefore have an error-handling strategy.

For example:

			
Change
  |
  v
Process
  |
  X Failure
  |
  +--> Retry
  |
  +--> Persistent failure
          |
          v
     Error/DLQ storage

		

An application might persist information about the failed change to another Cosmos DB container or another durable store so that the processing pipeline doesn’t remain permanently blocked by one problematic change.

17. Monitoring Change Feed Lag

A change feed processor can fall behind the incoming changes.

For example:

			
New changes:
1000 events/sec
Processing:
700 events/sec
Result:
Change feed lag increases

		

The change feed estimator can be used to monitor processor progress and estimate lag.

This can help identify:

Insufficient processing capacity
Slow downstream services
Throttling
Application errors
Lease problems
Processing bottlenecks

18. Request Units and the Change Feed

Change feed processing isn’t free from a Cosmos DB throughput perspective.

Reading the change feed from the monitored container consumes request units (RUs).

Operations involving the lease container also consume RUs.

For example:

			
Monitored Container
       |
       +--> Change feed reads --> RU consumption
Lease Container
       |
       +--> Lease reads
       +--> Lease updates
       +--> Lease coordination
                    |
                    v
               RU consumption

		

If the monitored or lease container experiences throttling, change processing can be delayed.

This is especially important when deploying multiple processor instances or multiple processing workloads that share a lease container.

19. Lease Container Permissions

When Microsoft Entra ID authentication is used, the processor’s identity needs appropriate permissions.

The monitored container requires permissions related to:

Reading account metadata
Reading the change feed

The lease container requires permissions for operations such as:

Reading items
Creating items
Replacing items
Deleting items
Executing queries

This is an important distinction:

The application doesn’t just need permission to read the monitored data; it also needs permission to maintain the processor’s lease state.

20. Using a Global Endpoint

For a change feed processor workload, Microsoft recommends using the global Cosmos DB endpoint rather than a region-specific endpoint.

For example:

			
Preferred:
https://contoso.documents.azure.com

rather than:

https://contoso-westus.documents.azure.com

Regional preferences should be configured through the appropriate SDK region settings.

This is important because lease documents are scoped to the configured endpoint. Changing endpoints can result in separate lease state.

21. A Typical AI Application Architecture

Consider an AI document-processing application.

A user uploads a document, and the application stores metadata in Cosmos DB.

The desired workflow is:

			
User
 |
 v
Application
 |
 v
Cosmos DB
 |
 | New/updated document
 v
Change Feed
 |
 v
Change Feed Processor
 |
 v
Processing Delegate
 |
 +--> Extract document text
 |
 +--> Generate embedding
 |
 +--> Store vector
 |
 +--> Update search metadata
 |
 +--> Notify downstream application

		

This architecture avoids repeatedly scanning the entire container looking for new work.

It also allows the processing workload to scale independently from the application that writes the data.

22. Example .NET Concept

A simplified .NET implementation conceptually looks like this:

			
var processor = monitoredContainer
    .GetChangeFeedProcessorBuilder<MyDocument>(
        "documentProcessor",
        HandleChangesAsync)
    .WithInstanceName("worker-01")
    .WithLeaseContainer(leaseContainer)
    .Build();
await processor.StartAsync();

		

The important concepts are:

monitoredContainer — where changes originate.
leaseContainer — where processing state is maintained.
HandleChangesAsync — your business logic.
WithInstanceName — uniquely identifies the processor instance.
Processor startup — begins monitoring the change feed.

The exact SDK APIs can vary by SDK version, so the exam focus should be on understanding the architecture and responsibilities rather than memorizing every method signature. The current change feed processor documentation identifies .NET V3 and Java V4 as the SDKs that provide the processor library.

23. Important Exam Concepts to Remember

For AI-200, make sure you can distinguish the following:

Monitored container

Contains the data whose changes are being detected.

Lease container

Maintains processor state and coordinates work across instances.

Delegate

Contains the application’s processing logic.

Compute instance

Hosts the change feed processor.

Latest-version mode

Captures the latest versions of creates and updates; deletes aren’t included.

All versions and deletes mode

Captures creates, updates, and deletes, including intermediate changes.

Checkpoint

Records the latest successfully processed position.

At-least-once delivery

A change can be processed more than once, so handlers should be idempotent.

Pull model

The application manages reading, continuation state, and processing coordination.

Change feed processor

Provides a higher-level push-based processing model with lease-based coordination.

Practice Exam Questions

Question 1

An AI application stores documents in an Azure Cosmos DB for NoSQL container. Whenever a document is created or updated, the application must perform additional processing. The development team wants Azure Cosmos DB to manage checkpointing and distribute processing across multiple application instances.

Which solution should the team implement?

A. A timer-triggered Azure Function that scans the container

B. Periodic SQL queries

C. Azure Cosmos DB analytical store queries

D. Change feed processor

Answer: D

Explanation

The change feed processor is designed to process changes incrementally and provides built-in lease-based coordination and checkpoint management. It can distribute change feed processing across multiple instances.

The other approaches require the application to identify changes itself and are less appropriate for event-driven incremental processing.

Question 2

A change feed processor processes a batch of changes successfully but fails before the processing state is checkpointed. What should the application expect?

A. The changes are permanently discarded

B. The batch can be delivered again

C. The entire Cosmos DB container is automatically restored

D. The change feed is permanently disabled

Answer: B

Explanation

The change feed processor provides at-least-once delivery. If processing succeeds but the checkpoint isn’t successfully advanced, the processor can process the same changes again.

Application processing logic should therefore be designed to be idempotent.

Question 3

Which component is primarily responsible for maintaining the state and coordinating ownership of change feed processing across multiple processor instances?

A. Monitored container

B. Compute instance

C. Lease container

D. Application Gateway

Answer: C

Explanation

The lease container stores the state used by the change feed processor to coordinate processing across instances.

The monitored container provides the source data, while compute instances host the processing application.

Question 4

An application uses the default latest-version change feed mode. An item is created and then updated three times before the processor reads the changes. What behavior should the application expect?

A. Only the delete operation is returned

B. All four versions are guaranteed to be returned

C. No changes are returned because the item changed multiple times

D. The latest version of the item is available rather than every intermediate version

Answer: D

Explanation

Latest-version mode provides the latest version of an item in the feed rather than preserving every intermediate change between reads.

If the application needs every create, update, and delete operation, it should consider all versions and deletes mode instead.

Question 5

A developer is building a change feed processor application that will run on three AKS pods. What is the primary purpose of assigning each processor instance a unique instance name?

A. To identify each compute instance participating in lease distribution

B. To specify the Cosmos DB partition key

C. To determine the consistency level

D. To select the Cosmos DB database

Answer: A

Explanation

Each change feed processor instance should have a unique instance name. The processor uses the instances and leases to distribute processing work across the deployment.

The instance name is unrelated to partition-key selection, database selection, or consistency configuration.

Question 6

An AI application must react when documents are deleted from an Azure Cosmos DB for NoSQL container. Which change feed capability is most appropriate?

A. Latest-version change feed mode

B. All versions and deletes change feed mode

C. Increasing the consistency level

D. Increasing the container’s RU/s

Answer: B

Explanation

All versions and deletes mode captures creates, updates, and deletes.

Latest-version mode does not capture deletes.

All versions and deletes mode has additional requirements, including continuous backup, and is specifically available for Azure Cosmos DB for NoSQL.

Question 7

A change feed processor application experiences increasingly large processing delays. Investigation shows that the application is processing changes correctly but cannot keep up with incoming changes.

Which metric or capability is most useful for determining whether the processor is falling behind?

A. Azure DNS query count

B. Azure Storage blob count

C. Change feed estimator

D. Azure Resource Manager activity log

Answer: C

Explanation

The change feed estimator can be used to estimate the lag between the changes available in the monitored container and the progress of the change feed processor.

This can help identify processing bottlenecks and determine whether additional processing capacity may be necessary.

Question 8

A change feed processor’s delegate updates an external database. The same change may occasionally be delivered more than once. What should the developer do?

A. Disable checkpointing

B. Use an idempotent processing design

C. Increase the Cosmos DB consistency level to strong

D. Disable leases

Answer: B

Explanation

The change feed processor provides at-least-once delivery, meaning a change can be processed more than once.

The delegate should therefore be designed to handle duplicate processing safely. Idempotent operations are one of the most important techniques for doing this.

Question 9

A company runs several change feed processor instances and notices that the lease container is experiencing RU throttling. What is a likely consequence?

A. Change feed processing can be delayed

B. All documents in the monitored container are deleted

C. The Cosmos DB account automatically switches to strong consistency

D. The application automatically receives unlimited RU/s

Answer: A

Explanation

The lease container performs operations that consume request units. If the lease container is throttled, lease coordination and renewal can be delayed, which can delay change feed processing.

The monitored container’s change feed reads also consume RUs. Both the monitored and lease containers should therefore be appropriately provisioned.

Question 10

A development team wants to consume an Azure Cosmos DB change feed from a Python application. They want to use the built-in change feed processor library that automatically handles lease-based processing.

What should the team do?

A. Use the .NET change feed processor library from Python

B. Use the Java change feed processor library from Python

C. Use the change feed pull model from Python

D. Use Azure SQL Database instead

Answer: C

Explanation

The Azure Cosmos DB change feed processor library is available for .NET and Java. Python applications can consume the change feed using the pull model, where the application manages continuation state and processing.

Key Takeaways

For the AI-200 exam, the most important ideas are:

The change feed records changes to Azure Cosmos DB items.
The change feed processor provides a push-based processing model.
The monitored container is the source of changes.
The lease container stores processing state and coordinates workers.
The delegate contains the application’s change-processing logic.
Multiple processor instances can share the workload through leases.
Change feed processing provides at-least-once delivery.
Handlers should therefore be idempotent.
Latest-version mode captures creates and updates but not deletes.
All versions and deletes mode captures creates, updates, and deletes.
The change feed processor library is available for .NET and Java; Python and Node.js use the pull model.
Change feed processing consumes RUs.
Throttling of the monitored or lease container can delay processing.
The change feed estimator can help identify processing lag.
The lease container is fundamental to distributed, fault-tolerant change feed processing.

The exam’s scenario questions are likely to test whether you can select the right change feed mode, processing model, lease architecture, error-handling strategy, and scaling approach, rather than simply recognizing the term “change feed.”

Go to the AI-200 Exam Prep Hub main page