GuidesAI development

Tutorial: a RAG with PostgreSQL and pgvector

By the Banzai Labs teamUpdated on 3 min read

PostgreSQL + pgvectoridtextsourceembeddingThe 5 nearest passages, in one query
A PostgreSQL table with a vector column; one SQL query returns the nearest passages.

1. The table

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE passages (
  id         bigserial PRIMARY KEY,
  source     text NOT NULL,      -- original document
  lang       text NOT NULL,      -- for filtering
  content    text NOT NULL,
  embedding  vector(1024)        -- your model's dimension
);

CREATE INDEX ON passages USING hnsw (embedding vector_cosine_ops);

2. Fill it

For each passage (see chunking documents): compute the embedding with your model, then insert the text, its source and the vector.

SELECT id, source, content
FROM passages
WHERE lang = $2
ORDER BY embedding <=> $1   -- cosine distance
LIMIT 5;

$1 is the embedding of the question, computed with the same model.

4. Answer

  1. 1The passagesAttached to the question, tagged with their source.
  2. 2The instructionAnswer from these passages only, and cite them.
  3. 3The checkAn answer with no citation is flagged.

The full principle: RAG, AI on your documents. Choosing the database: vector databases.

Frequently asked questions

What dimension for the vector column?
The one of the embedding model you chose, stated in its documentation. It must be the same for every vector in the column.
How far does pgvector scale?
Hundreds of thousands to a few million passages on a decent server, with an HNSW index. Beyond that, measure before switching tools.
Can it be combined with keyword search?
Yes, PostgreSQL also has full-text search. The two rankings can be merged.

Read next

All guides