Develop AI Solutions with Azure Data Management Services · Topic 2 of 3

Azure Database for PostgreSQL and pgvector

AI-200 PostgreSQL: connecting with SDKs, schema and index design, pgvector types and HNSW or IVFFlat indexes, sizing for vectors and connection pooling.

4 min

In a nutshell

Allow and create the vector extension; store embeddings as vector(n).
Operators: <-> L2, <=> cosine, <#> negative inner product.
HNSW: m, ef_construction, hnsw.ef_search. IVFFlat: lists, ivfflat.probes.

What it is

Azure Database for PostgreSQL (flexible server) is a managed PostgreSQL service. With the pgvector extension it stores embeddings in a vector column and runs similarity search in SQL, so relational data and vectors live together. This makes it a natural choice for RAG over business data.

How it works

Connecting and querying

Connect with a PostgreSQL driver, such as psycopg for Python, using the server name, database, user and either a password or a Microsoft Entra ID token.
Use parameterized queries to avoid SQL injection.
Extensions must be allowed on the server before you create them; then run CREATE EXTENSION vector;.

Schema design

Store an embedding as vector(n), where n is the number of dimensions of your embedding model.
Keep the text chunk, its embedding and metadata (source, tenant, date, category) in the same table, so you can filter and cite.
Choose appropriate data types and add normal B-tree indexes on columns you filter by.

Similarity search

pgvector provides distance operators:

Operator	Distance
`<->`	Euclidean (L2)
`<=>`	Cosine distance
`<#>`	Negative inner product

A typical RAG query: SELECT content FROM chunks WHERE tenant_id = $1 ORDER BY embedding <=> $2 LIMIT 5; With an approximate index, pgvector applies the filter after the index scan, so a very selective filter can return fewer rows than the LIMIT. Iterative index scans, or a B-tree index on the filter column for an exact search over few rows, address this.

Vector indexes

Index	How it works	Key settings
HNSW	Graph-based approximate search; good speed and recall	Build: `m`, `ef_construction`. Query: `hnsw.ef_search`
IVFFlat	Clusters vectors into lists and searches the nearest lists	Build: `lists`. Query: `ivfflat.probes`
DiskANN (pg_diskann extension)	Graph-based approximate search for very large datasets; optional product quantization shrinks the index so more of it fits in memory	Build: `max_neighbors`, `l_value_ib`. Query: `diskann.l_value_is`

Without an index, pgvector does an exact scan: accurate but slow on large tables. pgvector defaults are m 16, ef_construction 64, hnsw.ef_search 40 and ivfflat.probes 1. HNSW and IVFFlat index vector columns of up to 2,000 dimensions (halfvec up to 4,000). Raising ef_search or probes improves recall at the cost of speed. Index builds go faster with more maintenance_work_mem.

Sizing compute, memory and storage

Choose a compute tier: Burstable for dev and light loads, General Purpose for most production, Memory Optimized for large indexes that should stay in memory.
HNSW indexes perform best when they fit in memory.
Storage performance (IOPS) affects index builds and scans.

Connection optimization

Opening a database connection is expensive. Use connection pooling.
Azure Database for PostgreSQL flexible server includes a built-in PgBouncer pooler, turned on with the pgbouncer.enabled server parameter; apps connect to port 6432 instead of 5432. It is supported on the General Purpose and Memory Optimized tiers, not Burstable.
Reuse connections in the app, and keep queries short.

A worked example: a support assistant searches 2 million chunks per tenant. The team adds a tenant_id filter column with a B-tree index, an HNSW index on the embedding column, enables PgBouncer, and moves to a Memory Optimized tier so the index stays in memory.

Exam tip

If recall is too low with an HNSW index, raise hnsw.ef_search for queries. If index build is slow, raise maintenance_work_mem.

How the exam asks about this

"Which operator returns cosine distance in pgvector?" <=>.
"Many short-lived connections exhaust the server." Enable PgBouncer connection pooling.
"Search only one customer's documents by similarity." Filter on a tenant column and order by vector distance.

What to remember for the exam

Allow and create the vector extension; store embeddings as vector(n).
Operators: <-> L2, <=> cosine, <#> negative inner product.
HNSW: m, ef_construction, hnsw.ef_search. IVFFlat: lists, ivfflat.probes.
Use metadata filters for RAG; index filter columns.
Use PgBouncer for connection pooling; size memory for vector indexes.

Watch out

An IVFFlat index built on an empty or tiny table gives poor clusters. Build it after loading representative data.

Common mistakes

Creating the extension before allowing it on the server. Allow it first.

Using the wrong operator for the index's distance type. Match the operator to the index.

Opening a new connection per request. Pool connections.

Check yourself

Which pgvector operator gives cosine distance?
The <=> operator.
Which setting improves HNSW recall at query time?
hnsw.ef_search.
How do you reduce connection overhead on Azure Database for PostgreSQL flexible server?
Enable the built-in PgBouncer connection pooler and reuse connections.

Topic 2 of 3 in Develop AI Solutions with Azure Data Management Services

Test yourself on Develop AI Solutions with Azure Data Management Services

The full Microsoft AI-200 question bank has 650 questions, with timed practice exams and an explanation for every answer.

Subscribe Try Sample Exam

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.