pgvector Guide: Build AI Search and RAG in Postgres

pgvector is the Postgres extension that stores embeddings in a vector column and finds the nearest ones with an index, so your AI features can live in the same database as your users, permissions and app data. You do not need a separate vector database for most apps: one create extension, a column, an HNSW index and a SQL function give you semantic search and retrieval for RAG, with Row Level Security deciding which documents each user can retrieve. This guide builds that end to end on StitchDB, where vector is one of the available extensions.

#What you are building

A typical AI feature has two paths:

1. Ingest: when a document is saved, split it into chunks, get an embedding for each chunk from a model, and store chunk text plus vector.
2. Retrieve: when a user asks something, embed the question, find the closest chunks, and pass them to a language model as context.

pgvector handles storage and the "find the closest" step. The embedding model and the language model are API calls, best made from server code that holds the keys, such as an Edge Function.

#Step 1: enable pgvector and create the table

create extension if not exists vector with schema extensions;

create table public.documents (
  id bigint generated always as identity primary key,
  content text,
  embedding extensions.vector(1536)
);

The number in vector(1536) is the dimension count and must match your embedding model. For example, OpenAI's text-embedding-3-small returns 1,536 numbers by default. A vector column can hold up to 16,000 dimensions, but the HNSW and IVFFlat indexes support up to 2,000, which matters if you pick a large model.

For a real app you usually want ownership and a link back to the source:

create table public.chunks (
  id bigint generated always as identity primary key,
  user_id uuid not null default auth.uid() references auth.users on delete cascade,
  document_id bigint not null references public.documents on delete cascade,
  content text not null,
  embedding extensions.vector(1536) not null
);

alter table public.chunks enable row level security;

create policy "Users read their own chunks" on public.chunks
  for select to authenticated
  using (user_id = auth.uid());

This policy is the reason to keep vectors in Postgres. Retrieval for user A can never return user B's chunks, because the database filters them before the language model sees anything.

#Step 2: choose a distance

pgvector has four distance operators:

Cosine distance is the safe default for text embeddings. If your model returns normalized vectors (length 1), inner product gives the same order and is slightly cheaper; note that <#> returns the negative inner product, so smaller still means closer and order by stays ascending.

The index must use the operator class that matches the operator in your query, or Postgres will not use it.

#Step 3: add an index

Without an index, a nearest-neighbor query compares the question against every row. That is exact and fine for a few thousand rows. Beyond that, add an approximate index.

#HNSW

create index on public.chunks using hnsw (embedding vector_cosine_ops);

HNSW builds a layered graph. It gives a good speed-to-recall trade-off, works well on tables that grow, and can be created before you have data. It takes longer to build and uses more memory than IVFFlat. At query time, hnsw.ef_search (40 by default) controls how many candidates are explored; raise it for better recall at some cost in speed:

set hnsw.ef_search = 100;

#IVFFlat

create index on public.chunks using ivfflat (embedding vector_cosine_ops) with (lists = 100);

IVFFlat groups vectors into lists and searches the closest ones. It builds faster and uses less memory, but it should be created after the table has data, and the pgvector docs suggest about rows / 1000 lists for up to a million rows. If you are unsure, start with HNSW.

Both indexes are approximate: they can miss a true nearest neighbor in exchange for speed. For most search and RAG use, that is fine. Run select extversion from pg_extension where extname = 'vector' to see which pgvector version your database has; HNSW needs 0.5.0 or newer.

#Step 4: a match function

Wrap retrieval in a SQL function so clients call it with RPC:

create function public.match_chunks(query_embedding extensions.vector(1536), match_count int default 5)
returns table (id bigint, document_id bigint, content text, distance float)
language sql stable as $$
  select c.id, c.document_id, c.content, c.embedding operator(extensions.<=>) query_embedding as distance
  from public.chunks c
  order by c.embedding operator(extensions.<=>) query_embedding
  limit match_count
$$;

The operator(extensions.<=>) form names the schema where the extension lives; if extensions is on your search_path, plain <=> works too. The function is security invoker by default, so the policy on chunks applies: each user only matches their own rows.

You can add ordinary filters next to the vector search, for example where c.document_id = any(doc_ids) or a date range. Postgres applies them together with the policy. With an approximate index, very selective filters can return fewer than match_count rows, because filtering happens after the index finds candidates; raising hnsw.ef_search helps.

#Step 5: ingest from an Edge Function

The embedding API key must stay on the server. This Edge Function receives a document, embeds its chunks and stores them as the calling user, so RLS checks the insert:

import { createClient } from 'https://stitchdb.com/sdk/stitchdb-js.mjs'

async function embed(input: string[]) {
  const res = await fetch('https://api.openai.com/v1/embeddings', {
    method: 'POST',
    headers: { Authorization: `Bearer ${Deno.env.get('OPENAI_API_KEY')}`, 'Content-Type': 'application/json' },
    body: JSON.stringify({ model: 'text-embedding-3-small', input }),
  })
  const json = await res.json()
  return json.data.map((d: { embedding: number[] }) => d.embedding)
}

Deno.serve(async (req) => {
  const db = createClient(Deno.env.get('STITCHDB_URL')!, Deno.env.get('STITCHDB_ANON_KEY')!, {
    global: { headers: { Authorization: req.headers.get('Authorization') ?? '' } },
    auth: { persistSession: false },
  })
  const { document_id, text } = await req.json()
  const parts = text.match(/[\s\S]{1,2000}/g) ?? []   // naive 2,000-character chunks
  const vectors = await embed(parts)
  const rows = parts.map((content: string, i: number) => ({ document_id, content, embedding: vectors[i] }))
  const { error } = await db.from('chunks').insert(rows)
  if (error) return Response.json({ error: error.message }, { status: 400 })
  return Response.json({ chunks: rows.length })
})

Add the key with stitchdb secrets set OPENAI_API_KEY=.... For this insert to work, add an insert policy on chunks with with check (user_id = auth.uid()). Waiting on the embeddings API does not count toward a function's CPU time, only toward its wall clock. For large imports, move the work into a background job so each step is retried on failure.

Chunking matters more than most settings. Split on paragraphs or headings rather than fixed characters when you can, keep chunks a few hundred words long, and store a little overlap so an answer that spans two chunks is still found.

#Step 6: retrieve and answer

From the same function, or a second one, embed the question and call the match function:

const [queryEmbedding] = await embed([question])
const { data: matches } = await db.rpc('match_chunks', { query_embedding: queryEmbedding, match_count: 5 })
const context = (matches ?? []).map((m: { content: string }) => m.content).join('\n---\n')
// Send `context` and `question` to your language model, then return its answer.

Because db acts as the caller, the retrieved context only ever includes documents that user may read. That is much harder to get right when vectors live in a separate store with its own permission model.

#When you do not need any of this

If what you want is a search box that understands meaning, not a custom RAG pipeline, StitchDB's built-in search index does the embedding and ranking for you. Pick a table and columns in Database › Search, turn on search by meaning, and call stitchdb.search('posts', 'running shoes'). Hybrid mode merges keyword and semantic ranking, results respect RLS, and semantic search is included from the Pro plan with 500,000 embeddings a month (as of November 2026; see pricing). See Postgres full text and semantic search for that path.

Use pgvector directly when you need your own model, your own chunking, custom distance or filters, or retrieval for an LLM.

#Sizing and performance notes

• Storage: a vector(1536) takes about 6 KB per row (4 bytes per dimension). A million chunks is roughly 6 GB before indexes, which matters against your plan's database size (limits).
• Index memory: HNSW builds are faster when the index fits in maintenance_work_mem. Build large indexes in a quiet period.
• Dimensions: smaller embeddings are cheaper to store and search. Some models let you request fewer dimensions; test whether recall stays good enough.
• Re-embedding: changing models means new vectors for every row, and the column dimension may change. Keep the source text so you can re-run ingestion.

#FAQ

#Do I need a separate vector database?

Not for most apps. pgvector handles millions of vectors with an HNSW index, and keeping them in Postgres gives you joins, transactions and Row Level Security on the same rows.

#Which embedding model should I use?

Any model works as long as the column dimension matches and you use the same model for documents and queries. Compare a couple on your own data before committing.

#Can AI agents query my vectors?

Yes, through SQL. If you connect an assistant over MCP, give it a read-only, project-scoped connection; see connecting AI tools to Postgres with MCP.

#Does pgvector work with the REST API?

Yes, through RPC functions like match_chunks. Filtering by vector distance in a plain REST query is not the intended path; use a function.

Build your AI feature next to your data: create a free project and enable vector under extensions.