AI & Databases · Sep 2026 · 24 min read

Building AI-Powered Applications with Azure Database for PostgreSQL

A granular walkthrough of turning Azure Database for PostgreSQL into an AI application backend — pgvector embeddings, semantic search, in-database Azure OpenAI calls, a full RAG pipeline, and a simple generative agent.

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:

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://<your-resource>.openai.azure.com');
SELECT azure_ai.set_setting('azure_openai.subscription_key', '<key-or-managed-identity-token>');

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://<your-resource>.openai.azure.com", api_version="2024-08-01-preview")
conn = psycopg2.connect("dbname=ragdb host=<server>.postgres.database.azure.com user=<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.content

    tool_call = msg.tool_calls[0]
    fn = {"search_documents": search_documents, "get_ticket_sentiment_summary": get_ticket_sentiment_summary}[tool_call.function.name]
    args = json.loads(tool_call.function.arguments)
    result = fn(**args)

    follow_up = client.chat.completions.create(
        model="gpt-4o-mini",
        messages=[
            {"role": "user", "content": user_message},
            msg,
            {"role": "tool", "tool_call_id": tool_call.id, "content": result},
        ],
    )
    return follow_up.choices[0].message.content

The agent decides whether the question needs a document search, a sentiment summary, or neither — the SQL rag_answer function from Section 07 becomes one callable tool among several, not the only path through the system.

09 — Developing Against It: VS Code + the PostgreSQL Extension

The PostgreSQL extension for VS Code gives a connection browser, inline query execution, and schema introspection without leaving the editor — paired with GitHub Copilot, it's a genuinely faster loop for iterating on the SQL functions above: write the function, run it inline, see the actual row output, adjust, re-run, without a context switch to a separate SQL client.

Cost and Performance Notes

Closing Thoughts

The value of building this on Postgres rather than a separate vector store isn't a purity argument — it's fewer moving parts. One database to back up, one connection pool to manage, one permissions model (Row-Level Security) that automatically applies to both your relational queries and your semantic search. For a lot of RAG applications, that operational simplicity is worth more than whatever marginal performance a dedicated vector database might offer at a scale most teams never actually reach.

GitHub Repository: azure-postgresql-ai-rag-lab — schema, embedding scripts, the full RAG function, and the Python agent, ready to run.

Azure Database for PostgreSQL · pgvector · RAG · Azure OpenAI · Semantic Search · AI Agents