Category: NoSQL

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

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

This post is a part of the AI-200: Developing AI Cloud Solutions on Azure  Exam Prep Hub.
This topic falls under these sections:
Develop AI solutions by using Azure data management services (25–30%)
   --> Develop AI solutions by using Azure Database for PostgreSQL
      --> Model schemas and implement indexing strategies, including designing tables and choosing appropriate data types

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

Overview

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

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

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

Two fundamental decisions are involved:

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

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

1. Understanding Relational Schema Design

A relational schema defines how information is organized into:

Tables
Columns
Data types
Primary keys
Foreign keys
Constraints
Indexes
Relationships

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

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

		

And:

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

		

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

2. Choose Data Types Carefully

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

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

The general principle is:

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

Avoid automatically storing everything as TEXT.

2.1 Integer Types

PostgreSQL provides several integer types.

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

For example:

customer_id BIGINT

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

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

Exam consideration

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

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

3. Exact Versus Approximate Numeric Values

PostgreSQL provides exact numeric types such as:

			
numeric
decimal

and approximate floating-point types such as:

			
real
double precision

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

For example:

price NUMERIC(10,2)

is preferable to:

price DOUBLE PRECISION

when representing currency.

Exam tip

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

NUMERIC / DECIMAL

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

4. Character Data Types

Common character types include:

			
text
varchar(n)
char(n)

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

For example:

description TEXT

could be appropriate for a support-ticket description.

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

Important distinction

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

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

5. Date and Time Types

PostgreSQL supports several date/time types, including:

date
time
timestamp
timestamp with time zone
interval

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

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

For example:

created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP

is often preferable to:

created_at TIMESTAMP

when the application operates across multiple time zones.

Exam tip

If the requirement is:

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

Think:

TIMESTAMPTZ

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

DATE

6. Boolean Values

Use:

BOOLEAN

for true/false information.

Example:

is_active BOOLEAN NOT NULL DEFAULT TRUE

Don’t store values such as:

			
"Y"
"N"

or:

			
"true"
"false"

as text unless there is a specific interoperability requirement.

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

7. UUIDs

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

For example:

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

UUIDs can be particularly useful when:

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

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

8. JSON and JSONB

PostgreSQL supports both:

			
json
jsonb

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

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

For example:

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

A document might contain:

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

		

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

Important design consideration

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

If an attribute is:

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

a normal relational column may be more appropriate.

9. Primary Keys

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

Example:

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

A primary key provides:

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

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

Exam tip

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

For example, creating:

			
CREATE INDEX idx_products_product_id
ON products(product_id);

after declaring:

product_id BIGINT PRIMARY KEY

would normally be redundant.

10. Foreign Keys and Relationships

Foreign keys maintain relationships between tables.

For example:

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

		

This establishes:

			
Customer
   |
   +----< Orders

A foreign-key constraint protects referential integrity.

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

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

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

11. What Is an Index?

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

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

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

		

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

For example:

			
CREATE INDEX idx_customers_email
ON customers(email);

Now a query such as:

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

has an index available for locating the matching row.

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

12. The Cost of Indexes

Indexes aren’t free.

An index consumes:

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

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

Therefore:

More indexes do not automatically mean better performance.

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

A good indexing strategy balances:

Read performance

against

Write and storage overhead.

13. B-tree Indexes

The default PostgreSQL index type is the B-tree.

For example:

			
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

B-tree indexes are particularly useful for:

Equality comparisons
Range comparisons
Sorting
ORDER BY
Many common join operations

For example:

WHERE customer_id = 100

or:

WHERE order_date >= '2026-01-01'

or:

ORDER BY order_date

are common candidates for B-tree indexes.

14. Indexing Columns Used in WHERE Clauses

Consider:

			
SELECT *
FROM orders
WHERE customer_id = 12345;

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

			
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

The key question isn’t:

“Can I index this column?”

Almost any column can be indexed.

The better question is:

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

15. Selectivity Matters

Index usefulness depends partly on selectivity.

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

Suppose a table contains 10 million orders.

A query:

WHERE customer_id = 98765

might return only 20 rows.

That is highly selective.

An index is potentially very useful.

Now consider:

WHERE status = 'Active'

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

The predicate is not very selective.

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

Exam principle

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

Consider:

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

16. Composite Indexes

A composite, or multicolumn, index contains multiple columns.

For example:

			
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

This can be useful for queries such as:

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

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

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

Therefore:

(customer_id, order_date)

and:

(order_date, customer_id)

are not interchangeable from an optimization perspective.

17. Choosing Column Order in Composite Indexes

Suppose the application frequently runs:

			
WHERE customer_id = ?
AND order_date >= ?

An index such as:

			
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

is a natural candidate.

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

A useful general pattern is:

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

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

18. Indexes for ORDER BY

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

For example:

			
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

can potentially support queries involving:

			
WHERE customer_id = 100
ORDER BY order_date;

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

19. Unique Indexes

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

For example:

			
CREATE UNIQUE INDEX idx_customers_email
ON customers(email);

This can enforce uniqueness for email addresses.

Alternatively, define the business rule directly through a constraint:

email TEXT UNIQUE

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

20. Partial Indexes

A partial index indexes only rows satisfying a condition.

For example:

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

This can be particularly useful when:

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

A query such as:

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

may benefit from the partial index.

Why partial indexes can help

Instead of indexing millions of rows:

10 million total rows

the index may contain only:

500,000 open tickets

That can reduce index size and maintenance overhead.

21. Expression Indexes

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

For example:

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

This can support queries such as:

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

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

Exam concept

If a query consistently searches on:

LOWER(column)

consider whether an expression index on:

LOWER(column)

is appropriate.

22. Covering Indexes and INCLUDE

PostgreSQL supports indexes that include additional non-key columns.

For example:

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

The key column is:

customer_id

while:

			
order_date
total_amount

are included payload columns.

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

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

23. GIN, GiST, and BRIN

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

Important types include:

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

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

24. BRIN Indexes

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

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

For example:

			
CREATE INDEX idx_events_created_brin
ON events USING BRIN(created_at);

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

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

Exam clue

If you see:

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

consider:

BRIN

25. GIN Indexes and JSONB

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

For example:

			
CREATE INDEX idx_documents_metadata
ON documents USING GIN(metadata);

This can support queries that search within JSONB content.

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

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

		

and queries need to filter based on those attributes.

26. Schema Design for AI Applications

AI applications frequently combine traditional relational data with:

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

A relational schema might look like:

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

		

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

For example, conceptually:

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

		

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

27. Don’t Confuse Relational Indexes with Vector Indexes

This is particularly important for AI-200.

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

WHERE customer_id = 123

or:

ORDER BY created_at

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

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

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

The important conceptual distinction is:

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

versus:

			
Vector similarity search
        ↓
Vector-aware indexing

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

28. Don’t Over-Index

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

Imagine:

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

		

It might be tempting to create five indexes:

			
customer_id
merchant_id
amount
status
transaction_date

		

But that may not be optimal.

Suppose the application primarily runs:

			
WHERE customer_id = ?
AND transaction_date >= ?

A composite index might be much more valuable:

			
CREATE INDEX idx_transactions_customer_date
ON transactions(customer_id, transaction_date);

The actual workload should drive the decision.

29. Indexes and Write Performance

Suppose a table has:

			
table
indexes

Every insert potentially requires maintenance of those indexes.

Therefore:

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

		

The goal is not maximum indexing.

The goal is:

The right indexes for the application’s important queries.

30. Use Query Plans to Validate Indexing Decisions

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

Use PostgreSQL query-plan tools such as:

EXPLAIN

and:

EXPLAIN ANALYZE

For example:

			
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 100;

The query plan can help determine whether PostgreSQL is performing:

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

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

31. Statistics Matter

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

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

For example, the optimizer might estimate:

Expected rows: 100

when the query actually returns:

2,000,000 rows

That can lead to an inappropriate plan.

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

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

32. Query Store and Indexing

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

Query Store can help identify:

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

Query Store stores its information in the azure_sys database.

This makes Query Store particularly useful when deciding:

“Which queries actually need optimization?”

rather than guessing based on the schema alone.

33. Autonomous Tuning

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

It can analyze workload information and provide recommendations such as:

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

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

34. A Practical Indexing Process

A good indexing workflow looks like this:

Step 1: Understand the workload

Identify:

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

Step 2: Examine query plans

Use:

EXPLAIN

and:

EXPLAIN ANALYZE

Step 3: Identify bottlenecks

Determine whether the problem involves:

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

Step 4: Create the appropriate index

Choose among:

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

Step 5: Test the change

Compare:

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

		

Step 6: Monitor production behavior

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

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

35. Common AI-200 Exam Traps

Trap 1: “Index every column”

Incorrect.

Indexes consume storage and introduce write-maintenance overhead.

Trap 2: “Use B-tree for everything”

Incorrect.

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

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

Incorrect.

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

Trap 4: “A primary key needs another index”

Usually incorrect.

The primary-key constraint already creates a unique index.

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

Incorrect.

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

Trap 6: “More indexes always improve performance”

Incorrect.

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

Trap 7: “Use floating point for currency”

Generally incorrect.

Use an exact numeric type such as:

NUMERIC

when exact decimal arithmetic is required.

Trap 8: “Store all structured data as JSON”

Incorrect.

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

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

Incorrect.

Vector similarity searches require vector-aware approaches.

36. Quick Reference: Data Type Selection

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

37. Quick Reference: Index Selection

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

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

38. Key Takeaways for the AI-200 Exam

For this topic, remember these principles:

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

10 Practice Exam Questions

Question 1

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

Which data type should you use for the transaction amount?

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

Answer: B

Explanation

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

Question 2

An application frequently executes this query:

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

The table contains millions of rows.

Which index is the most appropriate starting point?

			
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

			
CREATE INDEX idx_orders_date_customer
ON orders(order_date, customer_id);

			
CREATE INDEX idx_orders_customer
ON orders(customer_id);

			
CREATE INDEX idx_orders_date
ON orders(order_date);

Answer: A

Explanation

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

The important concept is that the order of columns in a composite index matters.

Question 3

A PostgreSQL table contains 50 million event records. Records are inserted approximately in chronological order. Queries frequently retrieve events based on a range of timestamps.

Which index type could be particularly appropriate if the timestamp values have a strong correlation with physical row order?

A. GIN
B. Hash
C. BRIN
D. Expression B-tree

Answer: C

Explanation

BRIN indexes are designed for very large tables where indexed values have a useful correlation with the physical order of rows. Time-series data that is inserted chronologically is a classic example.

Question 4

A developer creates this table:

			
CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    name TEXT NOT NULL
);

The developer then proposes creating another standard index on customer_id.

What is the best response?

A. Create the index because primary keys cannot be indexed.
B. Create the index because primary keys only enforce uniqueness.
C. Create the index because primary-key lookups always require two indexes.
D. The additional index is normally unnecessary because the primary key already has a unique index.

Answer: D

Explanation

A PostgreSQL primary-key constraint is backed by a unique index. Creating another identical index on the same column would normally be redundant and would consume additional storage and maintenance resources.

Question 5

An application stores document metadata in a PostgreSQL jsonb column:

metadata JSONB

The application frequently searches within the JSON documents for matching attributes.

Which index type is commonly appropriate for this workload?

A. GIN
B. BRIN
C. Hash
D. B-tree on the table’s primary key

Answer: A

Explanation

GIN indexes are well suited to indexing composite or multivalued data and are commonly used with jsonb data. They can make searches involving JSONB contents much more efficient.

Question 6

An application frequently executes:

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

There is a normal B-tree index on:

email

but the query still isn’t benefiting from the index as expected.

Which approach could directly support this search pattern?

A. Create a BRIN index on email.
B. Create a GIN index on the primary key.
C. Create an expression index on LOWER(email).
D. Convert email to BIGINT.

Answer: C

Explanation

The query applies LOWER() to the column. An expression index can index the result of that expression:

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

This allows PostgreSQL to efficiently support queries using the same expression.

Question 7

A developer is designing a global AI application and wants identifiers that can be generated independently by multiple distributed application instances without coordinating a central sequence.

Which data type is the best fit?

A. SMALLINT
B. UUID
C. REAL
D. DATE

Answer: B

Explanation

PostgreSQL’s native UUID type provides 128-bit universally unique identifiers. UUIDs are particularly useful when identifiers need to be generated independently across distributed systems.

Question 8

A developer wants to improve application performance and proposes creating indexes on every column in a frequently updated table.

Which statement best describes the problem with this approach?

A. PostgreSQL supports only one index per table.
B. Indexes cannot be created on columns used in updates.
C. Indexes can improve reads but increase storage and write-maintenance overhead.
D. PostgreSQL automatically deletes indexes that are not used.

Answer: C

Explanation

Indexes can significantly improve read performance, but they aren’t free. Inserts, updates, and deletes may require corresponding index maintenance. Excessive indexing can therefore increase write overhead and storage consumption.

Question 9

A support system has 20 million tickets, but only 200,000 are currently open. Most application queries retrieve open tickets by customer.

Which indexing strategy could reduce index size while targeting the important workload?

A. Create a partial index containing only open tickets.
B. Create an index on every column in the table.
C. Create a BRIN index on the ticket description.
D. Store the ticket status as JSON.

Answer: A

Explanation

A partial index can index only rows satisfying a predicate:

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

Because the application primarily queries open tickets, this can provide a smaller, workload-focused index.

Question 10

An AI application stores text embeddings in Azure Database for PostgreSQL and needs to perform nearest-neighbor similarity searches.

Which statement is correct?

A. A standard B-tree index is always sufficient for high-dimensional vector similarity searches.
B. A primary-key index automatically provides vector similarity search.
C. A BRIN index should always be used for embeddings.
D. A vector-aware indexing mechanism should be used for vector similarity workloads.

Answer: D

Explanation

Traditional relational indexes such as B-tree are designed for conventional relational operations such as equality, range filtering, and ordering. Vector similarity search requires vector-aware data types, operators, and indexing mechanisms supported by the chosen PostgreSQL vector solution.

Final Exam Perspective

The most important mindset for this AI-200 topic is to think of database design as a workload-driven optimization problem.

When presented with a scenario, ask:

What data am I storing?

Then:

What is the correct data type?

Then:

How will the application access the data?

Then:

What index best supports those access patterns?

And finally:

Does the index actually improve the workload enough to justify its cost?

That sequence is much more valuable for the exam than simply memorizing lists of PostgreSQL data types and index types.

For Azure Database for PostgreSQL specifically, Query Store and related performance tooling can help move that decision from guesswork to evidence by identifying expensive queries and allowing performance to be compared before and after changes.

Go to the AI-200 Exam Prep Hub main page

AI-200, azure, 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

AI-200, azure, Cloud computing, Databases, NoSQL August 8, 2026August 15, 2026

Implement a change feed processor to detect and handle new or updated items (AI-200 Exam Prep)

This post is a part of the AI-200: Developing AI Cloud Solutions on Azure  Exam Prep Hub.
This topic falls under these sections:
Develop AI solutions by using Azure data management services (25–30%)
   --> Develop AI solutions by using Azure Cosmos DB for NoSQL
      --> Implement a change feed processor to detect and handle new or updated items

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

Overview

Azure Cosmos DB for NoSQL provides a change feed that records changes made to items in a container. Applications can consume this feed to react to data changes without repeatedly querying the entire container.

For the AI-200 exam, an important implementation pattern is the change feed processor. It provides a push-based mechanism for detecting changes and delivering them to application code for processing.

A change feed processor is particularly useful when an application needs to perform an action whenever items are created or updated, such as:

Processing newly submitted documents
Generating embeddings for newly created content
Updating a search index
Synchronizing data with another system
Running AI processing when new data arrives
Performing analytics or enrichment
Triggering downstream business workflows
Maintaining materialized or derived data

The change feed processor also handles important operational concerns such as checkpointing, load balancing, lease management, and recovery.

1. What Is the Azure Cosmos DB Change Feed?

The change feed is a persistent record of changes to items in an Azure Cosmos DB container.

Conceptually, it looks like this:

			
Application
    |
    | Creates/updates items
    v
Azure Cosmos DB Container
    |
    | Change feed
    v
Change Feed Processor
    |
    +--> Process new item
    +--> Generate embedding
    +--> Update search index
    +--> Call downstream service
    +--> Store derived data

		

Instead of repeatedly asking:

“Which items have changed since the last time I checked?”

the application can consume the change feed and process changes incrementally.

This makes the change feed especially useful for event-driven and near-real-time architectures.

2. Latest Version Change Feed Mode

For the AI-200 scenario involving detection of new or updated items, the default latest version change feed mode is particularly important.

In latest version mode:

Creates appear in the change feed.
Updates appear in the change feed.
Deletes do not appear.
If an item is changed multiple times before it is read, the feed provides the latest version rather than every intermediate version.

For example:

			
Item created
     |
     v
Status = "Pending"
     |
     v
Status = "Processing"
     |
     v
Status = "Completed"

		

If these changes occur before the consumer reads the feed, latest-version mode may expose the current version rather than every intermediate state.

Therefore, latest-version mode is appropriate when the application cares about the current state of changed items, rather than every individual mutation.

Important exam distinction

If an application must detect deletes or process every intermediate version, latest-version mode isn’t sufficient.

Azure Cosmos DB also supports all versions and deletes mode, which captures creates, updates, and deletes. That mode has additional requirements, including continuous backup, and is available for Azure Cosmos DB for NoSQL.

3. What Is a Change Feed Processor?

The change feed processor is a higher-level mechanism for consuming the Azure Cosmos DB change feed.

It uses a push model.

Rather than requiring your application to repeatedly pull batches and manage continuation state itself, the processor:

Reads changes from the monitored container.
Determines which changes need to be processed.
Delivers batches of changes to your application code.
Maintains processing state using a lease container.
Distributes work among multiple processor instances.
Recovers work when an instance fails.

The change feed processor is currently provided through the Azure Cosmos DB .NET V3 and Java V4 SDKs. Python and Node.js applications can consume the change feed using the pull model rather than the change feed processor library.

4. The Four Components of a Change Feed Processor

A key AI-200 concept is understanding the four major components.

4.1 Monitored Container

The monitored container is the Azure Cosmos DB container whose changes you want to process.

For example:

			
Database: AIApplication
Container: Documents
Partition key: /customerId

The processor monitors Documents.

When items are created or updated, those changes become available through the change feed.

4.2 Lease Container

The lease container stores the state used by the change feed processor to coordinate processing.

This is extremely important.

The lease container allows multiple processor instances to share the workload without processing the same lease simultaneously.

Conceptually:

                 Lease Container
                /       |       \
               /        |        \
              v         v         v
          Lease 1    Lease 2    Lease 3
             |          |          |
             v          v          v
          Worker A   Worker B   Worker C

The leases represent ownership and progress for portions of the change feed.

The lease container can be in the same Cosmos DB account as the monitored container or in a separate account.

Exam tip

If a question asks:

What component maintains the state of change feed processing?

The answer is generally:

The lease container.

5. Compute Instances

A compute instance hosts the change feed processor.

Examples include:

Azure Kubernetes Service pods
Azure App Service instances
Azure Virtual Machines
Long-running application processes
Hosted background services

For example:

			
AKS Cluster
Pod 1 --> Change Feed Processor
Pod 2 --> Change Feed Processor
Pod 3 --> Change Feed Processor

Each processor instance must have a unique instance name.

The processor distributes leases among the available instances.

6. The Delegate

The delegate is your application code that processes the changes.

For example, suppose an AI application stores documents in Cosmos DB.

When a document changes, the delegate might:

Extract the text.
Generate an embedding.
Store the embedding.
Update a vector index.
Record processing status.

Conceptually:

			
Cosmos DB Change
       |
       v
Change Feed Processor
       |
       v
Delegate
       |
       +--> Extract text
       |
       +--> Generate embedding
       |
       +--> Store embedding
       |
       +--> Update AI search data

		

The delegate is therefore where the application’s business logic lives.

7. How the Processing Lifecycle Works

The basic lifecycle is:

			
Read change feed
       |
       v
Are there changes?
   /          \
 No            Yes
 |              |
 v              v
Wait          Send batch
 |              |
 +------<-------+
                |
                v
        Delegate succeeds?
           /        \
         No          Yes
         |            |
         v            v
     Retry from    Update
     checkpoint    lease

		

More precisely, the processor:

Reads the change feed.
Waits if no changes are available.
Sends a batch of changes to the delegate.
Waits for successful processing.
Updates the lease with the latest successfully processed position.
Continues processing.

The checkpoint is therefore advanced after successful processing.

8. Why the Change Feed Processor Uses At-Least-Once Processing

One of the most important concepts for the exam is that the change feed processor provides an at-least-once delivery guarantee.

Suppose the processor reads:

			
Change A
Change B
Change C

and passes them to your delegate.

If the delegate fails before the checkpoint is successfully updated, the processor can process those changes again.

Therefore:

			
Change A
Change B
Change C
      |
      v
   Process
      |
      X Failure
      |
      v
Retry
      |
      v
Change A
Change B
Change C

		

This means your application should generally be idempotent.

9. Why Idempotency Matters

An idempotent operation can safely be executed more than once without producing an incorrect final result.

For example, suppose the change feed processor receives:

			
{
    "id": "document-123",
    "status": "completed"
}

Your processing logic might update a downstream record:

document-123 -> completed

If the same change is processed twice, the final state remains:

document-123 -> completed

That is preferable to an operation such as:

balance = balance + 100

where processing the same event twice could incorrectly add the amount twice.

Exam rule

Design change feed handlers assuming a change may be delivered more than once.

10. Lease-Based Load Distribution

The change feed processor can distribute processing across multiple instances.

For example:

			
Change Feed
------------------------------------------------
Partition Range 1
Partition Range 2
Partition Range 3
Partition Range 4
------------------------------------------------
        |          |          |          |
        v          v          v          v
     Worker 1   Worker 2   Worker 3   Worker 4

		

The lease container coordinates ownership of these workloads.

If one worker fails, its leases can eventually be acquired by another worker.

This provides fault tolerance without requiring the developer to manually coordinate workers.

11. Scaling the Change Feed Processor

Suppose you initially have:

Worker 1

and later add:

			
Worker 2
Worker 3

The change feed processor can redistribute leases among the workers.

Conceptually:

			
Before:
Worker 1
 ├── Lease 1
 ├── Lease 2
 ├── Lease 3
 └── Lease 4
After scaling:
Worker 1
 ├── Lease 1
 └── Lease 2
Worker 2
 └── Lease 3
Worker 3
 └── Lease 4

		

This allows processing to be parallelized.

However, simply adding instances does not mean that processing becomes infinitely parallel.

The available workload is constrained by the number of leases/partition ranges.

The number of processor instances should not exceed the number of available leases for meaningful distribution.

12. Partitioning and Change Feed Processing

Azure Cosmos DB containers are partitioned using a partition key.

For example:

			
Container: Documents
Partition key: /customerId

The change feed processor works with the underlying partition ranges.

Each range can be processed independently, allowing parallel processing.

This is one reason that selecting an appropriate partition key remains important even when using the change feed.

A poor partition key can create an uneven workload.

13. Starting Position

An important implementation detail is the processor’s starting position.

When a change feed processor is initialized for the first time, its starting point determines which changes it processes.

In latest-version mode, you can configure the processor to start from a specified time or from the beginning of the container’s lifetime.

For example:

			
Container history
|
|---- Change A
|---- Change B
|---- Change C
|---- Change D
|---- Change E
|
             ^
             |
       Start processor

		

If configured to begin at Change A, the processor can process the historical changes.

If configured to start from the current point, older changes aren’t processed.

Important

The starting-position configuration is used when initializing the processor. Once the lease container has established the processor’s state, changing the starting configuration doesn’t reset the existing checkpoint.

14. Change Feed Processor vs. Pull Model

There are two major approaches to consuming the change feed.

Feature	Change Feed Processor	Pull Model
Processing style	Push	Pull
Checkpoint management	Lease container	Application-managed continuation
Load balancing	Built in	Application responsibility
Error/retry infrastructure	Built in	Application responsibility
.NET support	Yes	Yes
Java support	Yes	Yes
Python	Not through processor library	Yes
Node.js	Not through processor library	Yes

The change feed processor is generally easier when you want Azure Cosmos DB to manage the mechanics of distributing work and maintaining processing state.

15. Change Feed Processor vs. Azure Functions Trigger

Another important distinction is between the change feed processor and the Azure Functions trigger for Cosmos DB.

Both can be used to build event-driven applications.

For example:

			
Cosmos DB
   |
   +----> Change Feed Processor
   |
   +----> Azure Functions Trigger

		

The change feed processor is useful when you need more direct control over a long-running processing application.

The Azure Functions trigger is useful when you want a serverless implementation.

The Azure Functions trigger also uses a lease container to maintain processing state.

16. Handling Processing Failures

Suppose your delegate encounters an exception:

			
Batch
  |
  v
Delegate
  |
  X Exception

		

The processor doesn’t simply assume the batch succeeded.

Because the checkpoint hasn’t advanced successfully, the processor can retry the batch.

This behavior produces the at-least-once guarantee.

Important design consideration

If a particular item consistently causes processing to fail, the processor can repeatedly encounter the same problem.

A robust application should therefore have an error-handling strategy.

For example:

			
Change
  |
  v
Process
  |
  X Failure
  |
  +--> Retry
  |
  +--> Persistent failure
          |
          v
     Error/DLQ storage

		

An application might persist information about the failed change to another Cosmos DB container or another durable store so that the processing pipeline doesn’t remain permanently blocked by one problematic change.

17. Monitoring Change Feed Lag

A change feed processor can fall behind the incoming changes.

For example:

			
New changes:
1000 events/sec
Processing:
700 events/sec
Result:
Change feed lag increases

		

The change feed estimator can be used to monitor processor progress and estimate lag.

This can help identify:

Insufficient processing capacity
Slow downstream services
Throttling
Application errors
Lease problems
Processing bottlenecks

18. Request Units and the Change Feed

Change feed processing isn’t free from a Cosmos DB throughput perspective.

Reading the change feed from the monitored container consumes request units (RUs).

Operations involving the lease container also consume RUs.

For example:

			
Monitored Container
       |
       +--> Change feed reads --> RU consumption
Lease Container
       |
       +--> Lease reads
       +--> Lease updates
       +--> Lease coordination
                    |
                    v
               RU consumption

		

If the monitored or lease container experiences throttling, change processing can be delayed.

This is especially important when deploying multiple processor instances or multiple processing workloads that share a lease container.

19. Lease Container Permissions

When Microsoft Entra ID authentication is used, the processor’s identity needs appropriate permissions.

The monitored container requires permissions related to:

Reading account metadata
Reading the change feed

The lease container requires permissions for operations such as:

Reading items
Creating items
Replacing items
Deleting items
Executing queries

This is an important distinction:

The application doesn’t just need permission to read the monitored data; it also needs permission to maintain the processor’s lease state.

20. Using a Global Endpoint

For a change feed processor workload, Microsoft recommends using the global Cosmos DB endpoint rather than a region-specific endpoint.

For example:

			
Preferred:
https://contoso.documents.azure.com

rather than:

https://contoso-westus.documents.azure.com

Regional preferences should be configured through the appropriate SDK region settings.

This is important because lease documents are scoped to the configured endpoint. Changing endpoints can result in separate lease state.

21. A Typical AI Application Architecture

Consider an AI document-processing application.

A user uploads a document, and the application stores metadata in Cosmos DB.

The desired workflow is:

			
User
 |
 v
Application
 |
 v
Cosmos DB
 |
 | New/updated document
 v
Change Feed
 |
 v
Change Feed Processor
 |
 v
Processing Delegate
 |
 +--> Extract document text
 |
 +--> Generate embedding
 |
 +--> Store vector
 |
 +--> Update search metadata
 |
 +--> Notify downstream application

		

This architecture avoids repeatedly scanning the entire container looking for new work.

It also allows the processing workload to scale independently from the application that writes the data.

22. Example .NET Concept

A simplified .NET implementation conceptually looks like this:

			
var processor = monitoredContainer
    .GetChangeFeedProcessorBuilder<MyDocument>(
        "documentProcessor",
        HandleChangesAsync)
    .WithInstanceName("worker-01")
    .WithLeaseContainer(leaseContainer)
    .Build();
await processor.StartAsync();

		

The important concepts are:

monitoredContainer — where changes originate.
leaseContainer — where processing state is maintained.
HandleChangesAsync — your business logic.
WithInstanceName — uniquely identifies the processor instance.
Processor startup — begins monitoring the change feed.

The exact SDK APIs can vary by SDK version, so the exam focus should be on understanding the architecture and responsibilities rather than memorizing every method signature. The current change feed processor documentation identifies .NET V3 and Java V4 as the SDKs that provide the processor library.

23. Important Exam Concepts to Remember

For AI-200, make sure you can distinguish the following:

Monitored container

Contains the data whose changes are being detected.

Lease container

Maintains processor state and coordinates work across instances.

Delegate

Contains the application’s processing logic.

Compute instance

Hosts the change feed processor.

Latest-version mode

Captures the latest versions of creates and updates; deletes aren’t included.

All versions and deletes mode

Captures creates, updates, and deletes, including intermediate changes.

Checkpoint

Records the latest successfully processed position.

At-least-once delivery

A change can be processed more than once, so handlers should be idempotent.

Pull model

The application manages reading, continuation state, and processing coordination.

Change feed processor

Provides a higher-level push-based processing model with lease-based coordination.

Practice Exam Questions

Question 1

An AI application stores documents in an Azure Cosmos DB for NoSQL container. Whenever a document is created or updated, the application must perform additional processing. The development team wants Azure Cosmos DB to manage checkpointing and distribute processing across multiple application instances.

Which solution should the team implement?

A. A timer-triggered Azure Function that scans the container

B. Periodic SQL queries

C. Azure Cosmos DB analytical store queries

D. Change feed processor

Answer: D

Explanation

The change feed processor is designed to process changes incrementally and provides built-in lease-based coordination and checkpoint management. It can distribute change feed processing across multiple instances.

The other approaches require the application to identify changes itself and are less appropriate for event-driven incremental processing.

Question 2

A change feed processor processes a batch of changes successfully but fails before the processing state is checkpointed. What should the application expect?

A. The changes are permanently discarded

B. The batch can be delivered again

C. The entire Cosmos DB container is automatically restored

D. The change feed is permanently disabled

Answer: B

Explanation

The change feed processor provides at-least-once delivery. If processing succeeds but the checkpoint isn’t successfully advanced, the processor can process the same changes again.

Application processing logic should therefore be designed to be idempotent.

Question 3

Which component is primarily responsible for maintaining the state and coordinating ownership of change feed processing across multiple processor instances?

A. Monitored container

B. Compute instance

C. Lease container

D. Application Gateway

Answer: C

Explanation

The lease container stores the state used by the change feed processor to coordinate processing across instances.

The monitored container provides the source data, while compute instances host the processing application.

Question 4

An application uses the default latest-version change feed mode. An item is created and then updated three times before the processor reads the changes. What behavior should the application expect?

A. Only the delete operation is returned

B. All four versions are guaranteed to be returned

C. No changes are returned because the item changed multiple times

D. The latest version of the item is available rather than every intermediate version

Answer: D

Explanation

Latest-version mode provides the latest version of an item in the feed rather than preserving every intermediate change between reads.

If the application needs every create, update, and delete operation, it should consider all versions and deletes mode instead.

Question 5

A developer is building a change feed processor application that will run on three AKS pods. What is the primary purpose of assigning each processor instance a unique instance name?

A. To identify each compute instance participating in lease distribution

B. To specify the Cosmos DB partition key

C. To determine the consistency level

D. To select the Cosmos DB database

Answer: A

Explanation

Each change feed processor instance should have a unique instance name. The processor uses the instances and leases to distribute processing work across the deployment.

The instance name is unrelated to partition-key selection, database selection, or consistency configuration.

Question 6

An AI application must react when documents are deleted from an Azure Cosmos DB for NoSQL container. Which change feed capability is most appropriate?

A. Latest-version change feed mode

B. All versions and deletes change feed mode

C. Increasing the consistency level

D. Increasing the container’s RU/s

Answer: B

Explanation

All versions and deletes mode captures creates, updates, and deletes.

Latest-version mode does not capture deletes.

All versions and deletes mode has additional requirements, including continuous backup, and is specifically available for Azure Cosmos DB for NoSQL.

Question 7

A change feed processor application experiences increasingly large processing delays. Investigation shows that the application is processing changes correctly but cannot keep up with incoming changes.

Which metric or capability is most useful for determining whether the processor is falling behind?

A. Azure DNS query count

B. Azure Storage blob count

C. Change feed estimator

D. Azure Resource Manager activity log

Answer: C

Explanation

The change feed estimator can be used to estimate the lag between the changes available in the monitored container and the progress of the change feed processor.

This can help identify processing bottlenecks and determine whether additional processing capacity may be necessary.

Question 8

A change feed processor’s delegate updates an external database. The same change may occasionally be delivered more than once. What should the developer do?

A. Disable checkpointing

B. Use an idempotent processing design

C. Increase the Cosmos DB consistency level to strong

D. Disable leases

Answer: B

Explanation

The change feed processor provides at-least-once delivery, meaning a change can be processed more than once.

The delegate should therefore be designed to handle duplicate processing safely. Idempotent operations are one of the most important techniques for doing this.

Question 9

A company runs several change feed processor instances and notices that the lease container is experiencing RU throttling. What is a likely consequence?

A. Change feed processing can be delayed

B. All documents in the monitored container are deleted

C. The Cosmos DB account automatically switches to strong consistency

D. The application automatically receives unlimited RU/s

Answer: A

Explanation

The lease container performs operations that consume request units. If the lease container is throttled, lease coordination and renewal can be delayed, which can delay change feed processing.

The monitored container’s change feed reads also consume RUs. Both the monitored and lease containers should therefore be appropriately provisioned.

Question 10

A development team wants to consume an Azure Cosmos DB change feed from a Python application. They want to use the built-in change feed processor library that automatically handles lease-based processing.

What should the team do?

A. Use the .NET change feed processor library from Python

B. Use the Java change feed processor library from Python

C. Use the change feed pull model from Python

D. Use Azure SQL Database instead

Answer: C

Explanation

The Azure Cosmos DB change feed processor library is available for .NET and Java. Python applications can consume the change feed using the pull model, where the application manages continuation state and processing.

Key Takeaways

For the AI-200 exam, the most important ideas are:

The change feed records changes to Azure Cosmos DB items.
The change feed processor provides a push-based processing model.
The monitored container is the source of changes.
The lease container stores processing state and coordinates workers.
The delegate contains the application’s change-processing logic.
Multiple processor instances can share the workload through leases.
Change feed processing provides at-least-once delivery.
Handlers should therefore be idempotent.
Latest-version mode captures creates and updates but not deletes.
All versions and deletes mode captures creates, updates, and deletes.
The change feed processor library is available for .NET and Java; Python and Node.js use the pull model.
Change feed processing consumes RUs.
Throttling of the monitored or lease container can delay processing.
The change feed estimator can help identify processing lag.
The lease container is fundamental to distributed, fault-tolerant change feed processing.

The exam’s scenario questions are likely to test whether you can select the right change feed mode, processing model, lease architecture, error-handling strategy, and scaling approach, rather than simply recognizing the term “change feed.”

Go to the AI-200 Exam Prep Hub main page

Overview

1. Why Database Connections Affect Performance

Connection overhead

2. Connection Pooling

Without pooling

With pooling

3. Client-Side Connection Pooling

Benefits

4. Azure Database for PostgreSQL Built-In PgBouncer

5. PgBouncer Port 6432

Exam tip

6. PgBouncer Transaction Pooling

7. PgBouncer Client Connections vs. PostgreSQL Connections

8. Do Not Simply Increase max_connections

Better approach

9. Choosing an Appropriate Pool Size

10. Connection Pooling in Scaled-Out Applications

Exam scenario

11. Serverless Applications and Connection Bursts

Recommended design

12. Connection Churn

Key exam concept

13. Application Location Matters

General principle

14. Availability Zones and Latency

15. Private Networking

Important distinction

16. DNS and Connection Reliability

17. TLS and Connection Overhead

18. Connection Timeouts

Connection timeout

Command timeout

Pool wait timeout

19. Retries and Transient Failures

Avoid retry storms

20. Connection Pooling and Transactions

21. Avoid Long-Running Transactions

22. PgBouncer Configuration to Know

23. Monitoring Connections

24. Diagnosing Connection-Related Performance Problems

Step 1: Check application latency

Step 2: Check connection counts

Step 3: Check connection churn

Step 4: Check pool configuration

Step 5: Check database resources

Step 6: Check network topology

Step 7: Optimize the actual workload

25. Connection Optimization Strategy

Application

Pooling

Network

Database

26. Common AI-200 Exam Traps

Trap 1: “Increase max_connections“

Trap 2: “Create a connection for every request”

Trap 3: “Use the largest possible pool”

Trap 4: “PgBouncer increases database processing capacity”

Trap 5: “More connections always means more throughput”

Trap 6: “Private networking automatically reduces latency”

Trap 7: “Connection timeout controls query execution time”

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

27. Key Takeaways for the AI-200 Exam

Practice Exam Questions

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Final Exam Perspective

Overview

1. Understanding Relational Schema Design

2. Choose Data Types Carefully

2.1 Integer Types

Exam consideration

3. Exact Versus Approximate Numeric Values

8. Do Not Simply Increase `max_connections`

Trap 1: “Increase `max_connections`“