Building a Private RAG Stack on PostgreSQL: pgvector, BM25 and TEI in Practice

A private RAG architecture can become complicated very quickly.

It is easy to end up with one database for application data, another vector database, a separate search engine, an embedding API, a reranking API, a cache and several synchronization jobs holding the whole thing together.

Sometimes that complexity is justified.

Sometimes it is architecture created before the workload asked for it.

While building CaBrain, my long-term memory and retrieval layer for AI agents, I chose a different center of gravity: PostgreSQL.

The stack combines relational data, vector retrieval, BM25-style lexical retrieval, reranking and dedicated embedding/reranking services. The goal is not to prove PostgreSQL can replace every specialized search product. It is to keep the system understandable while still giving retrieval more than one signal.

The architecture at a glance

Conceptually, the retrieval path looks like this:

Documents/memories → normalization/chunking → embeddings → PostgreSQL

At query time:

Query → vector retrieval + lexical/BM25 retrieval → fusion/ranking → reranking → selected context → model/agent

Around that sit metadata, permissions, namespaces, entity relationships and memory lifecycle logic.

Embedding and reranking can run behind TEI (Text Embeddings Inference) services, keeping those model-serving concerns separate from the application database.

This separation is important: PostgreSQL owns durable data and retrieval indexes; inference services do inference.

Why PostgreSQL as the center?

CaBrain already needs structured information, not only text chunks.

A memory or knowledge item can have:

• Source and provenance.
• Namespace or corpus.
• Entity relationships.
• Timestamps.
• Salience/lifecycle metadata.
• Permissions.
• Structured attributes.
• Vector representation.
• Lexical-search representation.

That makes a relational database a natural home for the durable record.

Using PostgreSQL also means transactions, constraints, joins, backups and operational tooling remain available instead of rebuilding those concerns around a vector-only store.

The point is not “one database is always better.”

The point is to avoid adding another stateful system until its benefit is clear.

pgvector adds semantic retrieval without moving the data away

Semantic retrieval is useful when the user's wording differs from the stored text.

Embeddings turn a query and stored content into vectors that can be compared for similarity.

With pgvector-compatible storage/indexing inside PostgreSQL, the vector representation can live beside the record it describes.

That makes several operations easier to reason about:

Record → metadata → permissions → vector

remain part of the same durable data model.

You do not need a synchronization pipeline whose only job is keeping a separate vector database consistent with your primary database.

That reduction in moving parts is valuable to me.

But vector search is not enough

Semantic similarity is powerful, but exact language still matters.

Consider queries containing:

• A repository name.
• A package name.
• An issue key.
• A product code.
• A person's name.
• A precise technical term.

The semantically nearest passage is not necessarily the passage containing the exact identifier the agent needs.

This is why the CaBrain retrieval design combines semantic retrieval with BM25-style lexical search.

The two signals solve different failure modes.

What BM25 contributes

Lexical retrieval rewards documents containing terms related to the query based on term occurrence and rarity rather than embedding proximity alone.

For exact terminology, it can be extremely useful.

Imagine a memory corpus containing several notes about databases, but only one mentions VectorChord or a particular issue key.

Semantic search may retrieve broadly related database material.

Lexical retrieval can strongly surface the exact term.

Neither method needs to be declared the winner.

Retrieve candidates from both and combine them.

Hybrid retrieval is about candidate diversity

A simplified hybrid pipeline can be:

1. Generate the query embedding.
2. Retrieve semantic candidates.
3. Retrieve lexical candidates.
4. Combine the candidate sets.
5. Apply a fusion/ranking strategy.
6. Rerank the strongest candidates.
7. Return only the context the downstream model needs.

The important idea is that the first retrieval stage should maximize the chance that the correct evidence enters the candidate pool.

The reranker can then spend more computation deciding which candidates are actually best for the query.

Why rerank after retrieval?

Initial retrieval methods are optimized for finding candidates efficiently.

A reranker can evaluate the query-document relationship more carefully over a much smaller set.

That gives the architecture two stages:

Fast retrieval → more expensive relevance judgment

instead of applying the expensive model to the entire corpus.

For RAG, this matters because context space is limited and irrelevant chunks have a cost: they consume tokens and can distract the generation model.

Better retrieval is also context engineering.

TEI keeps embedding infrastructure behind a service boundary

In CaBrain's architecture, embedding and reranking can be served through TEI rather than being buried inside the application process.

Conceptually:

Application → TEI embedding service → vectors

and:

Candidate pairs → TEI reranking service → relevance ordering

This gives the application a stable interface to inference infrastructure.

The embedding model can evolve without changing the domain model of the application.

The same principle applies to reranking.

This is especially useful in private AI environments where the organization wants those inference operations to run inside infrastructure it controls.

Private RAG is more than a local model

Running an LLM locally does not automatically make the complete system private.

Trace the entire data path.

A private RAG request may touch:

• Application API.
• Authentication layer.
• Database.
• Embedding model.
• Reranker.
• LLM.
• Logs/traces.
• Object storage.
• Backups.

If sensitive text is sent to an external embedding endpoint while the final LLM runs locally, the architecture is not fully private in the way many businesses expect.

Privacy is an end-to-end data-flow property.

Ingestion should preserve provenance

RAG quality starts before retrieval.

When content enters the system, keep enough information to answer:

• Where did this come from?
• When was it captured?
• Which document/version produced this chunk?
• Which tenant or namespace owns it?
• Is it still valid?
• Who is allowed to retrieve it?

Without provenance, a fluent generated answer may be impossible to audit.

This becomes particularly important when the knowledge base changes over time.

Chunking is a domain decision

There is no universal chunk size that makes every RAG system work.

A policy document, source-code repository, customer ticket and long-term agent memory have different structure.

Chunk boundaries should preserve useful meaning.

Sometimes headings and sections are natural boundaries. Sometimes structured records should not be converted into arbitrary text chunks at all.

Before tuning vector indexes, make sure the retrieval unit itself represents something useful.

Metadata filtering should happen early

Suppose the corpus contains several tenants or permission levels.

Do not retrieve globally and ask the LLM to ignore results it should not see.

Filter the search space at the data/retrieval layer using authorized metadata.

Examples include:

• Tenant/workspace.
• Corpus/namespace.
• Document type.
• Visibility level.
• Language.
• Validity state.

This improves both security and retrieval precision.

PostgreSQL makes structured filters natural

This is one area where keeping retrieval close to relational data is attractive.

The query is rarely just:

Find the nearest vector.

It is often closer to:

Find relevant memories in this corpus, belonging to this tenant, visible to this actor, still active, then rank them for this query.

Relational filters and retrieval signals naturally meet in that problem.

Fusion matters

When vector and lexical retrieval each return ranked lists, the system needs a way to combine them.

One approach is rank-based fusion such as Reciprocal Rank Fusion (RRF) rather than trying to compare raw scores produced by fundamentally different retrieval methods.

The exact strategy can vary.

The useful principle is to avoid pretending a cosine-similarity score and a lexical relevance score have directly comparable meaning.

Fuse rankings deliberately, then evaluate the result on your own queries.

Retrieval needs an evaluation set

A retrieval architecture cannot be judged from a few impressive demos.

Create a set of real questions with expected evidence.

Then measure things such as:

• Did the correct document enter the candidate set?
• Was the correct passage high enough in the final ranking?
• Did hybrid retrieval beat vector-only for exact-term queries?
• Did reranking improve or damage the ordering?
• Did metadata filters remove required evidence accidentally?
• Did stale documents outrank current ones?

The answers should drive architecture changes.

Retrieval evaluation and answer evaluation are separate

This distinction is worth repeating.

If the correct context never reaches the generation model, prompt tuning cannot repair retrieval.

If the correct context is present but the final answer is wrong, the retrieval system may be doing its job.

Log the layers independently:

Query → retrieved candidates → fused ranking → reranked context → generated answer

That trace makes debugging far more precise.

PostgreSQL does not remove scaling questions

Keeping the stack centered on PostgreSQL simplifies architecture, but it does not eliminate capacity planning.

As the corpus grows, you still need to think about:

• Index strategy.
• Query latency.
• Concurrent workload.
• Vector dimensionality.
• Ingestion throughput.
• Vacuum/maintenance behavior.
• Storage growth.
• Connection management.
• Replication/backups.

At some workload, a specialized system may become the correct choice.

The decision should come from measured constraints rather than the assumption that “AI requires a vector database product.”

Separate online and ingestion workloads when needed

Embedding large document batches can create a very different workload from serving user queries.

Use queues and workers so ingestion does not block interactive requests.

A practical path is:

Source → ingestion queue → parse/chunk → embedding service → transactional write/index

while queries follow:

Request → retrieve → rerank → generate

This makes backpressure and retries easier to manage.

Embedding model changes need a migration strategy

Vectors are derived data.

If you change embedding models, old and new vectors may not be compatible in the same similarity space.

Treat the embedding model/version as part of the indexed representation.

A migration may require generating a new vector set and switching traffic after it is ready rather than overwriting everything blindly.

The durable source content should remain available so derived indexes can be rebuilt.

The same applies to chunking

If you change chunking logic, the resulting retrieval units change.

That is another reason to keep source documents and provenance separate from derived chunks and embeddings.

RAG infrastructure should be rebuildable.

Your vector index should not become the only surviving copy of knowledge.

Caching can help, but freshness wins

Some embeddings and retrieval operations are reusable.

Unchanged document embeddings should not be regenerated for every query.

Frequently repeated queries may have reusable intermediate results depending on the use case.

But caching retrieved answers without understanding source freshness can create stale business responses.

Cache derived computation carefully; preserve authority in the underlying data.

RAG and long-term memory overlap, but are not identical

CaBrain uses retrieval as part of agent memory, which adds lifecycle questions beyond ordinary document RAG.

A document knowledge base often asks:

Which source passages answer this question?

Long-term memory also asks:

What should be retained, updated, consolidated or forgotten over time?

The retrieval infrastructure can be shared, while the memory lifecycle sits above it.

This separation lets the same underlying retrieval capabilities support more than one AI feature.

An entity graph complements text retrieval

Some questions are relational rather than textual.

If an agent needs to know which company uses which technology, which repository belongs to which organization or which event changed an entity, graph relationships can be more direct than repeatedly rediscovering those links from chunks.

In CaBrain, the entity graph complements hybrid recall rather than replacing it.

Again, different retrieval abstractions solve different questions.

Keep generation replaceable

A RAG architecture should not make its knowledge layer dependent on one generation provider.

The durable system should own documents, permissions, retrieval and provenance.

The final model is a consumer of selected context.

That lets you route between hosted and local models, change providers or use different models for different tasks without rebuilding the knowledge base.

For private AI, this flexibility is especially useful because privacy, quality and cost requirements can differ by workflow.

What I like about this architecture

The strongest property is not a benchmark number.

It is separation of concerns without unnecessary fragmentation.

PostgreSQL remains the durable center.

Vector and lexical retrieval provide complementary candidate signals.

Reranking improves the final context selection.

TEI keeps embedding/reranking inference behind explicit service boundaries.

The agent or LLM receives only selected context.

And additional capabilities — memory lifecycle or entity relationships — can sit above the same foundation.

What this architecture does not claim

It does not claim PostgreSQL will beat every specialized vector/search database at every scale.

It does not claim hybrid retrieval automatically improves every corpus.

It does not claim reranking is always worth its latency.

Those are workload-dependent questions.

The architecture gives you components you can measure independently and replace when evidence says you should.

That is more important to me than choosing infrastructure by trend.

A practical decision checklist

Before building a private RAG stack, ask:

1. What data must remain private?
2. Where will embeddings be generated?
3. Where will reranking run?
4. Where will the final LLM run?
5. What is the authoritative source of each document?
6. How are tenant and permission boundaries enforced?
7. Do exact terms matter enough to need lexical retrieval?
8. How will vector and lexical rankings be fused?
9. Is reranking improving retrieval on a real evaluation set?
10. Can embeddings/chunks be rebuilt after model or chunking changes?
11. How will stale content be removed or invalidated?
12. At what measured constraint would a specialized retrieval service become justified?

Those questions determine the architecture more reliably than asking which vector database is most popular.

Private RAG should be boring where possible

AI systems already contain uncertainty at the model boundary.

I prefer the surrounding infrastructure to be as explicit as possible.

Durable records in a proven database.

Clear permission filters.

Rebuildable derived indexes.

Observable retrieval stages.

Bounded inference services.

Measurable evaluation.

That gives the probabilistic part of the system a stable foundation.

For CaBrain, PostgreSQL plus vector and lexical retrieval, with TEI-backed inference services and reranking, provides that foundation while leaving room to evolve individual components later.

The goal is not the smallest technology list for its own sake.

It is an architecture where every additional component has a reason to exist.

Building a private RAG system and deciding how much infrastructure you actually need?

I design private AI and retrieval architectures around data boundaries, hybrid search, evaluation, model portability and production operations — from PostgreSQL-centered systems to specialized components when the workload genuinely requires them.

---

Related: Private AI, Agent Memory with CaBrain, RAG vs Fine-Tuning vs AI Agents, Local LLM vs Hosted AI and RAG Chatbot vs Scripted Bot.