ExamFinch.com My Study

Study guide

6.1 Azure Database for PostgreSQL: Connect & Query with SDKs

Practice this exam

Exam AI-200: Developing AI Cloud Solutions on Azure Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current Exam Facts 1.2 Audience Profile & Study Path

2 Chapter 1: Container Registry & App Service Hosting

1.1 ACR: Build, Store, Version & Manage Images 1.2 ACR Tasks: Build & Run Images 1.3 Deploy Containers to Azure App Service

3 Chapter 2: Azure Container Apps & KEDA Scaling

2.1 Azure Container Apps: Environments, Config & Revisions 2.2 KEDA Event-Driven Scaling in Container Apps

4 Chapter 3: AKS Manifests & Container Troubleshooting

3.1 Deploy & Manage Apps on AKS with Manifests 3.2 Monitor & Troubleshoot AKS and Container Apps

5 Chapter 4: Cosmos DB for NoSQL Fundamentals

4.1 Cosmos DB for NoSQL: SDK Connect & Queries 4.2 Optimize RUs: Indexing Policies & Consistency Levels

6 Chapter 5: Cosmos DB Vectors & Change Feed

5.1 Embeddings & Vector Similarity Search in Cosmos DB 5.2 Change Feed Processor for New/Updated Items

7 Chapter 6: Azure PostgreSQL Foundations for AI

6.1 Azure Database for PostgreSQL: Connect & Query with SDKs 6.2 Schema Design, Data Types & Indexing Strategies 6.3 pgvector Indexing: Latency & Compute Overhead

8 Chapter 7: PostgreSQL Vector Workloads & RAG

7.1 Configure Compute, Memory & Storage for Vector Workloads 7.2 Vector Search, Semantic Retrieval & RAG with Metadata Filters 7.3 Connection Optimization for Throughput & Latency

9 Chapter 8: Azure Managed Redis for AI Solutions

8.1 Azure Managed Redis: Caching, Expiration & Invalidation 8.2 Redis Vector Indexing for Similarity Search

10 Chapter 9: Service Bus & Event Grid for AI Pipelines

9.1 Service Bus Queues & Message Processing 9.2 Topics, Subscriptions & Dead-Letter Queues 9.3 Event Grid: Custom Events & Filters 9.4 Event Grid Retries & Event-Driven Workflows

11 Chapter 10: Azure Functions for Serverless AI Back Ends

10.1 Azure Functions: Triggers & Bindings 10.2 Build Serverless APIs with Functions 10.3 Configure & Deploy Function Apps

12 Chapter 11: Key Vault & App Configuration

11.1 Azure Key Vault: Secret Storage & Retrieval 11.2 Key Vault Secret Rotation Patterns 11.3 Azure App Configuration for App Settings

13 Chapter 12: OpenTelemetry & KQL Observability

12.1 Distributed Tracing with OpenTelemetry SDKs 12.2 Correlating Traces into Azure Monitoring 12.3 Write KQL Queries to Analyze Logs 12.4 KQL for Metrics & Troubleshooting Scenarios

6.1 Azure Database for PostgreSQL: Connect & Query with SDKs

Key Takeaways Explain This Topic AI

Azure Database for PostgreSQL Flexible Server is the production deployment model for AI workloads; Single Server is retired for new capacity

Always enable SSL/TLS (sslmode=require or verify-full) when connecting from applications—Azure enforces encrypted connections by default on Flexible Server

psycopg (sync) and asyncpg (async) are the primary Python drivers; both consume a connection string with host, database, user, password, and SSL parameters

Use connection pooling (PgBouncer on Flexible Server, or client-side pools) because embedding and RAG services open many short-lived queries

Prefer parameterized queries and prepared statements—never interpolate user text into SQL when retrieving vectors or metadata

Last updated: July 2026

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:

  • Parameter Role
  • host Flexible Server FQDN
  • port Usually 5432 (or the custom port you configured)
  • dbname Application database (not azure_maintenance )
  • user Often adminuser or adminuser@servername depending on auth mode
  • password Entra ID token or password from Key Vault—never hard-code in source
  • sslmode require , verify-ca , or verify-full for production

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:

Parameterized queries ( %s / bound parameters)—protects against injection when filtering by user or document IDs.

Context managers so connections and cursors close even on exceptions.

Connection pools ( psycopg_pool.ConnectionPool ) when a web API issues many short queries per request.

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.