RAG on PostgreSQL with pgvector, on your own documents
I build retrieval over your own documents on the PostgreSQL you already run, with pgvector, so answers come from your data and say where they came from.
- You bring
- Your documents, and the questions people ask of them
- You get back
- Retrieval in your Postgres, with sources and a measured hit rate
- How it is cut
- Three milestones: ingestion, retrieval with a test set, the application
What you get
- An ingestion pipeline for your formats that can be rerun when a document changes, without duplicating it.
- A schema in your PostgreSQL with pgvector: embeddings next to the rows and permissions they belong to, HNSW indexes sized for your volume.
- Retrieval that combines vector search with full-text search and your own filters, so a user only ever gets back what they are allowed to read.
- A test set written from real questions, and the measured rate at which the right passage comes back.
- The application layer: an answer that cites its passages and says when the documents do not contain one.
How the work is done
Keep it in the database you have
Embeddings belong next to the rows they describe. In PostgreSQL the same transaction writes the document, its permissions and its vector, and the same query filters by tenant and ranks by similarity. A separate vector database buys throughput most projects never need and a synchronisation problem every project gets.
Measure retrieval before generation
Most wrong answers are retrieval failures: the passage was never fetched. So the second milestone is retrieval alone, scored against questions with known answers. Chunking, the embedding model and the index parameters are tuned against that number, not against how fluent a demo sounds.
Make the answer checkable
Every answer carries the passages it was built from, and the system says so when nothing relevant was found. A reader who can check a source trusts the right answers and catches the wrong ones.
Where I have done this
Retrieval over each user's own documents at 1o1.org, on PostgreSQL 18 with pgvector and HNSW, with a memory graph that persists across sessions.
A personal knowledge system where a deterministic policy decides what may be stored and what never reaches a model.
Who it fits
A good fit when
- You run PostgreSQL already, or are happy to.
- The documents are yours and access to them depends on who is asking.
- People can tell you ten real questions and what a good answer to each looks like.
Not a fit when
- You need sub-10 ms search over billions of vectors: that is a job for a dedicated engine.
- The content is public web pages and a search API would do.
Questions about retrieval on Postgres
- Why pgvector and not a dedicated vector database?
- Because the vectors and the rows they describe are the same data. Keeping them in PostgreSQL means one transaction, one permission model and one backup, and a single query can filter by tenant and rank by similarity. A dedicated engine is the right choice at very large scale or very low latency; below that it mostly adds a second system to keep in sync.
- How do you know the retrieval is good?
- From a test set built out of real questions with known source passages. I report the rate at which the right passage is returned in the top results, before any model writes an answer, and I tune against that number. You keep the test set and can rerun it after every change.
- Can users only see answers from documents they are allowed to read?
- Yes, and that is a main reason to do this in PostgreSQL. The permission filter runs in the same query as the similarity search, so a passage a user cannot read is never fetched, rather than fetched and hidden afterwards.
- What does a RAG project cost?
- It follows the number of document formats, the volume, and whether access is per user. I quote per project in writing, with a price for each milestone, after reading your brief and looking at a sample of the documents.