AI-200 practice: Connect and query Azure Database for PostgreSQL by using SDKs

Question 1

Which Python package provides the client library to connect to Azure Database for PostgreSQL from application code?

Accepted Answer

psycopg with azure-identity for tokens

Answer

azure-storage-blob for object access

Answer

pymongo for MongoDB-style document use

Answer

azure-cosmos for Cosmos DB document use

Question 2

A Python service connects to Azure Database for PostgreSQL flexible server with Microsoft Entra authentication. It gets a token with `DefaultAzureCredential().get_token(...)` and passes the token as the password. Which token scope should it request?

Accepted Answer

https://ossrdbms-aad.database.windows.net/.default

Answer

https://management.azure.com/.default

Answer

https://database.windows.net/.default

Answer

https://cognitiveservices.azure.com/.default

Question 3

A long-running API uses a psycopg connection pool that authenticates to PostgreSQL with an Entra access token fetched once at startup. Roughly an hour after each deployment, every new connection fails with `password authentication failed`, while connections that are already open keep working. What is the fix?

Accepted Answer

Fetch a fresh token each time the pool opens a connection (for example, in a connection factory or password callback), relying on the credential's token cache

Answer

Raise max_connections so the pool never needs to open new connections

Answer

Switch sslmode to disable, because TLS renegotiation invalidates tokens

Answer

Restart the API every 50 minutes from a scheduled job

Question 4

A psycopg 3 function filters chunks by a `tenant_id` value that comes from user input. Which call is safe from SQL injection?

Accepted Answer

cur.execute("SELECT id FROM chunks WHERE tenant_id = %s", (tenant_id,))

Answer

cur.execute(f"SELECT id FROM chunks WHERE tenant_id = '{tenant_id}'")

Answer

cur.execute("SELECT id FROM chunks WHERE tenant_id = '%s'" % tenant_id)

Answer

cur.execute("SELECT id FROM chunks WHERE tenant_id = '{}'".format(tenant_id))

Question 5

A new container image connects to Azure Database for PostgreSQL flexible server with `sslmode=disable` copied from a local Docker Compose file. The connection is rejected with an error saying SSL is off and the server requires secure connections. What should you do?

Accepted Answer

Connect with sslmode=require, or verify-full with the trusted root CA for stronger checks

Answer

Set the server parameter require_secure_transport to OFF

Answer

Connect to port 6432 instead of 5432

Answer

Add the client IP address to the server firewall

Question 6

You use psycopg 3 with the `pgvector` Python package and want to pass NumPy arrays as query parameters and get `vector` columns back as NumPy arrays. What must you call on the connection?

Accepted Answer

register_vector(conn) from pgvector.psycopg

Answer

conn.execute("CREATE EXTENSION vector") on every connection

Answer

psycopg.types.json.Json(embedding) around each parameter

Answer

conn.adapters.register_dumper(bytes, 'vector')

Question 7

An Entra administrator has been configured on a flexible server. A Container App with a user-assigned managed identity named `rag-api-id` now needs to log in to the database through Entra authentication. What must the Entra administrator do in the database?

Accepted Answer

Run SELECT * FROM pgaadauth_create_principal('rag-api-id', false, false); and then GRANT the privileges the app needs

Answer

Run CREATE USER "rag-api-id" WITH PASSWORD '<client-secret>'

Answer

Add rag-api-id as a second Entra administrator so that it can log in

Answer

Nothing; every identity in the tenant can log in once Entra authentication is enabled

Question 8

A Python ingestion job inserts 2 million chunk rows (text plus a 1536-dimension embedding) into PostgreSQL. It currently runs one `INSERT` and one `commit()` per row, and the job takes hours. Which two changes give the biggest speed-up? Select two.

Accepted Answer

Stream rows with COPY (in psycopg 3, cur.copy("COPY chunks (text, embedding) FROM STDIN"))

Accepted Answer

Batch the inserts (for example executemany) inside fewer, larger transactions instead of committing every row

Answer

Open a new connection for each row so that inserts run in parallel

Answer

Create the HNSW index before loading so that it can be built incrementally

Answer

Set sslmode=disable to avoid encryption overhead

Question 9

An export job runs `SELECT id, text, embedding FROM chunks` over 5 million rows with psycopg 3 and runs out of memory, because the client buffers the whole result set before the loop starts. How should you read the rows?

Accepted Answer

Use a server-side (named) cursor, such as conn.cursor(name="export"), and iterate so rows arrive in batches (itersize)

Answer

Call cur.fetchall() and then delete the list after processing

Answer

Add LIMIT 5000000 to the query

Answer

Raise work_mem on the server

Question 10

You connect with password authentication to flexible server `pg-rag-prod` as administrator `ragadmin`. Which connection parameters are correct?

Accepted Answer

host=pg-rag-prod.postgres.database.azure.com port=5432 user=ragadmin

Answer

host=pg-rag-prod.postgres.database.azure.com port=5432 user=ragadmin@pg-rag-prod

Answer

host=pg-rag-prod.database.windows.net port=1433 user=ragadmin

Answer

host=pg-rag-prod.postgres.azure.net port=10000 user=ragadmin

10 questions