Building AI-Powered Applications with Azure Database for PostgreSQL

Why Postgres as the AI Backend The pitch for building AI features directly on Azure Database for PostgreSQL instead of bolting on a separate vector database: your relational data (users, orders, documents, permissions) and your vector embeddings live in the same database, the same transaction, the same backup. A RAG query that needs "find similar documents this user is actually allowed to see" is one SQL query with a vector similarity clause and a normal WHERE permissions filter - not an application-layer join between two separate systems that can drift out of sync. This is a granular, hands-on build: enabling vector search, generating embeddings, running semantic search, wiring in Azure OpenAI for generation, assembling a full RAG pipeline, and a minimal generative agent - all against one Postgres database. Before the how, the what - three terms this build leans on: - Embedding - a numerical representation of text (a list of numbers, typically 1000+ of them) produced by an AI model, positioned so that texts with similar meaning end up as numerically similar lists. "How do I cut my cloud bill" and "reduce cloud spend" produce very close embeddings despite sharing almost no words - that's what makes semantic search possible, versus a keyword search that would miss the match entirely. - Vector similarity search - given a query's embedding, finding the stored embeddings mathematically closest to it (typically by cosine similarity - how closely two vectors point in the same direction). This is the operation pgvector's index accelerates, and it's the retrieval half of RAG. - RAG (Retrieval-Augmented Generation) - instead of asking an LLM a question and hoping it knows the answer from its training data, you first retrieve the most relevant pieces of your own data (via vector similarity search), then hand those to the model as context and ask it to generate an answer grounded in that context. This is what lets an LLM answer accurately about your private documents it was never trained on. 01 - Enabling Generative AI in Postgres Two extensions do the real work: vector (pgvector - stores and indexes embeddings) and azure_ai (calls Azure OpenAI and Azure AI services directly from SQL). -- Run once per database, requires azure.extensions allowlisting first -- (Azure Portal → Server Parameters → azure.extensions → add "VECTOR,AZURE_AI") CREATE EXTENSION IF NOT EXISTS vector; CREATE EXTENSION IF NOT EXISTS azure_ai; Configure the azure_ai extension to know which Azure OpenAI deployment to call: SELECT azure_ai.set_setting('azure_openai.endpoint', 'https:// .openai.azure.com'); SELECT azure_ai.set_setting('azure_openai.subscription_key', ' '); Prefer a managed identity over a stored key where the server configuration supports it - the same "don't store a long-lived secret if you don't have to" principle from the load-testing/OIDC article applies here too. 02 - Generating and Storing Embeddings CREATE TABLE documents ( id SERIAL PRIMARY KEY, title TEXT NOT NULL, content TEXT NOT NULL, embedding VECTOR(1536), -- matches text-embedding-3-small's output dimension created_at TIMESTAMPTZ DEFAULT now() ); Generate an embedding inside SQL, via the azure_ai extension calling Azure OpenAI directly - no separate Python script needed for this step: INSERT INTO documents (title, content, embedding) VALUES ( 'FinOps for Kubernetes', 'Practical strategies to slash compute spend on AKS using spot node pools...', azure_openai.create_embeddings('text-embedding-3-small', 'Practical strategies to slash compute spend on AKS using spot node pools...') ); For bulk-loading an existing table of documents, wrap this in an UPDATE ... SET embedding = azure_openai.create_embeddings(...) over all rows, batched to stay under the embedding model's rate limits. 03 - Indexing for Fast Similarity Search A sequential scan comparing a query vector against every row works for a demo and falls over past a few thousand rows. pgvector supports two index types - HNSW (better recall, more memory) and IVFFlat (faster to build, needs tuning to the table size): -- HNSW: the better default for most workloads CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); vector_cosine_ops matches cosine similarity - the standard choice for text embeddings, since embedding magnitude isn't meaningful, only direction. Use vector_l2_ops only if you have a specific reason to care about Euclidean distance instead. 04 - Semantic Search -- Find the 5 most semantically similar documents to a query WITH query_embedding AS ( SELECT azure_openai.create_embeddings('text-embedding-3-small', 'how do I reduce my AKS bill') AS emb ) SELECT d.id, d.title, 1 - (d.embedding q.emb) AS similarity FROM documents d, query_embedding q ORDER BY d.embedding q.emb LIMIT 5; is pgvector's cosine-distance operator - smaller distance means more similar, so 1 - distance converts it into a more intuitive 0-1 similarity score. This single query is doing what would otherwise require a separate vector database, an API call to it, and an application-layer merge with the relational data. 05 - Row-Level Security Meets Semantic Search This is the part a bolted-on vector database can't do cleanly: combine similarity search with a normal permissions filter, enforced by the database itself. ALTER TABLE documents ENABLE ROW LEVEL SECURITY; CREATE POLICY document_access ON documents FOR SELECT USING (owner_id = current_setting('app.current_user_id')::int OR is_public = true); Now the semantic search query from Section 04 automatically only returns documents the requesting user is actually allowed to see - Postgres enforces it at the row level, so there's no way for an application bug to accidentally leak a similarity match the user shouldn't have access to. 06 - Integrating Azure AI Services Beyond Azure OpenAI, the azure_ai extension reaches Azure AI Language and Vision services directly from SQL - useful for enrichment at write time rather than at query time: -- Sentiment analysis on a support ticket, stored alongside the row UPDATE support_tickets SET sentiment = azure_cognitive.analyze_sentiment(body, 'en') WHERE sentiment IS NULL; -- Key phrase extraction, useful for tagging/search without a separate NLP pipeline SELECT azure_cognitive.extract_key_phrases(content, 'en') FROM documents WHERE id = 42; Enriching data at insert/update time (rather than computing sentiment or key phrases on every read) trades a small write-time cost for zero read-time latency - the right tradeoff for data that's written once and read often, which describes most support-ticket and document-search workloads. 07 - Building a Full RAG Pipeline Retrieval-Augmented Generation in one function: embed the question, retrieve the most relevant documents, hand them to the LLM as context, generate the answer. CREATE OR REPLACE FUNCTION rag_answer(user_question TEXT) RETURNS TEXT AS $$ DECLARE context_text TEXT; question_embedding VECTOR(1536); answer TEXT; BEGIN question_embedding := azure_openai.create_embeddings('text-embedding-3-small', user_question); -- Retrieval: top 3 most relevant chunks SELECT string_agg(content, E'\n---\n') INTO context_text FROM ( SELECT content FROM documents ORDER BY embedding question_embedding LIMIT 3 ) top_matches; -- Generation: the LLM answers using only the retrieved context SELECT azure_openai.create_chat_completion( 'gpt-4o-mini', jsonb_build_array( jsonb_build_object('role', 'system', 'content', 'Answer using only the provided context. If the context does not contain the answer, say so.'), jsonb_build_object('role', 'user', 'content', format(E'Context:\n%s\n\nQuestion: %s', context_text, user_question)) ) )->'choices'->0->'message'->>'content' INTO answer; RETURN answer; END; $$ LANGUAGE plpgsql; SELECT rag_answer('How do I reduce my AKS compute bill?'); The entire RAG loop - embed, retrieve, augment, generate - runs as a single SQL function call. Note the E'...' prefix on that format() string - a plain '...' literal doesn't interpret \n as a newline in Postgres; only an E-prefixed ("escape") string does, and it's an easy detail to miss until the context comes back as one unreadable line. The "if the context does not contain the answer, say so" instruction in the system prompt matters more than it looks: without it, the model will confidently answer from its own training data when the retrieved context is irrelevant, defeating the actual purpose of RAG (grounding answers in your data, not the model's general knowledge). 08 - A Minimal Generative Agent An "agent" here means: the model decides which of a small set of tools to call based on the question, rather than always following the same fixed retrieval path. import json import psycopg2 from openai import AzureOpenAI client = AzureOpenAI(azure_endpoint="https:// .openai.azure.com", api_version="2024-08-01-preview") conn = psycopg2.connect("dbname=ragdb host= .postgres.database.azure.com user= sslmode=require") TOOLS = [ { "type": "function", "function": { "name": "search_documents", "description": "Semantic search over the internal knowledge base", "parameters": { "type": "object", "properties": {"query": {"type": "string"}}, "required": ["query"], }, }, }, { "type": "function", "function": { "name": "get_ticket_sentiment_summary", "description": "Aggregate sentiment across recent support tickets", "parameters": {"type": "object", "properties": {}}, }, }, ] def search_documents(query: str) -> str: with conn.cursor() as cur: cur.execute("SELECT rag_answer(%s)", (query,)) return cur.fetchone()[0] def get_ticket_sentiment_summary() -> str: with conn.cursor() as cur: cur.execute("SELECT sentiment, count(*) FROM support_tickets GROUP BY sentiment") return json.dumps(cur.fetchall()) def run_agent(user_message: str) -> str: response = client.chat.completions.create( model="gpt-4o-mini", messages=[{"role": "user", "content": user_message}], tools=TOOLS, ) msg = response.choices[0].message if not msg.tool_calls: return msg.c