This post is a part of the AI-200: Developing AI Cloud Solutions on Azure  Exam Prep Hub.
This topic falls under these sections:
Develop AI solutions by using Azure data management services (25–30%)
   --> Develop AI solutions by using Azure Database for PostgreSQL
      --> Implement indexing strategies, including optimizing query latency and reducing pgvector compute overhead

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

Overview

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

This topic has two closely related areas:

1. Traditional PostgreSQL indexing and query optimization
2. pgvector indexing and vector-search optimization

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

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

1. Why Indexing Matters

Consider a table containing several million documents:

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

Suppose the application frequently executes:

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

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

An index such as:

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

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

The important exam concept is:

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

2. Common PostgreSQL Index Types

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

B-tree

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

It is appropriate for:

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

Examples:

CREATE INDEX ix_customer_email
ON customers (email);

and:

CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date);

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

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

Hash

Hash indexes are designed primarily for equality comparisons.

For example:

WHERE customer_id = 1001

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

GIN

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

• arrays
• JSONB
• full-text-search-related workloads

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

GiST

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

It can be useful for:

• geometric data
• range types
• specialized extensions

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

3. Index Columns Based on Query Patterns

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

Instead, examine the actual query workload.

Suppose the application frequently executes:

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

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

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

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

4. Composite Index Column Order Matters

Consider:

CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date);

This index is particularly useful for queries such as:

WHERE customer_id = 100

and:

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

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

WHERE order_date >= '2026-01-01'

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

Exam takeaway

When designing a composite index, think about:

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

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

5. Avoid Excessive Indexing

Indexes improve reads but aren’t free.

Every additional index can result in:

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

For example, if a table has:

100 million rows

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

Therefore:

Create indexes that provide measurable value to important queries.

Do not blindly index every column.

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

6. Use EXPLAIN to Understand Query Performance

One of the most important PostgreSQL performance tools is:

EXPLAIN

For example:

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 100;

To actually execute the query and obtain runtime information:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 100;

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

You might discover that PostgreSQL is performing:

Seq Scan

instead of:

Index Scan

That doesn’t automatically mean the database is wrong.

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

Important exam principle

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

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

7. Keep Statistics Current

PostgreSQL’s optimizer relies on statistics to estimate:

• number of rows
• data distribution
• selectivity
• expected query costs

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

ANALYZE updates table statistics:

ANALYZE documents;

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

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

8. Query Design Can Matter More Than Adding an Index

Consider:

SELECT *
FROM orders;

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

Instead:

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

This reduces:

• rows processed
• data transferred
• memory consumption
• network traffic
• application processing

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

9. Parameterize Queries

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

Instead of building:

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

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

Benefits include:

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

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

10. Understand pgvector

For AI applications, PostgreSQL can be extended with pgvector.

pgvector provides support for storing and searching vector embeddings.

A typical table might look like:

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

The vector might represent:

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

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

11. Exact Vector Search

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

For example:

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

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

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

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

• CPU
• memory
• I/O
• execution time

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

12. Approximate Nearest-Neighbor Search

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

Instead of asking:

“Which vector is closest among every vector?”

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

This can dramatically reduce search latency and compute requirements.

The tradeoff is:

ANN improves performance at the potential cost of recall.

For AI applications, this is often an excellent tradeoff.

13. IVFFlat

IVFFlat stands for Inverted File with Flat Compression.

It divides vectors into groups or lists based on clustering.

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

A simplified example:

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

The lists parameter controls the number of clusters/lists.

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

For example:

SET ivfflat.probes = 10;

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

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

IVFFlat characteristics

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

14. HNSW

HNSW stands for Hierarchical Navigable Small World.

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

Example:

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

HNSW has two important build-time parameters:

m
ef_construction

m controls the maximum number of connections per layer.

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

At query time, HNSW uses:

ef_search

For example:

SET hnsw.ef_search = 100;

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

HNSW characteristics

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

15. DiskANN

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

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

Microsoft describes DiskANN as offering a strong balance of:

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

DiskANN is supported on Azure Database for PostgreSQL Flexible Server.

Important DiskANN parameters include:

• max_neighbors
• l_value_ib
• l_value_is

For example:

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

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

16. Choosing Between IVFFlat, HNSW, and DiskANN

A useful exam-oriented comparison is:

The choice should be based on:

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

There is no universally “best” vector index.

17. Choose the Correct Distance Metric

pgvector supports different distance calculations.

Common operators include:

The index must use the corresponding operator class.

For cosine distance:

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

The query should use the cosine-distance operator:

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

For L2 distance:

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

and:

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

For inner product:

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

and:

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

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

18. Why the Distance Metric Matters

Suppose an embedding model is designed for cosine similarity.

Using the wrong distance metric can produce different rankings.

Therefore, developers should understand the relationship:

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

For example:

Cosine
   ↓
<=>
   ↓
vector_cosine_ops

This relationship is highly testable in scenario-based questions.

19. Reduce pgvector Compute Overhead

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

Several techniques can help.

Technique 1: Use ANN indexes

Instead of comparing against every vector:

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

ANN can narrow the candidate set:

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

This can substantially reduce CPU and latency.

Technique 2: Tune search parameters

For IVFFlat:

SET ivfflat.probes = 10;

For HNSW:

SET hnsw.ef_search = 100;

Higher values generally increase search work.

Therefore:

Don’t automatically maximize these parameters.

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

Technique 3: Return fewer results

If the application only needs five documents:

LIMIT 5

is preferable to:

LIMIT 10000

when the larger result set isn’t required.

This can reduce downstream processing and data transfer.

Technique 4: Filter before or alongside vector retrieval where appropriate

AI applications frequently combine semantic similarity with metadata.

For example:

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

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

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

20. Partial Indexes for Filtered Vector Workloads

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

For example:

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

Now the index contains only rows satisfying:

tier = 'premium'

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

However, the query must include the appropriate predicate:

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

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

21. Vector Dimensions and Indexing Limits

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

For example:

embedding vector(1536)

is indexable.

But:

embedding vector

does not provide a fixed dimension for the index.

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

Exam trap

A question may present:

embedding vector(3072)

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

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

22. Load Data Before Creating an IVFFlat Index

IVFFlat uses clustering to organize vectors into lists.

Consequently, the data distribution matters.

A common approach is:

1. Create table
2. Load embeddings
3. Create IVFFlat index
4. Tune probes
5. Benchmark

rather than:

1. Create table
2. Create IVFFlat index
3. Load all data

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

23. HNSW Does Not Require Training

This is an important contrast.

IVFFlat

Data
 ↓
Clustering/training
 ↓
Lists

HNSW

Data
 ↓
Graph construction

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

This difference is a common source of exam questions.

24. Index Build Memory

Vector indexes can be expensive to build.

PostgreSQL’s:

maintenance_work_mem

can affect index construction.

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

For example:

SET maintenance_work_mem = '8GB';

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

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

25. Connection Pooling

Query performance isn’t only about indexes.

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

Creating connections repeatedly can consume resources and add latency.

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

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

This is especially useful for:

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

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

26. Monitor Query Performance

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

A useful process is:

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

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

27. Understand Sequential Scans

Seeing:

Seq Scan

in an execution plan isn’t automatically a problem.

Suppose a table contains:

1,000 rows

and the query needs:

800 rows

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

But if a table contains:

100,000,000 rows

and the query needs:

10 rows

an appropriate index could provide a huge performance advantage.

Therefore:

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

28. Avoid Indexes That Don’t Match the Query

Suppose you create:

CREATE INDEX ix_products_category
ON products(category);

but the application primarily queries:

WHERE product_name = 'Laptop'

The index isn’t useful for that predicate.

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

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

29. Data Layout Matters

For AI workloads, data layout can significantly affect performance.

A document table might contain:

id
tenant_id
document_type
created_at
content
embedding

The developer should consider:

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

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

30. Exact vs. Approximate Search

This distinction is critical for AI-200.

The choice depends on application requirements.

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

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

31. Practical Optimization Strategy

A strong approach for an AI application is:

Step 1 — Understand the workload

Determine:

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

Step 2 — Start with correct query semantics

Choose:

• distance metric
• pgvector operator
• corresponding operator class

Step 3 — Benchmark exact search

This establishes a baseline.

Step 4 — Select an ANN index

Evaluate:

• IVFFlat
• HNSW
• DiskANN where supported

Step 5 — Tune search parameters

For example:

IVFFlat → probes
HNSW → ef_search
DiskANN → l_value_is

Step 6 — Measure recall and latency

Don’t optimize only for speed.

Measure both:

Latency
+
Recall
+
CPU
+
Memory

Step 7 — Optimize metadata filtering

Consider:

• conventional indexes
• composite indexes
• partial indexes
• appropriate data layout

Step 8 — Monitor continuously

Workloads change.

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

32. Key AI-200 Exam Takeaways

Remember these concepts:

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

Practice Exam Questions

Question 1

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

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

Which index is most appropriate for this query pattern?

A.

CREATE INDEX ix_orders_date
ON orders(order_date);

B.

CREATE INDEX ix_orders_customer
ON orders(customer_id);

C.

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

D.

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

Answer: C

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

Question 2

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

A. hnsw.ef_search

B. maintenance_work_mem

C. ivfflat.probes

D. hnsw.m

Answer: A

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

Question 3

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

What should the team consider?

A. Remove the vector column.

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

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

D. Create an approximate nearest-neighbor vector index.

Answer: D

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

Question 4

A developer creates the following index:

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

Which query is aligned with this index?

A.

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

B.

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

C.

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

D.

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

Answer: B

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

Question 5

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

Which approach is generally recommended?

A. Create the index before inserting any data.

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

C. Load the data before creating the IVFFlat index.

D. Disable all PostgreSQL statistics before creating the index.

Answer: C

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

Question 6

An application has a vector column defined as:

embedding vector(3072)

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

What is the most likely reason?

A. IVFFlat supports only integer vectors.

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

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

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

Answer: B

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

Question 7

An application frequently searches only premium documents:

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

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

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

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

B. Remove the tier predicate from the query.

C. Create an index on an unrelated timestamp column.

D. Store embeddings as JSON instead of vectors.

Answer: A

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

WHERE tier = 'premium'

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

Question 8

A PostgreSQL developer sees the following execution plan:

Seq Scan on orders

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

Which statement is most accurate?

A. PostgreSQL always uses an index when one exists.

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

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

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

Answer: C

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

Question 9

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

Which explanation is most accurate?

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

B. Increasing ef_search disables the vector index.

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

D. Increasing ef_search reduces the number of candidates examined.

Answer: A

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

Question 10

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

What is the most appropriate optimization?

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

B. Disable all indexes.

C. Increase the number of vector dimensions.

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

Answer: D

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

Final Exam Review

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

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

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

What is the query actually doing?

Which columns are being filtered, joined, or sorted?

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

Is exact vector search still appropriate at this scale?

Should I use IVFFlat, HNSW, or DiskANN?

Which distance metric and operator class are required?

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

Are statistics current?

Is connection overhead contributing to latency?

What does EXPLAIN ANALYZE actually show?

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

Go to the AI-200 Exam Prep Hub main page