Tutorial: RAG pipeline with Postgres and pgvector

The pipeline takes place in five stages. Upon ingestion, the text is cut up, each chunk is vectorized in batch then stored. Upon querying, the question is vectorized in turn, compared to the chunks stored by cosine similarity, and the closest snippets are injected into the prompt sent to the generation model.

Chunking (tiktoken)→Embeddings (batch)→pgvector storage (HNSW)→Similarity search→Augmented generation

An architectural detail that matters in production: no Postgres connection is held during network calls to the embedding or generation provider. The DB transaction closes before the external call and reopens after, to never block a shared PgBouncer backend for third-party network latency.

Unlike a typical self-hosted pgvector, you don't need to run CREATE EXTENSION vector or create a table yourself for this pipeline. The project's embeddings schema, with its vector columns and HNSW indexes, is provisioned automatically when the project is created.

# Aurabase account + CLI
npm i -g @aurabase/cli
aura login

# Create the project and link it to this folder
aura projects create mon-assistant --engine postgres
aura link --project-id <uuid-du-projet>

# Retrieve the API keys of the linked project
aura projects api-keys

The embedding provider is configured once, on the project side (Studio → IA → Suppliers). It is this provider which determines the effective dimension of your vectors, therefore the column used on the embeddingstable.

One call is enough to index a document: the text is divided into chunks, each chunk is vectorized, then stored in the requested namespace. The splitting uses a real tokenizer (tiktoken, encoding o200k_base), not a simple splitting by spaces, which remains correct on text without spaces like some Asian languages.

import { aura } from '@/lib/aurabase'

export async function POST(req: Request) {
  const { content, metadata } = await req.json()

  const { data, error } = await aura.ai.ragIngest({
    namespace: 'docs-produit',
    content,
    metadata,
  })

  if (error) return Response.json({ error }, { status: 400 })
  return Response.json(data) // { id, chunks }
}

In raw HTTP, the equivalent route is POST /v1/ai/{project_id}/rag/ingest, authenticated by the project's API key.

curl -X POST https://<votre-gateway>/v1/ai/<project_id>/rag/ingest \
  -H "apikey: <votre-cle-api>" \
  -H "Content-Type: application/json" \
  -d '{
    "namespace": "docs-produit",
    "content": "Le texte complet de votre document ici...",
    "metadata": { "source": "guide-utilisateur.pdf" }
  }'

Ingestion is idempotent by default: a document identifier is derived automatically (SHA-256 hash of the content, or metadata.document_id if you provide it). Reingesting the same content replaces its existing chunks instead of duplicating them, making a periodic sync job safe to replay.

No proprietary magic here: the table that receives your vectors is an ordinary Postgres table, with one vector column per dimension class and a partial HNSW index per column (active only on the rows that populate it). Here is its real definition, simplified:

create table embeddings (
  id               uuid primary key default gen_random_uuid(),
  project_id       text not null,
  namespace        text not null,
  content          text not null,
  embedding_768    vector(768),
  embedding_1536   vector(1536),
  embedding_3072   vector(3072),
  embedding_model  text,
  dims             int,
  metadata         jsonb default '{}'::jsonb,
  created_at       timestamptz not null default now()
);

-- A partial HNSW index by dimension class
create index idx_embeddings_vec_1536 on embeddings
  using hnsw (embedding_1536 vector_cosine_ops)
  where embedding_1536 is not null;

-- Beyond 2000 dimensions, HNSW does not support the vector type:
-- cast in halfvec for class 3072
create index idx_embeddings_vec_3072 on embeddings
  using hnsw ((embedding_3072::halfvec(3072)) halfvec_cosine_ops)
  where embedding_3072 is not null;

Each search compares the vectors with the cosine distance operator (<=>), the one targeted by the index's vector_cosine_ops and halfvec_cosine_ops operator classes. For details of the recall/latency trade-offs of HNSW versus IVFFlat, see the article dedicated to HNSW indexing. For the choice of the embedding dimension itself, see the comparison 768 vs 1536 vs 3072.

On the query side, rag() chains the vectorization of the question, the search by similarity in the namespace, the construction of the augmented prompt and the call to the generation model, in a single network round trip on the client side.

import { aura } from '@/lib/aurabase'

export async function POST(req: Request) {
  const { question } = await req.json()

  const { data, error } = await aura.ai.rag({
    question,
    namespace: 'docs-produit',
  })

  if (error) return Response.json({ error }, { status: 400 })
  return Response.json(data)
}

The response carries the generated response and its sources, with the provider actually used:

{
  "data": {
    "answer": "D'après la documentation, ...",
    "sources": [
      { "id": "...", "content": "...", "similarity": 0.87 }
    ],
    "model": "claude-3-5-sonnet",
    "tokens": 412,
    "provider": "anthropic",
    "fallback_used": false
  }
}

The retrieved content is never injected raw into the system prompt. This is unreliable data (user upload, indexed page): a document which contains, for example, a closing section tag followed by false instructions is neutralized before assembly, its angle brackets replaced by brackets, text preserved but structure defused.

If you've already built a RAG chatbot on PostgreSQL with LangChain, each manual brick has a server-side managed equivalent here, without changing the underlying database.

Are you migrating an existing project built on Supabase with this kind of manual assembly? The migration logic for the rest of the backend (schema, RLS policies, SDK) is covered in our Supabase to Aurabase migration guide.

Three settings directly affect cost and latency, all verified in the aura-aiservice code. The number of attempts on a transient embedding call (network failure, provider error 429) is 3 by default, with a base backoff of 100 ms. Large documents are embedded in sub-batches limited to 2048 chunks by default, to respect the suppliers' API ceilings without failing on a single massive document. A guardrail (10,000 chunks by default, configurable) explicitly rejects the ingestion of a document that would produce an aberrant number of chunks.

On the search side, the size of the HNSW candidate list automatically adjusts to max(64, top_k × 4): the more results you request, the more candidates the index explores to preserve recall. A fixed value remains possible via an environment variable if your corpus has a particular profile.

The vector spaces of different models are never compared with each other: each search remains limited to the current embedding model, and changing models requires an explicit reindexing rather than a silent switch which would break the consistency of the results.

The embedding dimension must fall into one of three supported classes: 768, 1536, or 3072. A provider that returns another dimension is rejected with an explicit error, never truncated or silently cast.

Embedding generation is only available via OpenAI or Google Gemini among the three native providers: Anthropic/Claude does not expose a public embeddings API, so it is only used for generating the final response in this pipeline, never for vectorization.

The JavaScript SDK does not yet expose the top_k and threshold overrides on the rag() call: they remain accessible in direct HTTP, limited respectively to [1, 50] and [0, 1], but not from aura.ai.rag() as is today. The default similarity threshold (0.3) is deliberately permissive; narrow it down to a dense corpus to avoid irrelevant sources in the prompt.

The RAG covers questions on unstructured content (documents, notes, tickets). For questions about your relational data, Aurabase's native NL2SQL directly translates a question into validated SQL. For details of the HNSW parameters and dimension classes mentioned above, see the article on HNSW indexing and the comparison of embedding dimensions. The full API reference remains the RAG & pgvector documentation and the AI Gateway documentation.