Task 2.2: Develop AI solutions by using Azure Database for PostgreSQL

• ACreate an ivfflat index with lists set to rows / 1000

  Incorrect

  IVFFlat has the same 2,000-dimension limit for indexing as HNSW.

• BCreate a diskann index with product_quantized set to true

  Correct

  With product quantization, DiskANN (pg_diskann 0.6 and later) can index vectors of up to 16,000 dimensions.

• CRecreate the HNSW index with m = 32 and ef_construction = 128

  Incorrect

  Build parameters don't lift the 2,000-dimension limit for HNSW indexes.

• DChange the column type to vector without a dimension

  Incorrect

  Dropping the declared dimension doesn't give any index type a way to handle these 3,072-dimension vectors.

pgvector stores vectors of any size but can index only up to 2,000 dimensions with IVFFlat or HNSW. On flexible server, DiskANN with product quantization supports high-dimension embeddings and keeps more of the index in memory.

• ARaise hnsw.ef_search for the session

  Incorrect

  ef_search tunes recall for an index that's already used; it doesn't make the planner pick a mismatched index.

• BChange the query to use the <#> operator

  Incorrect

  <#> is negative inner product, which needs vector_ip_ops, so it wouldn't match an L2 index either.

• CCreate the index with vector_cosine_ops, which matches the <=> operator

  Correct

  The database considers an index only when its access function matches the distance operator; <=> is cosine distance.

• DRun ANALYZE on the table so the planner refreshes its statistics

  Incorrect

  Fresh statistics don't fix an operator class that doesn't match the query's operator.

Each distance has its own operator and operator class: <-> with vector_l2_ops, <=> with vector_cosine_ops and <#> with vector_ip_ops. The same rule applies to IVFFlat, HNSW and DiskANN indexes.

• AEnable pgbouncer.enabled on the Burstable server and keep connecting on port 5432

  Incorrect

  The Burstable tier doesn't support built-in PgBouncer, and the pooler isn't on port 5432 anyway.

• BRaise max_connections and keep each function opening its own connection

  Incorrect

  PostgreSQL uses a process per connection, so piling up connections makes the resource problem worse.

• CSet pgbouncer.pool_mode to statement so each query gets its own connection

  Incorrect

  Statement pooling breaks multi-statement transactions and still needs a tier where PgBouncer runs.

• DMove to General Purpose, enable pgbouncer.enabled and connect on port 6432

  Correct

  Built-in PgBouncer runs only on General Purpose and Memory Optimized servers and listens on port 6432 on the same host name.

PgBouncer is built into flexible server on the General Purpose and Memory Optimized tiers, defaults to transaction pooling and accepts connections on port 6432. Changing to Burstable loses the feature.

• AAdd pgvector to shared_preload_libraries, restart the server and run CREATE EXTENSION pgvector

  Incorrect

  The documented step for this extension is allowlisting through azure.extensions, and its name is vector, not pgvector.

• BAllowlist vector in azure.extensions, then CREATE EXTENSION vector in each database

  Correct

  The extension must be allowlisted first, its name is vector, and it has to be created in every database that uses it.

• CRun CREATE EXTENSION vector once in the postgres database

  Incorrect

  An extension is created per database; creating it in postgres doesn't make it available in the other databases.

• DInstall the azure_ai extension, which includes the vector type

  Incorrect

  azure_ai calls Azure AI services such as embeddings; the vector type comes from the vector extension.

Flexible server only lets you create allowlisted extensions. Allow the extension through azure.extensions, check it with SHOW azure.extensions, then run CREATE EXTENSION vector in each target database.

• ARaise l_value_ib on the index to build a higher-quality graph

  Incorrect

  l_value_ib is the build-time search list size; a higher value gives a better index but makes the build slower, not faster.

• BSet diskann.iterative_search to off

  Incorrect

  This is a query-time search setting; it doesn't affect index build time.

• CRaise maintenance_work_mem and allow parallel workers for the build

  Correct

  CREATE INDEX uses maintenance_work_mem and parallel workers when resources allow, which shortens DiskANN builds.

• DCreate the index before loading the data

  Incorrect

  Microsoft recommends loading data first; building the index after the load is faster and gives a better layout.

For faster DiskANN builds, give CREATE INDEX more memory through maintenance_work_mem and enough parallel maintenance workers, then scale back after the build. The leader process doesn't take part in parallel builds.

• APremium SSD sized at 32 TiB to reach its top IOPS tier

  Incorrect

  Premium SSD ties IOPS to disk size, which is exactly the overprovisioning the team wants to avoid.

• BPremium SSD at 512 GiB, scaling the server to more vCores during the job window

  Incorrect

  More vCores raise the server's IOPS ceiling, but a 512 GiB Premium SSD disk is still provisioned for about 2,300 IOPS.

• CPremium SSD v2, raising provisioned IOPS only for the job window

  Correct

  Premium SSD v2 lets you adjust capacity, IOPS and throughput independently at any time, up to 80,000 IOPS from 160 GiB.

• DMove the server to the Burstable tier for the job window

  Incorrect

  Burstable has the lowest IOPS limits and isn't meant for sustained heavy load.

With Premium SSD, IOPS grow with the disk size you provision. Premium SSD v2 has no fixed sizes and lets you change IOPS and throughput independently of capacity, within the limits of the server's compute size.

• AIt gets a token for https://management.azure.com/.default and passes it as the password

  Incorrect

  A management-plane token is for Azure Resource Manager, not for signing in to the database.

• BIt reads the server admin password from an environment variable set from a Container Apps secret

  Incorrect

  That still stores a password, which the requirement rules out.

• CIt gets a token with DefaultAzureCredential for https://ossrdbms-aad.database.windows.net/.default and passes it as the password

  Correct

  Flexible server accepts a Microsoft Entra access token in place of a password for an Entra-mapped role.

• DIt sets sslmode=disable so that the managed identity is passed through the connection

  Incorrect

  sslmode only controls TLS for the connection, and the samples use sslmode=require; it plays no part in passing an identity.

With Microsoft Entra authentication, the client requests an access token for the Azure Database for PostgreSQL resource and uses it as the password. Tokens expire, so long-running apps fetch a fresh one for new connections.

• ADeclare the embedding column as vector(1536)

  Correct

  A vector column needs its dimensions declared before it can be indexed.

• BDeclare the embedding column as vector with no dimension so models can change

  Incorrect

  A vector column without dimensions can't be indexed.

• CStore tenant ID and document type in their own typed columns next to the embedding

  Correct

  Metadata in regular columns can be filtered in the WHERE clause and indexed, alongside the vector search.

• DStore the embedding as a JSON text array

  Incorrect

  Text can't use the vector operators or a vector index.

• EStore each embedding in a separate table per tenant

  Incorrect

  This multiplies tables and indexes; a tenant column with a filter serves the requirement.

Use the vector type with explicit dimensions for embeddings so that an IVFFlat, HNSW or DiskANN index can be built, and keep filterable metadata in ordinary columns so a query can filter and order by distance in one statement.

• ADrop the IVFFlat index so that queries run an exact search

  Incorrect

  Without an index the search is exact and recall is perfect, but every query scans the whole table, and the index is no longer used.

• BRaise ivfflat.probes from its default of 1 to about 50 (lists / 10)

  Correct

  probes sets how many lists a search visits; more lists raise recall at some cost in speed, and lists / 10 is the suggested start for tables up to a million rows.

• CSet hnsw.ef_search to 100 for the application's sessions

  Incorrect

  ef_search sizes the candidate list of HNSW indexes; an IVFFlat search ignores it.

• DSet ivfflat.probes to 500 so that every list is searched

  Incorrect

  With probes equal to lists, the search becomes an exact search over every list, and the planner stops using the index.

IVFFlat clusters vectors into lists; a search compares the query with the cluster centers and scans only the closest lists, one by default. Raising ivfflat.probes trades speed for recall, starting at lists / 10 up to a million rows and sqrt(lists) above. Probing every list is equivalent to a brute-force search, so the planner doesn't use the index then.

• ARank and index by inner product

  Correct

  For vectors normalized to length 1, Microsoft suggests inner product for the best performance.

• BRank and index by cosine distance

  Incorrect

  This ranks normalized vectors the same way, but Microsoft points to inner product as the faster choice for them.

• CRank and index by Euclidean (L2) distance

  Incorrect

  L2 distance works, but it isn't the recommended choice for normalized embeddings.

• DRank by cosine distance without an index, so every query is exact

  Incorrect

  Exact search without an index gives perfect recall at the expense of performance, which is what the team wants to avoid.

Microsoft's pgvector performance guidance recommends inner product for embeddings normalized to length 1, such as OpenAI embeddings. pgvector computes it with <#>, which returns the negative inner product.

• ARaise hnsw.ef_search for the session so that more candidates are compared

  Incorrect

  hnsw.ef_search tunes searches on HNSW indexes; it has no effect on a diskann index.

• BRebuild the index with product_quantized set to false so it holds 40 million full vectors in memory

  Incorrect

  Product quantization is what keeps a large index efficient; turning it off gives that up rather than reranking.

• CSend the top 10 to a separate ranking model instead of computing distances

  Incorrect

  The documented technique uses exact vector distances, not a ranking model.

• DFetch the top 50 through the index in a subquery, then order those by exact distance and keep 10

  Correct

  Reranking a small approximate candidate set with full-precision distances restores accuracy cheaply.

Product quantization compresses vectors so more of the index fits in memory, at some cost to precision. Two-step reranking queries the index for an approximate top 50, then reorders them by their actual distance.

• ARaise timeout_ms so that each call can wait longer for Azure OpenAI

  Incorrect

  Waiting longer per call doesn't lower the request rate that's being throttled.

• BSet throw_on_error to false and run the single UPDATE again

  Incorrect

  Rows whose calls fail would be left without useful embeddings, and the request rate stays just as high.

• CRaise the server's max_connections before running the UPDATE

  Incorrect

  Connection limits on PostgreSQL have nothing to do with throttling by Azure OpenAI.

• DUpdate rows in batches with a LIMIT, rerunning until none are left, and allow retries with max_attempts

  Correct

  Smaller batches avoid throttling and losing large transactions, and max_attempts retries retryable errors.

The azure_ai extension calls Azure OpenAI from SQL. The tutorials vectorize rows in batches (for example LIMIT 500) and repeat, waiting a minute after a failure; the function's max_attempts and retry_delay_ms arguments control retries.