Hybrid RAG on PostgreSQL: vectors, keywords and filters together
Why vector search alone fails on real enterprise data, and how to combine pgvector, full-text search and metadata in one database.
Key takeaways
- Most hallucination in enterprise assistants is a retrieval failure, so measure retrieval separately from generation.
- Combine vector similarity, keyword search and structured filters, then re-rank.
- Enforce permissions at retrieval time, not only in the prompt.
Retrieval-augmented generation looks simple in a demo: embed documents, find the nearest vectors, pass them to the model. On real enterprise data it breaks in predictable ways. Hybrid retrieval is how you fix most of them.
Where vector search alone fails
- Exact terms. Part numbers, error codes, people's names and acronyms embed poorly. A query for "ERR-4021" may return text about errors in general.
- Freshness and scope. The nearest vector may be from last year's policy, or from a department the user may not see.
- Negation and constraints. "Contracts expiring this quarter" is a filter, not a similarity question.
The hybrid recipe
1. Ingest with metadata
Parse documents, split them into chunks that respect structure (headings, tables, clauses), and store each chunk with metadata: source, date, owner, access group, document type. Metadata is what makes filters possible later.
2. Retrieve three ways
Run a vector similarity query, a keyword query using PostgreSQL full-text search, and apply structured filters to both. In PostgreSQL this can live in one database: a pgvector column for embeddings, a tsvector column for text, and ordinary columns for metadata.
3. Merge and re-rank
Combine the candidate lists, for example with reciprocal rank fusion, then optionally re-rank the top results with a cross-encoder or a model call. Pass a small set of the best passages to the generator.
4. Answer with sources
Ask the model to answer only from the passages and to cite them. If nothing relevant was retrieved, the correct behaviour is to say so.
Permissions belong in retrieval
Telling the model "do not reveal confidential documents" is not access control. Filter by the user's access groups in the retrieval query itself, so restricted chunks never reach the prompt.
Adding a graph when relationships matter
Some questions follow links: who owns this service, which contracts depend on this supplier. A lightweight graph in the same database, with entities and relationship tables, lets the system traverse instead of hoping the right chunks sit next to each other in vector space.
Measure retrieval on its own
Build a set of real questions with the passages that should answer them. Track recall at k and precision of the retrieved set before you look at the final answer. If retrieval is wrong, no prompt will rescue it.
Do you need a separate vector database?
Often not. PostgreSQL with pgvector handles many workloads and keeps data, permissions and metadata together, which simplifies operations. A dedicated vector store becomes attractive at very large scale or tight latency targets. Decide from measurements on your data, not from fashion.
We cover the engineering details on our hybrid RAG page, and the finished systems usually sit behind a grounded chatbot or an agent.