This post is a part of the AI-200: Developing AI Cloud Solutions on Azure  Exam Prep Hub.
This topic falls under these sections:
Develop AI solutions by using Azure data management services (25–30%)
   --> Develop AI solutions by using Azure Database for PostgreSQL
      --> Configure compute, memory, and storage resources to support vector workloads

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

Overview

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

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

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

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

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

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

A useful way to think about PostgreSQL performance is:

Compute → CPU and memory

Storage → capacity, IOPS, throughput, and latency

Workload → determines which resources become bottlenecks

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

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

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

2. Why CPU Matters for Vector Workloads

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

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

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

and compare it with thousands or millions of stored embeddings.

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

CPU becomes especially important when:

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

A useful rule for the exam is:

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

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

3. Choosing the Compute Tier

Burstable

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

It is useful for:

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

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

Exam consideration

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

4. General Purpose Compute

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

It is typically appropriate for:

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

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

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

5. Memory Optimized Compute

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

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

Memory Optimized compute can be appropriate when:

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

The important exam concept is:

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

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

6. Why Memory Is Important for pgvector

Vector workloads can be memory-intensive for several reasons.

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

The raw vector data requires approximately:

1,536 × 4 bytes = 6,144 bytes

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

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

The actual memory requirements depend on:

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

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

7. Storage Capacity Is Different From Storage Performance

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

Storage capacity determines how much data can be stored.

Storage performance involves:

• IOPS
• Throughput
• Latency

For example:

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

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

8. IOPS

IOPS means input/output operations per second.

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

Examples include:

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

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

9. Storage Throughput

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

Throughput becomes important for operations such as:

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

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

Think of the distinction this way:

IOPS = how many I/O operations

Throughput = how much data

Latency = how quickly an individual I/O operation completes

These concepts are related but are not interchangeable.

10. Storage Latency

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

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

For example, suppose an application performs:

1. Receive a user’s question.
2. Generate an embedding.
3. Search the vector database.
4. Retrieve metadata.
5. Send context to an AI model.
6. Generate a response.

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

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

11. Premium SSD and Premium SSD v2

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

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

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

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

For example, consider an application that requires:

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

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

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

12. Storage Capacity Can Affect Performance

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

Therefore:

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

It can also affect performance.

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

First determine whether the bottleneck is actually storage performance.

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

13. Compute and Storage Must Be Balanced

Consider this example:

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

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

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

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

This leads to an important principle:

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

You need sufficient:

• CPU
• Memory
• Storage IOPS
• Storage throughput
• Network capacity

to support the workload.

14. Vector Indexes Increase Resource Requirements

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

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

• IVFFlat
• HNSW
• DiskANN

These indexes have different performance and resource characteristics.

15. IVFFlat

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

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

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

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

Resource characteristics

IVFFlat generally:

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

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

16. HNSW

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

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

HNSW:

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

HNSW has configurable parameters including:

• m
• ef_construction
• ef_search

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

Resource implications

Increasing HNSW construction parameters can increase resource requirements.

Therefore:

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

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

17. DiskANN

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

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

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

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

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

18. Vector Dimensions Affect Resource Requirements

Vector dimensionality has a direct impact on storage requirements.

Suppose an application stores:

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

Raw vector storage is approximately:

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

or approximately 6.14 GB of raw vector values.

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

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

Consequently:

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

19. Dimension Limits and Indexing

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

For example:

embedding vector(1536)

is indexable.

A generic declaration such as:

embedding vector

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

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

This can influence architecture decisions when selecting an embedding model.

20. PostgreSQL Memory Configuration

PostgreSQL has several memory-related configuration settings.

One particularly important parameter for maintenance operations is:

maintenance_work_mem

It controls memory available for operations such as:

• Index creation
• VACUUM
• Certain maintenance operations

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

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

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

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

Exam principle

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

21. Index Creation Can Be Resource Intensive

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

• CPU
• Memory
• Storage I/O
• Time

This is particularly true for HNSW.

For large data sets, it can be beneficial to:

1. Load the data.
2. Validate the data.
3. Create the vector index.
4. Test the index.
5. Tune query parameters.

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

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

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

For example:

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

Therefore, evaluate both:

Build-time performance

and

Query-time performance

when selecting an indexing strategy.

23. Scaling Compute

Azure Database for PostgreSQL Flexible Server supports vertical scaling.

You can change:

• Compute tier
• Compute SKU
• vCores
• Memory

Compute and storage can be scaled independently.

Scale compute when:

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

24. Scale Memory When Memory Is the Bottleneck

Suppose monitoring shows:

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

Adding more CPU may not help much.

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

This is a classic exam scenario:

Identify the bottleneck before selecting the resource to scale.

25. Scale Storage When Capacity Is the Bottleneck

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

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

Storage growth planning should account for:

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

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

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

Consider a server where:

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

Adding more vCores may not solve the problem.

Instead, investigate:

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

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

27. Connection Pooling Matters

AI applications can generate large numbers of concurrent requests.

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

• CPU
• Memory
• Connection limits
• Network resources

Connection pooling allows applications to reuse database connections.

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

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

28. Combine Vector Search With Metadata Filtering

AI applications commonly need queries such as:

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

That means the database may need to perform:

1. Vector similarity search.
2. Metadata filtering.
3. Sorting/ranking.
4. Result retrieval.

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

For example:

CREATE INDEX idx_documents_tenant
ON documents (tenant_id);

and:

CREATE INDEX idx_documents_created
ON documents (created_at);

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

29. Partitioning Can Help Large Workloads

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

Possible partitioning strategies include:

• Tenant
• Geography
• Date
• Business unit
• Data lifecycle

For example:

documents_2025
documents_2026
documents_2027

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

However:

Partitioning is not automatically a vector-search optimization.

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

30. Monitor Before You Scale

One of the strongest principles for AI-200 is:

Measure first, then optimize.

Important metrics and observations include:

Compute

• CPU utilization
• Memory utilization
• CPU credits for Burstable instances

Storage

• Storage used
• Storage percentage
• I/O percentage
• IOPS
• Throughput
• Latency

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

PostgreSQL

Also examine:

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

Vector workload

Measure:

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

31. A Practical Resource-Sizing Process

A good process for configuring a PostgreSQL vector workload is:

Step 1: Estimate the data volume

Determine:

• Number of records
• Number of vectors
• Vector dimensions
• Expected growth

Step 2: Estimate vector storage

Calculate approximate raw vector size:

number of vectors × dimensions × bytes per dimension

Then add overhead for tables and indexes.

Step 3: Identify the workload

Determine whether the workload is primarily:

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

Step 4: Select compute

Choose among:

• Burstable
• General Purpose
• Memory Optimized

based on sustained CPU and memory requirements.

Step 5: Select storage

Consider:

• Capacity
• IOPS
• Throughput
• Latency
• Growth
• Cost

Step 6: Select the vector index

Evaluate:

• IVFFlat
• HNSW
• DiskANN

based on:

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

Step 7: Load and index

When practical:

1. Load the data.
2. Create the vector index.
3. Validate query plans.
4. Benchmark vector queries.

Step 8: Monitor

Measure the workload under realistic concurrency.

Step 9: Scale the actual bottleneck

Do not blindly increase vCores or storage.

32. Common Exam Scenarios

Scenario 1: CPU is consistently high

Problem: Vector searches are CPU-intensive.

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

Scenario 2: Memory is exhausted during HNSW index creation

Problem: HNSW requires substantial memory during construction.

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

Scenario 3: Storage I/O is saturated

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

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

Scenario 4: Storage capacity is nearly full

Problem: The database is approaching its provisioned capacity.

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

Scenario 5: The workload is low-volume and intermittent

Problem: The application spends most of its time idle.

Likely solution: Burstable compute may be appropriate.

Scenario 6: High-concurrency production vector search

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

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

33. Key AI-200 Exam Takeaways

Remember these relationships:

The central lesson is:

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

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

Practice Exam Questions

Question 1

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

What should you investigate first?

A. Increase storage capacity

B. Enable storage autogrow

C. Increase compute capacity

D. Increase storage throughput

Answer: C

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

Question 2

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

Which compute tier is potentially the most appropriate?

A. Burstable

B. Memory Optimized

C. Ultra-high-memory General Purpose

D. Dedicated high-IOPS compute

Answer: A

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

Question 3

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

Which action is most directly relevant?

A. Reduce storage capacity

B. Move to a larger-memory compute configuration

C. Enable storage autogrow

D. Reduce the number of PostgreSQL connections to zero

Answer: B

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

Question 4

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

What should the administrator investigate?

A. Increasing the number of embedding dimensions

B. Reducing available storage

C. Moving to Burstable compute

D. Increasing storage IOPS or otherwise improving storage performance

Answer: D

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

Question 5

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

A. Storage capacity and IOPS are always completely independent

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

C. Storage capacity determines CPU utilization

D. Storage capacity has no relationship to database performance

Answer: B

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

Question 6

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

Which compute option is generally more appropriate than Burstable?

A. A development-sized Burstable instance

B. A smaller Burstable instance with CPU credits

C. A server with minimal memory

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

Answer: D

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

Question 7

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

A. Memory

B. Storage capacity only

C. Network bandwidth only

D. CPU credits

Answer: A

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

Question 8

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

A. Burstable compute

B. Standard database backups

C. Premium SSD v2

D. Increasing PostgreSQL connection limits

Answer: C

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

Question 9

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

Which index is generally the better starting point?

A. HNSW

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

C. No index under any circumstances

D. IVFFlat

Answer: D

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

Question 10

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

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

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

C. Vector dimensionality has no effect on storage requirements

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

Answer: A

Explanation: The approximate raw vector storage is:

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

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

Final Exam Review

For AI-200, remember the following chain:

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

The most important distinctions are:

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

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

Go to the AI-200 Exam Prep Hub main page

This post is a part of the AI-200: Developing AI Cloud Solutions on Azure  Exam Prep Hub.
This topic falls under these sections:
Develop AI solutions by using Azure data management services (25–30%)
   --> Develop AI solutions by using Azure Database for PostgreSQL
      --> Connect and query Azure Database for PostgreSQL by using SDKs

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

Overview

Azure Database for PostgreSQL is a fully managed PostgreSQL service that provides a familiar PostgreSQL database engine while Azure manages much of the underlying infrastructure, availability, maintenance, and scaling.

For AI-200, developers need to understand how applications connect to Azure Database for PostgreSQL and how they use programming-language client libraries to execute SQL statements.

The key idea is:

Your application normally connects to Azure Database for PostgreSQL through a PostgreSQL client library/driver, establishes a secure connection, executes parameterized SQL commands, processes the results, and properly manages connections and transactions.

Azure Database for PostgreSQL supports commonly used PostgreSQL client interfaces including:

• Python — psycopg
• C#/.NET — Npgsql
• Java — JDBC
• Node.js — pg
• Go — PostgreSQL drivers such as pgx or pq
• PHP — php-pgsql
• Ruby — pg
• C/C++ — PostgreSQL client libraries
• ODBC — psqlODBC

These are PostgreSQL client libraries rather than an Azure-specific database SDK. (Microsoft Learn)

1. Understand the Connection Architecture

A typical application architecture looks like this:

Application
    |
    | PostgreSQL client library
    | (Npgsql, psycopg, JDBC, pg, etc.)
    v
Secure connection
    |
    | TLS
    v
Azure Database for PostgreSQL
    |
    v
PostgreSQL database
    |
    +-- Tables
    +-- Views
    +-- Indexes
    +-- Functions
    +-- Extensions

The application is responsible for using a PostgreSQL-compatible client library. Azure provides the managed PostgreSQL server.

For example:

C# application
      |
      v
   Npgsql
      |
      v
Azure Database for PostgreSQL

or:

Python application
      |
      v
   psycopg
      |
      v
Azure Database for PostgreSQL

This distinction is important for the exam.

Azure SDKs are commonly used to manage Azure resources and services.

PostgreSQL client libraries are used to communicate with the PostgreSQL database itself.

2. Obtain the Connection Information

An application generally needs:

• Server hostname
• Database name
• Port
• Username
• Authentication information
• TLS/SSL configuration

The standard PostgreSQL port is:

5432

An Azure Database for PostgreSQL server typically has a hostname similar to:

myserver.postgres.database.azure.com

A connection string might look conceptually like:

host=myserver.postgres.database.azure.com
port=5432
dbname=mydatabase
user=myuser
password=<secret>
sslmode=require

The exact connection-string syntax varies by client library.

Azure’s current guidance shows PostgreSQL connections using TLS and port 5432. (Microsoft Learn)

3. Secure Connections with TLS

Applications should connect to Azure Database for PostgreSQL using encrypted connections.

Azure Database for PostgreSQL supports TLS 1.2 and TLS 1.3 and rejects TLS 1.0 and 1.1. (Microsoft Learn)

For example, a connection string can include:

sslmode=require

This tells the client to use an encrypted connection.

More stringent certificate validation can be configured using settings such as:

sslmode=verify-ca

or:

sslmode=verify-full

verify-full provides stronger validation because it verifies both the certificate chain and the server hostname.

Exam tip

If a question describes:

“The application must communicate with PostgreSQL securely.”

Look for TLS/SSL configuration rather than simply changing the database port.

Changing the port does not provide encryption.

4. Authentication Options

Applications can authenticate to Azure Database for PostgreSQL in several ways.

Common approaches include:

PostgreSQL authentication

The application supplies a PostgreSQL username and password.

Conceptually:

Application
    |
    | username + password
    v
PostgreSQL

This is straightforward but requires careful secret management.

Microsoft Entra authentication

Applications can also authenticate using Microsoft Entra identities.

This allows applications to obtain an access token rather than embedding a PostgreSQL password in application code.

Azure supports both system-assigned and user-assigned managed identities for authentication to Azure Database for PostgreSQL. (Microsoft Learn)

A managed-identity architecture can look like:

Azure App Service / VM / Function / Container
             |
             | Managed identity
             v
       Microsoft Entra ID
             |
             | Access token
             v
Azure Database for PostgreSQL

This can eliminate the need to store a database password in the application.

Exam tip

If a question says:

“The application is hosted in Azure and should access PostgreSQL without storing credentials.”

The likely direction is Microsoft Entra authentication with a managed identity, assuming the relevant service and database configuration support it.

5. Network Connectivity Matters

Successful SDK code does not guarantee a successful connection.

The application must also have network access to the PostgreSQL server.

Azure Database for PostgreSQL Flexible Server supports two primary networking approaches:

• Public access, where allowed IP addresses are controlled through firewall rules
• Private access, using virtual network integration

(Microsoft Learn)

Therefore, when troubleshooting a connection, consider:

Application
   |
   +--> DNS resolution
   |
   +--> Network routing
   |
   +--> Firewall / network rules
   |
   +--> TLS
   |
   +--> Authentication
   |
   +--> Database authorization
   |
   v
PostgreSQL

A connection failure does not necessarily mean the SDK code is incorrect.

6. Python and psycopg

For Python applications, psycopg is a current PostgreSQL client library.

The basic pattern is:

import psycopg
conn = psycopg.connect(
    "host=myserver.postgres.database.azure.com "
    "port=5432 "
    "dbname=mydatabase "
    "user=myuser "
    "password=<password> "
    "sslmode=require"
)
cursor = conn.cursor()
cursor.execute(
    "SELECT id, name FROM products WHERE category = %s",
    ("AI",)
)
rows = cursor.fetchall()
for row in rows:
    print(row)
cursor.close()
conn.close()

The important concepts are:

1. Create a connection.
2. Create a cursor.
3. Execute SQL.
4. Retrieve results.
5. Commit changes when appropriate.
6. Close resources.

Microsoft’s current Python guidance uses psycopg and demonstrates parameterized SQL through cursor.execute(). (Microsoft Learn)

7. Parameterized Queries

One of the most important development practices is to avoid constructing SQL by concatenating user input.

Avoid:

name = request.args["name"]
sql = "SELECT * FROM products WHERE name = '" + name + "'"
cursor.execute(sql)

This can expose the application to SQL injection.

Instead, use parameters:

cursor.execute(
    "SELECT * FROM products WHERE name = %s",
    (name,)
)

The database driver handles the parameter separately from the SQL statement.

Why this matters

Parameterized queries provide:

• Better security
• Safer handling of user input
• Cleaner code
• Better separation between SQL and data

Exam clue

If the question says:

“The application accepts user-provided values and must prevent SQL injection.”

The answer should generally involve parameterized queries, not string concatenation.

8. C#/.NET and Npgsql

For .NET applications, Npgsql is the commonly recommended PostgreSQL ADO.NET data provider.

(Microsoft Learn)

Install it using:

dotnet add package Npgsql

A basic example is:

using Npgsql;
var connectionString =
    "Host=myserver.postgres.database.azure.com;" +
    "Port=5432;" +
    "Database=mydatabase;" +
    "Username=myuser;" +
    "Password=<password>;" +
    "SSL Mode=Require;";
await using var connection =
    new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var command =
    new NpgsqlCommand(
        "SELECT id, name FROM products WHERE category = @category",
        connection);
command.Parameters.AddWithValue("category", "AI");
await using var reader =
    await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine(
        $"{reader.GetInt32(0)} - {reader.GetString(1)}");
}

Notice the use of:

@category

instead of concatenating a value into the SQL string.

9. JDBC for Java Applications

Java applications commonly use the PostgreSQL JDBC driver.

A conceptual example is:

String url =
    "jdbc:postgresql://myserver.postgres.database.azure.com:5432/mydatabase"
    + "?sslmode=require";
Connection connection =
    DriverManager.getConnection(
        url,
        username,
        password);
PreparedStatement statement =
    connection.prepareStatement(
        "SELECT id, name FROM products WHERE category = ?");
statement.setString(1, "AI");
ResultSet results = statement.executeQuery();
while (results.next()) {
    System.out.println(results.getString("name"));
}

The important pattern is:

Connection
    ↓
PreparedStatement
    ↓
Parameters
    ↓
executeQuery()
    ↓
ResultSet

Exam tip

If you see:

PreparedStatement

think:

Parameterized SQL and protection against SQL injection.

10. Node.js and the pg Package

Node.js applications can use the PostgreSQL pg package.

Conceptually:

const { Client } = require("pg");
const client = new Client({
    host: "myserver.postgres.database.azure.com",
    port: 5432,
    database: "mydatabase",
    user: "myuser",
    password: "<password>",
    ssl: true
});
await client.connect();
const result = await client.query(
    "SELECT id, name FROM products WHERE category = $1",
    ["AI"]
);
console.log(result.rows);
await client.end();

Notice that PostgreSQL parameters use placeholders such as:

$1
$2
$3

rather than constructing SQL dynamically.

11. Querying Data

Applications can use the client library to execute standard PostgreSQL SQL.

For example:

SELECT id, name, price
FROM products
WHERE category = 'AI'
ORDER BY price DESC;

The client library sends the SQL statement to PostgreSQL and returns the results to the application.

A typical workflow is:

Build SQL
   ↓
Bind parameters
   ↓
Execute command
   ↓
Database processes query
   ↓
Return rows
   ↓
Application processes rows

12. Executing INSERT, UPDATE, and DELETE

SDK/client libraries aren’t limited to SELECT.

They can execute data modification statements.

INSERT

INSERT INTO products (name, category, price)
VALUES ($1, $2, $3);

UPDATE

UPDATE products
SET price = $1
WHERE id = $2;

DELETE

DELETE FROM products
WHERE id = $1;

Applications must properly handle transactions for operations where multiple changes need to succeed or fail together.

13. Transactions

A transaction groups multiple database operations into a logical unit.

For example:

BEGIN
   |
   +--> INSERT order
   |
   +--> INSERT order item
   |
   +--> UPDATE inventory
   |
COMMIT

If something fails:

BEGIN
   |
   +--> INSERT order
   |
   +--> INSERT order item
   |
   +--> ERROR
   |
ROLLBACK

This provides atomicity.

Typical transaction pattern

with psycopg.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        cursor.execute(
            "INSERT INTO orders(customer_id) VALUES (%s)",
            (customer_id,)
        )
        cursor.execute(
            "UPDATE inventory SET quantity = quantity - %s "
            "WHERE product_id = %s",
            (quantity, product_id)
        )

If an exception occurs within the transaction context, the transaction can be rolled back rather than leaving partially applied changes.

14. Connection Pooling

Opening a new database connection for every request can be inefficient.

Consider a web API receiving 1,000 requests:

Request 1 → Open connection → Query → Close
Request 2 → Open connection → Query → Close
Request 3 → Open connection → Query → Close
...

This creates unnecessary connection overhead.

A connection pool instead maintains a set of reusable connections:

                Connection Pool
              +------------------+
Request ----->| Connection 1     |
Request ----->| Connection 2     |
Request ----->| Connection 3     |
Request ----->| Connection 4     |
              +------------------+

The application:

1. Requests a connection.
2. Uses it.
3. Returns it to the pool.

Benefits

Connection pooling can:

• Reduce connection establishment overhead
• Improve application performance
• Handle concurrent workloads more efficiently
• Reduce unnecessary database connection churn

Important distinction

A connection pool is not the same thing as a database transaction.

A pool manages reusable connections.

A transaction manages the atomicity of database operations.

15. Asynchronous Database Operations

Modern applications often use asynchronous database operations.

For example, .NET applications can use:

await connection.OpenAsync();

and:

await command.ExecuteReaderAsync();

This helps applications avoid blocking a thread while waiting for database I/O.

This can be particularly important for:

• Web APIs
• Serverless applications
• High-concurrency applications
• AI applications processing many requests

16. Handling Query Results

A database query may return:

• Zero rows
• One row
• Many rows

Applications should not assume that a result always exists.

For example:

SELECT id, name
FROM products
WHERE id = $1;

The application should handle the case where no matching product exists.

For multiple rows, the application generally iterates over a cursor, reader, or result set.

17. Avoid Retrieving More Data Than Necessary

A common application mistake is:

SELECT *
FROM products;

when the application only needs two columns.

Prefer:

SELECT id, name
FROM products;

Similarly, use filtering:

SELECT id, name
FROM products
WHERE category = $1;

rather than retrieving an entire table and filtering the results in application code.

This reduces:

• Data transferred over the network
• Application memory usage
• Database processing in some scenarios
• Unnecessary work

18. Use the Database to Perform Database Work

Suppose an application needs the average product price.

Avoid:

Retrieve every product
       ↓
Send all products to application
       ↓
Calculate average in application

Prefer:

SELECT AVG(price)
FROM products;

The database is optimized to perform database operations.

Other useful SQL operations include:

COUNT()
SUM()
AVG()
MIN()
MAX()
GROUP BY
ORDER BY
JOIN

This is particularly relevant to AI applications because unnecessarily moving large datasets into application memory can become expensive and slow.

19. Stored Procedures and Functions

PostgreSQL supports database-side functions and procedures.

An application can invoke them through its client library.

For example:

SELECT calculate_customer_score($1);

This can be useful when business or database logic is intentionally centralized in PostgreSQL.

However, don’t automatically move all application logic into database functions.

Consider:

• Maintainability
• Performance
• Security
• Deployment complexity
• Transaction requirements
• Whether the logic belongs in the database or application

20. Connection Lifecycle

A reliable application should carefully manage database resources.

The general lifecycle is:

Create/acquire connection
          ↓
Open connection
          ↓
Create command/cursor
          ↓
Execute SQL
          ↓
Process results
          ↓
Commit or rollback
          ↓
Close/release resources

Using language-supported resource-management features is preferable.

For example, C# uses:

await using

and Python can use:

with

This reduces the chance of leaking connections or other resources.