6.1 Azure Database for PostgreSQL: Connect & Query with SDKs

6.1 Azure Database for PostgreSQL: Connect & Query with SDKs

Quick Answer: Use Azure Database for PostgreSQL — Flexible Server with an SSL-required connection string. In Python, connect with psycopg (sync) or asyncpg (async), enable pooling, and run parameterized SQL. These patterns are the foundation for storing embeddings, metadata, and RAG retrieval results on the AI-200 exam.

AI solutions on Azure often need a relational store that also holds vector embeddings. Azure Database for PostgreSQL is that store: it gives you managed Postgres, enterprise networking, and the pgvector extension for similarity search. Before you design schemas or tune HNSW indexes, you must connect and query correctly with SDKs.

Flexible Server vs legacy Single Server

Microsoft positions Flexible Server as the current, fully supported deployment option. Flexible Server gives you:

• Zone-redundant high availability and configurable maintenance windows
• Ability to stop/start the server for cost control in non-production
• Built-in PgBouncer connection pooling on supported tiers
• Native support for extensions such as vector (pgvector), uuid-ossp, and pg_trgm

Single Server is retired for new deployments. On exam scenarios that mention “Azure Database for PostgreSQL,” assume Flexible Server unless the question explicitly says otherwise. When you provision Flexible Server, choose compute tier (Burstable, General Purpose, Memory Optimized), storage size, and PostgreSQL major version that supports the extensions your AI workload needs.

Connection strings and SSL

Azure issues a host name like myserver.postgres.database.azure.com. A typical connection URI looks like:

postgresql://myadmin@myserver:5432/ai_app?sslmode=require

Key parameters:

Flexible Server requires SSL for client connections by default. If your SDK omits TLS, the handshake fails. In production AI services, prefer sslmode=verify-full with the Azure CA bundle so you reject man-in-the-middle hosts. Store secrets in Azure Key Vault and inject them at runtime (App Service settings, Container Apps secrets, or AKS CSI driver).

Network options matter for AI backends:

• Public access with firewall rules for known egress IPs (quick demos)
• Private access via Private Link / VNet integration (recommended for production RAG APIs)

Exam tip: a Function App or Container App that “cannot connect” after moving behind a VNet usually needs the Flexible Server private endpoint and correct DNS—not a different SDK.

SDK patterns: psycopg

psycopg (psycopg3) is the standard synchronous Python driver. A minimal pattern:

import psycopg

conninfo = (
    "host=myserver.postgres.database.azure.com "
    "dbname=ai_app user=myadmin password=*** "
    "sslmode=require"
)

with psycopg.connect(conninfo) as conn:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, content FROM documents WHERE tenant_id = %s",
            (tenant_id,),
        )
        rows = cur.fetchall()

Practices that score on the exam and in production:

1. Parameterized queries (%s / bound parameters)—protects against injection when filtering by user or document IDs.
2. Context managers so connections and cursors close even on exceptions.
3. Connection pools (psycopg_pool.ConnectionPool) when a web API issues many short queries per request.
4. Server-side cursors for streaming large result sets without loading millions of rows into memory.

For embedding writes, batch inserts with executemany or COPY reduce round-trips when you hydrate a vector table from a document pipeline.

SDK patterns: asyncpg

asyncpg is the high-performance async driver used with FastAPI, aiohttp, and other async Azure workloads:

import asyncpg

pool = await asyncpg.create_pool(
    host="myserver.postgres.database.azure.com",
    database="ai_app",
    user="myadmin",
    password=secret,
    ssl="require",
    min_size=2,
    max_size=10,
)

async with pool.acquire() as conn:
    row = await conn.fetchrow(
        "SELECT embedding FROM chunks WHERE id = $1",
        chunk_id,
    )

Notice asyncpg uses $1, $2 placeholders (not %s). Mixing placeholder styles is a common bug when migrating code between drivers. Async pools shine when your AI orchestration layer awaits embedding API calls and database I/O concurrently—exactly the pattern for retrieval-augmented generation (RAG) services on Azure.

Querying for AI workloads

After connect, typical SDK operations include:

• DDL: create tables, enable CREATE EXTENSION vector, add indexes (covered in later sections)
• DML: insert documents, chunk text, store vector(n) embeddings
• Similarity search: ORDER BY embedding <=> $1 LIMIT k once pgvector is enabled
• Hybrid filters: combine vector distance with B-tree predicates on tenant_id, project_id, or timestamps

Always set a sensible statement timeout for similarity queries so a bad plan cannot exhaust Flexible Server CPU. Log slow queries with pg_stat_statements when diagnosing latency before you jump to index changes.

Authentication choices

Password auth is fine for labs. For production Azure AI apps, prefer Microsoft Entra ID authentication to Flexible Server: your app obtains an access token and passes it as the password. Rotate tokens on a schedule; never commit them. Pair Entra auth with managed identities on App Service, Functions, or AKS workloads to eliminate long-lived secrets.

Putting it together

On the AI-200 exam, connecting correctly means: Flexible Server + SSL connection string + SDK of choice (psycopg or asyncpg) + parameterized SQL + pooling. Those habits unlock the schema and pgvector indexing work in the next sections.