This post is a part of the AI-200: Developing AI Cloud Solutions on Azure Exam Prep Hub.
This topic falls under these sections:
Develop AI solutions by using Azure data management services (25–30%)
--> Develop AI solutions by using Azure Database for PostgreSQL
--> Implement indexing strategies, including optimizing query latency and reducing pgvector compute overhead
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Overview
Azure Database for PostgreSQL is a managed PostgreSQL service that can support both traditional relational workloads and AI workloads involving vector embeddings. For AI-200, developers should understand how to design indexes and tune queries so that applications can retrieve data efficiently while minimizing CPU, memory, I/O, and overall compute consumption.
This topic has two closely related areas:
- Traditional PostgreSQL indexing and query optimization
- pgvector indexing and vector-search optimization
The key objective is not simply to “add indexes.” An index can dramatically improve read performance, but indexes also consume storage and require additional work when rows are inserted, updated, or deleted. A good design balances query latency, workload characteristics, storage, and maintenance overhead.
For vector workloads, there is an additional tradeoff: approximate nearest-neighbor (ANN) indexes can substantially reduce the amount of computation required for similarity searches, but they can trade some recall for performance.
1. Why Indexing Matters
Consider a table containing several million documents:
CREATE TABLE documents( id BIGINT PRIMARY KEY, tenant_id BIGINT, category VARCHAR(100), title TEXT, content TEXT, created_at TIMESTAMPTZ);
Suppose the application frequently executes:
SELECT *FROM documentsWHERE tenant_id = 42ORDER BY created_at DESCLIMIT 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_createdON 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
JOINconditions MIN()andMAX()patterns in appropriate circumstances
Examples:
CREATE INDEX ix_customer_emailON customers (email);
and:
CREATE INDEX ix_orders_customer_dateON 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 ordersWHERE 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_dateON 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_dateON 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
INSERToperations - longer
UPDATEoperations - longer
DELETEoperations - 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:
EXPLAINSELECT *FROM ordersWHERE customer_id = 100;
To actually execute the query and obtain runtime information:
EXPLAIN ANALYZESELECT *FROM ordersWHERE 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_dateFROM ordersWHERE customer_id = 100ORDER BY order_date DESCLIMIT 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 customersWHERE 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, contentFROM documentsORDER 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_idxON documentsUSING ivfflat (embedding vector_cosine_ops)WITH (lists = 100);
The lists parameter controls the number of clusters/lists.
During querying, ivfflat.probes controls how many lists are searched.
For example:
SET ivfflat.probes = 10;
Increasing probes generally improves recall but requires more computation and can increase latency.
Microsoft recommends starting points for lists and probes based on dataset size, but these are starting points rather than universal values. They should be benchmarked against the actual workload.
IVFFlat characteristics
| Characteristic | IVFFlat |
|---|---|
| Index type | ANN |
| Build speed | Relatively fast |
| Memory use | Lower than HNSW |
| Training | Requires clustering/training |
| Query tuning | probes |
| Main tradeoff | Speed vs. recall |
A particularly important point is that IVFFlat works best when the index is created after the initial dataset has been loaded, because its clustering depends on the data distribution.
14. HNSW
HNSW stands for Hierarchical Navigable Small World.
It creates a multilayer graph structure that allows the search to navigate toward likely nearest neighbors.
Example:
CREATE INDEX documents_embedding_hnsw_idxON documentsUSING hnsw (embedding vector_cosine_ops);
HNSW has two important build-time parameters:
mef_construction
m controls the maximum number of connections per layer.
ef_construction controls the size of the candidate list used during index construction.
At query time, HNSW uses:
ef_search
For example:
SET hnsw.ef_search = 100;
Increasing ef_search generally considers more candidates and can improve recall at the expense of additional computation and latency.
HNSW characteristics
| Characteristic | HNSW |
|---|---|
| Index type | ANN |
| Query performance | Generally strong |
| Memory consumption | Higher than IVFFlat |
| Build cost | Higher than IVFFlat |
| Training step | None |
| Query tuning | ef_search |
| Build tuning | m, ef_construction |
One important advantage is that HNSW does not require a separate training phase, so it can be created even when the table is empty.
15. DiskANN
Azure Database for PostgreSQL Flexible Server also supports DiskANN for vector search.
DiskANN is designed for scalable approximate nearest-neighbor search and is particularly useful for very large vector datasets.
Microsoft describes DiskANN as offering a strong balance of:
- high recall
- high queries per second
- low latency
- large-scale vector search
DiskANN is supported on Azure Database for PostgreSQL Flexible Server.
Important DiskANN parameters include:
max_neighborsl_value_ibl_value_is
For example:
CREATE INDEX documents_embedding_diskann_idxON documentsUSING diskann (embedding vector_cosine_ops);
DiskANN can be an important option when workloads become very large and vector-search scalability becomes a primary concern.
16. Choosing Between IVFFlat, HNSW, and DiskANN
A useful exam-oriented comparison is:
| Requirement | Potential choice |
|---|---|
| Faster index creation and lower memory | IVFFlat |
| Strong speed/recall tradeoff | HNSW |
| Large-scale vector workloads on Flexible Server | DiskANN |
| Need an index before data is loaded | HNSW or DiskANN |
| Need tunable candidate/list searching | IVFFlat/HNSW/DiskANN |
| Exact search required | No ANN index |
The choice should be based on:
- dataset size
- insertion/update pattern
- acceptable latency
- required recall
- available memory
- index build time
- query volume
- workload growth
There is no universally “best” vector index.
17. Choose the Correct Distance Metric
pgvector supports different distance calculations.
Common operators include:
| Operator | Distance/similarity |
|---|---|
<=> | Cosine distance |
<-> | L2/Euclidean distance |
<#> | Negative inner product |
The index must use the corresponding operator class.
For cosine distance:
CREATE INDEX documents_embedding_idxON documentsUSING hnsw (embedding vector_cosine_ops);
The query should use the cosine-distance operator:
SELECT id, contentFROM documentsORDER BY embedding <=> '[...]'LIMIT 10;
For L2 distance:
CREATE INDEX documents_embedding_l2_idxON documentsUSING hnsw (embedding vector_l2_ops);
and:
ORDER BY embedding <-> '[...]'
For inner product:
CREATE INDEX documents_embedding_ip_idxON documentsUSING 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 search1,000,000 vectors↓Potentially evaluate 1,000,000 candidates
ANN can narrow the candidate set:
ANN search1,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, contentFROM documentsWHERE 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_idxON documentsUSING 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 table2. Load embeddings3. Create IVFFlat index4. Tune probes5. Benchmark
rather than:
1. Create table2. Create IVFFlat index3. 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_categoryON 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:
idtenant_iddocument_typecreated_atcontentembedding
The developer should consider:
- how frequently each column is filtered
- how frequently vector searches are performed
- tenant isolation
- metadata filtering
- vector dimensions
- number of vectors
- update frequency
- index size
- workload growth
For example, a multi-tenant application may benefit from organizing indexes and queries around tenant_id rather than treating all tenants as one undifferentiated search space.
30. Exact vs. Approximate Search
This distinction is critical for AI-200.
| Feature | Exact Search | ANN Search |
|---|---|---|
| Recall | Perfect | Potentially lower |
| CPU cost | Higher | Lower |
| Latency | Higher at scale | Lower at scale |
| Index required | No | Yes |
| Best for | Small datasets/high recall | Large datasets/low latency |
| Examples | Sequential vector comparison | IVFFlat/HNSW/DiskANN |
The choice depends on application requirements.
If absolute recall is more important than latency, exact search may be appropriate.
If an application must search millions of embeddings with low latency, ANN is usually more appropriate.
31. Practical Optimization Strategy
A strong approach for an AI application is:
Step 1 — Understand the workload
Determine:
- number of vectors
- vector dimensions
- queries per second
- expected latency
- required recall
- update frequency
- filtering requirements
Step 2 — Start with correct query semantics
Choose:
- distance metric
- pgvector operator
- corresponding operator class
Step 3 — Benchmark exact search
This establishes a baseline.
Step 4 — Select an ANN index
Evaluate:
- IVFFlat
- HNSW
- DiskANN where supported
Step 5 — Tune search parameters
For example:
IVFFlat → probesHNSW → ef_searchDiskANN → 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.
EXPLAINshows the optimizer’s plan.EXPLAIN ANALYZEexecutes 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
probesto control how many lists are searched. - HNSW uses
ef_searchto control the search candidate list. - HNSW uses
mandef_constructionduring 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_memcan 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 ordersWHERE 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_dateON orders(order_date);
B.
CREATE INDEX ix_orders_customerON orders(customer_id);
C.
CREATE INDEX ix_orders_customer_dateON orders(customer_id, order_date DESC);
D.
CREATE INDEX ix_orders_date_customerON 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_idxON documentsUSING hnsw (embedding vector_cosine_ops);
Which query is aligned with this index?
A.
SELECT *FROM documentsORDER BY embedding <-> '[...]'LIMIT 10;
B.
SELECT *FROM documentsORDER BY embedding <=> '[...]'LIMIT 10;
C.
SELECT *FROM documentsORDER BY embedding <#> '[...]'LIMIT 10;
D.
SELECT *FROM documentsORDER 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
