pgvector-authoring-cli

name

pgvector-authoring-cli

description

Set up and use pgvector and pg_diskann for vector similarity search on Azure Database for PostgreSQL Flexible Server. Use when the user wants to: (1) install and configure pgvector or pg_diskann, (2) create tables with vector columns, (3) build vector indexes (IVFFlat, HNSW, DiskANN), (4) run similarity search queries, (5) implement RAG patterns, (6) store and query embeddings from Microsoft Foundry / Azure AI models, (7) choose between HNSW and DiskANN for large-scale vector workloads. Triggers: "pgvector", "vector search", "similarity search", "embeddings postgres", "RAG postgres", "vector index", "HNSW", "IVFFlat", "cosine similarity", "nearest neighbor postgres", "AI postgres", "semantic search postgres", "pg_diskann", "DiskANN", "diskann index", "scalable vector search".

Update Check — ONCE PER SESSION (mandatory) The first time this skill is used in a session, run the check-updates skill before proceeding.

• GitHub Copilot CLI / VS Code: invoke the check-updates skill.
• Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.
• Skip if the check was already performed earlier in this session.

pgvector — Vector Similarity Search Skill

Prerequisites

Enable pgvector

# Allow pgvector extension
az postgres flexible-server parameter set \
  --resource-group your-resource-group \
  --server-name your-server-name \
  --name azure.extensions \
  --value "vector"

# Install pgvector in your database
psql "host=$FQDN port=5432 dbname=$DB user=$USER sslmode=require" \
  -c "CREATE EXTENSION IF NOT EXISTS vector;"

---

Vector Table Design

Basic Vector Table

CREATE TABLE documents (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    content text NOT NULL,
    embedding vector(1536),  -- text-embedding-3-small dimension (Microsoft Foundry / Azure AI)
    metadata jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

Vector Dimensions by Model

Model	Dimensions	Notes
text-embedding-ada-002	1536	Legacy — available via Microsoft Foundry
text-embedding-3-small	1536	Recommended — available via Microsoft Foundry
text-embedding-3-large	3072	Highest quality — available via Microsoft Foundry
Cohere embed-v3	1024	Alternative — available via Microsoft Foundry
Phi-3 / Phi-4 (custom fine-tuned)	Varies	Microsoft Foundry fine-tuned models

---

Inserting Vectors

-- Insert a single embedding
INSERT INTO documents (content, embedding, metadata)
VALUES (
    'PostgreSQL is a powerful database',
    '[0.1, 0.2, 0.3, ...]'::vector,  -- 1536-dim vector
    '{"source": "docs", "category": "database"}'
);

-- Bulk insert from application
-- Use parameterized queries with your application's embedding API

---

Similarity Search

Distance Operators

Operator	Distance Metric	Index Support
<->	L2 (Euclidean)	IVFFlat, HNSW, DiskANN
<=>	Cosine	IVFFlat, HNSW, DiskANN
<#>	Inner Product (negative)	IVFFlat, HNSW, DiskANN

Tip: For normalized embeddings (e.g., from Microsoft Foundry models), inner product (<#>) gives best performance.

Vector Functions

Function	Returns	Description
cosine_distance(a, b)	double precision	Cosine distance between two vectors
inner_product(a, b)	double precision	Inner product of two vectors
l2_distance(a, b)	double precision	Euclidean (L2) distance
l1_distance(a, b)	double precision	Taxicab (L1/Manhattan) distance
vector_dims(v)	integer	Number of dimensions
vector_norms(v)	double precision	Euclidean norm of the vector

Vector Aggregates

Aggregate	Returns	Description
AVG(v)	vector	Average of processed vectors
SUM(v)	vector	Sum of processed vectors

Basic Similarity Search

-- Find 10 most similar documents (cosine distance)
SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;

Filtered Similarity Search

-- Similarity search with metadata filter
SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM documents
WHERE metadata->>'category' = 'database'
ORDER BY embedding <=> $1
LIMIT 10;

RAG Pattern: Retrieve + Augment

-- Step 1: Semantic search for relevant context
WITH relevant_docs AS (
    SELECT content, 1 - (embedding <=> $1) AS similarity
    FROM documents
    WHERE 1 - (embedding <=> $1) > 0.7  -- similarity threshold
    ORDER BY embedding <=> $1
    LIMIT 5
)
SELECT string_agg(content, E'\n---\n') AS context
FROM relevant_docs;

---

Vector Indexes

HNSW Index (Recommended)

Better recall, faster queries, slower build time.

-- Cosine distance
CREATE INDEX idx_documents_embedding_hnsw ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Set search parameter
SET hnsw.ef_search = 100;  -- higher = better recall, slower

IVFFlat Index

Faster build time, requires training data.

-- Build index (after data is loaded)
CREATE INDEX idx_documents_embedding_ivf ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);  -- sqrt(row_count) as starting point

-- Set search parameter
SET ivfflat.probes = 10;  -- higher = better recall, slower

DiskANN Index (Azure-Specific)

High recall at scale with lower memory than HNSW. Requires pg_diskann extension — Azure-native, separate from pgvector.

Enable pg_diskann

# Allowlist both extensions (pgvector + pg_diskann)
az postgres flexible-server parameter set \
  --resource-group your-resource-group \
  --server-name your-server-name \
  --name azure.extensions \
  --value "vector,pg_diskann"

-- Install both extensions in your database
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_diskann;

Create DiskANN Index

-- Default (cosine distance — recommended for normalized embeddings)
CREATE INDEX idx_documents_diskann ON documents
USING diskann (embedding vector_cosine_ops);

-- L2 distance
CREATE INDEX idx_documents_diskann_l2 ON documents
USING diskann (embedding vector_l2_ops);

-- Inner product
CREATE INDEX idx_documents_diskann_ip ON documents
USING diskann (embedding vector_ip_ops);

-- Tune search recall (session-level)
SET diskann.search_list_size = 100;  -- default 100; higher = better recall, slower

DiskANN vs HNSW vs IVFFlat

Factor	DiskANN	HNSW (pgvector)	IVFFlat (pgvector)
Dataset size	Large (millions+)	Medium–Large	Medium
Build time	Faster	Slower	Fast (needs data first)
Memory usage	Lower (disk-based)	Higher (in-memory)	Medium
Query recall	High	High	Moderate
Filtered search	Good	Good	Needs probes tuning
Best for	Scale + cost efficiency on Azure	Low-latency small-medium datasets	Quick prototyping

Index Guidelines

Dataset Size	Recommended Index	Notes
< 10K rows	No index needed	Exact search is fast enough
10K - 1M	HNSW	m=16, ef_construction=64
1M - 10M	HNSW or DiskANN	DiskANN uses less memory
10M+	DiskANN	Best scale + cost efficiency

---

Performance Tuning

Query Plan Analysis

-- Always check the query plan first
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM documents ORDER BY embedding <-> '[1,2,3]' LIMIT 5;

-- For slow queries, omit ANALYZE for instant plan
EXPLAIN (VERBOSE, BUFFERS)
SELECT * FROM documents ORDER BY embedding <-> '[1,2,3]' LIMIT 5;

Key Parameters

-- Increase maintenance_work_mem for index building
SET maintenance_work_mem = '2GB';

-- Adjust HNSW search quality
SET hnsw.ef_search = 200;  -- default 40

-- Adjust IVFFlat probes
SET ivfflat.probes = 20;   -- default 1

-- Adjust DiskANN search list
SET diskann.l_value_is = 100;  -- default varies

-- Enable parallel execution for exact search
SET max_parallel_workers_per_gather = 4;

Partial Indexes for Filtered Searches

-- Index only premium-tier documents
CREATE INDEX idx_premium_embedding ON documents
USING hnsw (embedding vector_cosine_ops)
WHERE tier = 'premium';

Monitor Index Build Progress

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

Monitor Index Usage

SELECT
    indexrelname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE indexrelname LIKE '%embedding%';

In-Database Embedding Generation (Microsoft Foundry via azure_ai)

The azure_ai extension connects directly to your Microsoft Foundry endpoint to generate embeddings inside SQL.

-- Generate embeddings directly in SQL via azure_ai extension
SELECT azure_openai.create_embeddings('text-embedding-3-small', 'search query');

-- Semantic search with in-database embedding generation
SELECT id, content FROM documents
ORDER BY embedding <=> azure_openai.create_embeddings('text-embedding-3-small', $1)
LIMIT 10;

Note: The azure_openai schema in the azure_ai extension routes to your Microsoft Foundry (or Azure AI) endpoint. Configure the endpoint and key via:

SELECT azure_ai.set_setting('azure_openai.endpoint', 'https://your-foundry-endpoint.openai.azure.com');
SELECT azure_ai.set_setting('azure_openai.subscription_key', 'your-key');

---

Must

• Match vector dimensions to your embedding model
• Create indexes after initial data load (not on empty tables for IVFFlat)
• Use parameterized queries for embedding values (prevent SQL injection)
• Choose the right distance metric for your use case
• Use extension name vector (not pgvector) when allowlisting and in CREATE EXTENSION

Prefer

• HNSW over IVFFlat for better recall out-of-the-box
• DiskANN for large-scale datasets (millions+ rows) on Azure — lower memory cost than HNSW
• Allowlisting both vector and pg_diskann together when using DiskANN
• Cosine distance (<=>) for normalized embeddings
• Inner product (<#>) for normalized embeddings (best performance)
• Pre-filtering with metadata before vector search
• Partial indexes to reduce index size for filtered workloads
• Batched inserts for embedding ingestion
• EXPLAIN (ANALYZE, VERBOSE, BUFFERS) to verify index usage

Avoid

• Creating IVFFlat indexes on empty tables (needs training data)
• Using exact nearest neighbor search on large tables (use indexes)
• Setting hnsw.ef_search, ivfflat.probes, or diskann.search_list_size too low (poor recall)
• Storing very high-dimensional vectors without dimensionality reduction
• Indexing columns with > 2000 dimensions (pgvector limit)
• Creating both a HNSW and DiskANN index on the same column — pick one
• Enabling pg_diskann without also enabling vector (pg_diskann depends on pgvector's vector type)