Build AI Apps with Azure Database for PostgreSQL

Azure Database for PostgreSQL is a solid backbone for AI applications because it can hold your application data, your vector embeddings, and your retrieval logic in one managed engine. You don't need a separate vector database for most workloads — Flexible Server ships with the pgvector extension, which handles similarity search directly inside the same Postgres instance you already use for users, sessions, and business data.

This guide walks through the practical steps: enabling pgvector on Azure Database for PostgreSQL, designing a schema for embeddings, running similarity queries, and wiring the whole thing to an LLM so you get a working retrieval-augmented generation (RAG) pipeline, not just a vector store with no application in front of it.

Why Azure Database for PostgreSQL for AI workloads

A few properties make it a reasonable default choice over a dedicated vector database:

• One database, one connection pool. Application data, chat history, and embeddings live together, which avoids the join-across-services problem RAG apps often hit.
• pgvector is a first-class extension on Flexible Server — no custom images, no sidecar services.
• Standard Postgres tooling — backups, read replicas, point-in-time restore, and role-based access — works exactly as it does for any other Postgres workload.
• Predictable scaling. You can start on a Burstable tier for prototyping and move to General Purpose or Memory Optimized as embedding volume and query load grow.

The tradeoff is that pgvector isn't as fast as a purpose-built vector engine at very large scale (tens of millions of vectors with strict latency SLAs). For most product-level AI apps — internal knowledge bases, support bots, semantic search over a few hundred thousand documents — it's more than enough.

Enabling pgvector

On an existing Flexible Server instance, enable the extension through the allowlist first:

az postgres flexible-server parameter set \
  --resource-group my-rg \
  --server-name my-pg-server \
  --name azure.extensions \
  --value vector

Then, in your database:

CREATE EXTENSION IF NOT EXISTS vector;

Designing the schema

A minimal RAG schema needs a table for source documents and a table (or column) for their embeddings:

CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  metadata JSONB DEFAULT '{}',
  embedding VECTOR(1536),
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops);

Use hnsw over ivfflat for most new projects — it doesn't require a training pass and gives better recall at similar speed. The vector dimension (1536 here) has to match whatever embedding model you use, so set it once and don't change it later without a migration.

Querying by similarity

Once documents are embedded and stored, retrieval is a single SQL query:

SELECT id, content, metadata
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

<=> is the cosine distance operator pgvector registers. You compute the query embedding in your application, pass it as $1, and Postgres returns the nearest neighbors. This is the entire "retrieval" half of RAG — no external service required.

Wiring retrieval to an LLM

The other half is generation: taking the retrieved rows and asking a model to answer using them. This is where most teams either build a custom proxy around a model provider's SDK, or use an API that already handles keys, streaming, and usage tracking. If you're already paying for Claude access and want an HTTPS endpoint you can call from a server or edge function, SubToAPI turns that into a standard API with sub_live_... keys, so the LLM call looks the same regardless of which backend serves it:

const rows = await pool.query(
  `SELECT content FROM documents ORDER BY embedding <=> $1 LIMIT 5`,
  [queryEmbedding]
);

const context = rows.rows.map(r => r.content).join("\n\n");

const res = await fetch("https://api.subtoapi.app/v1/messages", {
  method: "POST",
  headers: {
    "Authorization": `Bearer ${process.env.SUBTOAPI_KEY}`,
    "Content-Type": "application/json"
  },
  body: JSON.stringify({
    model: "claude-sonnet",
    max_tokens: 1024,
    messages: [
      {
        role: "user",
        content: `Answer using only this context:\n${context}\n\nQuestion: ${userQuestion}`
      }
    ]
  })
});

const data = await res.json();
console.log(data.content);

That's the full loop: Postgres retrieves relevant rows, the model generates an answer grounded in them. For chat-style apps you'll want streaming so the response appears token by token instead of after a long wait — see /docs/streaming for how that works, and /docs/messages for the full request/response shape.

Production considerations

A few things that matter once this moves past a prototype:

• Connection pooling. LLM calls are slow relative to database queries; don't hold a Postgres connection open while waiting on a model response. Use a pool (PgBouncer or Azure's built-in pooling) sized for your concurrent request count, not your LLM concurrency.
• Batch your embeddings. Re-embedding one document at a time is wasteful. Batch inserts and batch embedding calls where your provider supports it.
• Index maintenance. hnsw indexes grow with your table. Monitor index size and rebuild if write-heavy workloads cause bloat.
• Separate read and write paths. If your app does heavy similarity search, consider a read replica so retrieval traffic doesn't compete with writes from ingestion jobs.
• Track model usage separately from database usage. Postgres metrics tell you query load; they don't tell you token spend. If you're calling a model through SubToAPI, usage metadata comes back with each response, which makes it easy to log cost per request without instrumenting your own token counter — see /docs for the full reference.

For teams evaluating cost, Azure Database for PostgreSQL bills for compute and storage independent of AI usage, while your model calls are billed separately by whatever provider or gateway you use. If you want a single flat rate per seat instead of managing provider billing directly, plans start at €9/month — see /pricing.

Questions

Does Azure Database for PostgreSQL support vector search out of the box? Not by default — you need to enable the pgvector extension through the server's allowlist parameter, then create it with CREATE EXTENSION vector in your database. After that, VECTOR columns and similarity queries work like any other SQL feature.

Do I need a separate vector database alongside Postgres? For most applications, no. pgvector with an hnsw index handles semantic search well up to roughly a few million vectors. Only move to a dedicated vector engine if you have strict low-latency requirements at very large scale.

How do I connect an LLM to data stored in Azure Database for PostgreSQL? Retrieve the relevant rows with a similarity query, insert that text into your prompt as context, then call your model provider's API. Tools like /docs/quickstart show how to send that context-augmented prompt to a model over a simple HTTPS request.