GuidesAI development
Tutorial: a RAG with PostgreSQL and pgvector
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.
3. Search
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
- 1The passagesAttached to the question, tagged with their source.
- 2The instructionAnswer from these passages only, and cite them.
- 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.