AI-200 practice: Implement connection optimization to improve throughput and minimize latency

Question 1

A serverless AI function opens a connection to Azure Database for PostgreSQL on each short-lived invocation, issues one or two simple queries, and closes the connection. Under load, the database reports connection exhaustion even though most connections are idle most of the time.

What should you implement?

Accepted Answer

A PgBouncer-style connection pooler in transaction pooling mode that returns server connections to the pool after each short transaction

Answer

A connection pooler in session pooling mode that keeps a dedicated server connection for the entire lifetime of each client session

Answer

A much higher max_connections setting on PostgreSQL with no pooler, accepting more concurrent sockets to avoid any pooling overhead on short queries

Answer

A single permanently open Npgsql connection shared by all concurrent function invocations through a static singleton in process memory

Question 2

Your Azure Database for PostgreSQL instance is under heavy connection churn from a serverless AI application that opens many short-lived connections, causing latency spikes and occasional connection refusals. What should you implement to improve throughput?

Accepted Answer

PgBouncer or Azure pooler in transaction mode

Answer

Raise max_connections high with no pooler

Answer

Open a new database on every app connect

Answer

Disable SSL to cut connection handshake cost

Question 3

Your application connects to Azure Database for PostgreSQL and experiences elevated latency due to establishing a new TCP+SSL handshake for every short-lived query. Besides pooling, what connection-level optimization helps?

Accepted Answer

Reuse persistent or pooled connections across requests

Answer

Disable TCP keepalives on every database connection

Answer

Force SSL renegotiation on each individual query

Answer

Use SSL mode disable for all application traffic

Question 4

In Azure Database for PostgreSQL, you are seeing high query latency under heavy concurrency during vector search operations. You want to enable connection pooling to minimize connection overhead. Which built-in component should you use?

Accepted Answer

PgBouncer connection pooler

Answer

PostgreSQL Foreign Data Wrapper

Answer

Azure Traffic Manager endpoint

Answer

Azure Front Door backend pool

Question 5

You enable the built-in PgBouncer on a flexible server by setting `pgbouncer.enabled` to `true`. Which port should applications use so that their connections go through the pooler?

Accepted Answer

6432

Answer

5432

Answer

6380

Answer

1433

Question 6

An app connects through PgBouncer in transaction pooling mode. It runs `SET hnsw.ef_search = 100;` once after connecting, then runs search queries in later transactions. Recall is inconsistent: some queries behave as if ef_search were still 40. What should you change?

Accepted Answer

Run SET LOCAL hnsw.ef_search = 100 inside the same transaction as each search query (or set it as a role or database default)

Answer

Switch PgBouncer to statement pooling mode

Answer

Increase PgBouncer's default_pool_size

Answer

Run the SET command twice to make sure it applies

Question 7

A FastAPI service calls `psycopg.connect(...)` at the start of every request and closes the connection at the end. With Entra authentication and TLS, connection setup adds 30–60 ms to each request. What should you implement in the application?

Accepted Answer

A single psycopg_pool.ConnectionPool created at startup, with each request borrowing a connection through with pool.connection() as conn:

Answer

A new connection in a background thread for each request

Answer

One global connection shared by all concurrent requests without locking

Answer

Disable TLS to shorten the handshake

Question 8

A retrieval API in West Europe queries a flexible server in North Europe. Each request runs four short queries, and p50 latency is dominated by network time. Which two changes reduce latency the most? Select two.

Accepted Answer

Place the API and the database in the same region (ideally the same availability zone), for example with a read replica near the API

Accepted Answer

Cut round trips by combining the four queries into one statement (for example with joins or WHERE id = ANY(%s)) and reusing pooled connections

Answer

Raise max_connections to 5000

Answer

Open a fresh connection for each of the four queries

Answer

Raise shared_buffers

Question 9

A Container App scales out to 40 replicas, and each replica's SQLAlchemy pool allows up to 50 connections (pool_size plus max_overflow). The flexible server's max_connections is 1,700. Under peak load, new connections fail with `remaining connection slots are reserved`. What is the best fix?

Accepted Answer

Make total demand fit the server: reduce each replica's pool size, cap maxReplicas, and route through PgBouncer in transaction mode so many client connections share fewer server connections

Answer

Raise max_connections to 10,000

Answer

Restart the database during peak hours

Answer

Set pool_size to 0 so that connections aren't pooled

Question 10

After retrieving 20 chunk IDs from a vector search, the service fetches each chunk's parent document title with a separate `SELECT title FROM documents WHERE id = %s` call. The 20 sequential round trips add about 40 ms. How should you rewrite the lookup?

Accepted Answer

Run a single query, SELECT id, title FROM documents WHERE id = ANY(%s), passing the list of IDs (or join documents into the original vector query)

Answer

Run the 20 queries over 20 separate new connections at once

Answer

Cache each title in a Python dictionary that is never invalidated

Answer

Add an index on documents.title

11 questions