Category: azure

AI-200, azure, Cloud computing, Microsoft Certification August 15, 2026

Implement Azure Managed Redis data operations, including caching, expiration, and invalidation (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 Azure Managed Redis data operations, including caching, expiration, and invalidation

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 Managed Redis is a fully managed, in-memory data store based on Redis Enterprise. It provides high-throughput, low-latency access to frequently used application data and can be used to improve the performance and scalability of applications that otherwise depend heavily on backend databases or services.

For the AI-200: Developing AI Cloud Solutions on Azure exam, developers should understand how to implement Redis data operations and, in particular, how to use Redis for:

Caching frequently accessed data
Storing and retrieving key-value data
Setting expiration times on cached data
Removing or invalidating stale data
Implementing cache-aside patterns
Reducing database and backend-service load
Improving application responsiveness
Selecting appropriate Redis data structures
Designing cache keys appropriately
Handling cache misses
Understanding eviction versus expiration
Avoiding common Redis performance mistakes

Azure Managed Redis is generally best viewed as a high-performance cache or temporary data store, rather than the authoritative system of record. Applications should normally retain authoritative data in a durable backend such as Azure Database for PostgreSQL, Azure SQL Database, or Azure Cosmos DB.

1. Why Use Azure Managed Redis?

Traditional applications frequently retrieve data from databases or external services. Although these systems are designed for reliability and scalability, repeatedly retrieving the same information can introduce unnecessary:

Network traffic
Database CPU utilization
Query processing
Connection utilization
Application latency
Backend service load

Redis addresses this by keeping frequently accessed information in memory.

A simplified architecture looks like this:

			
Application
     |
     v
Azure Managed Redis
     |
     | Cache miss
     v
Primary Database

		

When the requested information is already in Redis, the application can return it without querying the primary database.

This can dramatically reduce response times for frequently accessed information.

Common examples

Redis can be useful for caching:

Product information
User profiles
Configuration data
Frequently requested database queries
API responses
Session information
Authentication-related application state
Frequently accessed reference data
AI application results
Semantic-cache results
Embeddings and vectors

Azure Managed Redis supports data caching, session storage, messaging scenarios, and AI-oriented scenarios such as storing embeddings and implementing semantic caching.

2. The Cache-Aside Pattern

One of the most important caching patterns for the AI-200 exam is the cache-aside pattern, sometimes called lazy loading.

The application is responsible for checking Redis before querying the authoritative data source.

The basic process is:

			
1. Application receives request
          |
          v
2. Look for data in Redis
          |
       +--+--+
       |     |
      Hit   Miss
       |     |
       v     v
    Return  Query
     data   database
               |
               v
          Store result
             in Redis
               |
               v
          Return result

		

Cache hit

A cache hit occurs when the requested data is already in Redis.

Application → Redis → Data returned

The database does not need to be queried.

Cache miss

A cache miss occurs when the requested data isn’t present in Redis.

The application:

Queries the authoritative database.
Receives the result.
Stores the result in Redis.
Returns the result to the caller.

This pattern allows the cache to populate naturally based on actual application usage.

Conceptual pseudocode

			
value = Redis.GET(key)
IF value exists:
    return value
value = Database.Query(...)
Redis.SET(key, value, expiration)
return value

		

The important principle is that Redis is populated when the application needs the data, rather than loading the entire database into memory.

3. Why Cache-Aside Is Particularly Useful

Suppose an application has one million customer records but only 20,000 customers access the application regularly.

Loading all one million records into Redis may waste memory.

With cache-aside:

Frequently accessed records enter the cache.
Infrequently accessed records remain in the database.
Expired records can be removed.
Redis memory is focused on valuable data.

This makes the cache more efficient.

Azure’s guidance specifically identifies cache-aside as a common data-cache pattern in which data is loaded into the cache only when needed.

4. Redis Key-Value Operations

At its simplest, Redis stores data using keys and values.

For example:

			
Key:
customer:12345
Value:
{"id":12345,"name":"Norm","tier":"Gold"}

The application can retrieve the value using the key.

Conceptually:

			
SET customer:12345 {...}
GET customer:12345

A good Redis key should:

Be unique within the application’s namespace
Be predictable
Be easy to construct
Identify the cached resource clearly
Avoid unnecessary length
Avoid collisions between unrelated data

A useful naming convention might be:

			
customer:12345
product:9876
order:54321
embedding:document:123

For larger applications, namespaces can make keys easier to manage:

			
customer:profile:12345
product:details:9876
ai:response:abc123

5. Choosing Redis Data Structures

Redis supports more than simple strings.

Common data structures include:

Data Structure	Typical Use
String	Simple values, JSON, counters
Hash	Objects with multiple fields
List	Ordered collections or queues
Set	Unique unordered values
Sorted Set	Ranked or scored collections
Stream	Event/message processing
Vector-related structures	AI embeddings and similarity scenarios

For ordinary application caching, strings and hashes are particularly common.

For example, a customer object might be stored as a JSON string:

			
customer:12345
    |
    +-- {"id":12345,"name":"Norm","status":"Active"}

Alternatively, a Redis hash could store individual fields:

			
customer:12345
    name → Norm
    status → Active
    tier → Gold

The appropriate choice depends on how the application reads and updates the data.

6. Cache Expiration

Caching introduces an important problem:

What happens when the cached value becomes stale?

Redis provides key expiration, also called a time-to-live or TTL.

For example:

			
customer:12345
TTL = 300 seconds

After the expiration period passes, Redis automatically removes the key.

Azure Managed Redis supports setting timeouts on keys, and expired keys are automatically removed when their configured timeout passes.

7. Why Expiration Matters

Consider an application that caches weather information.

Suppose:

			
weather:orlando
TTL = 5 minutes

If the weather changes, the cached information should eventually disappear so that a subsequent request retrieves fresh information.

Without expiration, stale data could remain indefinitely.

Expiration therefore provides a simple mechanism for balancing:

Performance
Memory usage
Data freshness

8. Choosing an Appropriate TTL

The correct TTL depends on how quickly the underlying data changes.

Short TTL

Use a short expiration time when data changes frequently.

Examples:

			
stock price → seconds
real-time availability → seconds/minutes
weather → minutes

Medium TTL

Useful for data that changes periodically.

Examples:

			
product catalog → minutes/hours
exchange rates → minutes
application configuration → minutes

Long TTL

Useful for relatively stable data.

Examples:

			
reference data → hours
static metadata → hours/days

There is no universally correct TTL.

The developer should consider:

How frequently the source data changes
How stale the application can tolerate the data being
How expensive the source query is
How much Redis memory is available
How frequently the cached value is requested

9. Expiration Versus Deletion

Expiration and explicit deletion are related but different.

Expiration

The application specifies a timeout.

			
SET product:123 value
EXPIRE product:123 300

Redis eventually removes the key automatically.

Explicit deletion

The application deliberately removes the key.

Conceptually:

DEL product:123

This is useful when the underlying data changes and the application knows that the cached copy is no longer valid.

Azure Managed Redis identifies expiration, eviction, and explicit deletion as distinct reasons that cached keys can disappear.

10. Cache Invalidation

Cache invalidation means removing or updating cached data when it is no longer valid.

A classic example is updating a customer record.

Suppose the database contains:

			
Customer 123
Status = Active

Redis contains:

			
customer:123
Status = Active

The application changes the database:

Status = Suspended

If Redis still contains the old value, the application could continue returning:

Status = Active

The cache is now stale.

The application therefore needs an invalidation strategy.

11. Common Cache Invalidation Strategies

There are several common approaches.

Strategy 1: Delete the cache entry

After changing the authoritative database:

			
UPDATE database
DEL customer:123

The next request becomes a cache miss.

The application retrieves the current value from the database and repopulates Redis.

This is often a simple and effective approach.

Strategy 2: Update the cache

Instead of deleting the cache entry, the application updates Redis with the new value.

			
UPDATE database
SET customer:123 = new value

The advantage is that subsequent requests can immediately use the updated cache.

The disadvantage is that the application must carefully keep the database and cache synchronized.

Strategy 3: Rely on expiration

The application allows the cached value to expire naturally.

This is simpler but potentially allows stale data to remain available until the TTL expires.

For example:

TTL = 10 minutes

A database update occurring immediately after the cache was populated could result in stale data being served for almost 10 minutes.

Therefore, expiration alone may not be sufficient when data freshness is important.

12. Combining Invalidation and Expiration

A strong caching strategy often combines explicit invalidation with TTL.

For example:

			
Cache customer data
TTL = 30 minutes

When the customer changes:

			
UPDATE database
DELETE Redis key

The TTL provides protection against stale data if the invalidation process fails, while explicit invalidation removes known-stale data immediately.

This gives the application two levels of protection:

			
Normal update
     |
     v
Explicit invalidation
     |
     v
Immediate freshness
Unexpected missed invalidation
     |
     v
TTL expiration
     |
     v
Eventual freshness

		

This is an important architectural pattern to recognize in exam scenarios.

13. Cache Invalidation and the Source of Truth

A fundamental rule is:

The cache should generally not become the authoritative source of application data.

For example:

			
Azure Database for PostgreSQL
        |
        | authoritative data
        v
Azure Managed Redis
        |
        | cached copy
        v
Application

		

If Redis is lost, the application should be capable of rebuilding its cache from the authoritative data source.

Azure Managed Redis is designed primarily as a cache and temporary data store rather than a primary database.

14. Handling Cache Misses

Applications must always be designed to handle cache misses.

A cache miss is not necessarily an error.

It is an expected condition.

A typical workflow is:

			
GET key
   |
   +-- Found → return value
   |
   +-- Not found
           |
           v
      Query database
           |
           v
      Store in Redis
           |
           v
      Return value

		

A well-designed application should therefore never assume:

“If the value isn’t in Redis, something is broken.”

Instead:

“If the value isn’t in Redis, retrieve it from the authoritative source.”

15. Cache Stampede

A cache stampede occurs when a frequently accessed cache entry expires and many requests simultaneously attempt to rebuild it.

For example:

			
Popular key expires
       |
       +-- Request 1 → Database
       +-- Request 2 → Database
       +-- Request 3 → Database
       +-- Request 4 → Database
       +-- ...
       +-- Request 10,000 → Database

		

The cache was supposed to reduce database traffic, but expiration temporarily creates a massive burst of database requests.

Potential strategies include:

Staggering expiration times
Using appropriate TTLs
Refreshing hot data before expiration
Coordinating cache regeneration
Using locking or request coalescing techniques
Using a background refresh strategy

The exact implementation depends on application requirements.

16. Avoiding the “Thundering Herd”

A related problem is the thundering herd effect.

Suppose thousands of requests need the same data and the cache expires.

If every request independently queries the database, the backend can become overloaded.

A common mitigation is to allow one process to refresh the data while other requests wait briefly or use the previous value where appropriate.

Conceptually:

                Cache miss
                    |
            +-------+-------+
            |               |
        First request    Other requests
            |               |
        Refresh cache    Wait/use fallback
            |
            v
        New cached value

The goal is to prevent thousands of identical backend queries.

17. Cache-Aside Write Pattern

There are multiple ways to handle writes with a cache-aside architecture.

One common approach is:

			
Update database
Delete corresponding Redis key

For example:

			
UPDATE products
SET price = 25.00
WHERE product_id = 100;
DEL product:100;

The next read retrieves the new database value and caches it.

This pattern is attractive because the database remains the source of truth.

18. Why Delete-After-Write Is Often Safer Than Cache-First Updates

Consider:

			
Application
     |
     +--> Redis
     |
     +--> Database

		

If the application updates Redis first and the database update subsequently fails, the cache could contain a value that doesn’t exist in the database.

By updating the authoritative store first and invalidating the cache afterward, the application reduces this risk.

A typical sequence is:

			
Database update
       |
       v
Cache invalidation
       |
       v
Next request repopulates cache

		

The exact transaction and failure-handling strategy should be designed according to the application’s consistency requirements.

19. Expiration Does Not Mean Eviction

This is an important exam distinction.

Expiration

A key reaches its configured TTL.

			
TTL reaches zero
        ↓
Key expires

Eviction

Redis needs to free memory and removes keys according to its configured memory/eviction behavior.

			
Memory pressure
        ↓
Eviction policy
        ↓
Keys removed

		

Explicit deletion

The application deliberately removes a key.

			
DEL key
   ↓
Key removed

These are three different mechanisms.

Azure Managed Redis documentation identifies expiration, eviction, and explicit deletion as separate causes of keys disappearing from the cache.

20. Eviction and Memory Pressure

Redis is an in-memory service, so memory management is critical.

If the cache approaches its memory capacity, Redis can remove keys according to its configured eviction behavior.

Therefore, an application should not interpret every missing key as an expiration event.

Possible causes include:

TTL expiration
Memory eviction
Explicit deletion
Cache flushing
Failover/replication behavior
Other infrastructure-related events

Monitoring cache metrics can help distinguish these scenarios.

21. Key Naming Best Practices

A good key strategy makes a Redis implementation easier to maintain.

Consider:

customer:12345

instead of:

The first provides context.

For a larger application:

			
customer:profile:12345
customer:orders:12345
customer:preferences:12345

This makes it easier to understand what each key represents.

Avoid unnecessarily large keys because Redis is optimized for high-performance operations and memory usage matters.

22. Avoid Storing Excessively Large Values

Redis is designed for fast in-memory access.

Large values can:

Consume significant memory
Increase network traffic
Increase serialization/deserialization costs
Increase latency
Reduce cache efficiency

For example, rather than caching a massive database object containing thousands of unnecessary fields, cache only the information needed by the application.

A useful principle is:

Cache what the application needs, not everything the database can provide.

Azure’s current guidance also recommends avoiding unnecessarily large Redis values because smaller values generally provide better performance characteristics.

23. Connection Management

Applications should avoid creating a new Redis connection for every request.

For example, this is generally a poor pattern:

			
Request 1 → Create connection → Redis → Close
Request 2 → Create connection → Redis → Close
Request 3 → Create connection → Redis → Close

Instead, applications should generally use a long-lived connection/client that can be reused across requests.

For .NET applications using StackExchange.Redis, Microsoft recommends a single long-lived ConnectionMultiplexer rather than creating a new connection for each request.

This reduces:

Connection overhead
Resource consumption
Latency
Connection churn

24. Connection Resilience

Applications should also assume that Redis connections can occasionally experience interruptions because of:

Maintenance
Failover
Network problems
Infrastructure events

The application should be designed to reconnect and handle transient failures appropriately.

For example:

			
Application
     |
     v
Redis connection
     |
  failure
     |
     v
Reconnect
     |
     v
Continue processing

		

For a cache, a Redis outage should ideally degrade application performance rather than completely destroy application functionality.

The application can fall back to the authoritative database when appropriate.

25. Redis as a Performance Layer

A useful way to conceptualize Azure Managed Redis is as a performance layer:

                +----------------+
                |   Application  |
                +-------+--------+
                        |
                        v
                +---------------+
                | Azure Managed |
                |     Redis     |
                +-------+-------+
                        |
                  Cache miss
                        |
                        v
                +---------------+
                |   Database    |
                +---------------+

The application gets:

Fast reads from Redis
Durable storage from the database
Reduced database workload
Better scalability

This separation is central to effective caching architecture.

26. Caching AI Application Data

Azure Managed Redis is particularly relevant to AI applications.

Possible cached information includes:

Embeddings
Frequently retrieved documents
AI-generated responses
Prompt-related information
Semantic-cache entries
User session state
Frequently accessed metadata

For example, a semantic cache might store:

			
Question:
"What is our vacation policy?"
Embedding / semantic representation
          |
          v
Redis
          |
          v
Previously generated answer

		

If another request is sufficiently similar, the application may reuse an existing result rather than repeatedly invoking an AI model.

This can reduce:

Model calls
Latency
Cost
Backend processing

Azure Managed Redis specifically supports AI scenarios such as vector storage and semantic caching.

27. Caching Versus Persistent Storage

A common exam trap is assuming that Redis should replace the database.

Generally:

Requirement	Better Choice
Authoritative relational data	PostgreSQL
Durable transactional data	PostgreSQL
Large persistent document store	Cosmos DB or other durable storage
Frequently accessed temporary data	Redis
Session state	Redis
Short-lived application cache	Redis
Semantic cache	Redis
Embedding/vector workloads	Redis or specialized vector-capable data service

Redis should generally complement rather than replace the authoritative data store.

28. Cache Invalidation Strategies Compared

Strategy	Advantage	Disadvantage
TTL expiration	Simple	Data can remain stale until TTL expires
Explicit deletion	Immediate invalidation	Application must know when data changes
Update cache	Fresh cache immediately	More synchronization complexity
TTL + deletion	Strong balance	Requires both mechanisms
Background refresh	Good for hot data	More application complexity

For many applications, TTL plus explicit invalidation is an effective design.

29. Common Exam Scenario

Suppose an application retrieves product information from Azure Database for PostgreSQL.

The application receives thousands of requests for the same product.

The best architecture is:

			
Request
   |
   v
Redis GET product:123
   |
   +---- Hit ----> Return cached product
   |
   +---- Miss
          |
          v
   Query PostgreSQL
          |
          v
   Store in Redis with TTL
          |
          v
       Return

		

When the product changes:

			
Update PostgreSQL
       |
       v
Delete product:123 from Redis

The next request retrieves the current value and repopulates the cache.

This is a classic cache-aside implementation.

30. Common Mistakes to Avoid

Mistake 1: Treating Redis as the primary database

Redis should generally be treated as a cache or temporary store, not the authoritative system of record.

Mistake 2: Never setting expiration

Without expiration, stale data can remain indefinitely and memory consumption can increase.

Mistake 3: Relying only on expiration

If freshness is important, explicit invalidation may be necessary.

Mistake 4: Confusing expiration with eviction

Expiration happens because a TTL expires.

Eviction happens because Redis needs memory and removes keys according to its configured policy.

Mistake 5: Creating a connection for every request

Reuse long-lived Redis connections/clients.

Mistake 6: Caching enormous objects

Large values increase memory and network costs.

Mistake 7: Ignoring cache misses

A cache miss should be an expected application path.

Mistake 8: Updating the cache without considering database consistency

The authoritative data store and cache must be handled carefully during writes.

Mistake 9: Assuming cached data is permanent

Redis is an in-memory service. Applications should be designed to tolerate cache loss and rebuild cached information when necessary.

31. AI-200 Exam Takeaways

For the AI-200 exam, remember these core concepts:

Cache-aside

Check Redis → if miss, retrieve from database → store in Redis → return data.

Expiration

A TTL automatically removes a key after the configured timeout.

Invalidation

Explicitly remove or update cached data when the authoritative data changes.

Eviction

Redis removes keys because of memory pressure according to its configured eviction behavior.

Source of truth

Keep authoritative data in a durable backend.

Connection management

Reuse long-lived Redis client connections rather than creating connections for every request.

Performance

Keep cached values reasonably small and avoid unnecessarily expensive Redis operations.

Resilience

Design the application to tolerate Redis connection failures and cache misses.

AI scenarios

Redis can support semantic caching, embedding/vector storage, session state, and other high-performance AI application patterns.

Practice Exam Questions

Question 1

An application retrieves product information from Azure Database for PostgreSQL. The same products are requested thousands of times per minute. The developer wants to reduce database load while keeping PostgreSQL as the authoritative data source.

Which approach should the developer implement?

A. Store all PostgreSQL tables permanently in Redis and stop using PostgreSQL for reads.

B. Use a cache-aside pattern in which the application checks Redis first and retrieves data from PostgreSQL on a cache miss.

C. Write every PostgreSQL transaction directly to Redis and use Redis as the primary database.

D. Query PostgreSQL for every request and use Redis only for logging.

Answer: B

Explanation:
The cache-aside pattern checks Redis first. On a cache miss, the application queries PostgreSQL, stores the result in Redis, and returns it. PostgreSQL remains the authoritative data source. This reduces repeated database queries while preserving the database as the system of record.

Question 2

An application caches weather information in Azure Managed Redis. Weather information should never remain in the cache for more than five minutes.

What should the developer configure?

A. A Redis key expiration of five minutes.

B. A five-minute Redis connection timeout.

C. A five-minute eviction policy.

D. A five-minute database transaction timeout.

Answer: A

Explanation:
Key expiration uses a TTL to automatically remove a key after a specified period. A five-minute TTL ensures the cached weather information does not remain cached beyond the configured lifetime. Expiration is different from eviction, which occurs because of memory pressure.

Question 3

A customer record is stored in both PostgreSQL and Redis. The customer updates their address. The application successfully updates PostgreSQL but the old address remains in Redis.

What is the best way to ensure the next read retrieves the current address?

A. Increase the Redis memory allocation.

B. Restart the Redis instance.

C. Delete the cached customer key after successfully updating PostgreSQL.

D. Disable Redis expiration.

Answer: C

Explanation:
Deleting the cached key explicitly invalidates the stale value. The next request causes a cache miss, retrieves the current customer record from PostgreSQL, and can repopulate Redis.

Question 4

A developer notices that Redis keys are disappearing before their expected TTL values are reached. The Redis instance is experiencing high memory utilization.

What is the most likely explanation?

A. PostgreSQL automatically deleted the Redis keys.

B. The Redis connection expired.

C. The application’s DNS record changed.

D. Redis evicted keys because of memory pressure.

Answer: D

Explanation:
Expiration and eviction are different. A key can be removed because its TTL expires, but Redis can also remove keys when memory pressure requires space to be reclaimed according to the configured eviction behavior.

Question 5

A web application creates a new Redis connection every time an HTTP request needs to retrieve cached data.

What should the developer generally do instead?

A. Use a single long-lived Redis client/connection that can be reused across requests.

B. Create two Redis connections for every request to provide redundancy.

C. Disable connection reuse so that every request receives a fresh connection.

D. Store Redis connection objects in every cached value.

Answer: A

Explanation:
Creating connections repeatedly introduces unnecessary overhead and connection churn. Redis applications should generally reuse long-lived client connections. For example, .NET applications using StackExchange.Redis commonly use a shared, long-lived ConnectionMultiplexer.

Question 6

A developer wants cached customer information to remain available for up to one hour but also wants changes to a customer record to become visible immediately.

Which strategy is most appropriate?

A. Use a one-hour TTL and never invalidate the cache.

B. Disable expiration and update Redis once per day.

C. Use a one-hour TTL and explicitly invalidate the customer’s cache entry when the database record changes.

D. Store the customer only in Redis and remove the PostgreSQL record.

Answer: C

Explanation:
Combining TTL with explicit invalidation provides two layers of protection. Explicit invalidation removes known-stale data immediately, while the TTL prevents an entry from remaining cached indefinitely if an invalidation event is missed.

Question 7

Thousands of users request the same product. The product’s Redis entry expires at nearly the same time, causing thousands of requests to query PostgreSQL simultaneously.

What problem does this scenario represent?

A. Cache encryption failure.

B. Cache stampede or thundering herd.

C. Redis key collision.

D. Database normalization.

Answer: B

Explanation:
A cache stampede occurs when a popular cached item expires and many requests simultaneously attempt to rebuild the cache. This can overwhelm the backend database. Techniques such as request coordination, locking, staggered expiration, and background refresh can reduce the problem.

Question 8

An application stores the following information in Redis:

			
customer:12345
customer:12346
customer:12347

What is the primary benefit of this naming convention?

A. It automatically encrypts the values.

B. It prevents Redis from expiring the keys.

C. It increases the Redis memory limit.

D. It provides a predictable namespace that identifies the type and identity of the cached resource.

Answer: D

Explanation:
A structured naming convention makes keys predictable, understandable, and easier to manage. Prefixes such as customer: distinguish customer records from other application data.

Question 9

An AI application frequently receives semantically similar questions. Generating a response for every request requires an expensive model invocation.

How could Azure Managed Redis help?

A. Cache previously generated results or semantic representations so suitable requests can reuse existing results.

B. Replace the AI model with Redis commands.

C. Store all model training data exclusively in Redis.

D. Use Redis expiration to permanently store every model response.

Answer: A

Explanation:
Azure Managed Redis can support semantic caching and AI workloads. An application can cache suitable AI responses or related representations and reuse them when a later request is sufficiently similar. This can reduce model calls, latency, and cost.

Question 10

A developer is designing an application that uses Redis for caching. The developer wants the application to continue functioning if cached data disappears.

Which design is most appropriate?

A. Treat Redis as the only authoritative copy of the data.

B. Disable all Redis expiration and eviction mechanisms.

C. Keep authoritative data in a durable database and design the application to repopulate Redis after cache misses.

D. Write all application data to Redis and periodically delete the database.

Answer: C

Explanation:
A resilient caching architecture treats Redis as a performance layer rather than the authoritative data store. If a cached item disappears because of expiration, eviction, deletion, or another event, the application can retrieve the authoritative value from the durable database and repopulate the cache.

Final Study Summary

For the AI-200 exam, the most important distinction is between the authoritative data store and the cache.

A typical architecture is:

                    Application
                         |
                         v
                 Azure Managed Redis
                    /           \
                 Hit             Miss
                  |                |
                  v                v
              Return          Query database
                                 |
                                 v
                           Populate Redis
                                 |
                                 v
                              Return

When data changes:

			
Update authoritative database
             |
             v
Invalidate Redis entry
             |
             v
Next request repopulates cache

		

And when a TTL expires:

			
TTL reaches zero
       |
       v
Key expires
       |
       v
Next request causes cache miss
       |
       v
Retrieve fresh data

		

Keep these concepts distinct:

Concept	Meaning
Cache hit	Requested data exists in Redis
Cache miss	Requested data isn’t in Redis
TTL	Amount of time a key is allowed to remain cached
Expiration	Automatic removal after TTL expires
Invalidation	Application-driven removal/update of stale data
Eviction	Removal caused by memory pressure and eviction policy
Cache-aside	Application checks cache, then authoritative store on a miss
Cache stampede	Many requests rebuild an expired cache entry simultaneously
Source of truth	Durable system containing authoritative data
Semantic cache	Cache that can reuse results for sufficiently similar AI requests

The exam-ready mental model is simple:

Cache for speed, expire for freshness, invalidate when you know data changed, and keep the database as the source of truth.

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, Microsoft Certification August 15, 2026

Run vector similarity search, including storing embeddings, semantic retrieval, and implementing retrieval-augmented generation (RAG) patterns by using metadata filter (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
      --> Run vector similarity search, including storing embeddings, semantic retrieval, and implementing retrieval-augmented generation (RAG) patterns by using metadata filter

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

Overview

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

“How can I reset my company laptop password?”

while the stored document might say:

“Instructions for recovering forgotten corporate device credentials.”

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

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

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

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

1. Understanding Embeddings

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

For example, an embedding model might transform:

"How do I reset my password?"

into a vector resembling:

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

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

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

For example:

"How do I reset my password?"

and

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

should have embeddings that are relatively close to each other.

Conversely:

"What is the weather forecast?"

should produce an embedding that is considerably different.

Important exam concept

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

A typical database record might contain:

			
id
title
content
category
tenant_id
created_at
embedding

		

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

2. What Is Semantic Retrieval?

Traditional database retrieval often asks:

“Which records contain these words?”

Semantic retrieval asks:

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

This distinction is extremely important for AI applications.

Consider a knowledge base containing:

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

		

A user asks:

"I can't remember my login credentials."

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

The basic process is:

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

		

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

3. Using pgvector in Azure Database for PostgreSQL

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

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

A simplified table might look like:

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

		

The number 1536 represents the dimensionality of the embedding.

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

Important

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

VECTOR(768)

The dimensions must be compatible.

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

4. Generating and Storing Embeddings

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

A common architecture is:

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

		

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

"Azure Database for PostgreSQL supports flexible compute options."

into a vector.

The application then stores both the text and its vector:

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

		

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

5. Vector Distance and Similarity

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

Common distance measures include:

Cosine distance
Euclidean/L2 distance
Inner product

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

Cosine similarity

Cosine similarity measures the angle between two vectors.

It is commonly used for semantic text embeddings.

A related pgvector distance operator is:

<=>

For example:

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

		

Here $1 represents the query embedding.

The rows with the smallest distance are the most similar.

Exam tip

Do not confuse distance with similarity.

For distance-based queries:

smaller distance = more similar

For a similarity score where larger is better:

larger similarity = more similar

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

6. Performing a Basic Semantic Search

Suppose the user asks:

"How can I recover my forgotten password?"

The application first generates an embedding:

query_embedding

It then passes that vector to PostgreSQL.

A typical query is:

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

		

Conceptually:

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

		

The LIMIT value determines how many candidates are returned.

7. Exact vs. Approximate Vector Search

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

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

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

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

Common pgvector index types include:

HNSW
IVFFlat
DiskANN in supported Azure Database for PostgreSQL configurations.

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

8. HNSW

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

A simplified conceptual representation is:

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

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

A cosine HNSW index can be created with:

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

HNSW has several important characteristics:

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

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

9. IVFFlat

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

A simplified representation is:

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

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

An example index is:

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

The lists parameter controls the number of clusters.

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

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

Important exam distinction

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

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

HNSW does not require this training step.

10. Choosing Between HNSW and IVFFlat

A useful exam-oriented comparison is:

Characteristic	HNSW	IVFFlat
Search approach	Graph	Cluster/list
Approximate search	Yes	Yes
Build speed	Generally slower	Generally faster
Memory use	Generally higher	Generally lower
Query performance	Generally stronger	Generally lower
Training required	No	Yes
Main tuning concepts	`m`, `ef_construction`, `ef_search`	`lists`, `probes`

There is no universal “best” index.

The correct choice depends on:

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

11. Metadata Filtering

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

They often need to restrict the search based on metadata.

For example:

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

A vector search might therefore look like:

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

		

This combines:

Semantic similarity.
Traditional relational filtering.

This is extremely important for enterprise RAG systems.

12. Why Metadata Filtering Matters

Suppose a company has documents for 10,000 customers.

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

A metadata filter such as:

WHERE tenant_id = $2

can restrict the search to the correct tenant.

Other examples include:

WHERE department = 'Finance'

or:

WHERE document_type = 'Policy'

or:

WHERE language = 'en'

or:

WHERE created_at >= CURRENT_DATE - INTERVAL '1 year'

Metadata filters are therefore useful for:

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

13. Indexing Metadata Columns

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

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

For example:

			
CREATE INDEX documents_tenant_idx
ON documents (tenant_id);

Or:

			
CREATE INDEX documents_category_idx
ON documents (category);

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

			
CREATE INDEX documents_tenant_category_idx
ON documents (tenant_id, category);

The appropriate indexing strategy depends on:

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

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

14. An Important Performance Consideration with ANN and Filters

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

Consider:

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

		

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

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

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

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

For HNSW, for example:

SET hnsw.iterative_scan = strict_order;

or:

SET hnsw.iterative_scan = relaxed_order;

strict_order maintains exact ordering by distance.

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

15. Partial Vector Indexes

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

For example:

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

This creates an index containing only rows matching the condition.

This can be useful when:

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

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

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

16. RAG: Retrieval-Augmented Generation

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

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

A simplified RAG architecture is:

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

17. Why RAG Uses Embeddings

Suppose a user asks:

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

The system generates an embedding for the question.

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

The vector search might return:

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

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

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

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

18. Document Chunking

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

Instead, documents are typically divided into smaller chunks.

For example:

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

		

Each chunk receives its own embedding.

The database might therefore contain:

			
document_id
chunk_id
content
metadata
embedding

		

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

The ideal chunk size depends on:

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

19. Metadata in a RAG System

Metadata is particularly important in production RAG systems.

A chunk might contain:

			
document_id
chunk_id
tenant_id
department
document_type
language
security_level
created_at
source_url
content
embedding

		

The embedding provides semantic information.

The metadata provides structured information.

Together they allow sophisticated retrieval.

For example:

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

		

The query is effectively saying:

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

20. Metadata Filtering and Security

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

For example:

WHERE tenant_id = $1

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

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

Exam mindset

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

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

21. A Complete RAG Retrieval Query

A simplified RAG retrieval query might look like:

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

		

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

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

		

The LLM generates the final response.

22. Why Retrieval Quality Matters in RAG

An LLM can only use the context it receives.

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

Therefore, RAG quality depends on several stages:

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

		

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

23. Hybrid Retrieval

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

Some searches depend on exact terms such as:

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

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

This is often called hybrid retrieval.

Conceptually:

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

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

24. Query Performance Considerations

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

Important performance considerations include:

Use an appropriate ANN index

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

Index metadata columns

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

Tune ANN parameters

For HNSW, parameters such as:

			
m
ef_construction
ef_search

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

For IVFFlat, important parameters include:

			
lists
probes

Monitor recall

Optimizing solely for latency can reduce retrieval quality.

A good RAG system needs an appropriate balance between:

			
Latency
Cost
Recall
Precision

25. Query Plans and Performance Testing

Do not guess about performance.

Use PostgreSQL tools such as:

EXPLAIN

and:

EXPLAIN ANALYZE

to understand query execution.

For example:

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

		

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

For vector workloads, performance testing should use realistic:

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

26. Compute and Memory Considerations

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

Azure Database for PostgreSQL Flexible Server offers compute tiers including:

Burstable
General Purpose
Memory Optimized

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

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

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

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

27. Storage Considerations

Vector embeddings consume storage.

The storage requirement depends on:

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

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

			
1,536 × 4 bytes
= 6,144 bytes

per vector, before considering PostgreSQL row overhead and indexes.

For millions of vectors, this can become substantial.

Therefore:

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

		

28. Choosing the Right Embedding Model

The embedding model is a major component of retrieval quality.

Important considerations include:

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

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

For example:

embedding VECTOR(1536)

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

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

29. Common AI-200 Exam Traps

Trap 1: Confusing embeddings with similarity scores

An embedding is the vector representation.

A similarity or distance calculation compares two embeddings.

Trap 2: Assuming vector search requires an ANN index

It does not.

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

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

Trap 3: Assuming HNSW and IVFFlat are identical

They are not.

Remember:

			
HNSW → graph-based
IVFFlat → clustered/inverted lists

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

Trap 4: Forgetting the distance operator class

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

vector_cosine_ops

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

Trap 5: Ignoring metadata filters

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

The search may also need:

WHERE tenant_id = ...

or:

WHERE department = ...

Trap 6: Assuming ANN filtering always returns K perfect matches

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

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

Trap 7: Using storage scaling to solve every performance problem

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

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

Trap 8: Assuming more retrieved documents are always better

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

RAG systems should balance:

			
Recall
Precision
Latency
Context size
Cost

		

30. AI-200 Quick Review

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

Embeddings

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

pgvector

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

ANN indexes

Know the differences between:

			
Exact search
HNSW
IVFFlat
DiskANN

Metadata filtering

Understand how to combine:

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

and why filtering can affect ANN search results.

RAG

Understand:

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

		

Performance

Know how:

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

can affect vector-search performance.

Practice Exam Questions

Question 1

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

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

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

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

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

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

Correct answer: A

Explanation

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

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

Question 2

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

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

A. Increase the HNSW ef_search value.

B. Add a tenant predicate to the retrieval query.

C. Increase the vector dimensionality.

D. Replace the vector index with an IVFFlat index.

Correct answer: B

Explanation

The retrieval query should include a metadata predicate such as:

WHERE tenant_id = 100

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

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

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

Question 3

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

Which index is the best choice?

A. B-tree

B. IVFFlat

C. HNSW

D. Hash

Correct answer: C

Explanation

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

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

Question 4

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

A. m

B. ef_construction

C. ef_search

D. probes

Correct answer: D

Explanation

For IVFFlat:

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

Increasing probes generally increases search work and can improve recall.

m, ef_construction, and ef_search are associated with HNSW.

Question 5

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

What is the best architectural improvement?

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

B. Increase the number of PostgreSQL connections.

C. Remove all metadata from the documents.

D. Replace the embedding column with a Boolean column.

Correct answer: A

Explanation

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

A typical RAG pipeline is:

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

		

This generally provides more precise context to the generative model.

Question 6

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

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

		

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

What is a likely explanation?

A. PostgreSQL cannot use WHERE clauses with vector searches.

B. HNSW only supports integer vectors.

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

D. Cosine distance always returns exactly three rows.

Correct answer: C

Explanation

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

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

Question 7

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

Which index definition is appropriate?

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

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

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

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

Correct answer: A

Explanation

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

vector_cosine_ops

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

Question 8

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

Which query best represents this requirement?

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

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

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

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

		

Correct answer: D

Explanation

This query combines:

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

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

Question 9

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

Which statement is correct?

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

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

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

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

Correct answer: B

Explanation

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

IVFFlat uses clustering and has tunable lists and probes parameters.

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

Question 10

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

What is the most appropriate first optimization to investigate?

A. Remove the embeddings from the database.

B. Convert all embeddings to strings.

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

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

Correct answer: C

Explanation

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

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

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

Final Exam Takeaways

For this AI-200 topic, remember these relationships:

			
Embedding
    ↓
Numerical representation of semantic meaning

			
Vector similarity
    ↓
Measures how close two embeddings are

			
pgvector
    ↓
PostgreSQL extension providing vector storage and search

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

		

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

		

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

		

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

		

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

Go to the AI-200 Exam Prep Hub main page

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, 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

azure, Cloud computing, Microsoft Certification August 15, 2026August 15, 2026

Monitor and troubleshoot solutions on AKS and Container Apps by inspecting logs, events, and end-to-end connectivity (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 containerized solutions on Azure (20–25%)
   --> Implement container-orchestrated solutions
      --> Monitor and troubleshoot solutions on AKS and Container Apps by inspecting logs, events, and end-to-end connectivity

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 run as distributed containerized solutions. A typical application might include several containers, APIs, background workers, databases, messaging services, and external Azure services. When something goes wrong, determining where the problem exists is often more difficult than identifying that a problem exists.

For the AI-200 exam, developers should understand how to troubleshoot applications running on:

Azure Kubernetes Service (AKS)
Azure Container Apps
The networking and services that connect application components
Azure monitoring and logging capabilities

A particularly important skill is knowing how to work from the application outward:

Application/container → Pod or replica → Service/ingress → Network → Destination

This approach helps isolate whether a problem is caused by the application itself, the container runtime, Kubernetes configuration, service discovery, ingress, networking, or an external dependency.

1. The Troubleshooting Mindset

When an application is failing, avoid immediately changing configuration. First determine which layer is failing.

A useful troubleshooting sequence is:

Is the application running?
Is the container healthy?
Are there useful application logs?
Are there Kubernetes or platform events indicating a problem?
Can the application communicate with its immediate dependency?
Can the service route traffic to the application?
Can traffic enter or leave the application environment?
Is the external dependency itself healthy?

For AKS, Microsoft recommends an inside-out approach for connectivity problems: begin with the pod and application, then work outward through the service and networking layers toward the client or destination.

This approach is particularly useful on the exam because a scenario may provide several symptoms but only one layer is actually responsible for the failure.

2. Logs vs. Events vs. Metrics

One of the most important distinctions to understand is the difference between logs, events, and metrics.

Signal	What it tells you	Typical use
Logs	What the application or platform reported	Application errors, exceptions, startup failures
Events	What happened to an infrastructure/resource object	Scheduling failures, image pulls, restarts
Metrics	Numerical measurements over time	CPU, memory, request rate, latency
Traces	How a request traveled through distributed components	End-to-end request troubleshooting

Logs

Logs are particularly useful when the application itself knows why it failed.

Examples include:

Database connection failures
Authentication errors
Exceptions
Invalid configuration
Failed API calls
Application startup errors

Events

Events are especially useful when Kubernetes or the hosting platform is having difficulty creating, scheduling, starting, or managing a workload.

Examples include:

Failed scheduling
Failed image pulls
Container creation failures
Probe failures
Pod restarts
Resource constraints

Metrics

Metrics help identify patterns rather than individual failures.

Examples include:

CPU utilization
Memory utilization
Request rate
Replica count
Network traffic
Latency
Restart counts

A common exam scenario is:

An application is slow and occasionally unavailable.

Logs may identify the immediate application error, while metrics may reveal that CPU or memory is saturated and events may reveal that pods are being restarted.

You often need all three signals to understand the complete problem.

3. Monitoring and Troubleshooting AKS

AKS provides Kubernetes-native troubleshooting capabilities together with Azure monitoring services.

Important tools include:

kubectl get
kubectl describe
kubectl logs
kubectl exec
kubectl get events
Azure Monitor
Container insights
Azure portal
Application logs
Kubernetes events
Metrics

4. Start by Checking Pod Status

The first question is simple:

Is the workload actually running?

Use:

kubectl get pods

For a specific namespace:

kubectl get pods -n <namespace>

To see pods across all namespaces:

kubectl get pods -A

You might see states such as:

Running
Pending
Succeeded
Failed
CrashLoopBackOff
ImagePullBackOff
ErrImagePull
ContainerCreating
Terminating

These statuses provide an initial indication of where to investigate.

Example

Suppose you see:

			
NAME                         READY   STATUS             RESTARTS
ai-worker-7f4b8c9d8-x2k4m   0/1     CrashLoopBackOff   8

The pod is repeatedly starting and failing.

The next step should generally be to investigate the pod rather than immediately examining the network.

5. Use kubectl describe to Examine Resource Details and Events

Use:

kubectl describe pod <pod-name>

Or:

kubectl describe pod <pod-name> -n <namespace>

kubectl describe provides detailed information about the Kubernetes object, including its configuration, status, conditions, and associated events.

This is particularly useful for identifying problems such as:

Failed scheduling
Image pull failures
Insufficient resources
Failed health probes
Volume mount problems
Container startup problems

For example, an event such as:

Failed to pull image

points toward an image or registry problem rather than an application networking problem.

Likewise:

FailedScheduling

suggests that Kubernetes cannot place the pod on an appropriate node.

6. Kubernetes Events

Kubernetes events record significant activities involving Kubernetes resources.

Examples include:

Pod scheduling
Container creation
Container startup
Image pulling
Failed scheduling
Probe failures
Resource-related problems

You can list events with:

kubectl get events

For a namespace:

kubectl get events -n <namespace>

Events can also be sorted or filtered when investigating a particular problem.

Kubernetes events are extremely useful for troubleshooting, but they are not intended to be a permanent application log store. By default, Kubernetes events have limited retention; current Azure documentation notes that events are available for approximately one hour unless longer-term collection is configured through monitoring capabilities such as Container insights.

Exam Tip

If a question asks:

“Which tool should you use to determine why a pod failed to start?”

Think:

kubectl describe pod and Kubernetes events

If the question asks:

“What did the application itself report?”

Think:

container logs

7. Inspect Container Logs in AKS

Use:

kubectl logs <pod-name>

For a specific namespace:

kubectl logs <pod-name> -n <namespace>

For a particular container in a multi-container pod:

kubectl logs <pod-name> -c <container-name>

This is especially useful when:

The application starts and then crashes
The application throws an exception
A dependency cannot be reached
Configuration is invalid
Authentication fails
The application is returning errors

8. Inspect Logs from a Previous Container Instance

This is an important troubleshooting technique.

If a container has crashed and restarted, its current log may not contain the information from the previous instance.

Use:

kubectl logs <pod-name> --previous

For a particular container:

kubectl logs <pod-name> -c <container-name> --previous

This is particularly valuable when diagnosing:

CrashLoopBackOff
Startup failures
Unexpected application termination
Configuration errors during initialization

Exam Scenario

A pod repeatedly restarts. The current container appears healthy, but you need to determine why the previous instance terminated.

The appropriate command is:

kubectl logs <pod-name> --previous

9. Kubernetes Health Probes

Health probes are another major source of troubleshooting information.

Kubernetes supports:

Liveness probe

Determines whether a container is still functioning.

If the liveness probe repeatedly fails, Kubernetes can restart the container.

Readiness probe

Determines whether the application is ready to receive traffic.

A container can be running but not ready.

Startup probe

Provides additional time for applications that require significant startup time before liveness/readiness checks should begin.

Why Probes Matter

Consider an AI inference service that requires 60 seconds to load a model.

If its liveness probe begins failing after only 10 seconds, Kubernetes may repeatedly restart the container before the model finishes loading.

The result can be:

CrashLoopBackOff

even though the application itself is not fundamentally broken.

Therefore, when investigating repeated restarts, inspect:

kubectl describe pod <pod-name>

and look for probe-related events.

10. Inspect AKS Services

A pod’s IP address is generally not the endpoint that clients should depend on.

Kubernetes Services provide stable networking for workloads.

List services:

kubectl get svc

Describe a service:

kubectl describe svc <service-name>

You should investigate:

Service type
Port
Target port
Selector
Cluster IP
Endpoints
Associated pods

A common failure is a Service selector that does not match the labels on the intended pods.

For example, a Service might select:

			
selector:
  app: ai-api

while the pods actually have:

			
labels:
  app: ai-service

The pods may be healthy, but the Service has no appropriate endpoints.

11. Check Service Endpoints

One of the most important connectivity checks is determining whether a Service actually has endpoints.

For example:

kubectl get endpoints <service-name>

Depending on the Kubernetes version and configuration, EndpointSlices can also be examined:

kubectl get endpointslices

If the Service has no usable endpoints, traffic cannot be routed to the expected application pods.

This creates an important troubleshooting distinction:

Pod is healthy ≠ Service is correctly routing traffic

12. Test Connectivity from Inside the Cluster

When troubleshooting network connectivity, testing from inside the cluster can eliminate several variables.

For example, you can run a temporary diagnostic pod and test connectivity to another service.

Useful tools can include:

nslookup <service-name>

curl http://<service-name>:<port>

or:

nc -z -v <host> <port>

The exact tools available depend on the container image.

This allows you to determine whether:

DNS resolution works
The destination port is reachable
The service responds
The application is actually listening

13. End-to-End AKS Connectivity Troubleshooting

Consider this architecture:

			
Internet
   |
   v
Ingress / Load Balancer
   |
   v
Kubernetes Service
   |
   v
Pod
   |
   v
Application
   |
   v
External Azure Service

		

A useful troubleshooting process is to work through the architecture one layer at a time.

Step 1: Is the pod running?

kubectl get pods

Step 2: Is the application healthy?

kubectl logs <pod-name>

Step 3: Are there Kubernetes events?

kubectl describe pod <pod-name>

Step 4: Does the Service exist?

kubectl get svc

Step 5: Does the Service have endpoints?

kubectl get endpoints <service-name>

Step 6: Can another pod reach the Service?

Use a test container and:

curl http://<service-name>:<port>

Step 7: Does DNS work?

For example:

nslookup <service-name>

Step 8: Does external ingress work?

Test the externally exposed endpoint.

Step 9: Can the application reach external dependencies?

Test the required destination from inside the workload.

This approach prevents you from assuming that every connectivity problem is an ingress problem.

14. Container Insights for AKS

Azure Monitor Container insights provides monitoring capabilities for AKS.

It can provide visibility into:

Container logs
Kubernetes events
Pod metrics
Cluster information
Resource utilization

The Live Data capability can provide direct access to AKS container logs, events, and pod metrics for real-time troubleshooting.

This can be particularly useful when you want Azure-based monitoring rather than relying exclusively on command-line Kubernetes tools.

Important distinction

kubectl logs is a Kubernetes-native method for retrieving container logs.

Container insights provides an Azure monitoring experience that can aggregate and visualize Kubernetes telemetry.

15. Azure Container Apps Monitoring

Azure Container Apps abstracts much of the underlying Kubernetes infrastructure.

Unlike AKS, you generally do not troubleshoot Container Apps by directly managing Kubernetes nodes and pods.

Instead, use Container Apps’ platform-level monitoring capabilities.

Important sources include:

Container console logs
System logs
HTTP logs
Log streams
Azure Monitor
Application Insights
Metrics
Diagnose and solve problems

16. Container App Console Logs

Container console logs originate from the application’s:

stdout
stderr

These are useful for diagnosing application-level problems.

For example:

Database connection failed

or:

Authentication failed

or:

Unhandled exception

These messages can help identify problems inside the application.

Azure Container Apps allows console logs to be viewed through the Azure portal and CLI.

17. Container Apps System Logs

System logs are generated by the Container Apps service rather than directly by the application.

They can help identify platform-level problems such as:

Revision provisioning failures
Container startup issues
Configuration problems
Volume mounting failures
Dapr component issues
Application configuration changes
Other service-level events

This creates an important exam distinction:

Problem	Most useful source
Application exception	Console logs
Revision provisioning failure	System logs
Container lifecycle issue	System/platform logs
HTTP request behavior	HTTP logs
Resource utilization	Metrics

18. Viewing Container Apps Log Streams

In the Azure portal, navigate to the Container App and select:

Monitoring → Log stream

You can select between:

Console
System

The console stream displays application/container output, while the system stream provides platform-level information.

You can also use the Azure CLI.

For example:

			
az containerapp logs show \
  --name <CONTAINER_APP_NAME> \
  --resource-group <RESOURCE_GROUP> \
  --type console

For system logs:

			
az containerapp logs show \
  --name <CONTAINER_APP_NAME> \
  --resource-group <RESOURCE_GROUP> \
  --type system

You can use --tail to limit the number of messages and --follow to continuously stream logs.

19. Container Apps Revisions and Replicas

Container Apps uses revisions and replicas.

This matters when troubleshooting because the application may have:

Multiple revisions
Multiple replicas
Multiple containers

A log problem might exist only in one revision or replica.

Therefore, when investigating Container Apps logs, determine:

Which revision is receiving traffic?
Which replica is experiencing the problem?
Which container is producing the error?
Is the problem isolated or occurring across all replicas?

This is particularly important during deployments.

For example:

			
Revision A → healthy
Revision B → failing

If traffic has been shifted to Revision B, users may experience failures even though Revision A remains healthy.

20. Container Apps and Scaling to Zero

Container Apps can scale an application down to zero replicas depending on its scaling configuration.

This creates a potential troubleshooting trap.

If an application is scaled to zero, there may be no active replica from which to stream console logs.

If the log stream indicates that the revision is scaled to zero, you may need to temporarily configure a minimum replica count greater than zero to investigate the running application.

Exam Tip

If a Container App has no active replicas:

Don’t assume the application has crashed.

It may simply have scaled to zero.

21. Container Apps HTTP Logs

Container Apps can also provide HTTP-related telemetry through its ingress layer when diagnostic settings are configured.

These logs can help investigate:

HTTP status codes
Request behavior
Client requests
Ingress problems
Application availability

This is useful when the container itself appears healthy but clients are receiving errors.

For example:

Client → Container Apps ingress → Container

If the container logs show no corresponding request, investigate the ingress/routing layer.

22. Diagnose and Solve Problems in Container Apps

Azure Container Apps provides a Diagnose and solve problems experience for investigating application health, configuration, and performance.

This can be useful when problems are not immediately obvious from application logs.

For example, Container Apps diagnostics can help investigate container exit events and provide information about possible causes and resolutions.

23. AKS vs. Container Apps Troubleshooting

Understanding the difference between AKS and Container Apps is important for AI-200.

Area	AKS	Azure Container Apps
Kubernetes API access	Yes	Abstracted from developer
`kubectl` troubleshooting	Yes	Generally not the primary approach
Pod troubleshooting	Yes	Platform abstracts replicas
Kubernetes events	Directly available	Platform-level diagnostics/logs
Container logs	`kubectl logs`	Log stream / CLI
System logs	Kubernetes/Azure monitoring	Container Apps system logs
Service configuration	Kubernetes Services	Container Apps ingress
Scaling	Kubernetes autoscaling mechanisms	Container Apps scaling rules
Node troubleshooting	Possible	Managed/abstracted
Azure Monitor	Yes	Yes
Container Insights	Available	Not the primary troubleshooting interface

Key Exam Principle

If a question emphasizes:

Pods, nodes, Services, Deployments, Kubernetes events, kubectl

think:

AKS

If it emphasizes:

Revisions, replicas, Container Apps log streams, system logs, console logs, ingress

think:

Azure Container Apps

24. Troubleshooting Common AKS Problems

Problem: Pod is Pending

Check:

kubectl describe pod <pod-name>

Look for events such as:

FailedScheduling

Potential causes include:

Insufficient CPU
Insufficient memory
Node constraints
Affinity rules
Taints and tolerations
Resource quotas

Problem: ImagePullBackOff

Check:

kubectl describe pod <pod-name>

Potential causes include:

Incorrect image name
Incorrect image tag
Private registry authentication
Network connectivity to the registry
Image does not exist

Problem: CrashLoopBackOff

Check:

kubectl logs <pod-name>

Then:

kubectl logs <pod-name> --previous

And:

kubectl describe pod <pod-name>

Potential causes include:

Application crash
Invalid configuration
Missing secret
Failed dependency connection
Failed liveness probe
Incorrect startup behavior

Problem: Pod is Running but Requests Fail

Investigate:

Application logs
Pod readiness
Service configuration
Service endpoints
DNS
Network policies
Ingress/load balancer
External networking

A Running status does not guarantee that an application is reachable.

25. Troubleshooting Common Container Apps Problems

Problem: Container exits

Check:

Console logs
System logs
Container exit events
Revision status
Application startup configuration

A zero exit code can indicate normal termination, while a nonzero exit code generally indicates failure. Container Apps provides diagnostic information about container exit events.

Problem: Application is unavailable

Check:

Active revision
Replica count
Ingress configuration
Console logs
System logs
HTTP logs
Health probes
Application dependencies

Problem: New deployment fails

Check:

Revision provisioning
Container image
Environment variables
Secrets
Managed identity
Registry access
Container startup
Application logs

A new revision can fail while a previous revision continues to operate.

26. Troubleshooting End-to-End Connectivity

End-to-end connectivity problems require a broader perspective.

Consider an AI application with this architecture:

			
User
  |
  v
Azure Front Door / Application Gateway
  |
  v
Container App or AKS Ingress
  |
  v
Application
  |
  +------> Azure OpenAI
  |
  +------> Azure Cosmos DB
  |
  +------> Azure Service Bus
  |
  +------> Azure Storage

		

A failure could occur anywhere along this path.

The correct troubleshooting approach is to identify the first point at which communication fails.

27. Test from the Same Network Context

A common troubleshooting mistake is testing connectivity from your laptop when the actual application runs inside Azure.

For example:

Laptop → Azure service

may work while:

Container → Azure service

fails.

The application should therefore be tested from the same network context in which it runs.

For AKS, this may mean executing commands from a diagnostic pod.

For Container Apps, troubleshooting may involve application logs, platform diagnostics, ingress configuration, and network configuration.

28. DNS Troubleshooting

DNS problems can make a healthy application appear unavailable.

Suppose an application attempts:

https://my-database.example.com

but cannot resolve the hostname.

The application may produce errors such as:

Name or service not known

or:

DNS resolution failed

In AKS, test DNS from inside the cluster:

nslookup <hostname>

or:

nslookup <service-name>

If DNS resolution fails, investigate DNS configuration before investigating the application itself.

29. Port and Protocol Troubleshooting

A common problem is confusing:

Container port
Service port
Target port
External port

For example:

			
Client
  |
  | TCP 443
  v
Ingress
  |
  | TCP 8080
  v
Service
  |
  | TCP 8080
  v
Pod

		

The application must actually be listening on the expected port.

A connectivity test such as:

nc -z -v <host> <port>

can help determine whether a TCP port is reachable.

30. Application Connectivity vs. Infrastructure Connectivity

Another important distinction is:

Can the network connection be established?

versus:

Does the application successfully process the request?

For example:

			
TCP connection succeeds
        |
        v
HTTP 500

The network is functioning, but the application has an error.

Conversely:

Connection timeout

may indicate a networking, routing, firewall, DNS, or service availability problem.

The HTTP response code and application logs should therefore be considered together.

31. A Practical AKS Troubleshooting Playbook

When an AKS application is unavailable, use this sequence.

Step 1 — Check pods

kubectl get pods -A

Step 2 — Inspect unhealthy pods

kubectl describe pod <pod-name>

Step 3 — Read logs

kubectl logs <pod-name>

Step 4 — Check previous container logs

kubectl logs <pod-name> --previous

Step 5 — Check events

kubectl get events

Step 6 — Check Services

kubectl get svc

Step 7 — Check endpoints

kubectl get endpoints <service-name>

Step 8 — Test DNS

nslookup <service-name>

Step 9 — Test connectivity

curl http://<service-name>:<port>

Step 10 — Investigate ingress and external networking

Only after the internal application path is confirmed should you move farther outward.

32. A Practical Container Apps Troubleshooting Playbook

For Azure Container Apps:

Step 1 — Check revision status

Determine whether the expected revision is active and healthy.

Step 2 — Check replica state

Determine whether the application has active replicas or has scaled to zero.

Step 3 — Inspect console logs

Look for application-level errors.

Step 4 — Inspect system logs

Look for platform and revision-level problems.

Step 5 — Inspect HTTP/ingress telemetry

Determine whether requests are reaching the application.

Step 6 — Check configuration

Review:

Environment variables
Secrets
Managed identity
Registry configuration
Ingress
Health probes

Step 7 — Check external dependencies

Determine whether the application can communicate with required Azure services.

Step 8 — Use Azure diagnostics

Use the Container Apps diagnostic capabilities when the source of the problem remains unclear.

33. Common Troubleshooting Mistakes

Mistake 1: Assuming Running Means Healthy

A pod can be Running while the application inside it is broken.

Use readiness status, logs, and probes.

Mistake 2: Looking Only at Application Logs

Infrastructure events may reveal the actual problem.

For example:

ImagePullBackOff

is unlikely to be explained by an application log because the application may never have started.

Mistake 3: Looking Only at Events

Events can tell you that something happened, but application logs may explain why the application itself failed.

Use both.

Mistake 4: Troubleshooting Ingress First

If the pod isn’t running, spending time troubleshooting ingress is premature.

Work from the application outward.

Mistake 5: Ignoring Previous Container Logs

A restarted container may have lost the most useful evidence.

Use:

kubectl logs --previous

Mistake 6: Assuming a Container App with No Logs Is Broken

The application might be scaled to zero.

Check its replica/scaling state.

Mistake 7: Testing from the Wrong Location

A connection that succeeds from your development machine does not prove that it will succeed from the Azure-hosted application.

Test from the application’s network context whenever possible.

34. Exam-Focused Command Reference

Task	Command
List pods	`kubectl get pods`
List all pods	`kubectl get pods -A`
Describe pod	`kubectl describe pod <pod>`
View container logs	`kubectl logs <pod>`
View previous container logs	`kubectl logs <pod> --previous`
View a specific container	`kubectl logs <pod> -c <container>`
List events	`kubectl get events`
List services	`kubectl get svc`
Describe service	`kubectl describe svc <service>`
View endpoints	`kubectl get endpoints <service>`
Test HTTP connectivity	`curl <url>`
Test DNS	`nslookup <hostname>`
Test TCP connectivity	`nc -z -v <host> <port>`
Container Apps console logs	`az containerapp logs show --type console`
Container Apps system logs	`az containerapp logs show --type system`
Follow Container Apps logs	`az containerapp logs show --follow`

35. Key Concepts to Remember for AI-200

The following distinctions are particularly important for exam preparation.

AKS

kubectl get

Use it to see the current state of Kubernetes resources.

kubectl describe

Use it to investigate resource configuration, status, conditions, and events.

kubectl logs

Use it to inspect application/container output.

kubectl logs --previous

Use it to inspect logs from a previous container instance.

kubectl get events

Use it to investigate Kubernetes lifecycle and scheduling events.

Services and endpoints

Use them to determine whether traffic can be routed from a Kubernetes Service to the intended pods.

Container insights

Use Azure Monitor capabilities for broader monitoring, logs, events, and metrics.

Azure Container Apps

Console logs

Application/container output.

System logs

Container Apps platform/service events.

HTTP logs

Ingress-level HTTP activity when configured.

Log stream

Near-real-time access to console and system logs.

Revisions

Different deployed versions of an application.

Replicas

Running instances of a revision.

Diagnose and solve problems

Azure’s diagnostic capabilities for investigating application health and platform problems.

36. Final Exam Strategy

When presented with a troubleshooting scenario, identify the symptom first.

If the question mentions:

CrashLoopBackOff

Think:

kubectl logs
kubectl logs --previous
kubectl describe pod
Health probes

ImagePullBackOff

Think:

Image name/tag
Container registry
Authentication
kubectl describe pod

FailedScheduling

Think:

Node resources
Scheduling constraints
Taints/tolerations
kubectl describe pod

Pod is Running but service is unreachable

Think:

Service
Selector
Endpoints
DNS
Ports
Network policies
Ingress

Container Apps application error

Think:

Console logs

Container Apps platform/revision problem

Think:

System logs
Revision status

Container App has no active replica

Think:

Scaling to zero

Requests reach the application but return HTTP errors

Think:

Application logs
HTTP logs
Dependency failures

Application cannot reach an Azure service

Think:

DNS
Network routing
Firewall/network restrictions
Identity/authentication
Service availability
Test from the application’s network context

The most important principle is:

Don’t troubleshoot the entire system at once. Start at the failing workload and move outward until you find the first broken connection or component.

Practice Exam Questions

Question 1

An application running on AKS repeatedly enters the CrashLoopBackOff state. The development team wants to determine what happened immediately before the most recent container restart.

Which command should you use?

A. kubectl get svc <pod-name>

B. kubectl logs <pod-name> --previous

C. kubectl get events --all-namespaces

D. kubectl top nodes

Answer: B

Explanation:
kubectl logs --previous retrieves logs from the previous instance of a container. This is particularly useful when a container has crashed and restarted. kubectl get events can provide additional context, but it does not provide the application’s actual log output from the previous container instance.

Question 2

An AKS pod remains in the Pending state. You need to determine why Kubernetes has not scheduled the pod onto a node.

Which action should you take first?

A. Run kubectl logs on the pod.

B. Restart the deployment.

C. Run kubectl describe pod and inspect the Events section.

D. Check the application’s HTTP logs.

Answer: C

Explanation:
kubectl describe pod provides detailed information about the pod and its associated events. Scheduling failures such as insufficient resources, taints, affinity constraints, or other scheduling problems are commonly reported there. A pod that has not started generally will not have useful application logs.

Question 3

An AKS application is running successfully in its pod. However, requests sent through a Kubernetes Service do not reach the application.

Which investigation is most appropriate next?

A. Check whether the Service has endpoints corresponding to the application pods.

B. Restart the AKS cluster.

C. Examine only the application’s CPU utilization.

D. Delete and recreate the container image.

Answer: A

Explanation:
A healthy pod does not guarantee that a Service is routing traffic to it. Checking the Service and its endpoints helps determine whether the Service selector matches the intended pods and whether usable endpoints have been registered.

Question 4

An Azure Container Apps application is returning errors. The developer wants to see messages written by the application’s container to stdout and stderr.

Which log source should be inspected?

A. Container Apps system logs

B. Azure Activity Log

C. Kubernetes events

D. Container Apps console logs

Answer: D

Explanation:
Container Apps console logs contain output from the application’s containers, including stdout and stderr. System logs instead contain information generated by the Container Apps service.

Question 5

An Azure Container Apps application was working yesterday but now appears to have no running instances. No application errors are visible in the console log stream.

What should you investigate first?

A. Whether the container image has been deleted.

B. Whether the application has scaled to zero replicas.

C. Whether Kubernetes nodes are running.

D. Whether the AKS API server is reachable.

Answer: B

Explanation:
Container Apps can scale applications to zero replicas depending on the configured scaling rules. When no replicas are running, there may be no active container instance producing console logs. AKS node and API-server troubleshooting is not appropriate because Container Apps abstracts the underlying Kubernetes infrastructure.

Question 6

An AKS application is accessible from one pod but cannot resolve the DNS name of another Kubernetes Service.

Which troubleshooting technique is most appropriate?

A. Increase the pod’s CPU limit.

B. Restart every node in the cluster.

C. Run a DNS lookup such as nslookup from the application’s network context.

D. Rebuild the container image.

Answer: C

Explanation:
If the problem appears to be DNS resolution, testing DNS from inside the cluster helps determine whether the workload can resolve the target name. Testing from the same network context as the application is important because DNS behavior can differ between environments.

Question 7

A new revision of an Azure Container Apps application fails during deployment, while the previous revision continues to operate correctly.

Which information is most useful for determining whether the new revision encountered a platform-level provisioning problem?

A. The system logs for the Container App

B. The developer’s local application logs

C. The user’s browser cache

D. The CPU utilization of an unrelated Azure VM

Answer: A

Explanation:
Container Apps system logs contain platform-level information, including revision provisioning and service-level events. They are therefore appropriate when investigating deployment or revision provisioning failures.

Question 8

An AKS application is running, but clients receive connection timeouts. The development team wants to troubleshoot the problem using an inside-out approach.

Which sequence is most appropriate?

A. Check the external client, then immediately restart the cluster.

B. Check the Azure subscription, then rebuild the application.

C. Check the ingress first and ignore the pods.

D. Check the pod/application, then Service and endpoints, then networking and external access.

Answer: D

Explanation:
An inside-out approach begins with the workload itself and progressively moves outward. First verify that the pod and application are healthy, then verify Service routing and endpoints, and finally investigate ingress and external networking. This approach helps identify the first layer where connectivity fails.

Question 9

An AKS application container is repeatedly restarted. The application logs show no obvious error, but kubectl describe pod reports repeated liveness probe failures.

What is the most likely area to investigate?

A. The Azure subscription’s billing configuration.

B. The container’s liveness probe configuration and application startup/health behavior.

C. The user’s browser DNS cache.

D. The container registry’s image retention policy.

Answer: B

Explanation:
Repeated liveness probe failures can cause Kubernetes to restart a container. The probe’s path, port, timing, timeout, and failure thresholds should be evaluated against the application’s actual startup and health behavior.

Question 10

An AI application running in AKS can connect to an external Azure service from a developer workstation but receives connection timeouts when running inside the cluster.

Which approach provides the most useful next diagnostic step?

A. Assume the external service is unavailable.

B. Increase the application’s memory allocation.

C. Test DNS and network connectivity to the destination from inside the AKS network context.

D. Delete the application deployment and recreate it.

Answer: C

Explanation:
Successful connectivity from a developer workstation does not prove that connectivity from AKS is working. Testing DNS resolution and network connectivity from inside the cluster helps isolate problems involving routing, firewall rules, network policies, private endpoints, DNS, or other network-specific configuration.

Summary

For AI-200, monitoring and troubleshooting containerized applications is fundamentally about understanding where the failure occurs.

For AKS, become comfortable with:

kubectl get
kubectl describe
kubectl logs
kubectl logs --previous
kubectl get events
Services
Endpoints
DNS testing
Connectivity testing
Health probes
Azure Monitor and Container insights

For Azure Container Apps, understand:

Console logs
System logs
HTTP logs
Log streams
Revisions
Replicas
Scaling to zero
Ingress
Container exit events
Azure diagnostics

Most importantly, develop an inside-out troubleshooting methodology:

Container → application → pod/replica → Service/ingress → network → external dependency

When you can identify the first layer where communication or execution breaks, you can usually identify the correct troubleshooting tool and the most appropriate remediation.

Go to the AI-200 Exam Prep Hub main page