Azpg pgvector rag

pgvector RAG on Azure Database for PostgreSQL Flexible Server

Build vector / RAG search on Flexible Server using pgvector for storage and similarity, the azure_ai extension for in-database Azure OpenAI embeddings, and DiskANN / HNSW / IVFFlat for approximate nearest-neighbour indexing.

This skill is read-write. Inspection is safe and runs automatically; anything that mutates the server or database (allow-listing extensions, CREATE EXTENSION, CREATE INDEX, bulk embedding) is a guarded write — present a plan, get explicit confirmation, and run the bundled scripts/ (transaction-wrapped, --dry-run capable) rather than free-handing destructive SQL. See Safety protocol.

All SQL and CLI in this skill is verified against Microsoft Learn (azure_ai, pgvector, pg_diskann on Flexible Server). Do not substitute unverified syntax. For parameter-level depth beyond this file, read reference.md; for platform limits read azure-constraints.md.

Live instance context (dynamic injection)

Read the target instance's live state before recommending anything, so guidance is version-aware rather than hardcoded. Assumes PGCONN is a libpq connection string (or set PGHOST/PGDATABASE/PGUSER/PGPASSWORD) and the Azure CLI is logged in. These are read-only and safe to auto-run.

• Installed vector-stack extensions and versions: !psql "$PGCONN" -tAc "SELECT extname, extversion FROM pg_extension WHERE extname IN ('vector','azure_ai','pg_diskann') ORDER BY extname;" 2>/dev/null || echo "(could not connect — ask the user for connection details)"
• Server allow-list (what CAN be created here): !psql "$PGCONN" -tAc "SHOW azure.extensions;" 2>/dev/null || echo "(unknown — check azure.extensions parameter)"
• Postgres major version: !psql "$PGCONN" -tAc "SHOW server_version;" 2>/dev/null || echo "(unknown)"

If injection is unavailable in the current runtime, run scripts/inspect_vector_readiness.sql and read its output instead. Never assume an extension is installed — verify.

When to use this skill

Trigger on: "semantic search over my documents", "add RAG to this app", "store embeddings in Postgres", "find similar rows/products/tickets", "vector index is slow", "which index — HNSW or DiskANN?", "generate embeddings in SQL", or any schema with a vector(...) column. If the task is purely lexical full-text search with no embeddings, prefer fulltext-and-bm25; for combining both, this skill hands off to hybrid-search-rrf.

Decision flow

Work through these in order. Steps 1–2 are read/inspect (safe); steps 3–6 are write/mutate (guarded).

1. Inspect the live instance (section above). Establish: is vector installed? azure_ai? pg_diskann? What is on the allow-list? What is the embedding source and dimension?
2. Design the schema and choose an index. Use the tables below plus reference.md to pick DiskANN vs HNSW vs IVFFlat and the distance operator. Decide dimension from the embedding model (e.g. text-embedding-3-small → 1536; text-embedding-3-large → 3072).
3. Enable extensions (guarded write) — allow-list + CREATE EXTENSION.
4. Configure azure_ai (guarded write) — endpoint + auth for Azure OpenAI.
5. Ingest + embed (guarded write) — populate the vector column in batches.
6. Index + query — build the ANN index (guarded write), then write the retrieval query (read).

Choosing an index (summary)

Index	Build cost	Recall/latency	Best for	Notes
DiskANN (diskann)	Moderate, parallelisable	Excellent balance	Large / growing sets, >1M rows, high-dim	Flexible-Server-only; PQ enables >2000 dims
HNSW (hnsw)	Higher memory/time	Best speed-recall at small–mid scale	≤ a few M rows	≤2000 dims for the index
IVFFlat (ivfflat)	Cheapest	Lower recall	Fast builds, cost-sensitive	Needs data loaded before build (k-means)

Full guidance and parameter tables: reference.md.

Distance operator ↔ ops-class (must match)

Pick one and keep the operator and index ops-class aligned, or the planner will ignore the index:

Metric	Operator	ops-class
Cosine distance	<=>	vector_cosine_ops
Euclidean (L2)	<->	vector_l2_ops
(Negative) inner product	<#>	vector_ip_ops

For normalised embeddings (OpenAI vectors have length 1), inner product <#> is cheapest; cosine <=> is the safe default. Use the same metric for the index and every query.

Read / inspect steps (safe · auto)

These never mutate state. Run them freely to ground your recommendations.

• Readiness report — scripts/inspect_vector_readiness.sql lists the vector stack, the allow-list, existing vector columns, existing ANN indexes, and whether azure_ai settings are configured.

• Confirm an index is used — EXPLAIN a representative query and check for an Index Scan using ... (not Seq Scan):

  EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
  SELECT id FROM docs ORDER BY embedding <=> '[0.1,0.2,0.3]' LIMIT 5;
  

• Check index build progress (safe, during a long build):

  SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS "%"
  FROM pg_stat_progress_create_index;
  

Write / mutate steps (explicit · guarded)

Do not run these without presenting a plan and getting explicit confirmation. Prefer the bundled scripts — they are transaction-wrapped where possible and support --dry-run. On Flexible Server there is no superuser; extensions must be on the azure.extensions allow-list first (a server parameter change that may trigger a brief deployment). See azure-constraints.md.

3 · Enable extensions

Allow-list at the server, then create in each database. pgvector's extension name is vector (not "pgvector"). Use scripts/enable_extensions.sh — it does the az ... parameter set allow-list step and the transaction-wrapped CREATE EXTENSION step, and prints everything under --dry-run without changing anything.

Verified underlying commands:

az postgres flexible-server parameter set \
  --resource-group <rg> --server-name <server> \
  --name azure.extensions --value "vector,azure_ai,pg_diskann"

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS azure_ai;
CREATE EXTENSION IF NOT EXISTS pg_diskann CASCADE;  -- CASCADE pulls in vector

4 · Configure azure_ai (Azure OpenAI)

Point azure_ai at an Azure OpenAI resource with a deployed embeddings model. Only superusers and members of azure_ai_settings_manager may call set_setting; on Flexible Server azure_pg_admin holds this by default.

SELECT azure_ai.set_setting('azure_openai.endpoint', 'https://<name>.openai.azure.com');
-- Preferred: managed identity (no secret in the DB). See azure-constraints.md.
SELECT azure_ai.set_setting('azure_openai.auth_type', 'managed-identity');
-- Or, key-based:
-- SELECT azure_ai.set_setting('azure_openai.subscription_key', '<API key>');

Verify: SELECT azure_ai.get_setting('azure_openai.endpoint'); and SELECT azure_ai.version();. The API-key path writes a secret into settings — treat that as sensitive and prefer managed identity.

5 · Ingest + embed

Store embeddings in a vector(<dim>) column. azure_openai.create_embeddings generates them in-database; the text[] overload with batch_size is far more efficient for bulk loads than row-by-row calls. Use scripts/bulk_embed.sh (batched, transaction-wrapped, --dry-run reports the backfill count and cost estimate without calling the API).

-- Single value → real[]; cast to vector for storage/search.
INSERT INTO docs (body, embedding)
VALUES ('some text',
        azure_openai.create_embeddings('<deployment>', 'some text')::vector);

Load data before building the ANN index — it is faster and yields a better layout (essential for IVFFlat's k-means; recommended for all types).

6 · Index + query

Build the chosen ANN index with a matching ops-class, then query with the matching operator. Use scripts/create_vector_index.sh (--index-type diskann|hnsw|ivfflat, transaction-wrapped, --dry-run, optional --concurrently for online builds that must run outside a transaction).

-- DiskANN (Flexible Server): great default for scale.
CREATE INDEX ON docs USING diskann (embedding vector_cosine_ops);

-- HNSW: strong speed-recall at ≤2000 dims.
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Retrieval (cosine): operator MUST match the ops-class.
SELECT id, body
FROM docs
ORDER BY embedding <=> azure_openai.create_embeddings('<deployment>', 'user query')::vector
LIMIT 5;

Recall/latency knobs (per-session or SET LOCAL in a txn): SET hnsw.ef_search = 100;, SET diskann.l_value_is = 100;, SET ivfflat.probes = 10;. Parameter tables and the DiskANN PQ / high-dimension path are in reference.md.

A complete end-to-end walkthrough with expected output is in examples/basic_rag.md. For blending vector with lexical ranking, hand off to hybrid-search-rrf.

Safety protocol

1. Inspect first. Always read live state before proposing writes; never assume an extension, setting, or index exists.
2. Plan → confirm → execute for every mutating step. State exactly what will change (server parameter, which database, which table/index) and the blast radius before running.
3. Use the scripts, not free-hand DDL for allow-listing, CREATE EXTENSION, index builds, and bulk embedding. Run --dry-run first and show the user the planned statements.
4. Prefer online / low-lock paths on populated tables: CREATE INDEX CONCURRENTLY (note: cannot run inside a transaction block) and scale maintenance_work_mem up for the build, then back down.
5. Precheck backups before schema-destructive changes — Flexible Server provides automated backups/PITR; confirm the restore point is recent. See azure-constraints.md.
6. Guard secrets. Prefer managed-identity auth for azure_ai; never commit an API key or echo it into logs.
7. Contain blast radius. Run mutating work in a subagent where the harness supports it.

Bundled files

• reference.md — deep reference: embedding models & dimensions, index deep-dive (DiskANN PQ, HNSW/IVFFlat params, recall/latency tables), distance functions, dimensionality limits, troubleshooting.
• azure-constraints.md — Flexible Server specifics: no superuser, allow-listing, managed identity, HA/backup, version drift.
• scripts/ — deterministic, guarded helpers (one read-only inspector; three --dry-run-capable writers).
• examples/basic_rag.md — end-to-end worked example with expected output.