Sending sensitive proprietary data (internal medical records, source code, financial audits) to public cloud LLM APIs is an immediate compliance violation for many enterprises.
Here is how to build a production-grade Hybrid Retrieval-Augmented Generation (RAG) engine running 100% locally on your own hardware using Ollama, PostgreSQL with pgvector, and BGE-M3 multi-function embeddings.
1. The Tri-Modal Retrieval Funnel
┌────────────────────────────────┐
│ User Question / Query │
└───────────────┬────────────────┘
│
┌───────────────────────┴───────────────────────┐
▼ ▼
┌─────────────────────────┐ ┌─────────────────────────┐
│ Dense Vector Search │ │ Sparse Lexical (BM25) │
│ (Semantic Concepts) │ │ (Exact Product SKUs) │
└────────────┬────────────┘ └────────────┬────────────┘
│ │
└───────────────────────┬───────────────────────┘
│
┌───────────▼───────────┐
│ Reciprocal Rank Fusion│ (RRF Scoring)
│ & BGE-Reranker │
└───────────┬───────────┘
│ Top 5 Reranked Chunks
┌───────────▼───────────┐
│ Ollama Local LLM │ (Llama 3.3 / Qwen 2.5)
└───────────────────────┘
2. PostgreSQL Schema Setup
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE document_chunks (
id BIGSERIAL PRIMARY KEY,
content TEXT NOT NULL,
dense_embedding vector(1024), -- BGE-M3 dimension
tsv_tokens tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED
);
-- High-speed Approximate Nearest Neighbor (ANN) index
CREATE INDEX ON document_chunks USING hnsw (dense_embedding vector_cosine_ops);
-- High-speed Full-Text Search GIN index
CREATE INDEX ON document_chunks USING gin (tsv_tokens);
3. Reciprocal Rank Fusion (RRF) Query
WITH semantic_search AS (
SELECT id, RANK() OVER (ORDER BY dense_embedding <=> $1) as rank
FROM document_chunks
ORDER BY dense_embedding <=> $1
LIMIT 20
),
keyword_search AS (
SELECT id, RANK() OVER (ORDER BY ts_rank_cd(tsv_tokens, plainto_tsquery('english', $2)) DESC) as rank
FROM document_chunks
WHERE tsv_tokens @@ plainto_tsquery('english', $2)
LIMIT 20
)
SELECT d.id, d.content,
COALESCE(1.0 / (60 + s.rank), 0.0) + COALESCE(1.0 / (60 + k.rank), 0.0) AS rrf_score
FROM document_chunks d
LEFT JOIN semantic_search s ON d.id = s.id
LEFT JOIN keyword_search k ON d.id = k.id
WHERE s.id IS NOT NULL OR k.id IS NOT NULL
ORDER BY rrf_score DESC
LIMIT 5;
4. Key Takeaways
- Zero Data Leakage: Not a single byte leaves your private network boundary.
- Flawless Retrieval Accuracy: Dense semantics capture intent while lexical tokens guarantee exact part-number matches.
- Low Infrastructure Cost: Runs entirely on a single Ubuntu workstation with 32GB RAM and an RTX 4090 GPU.
WEEKLY NEWSLETTER
Get Weekly AI Architect Cost & Strategy Updates
Join 14,000+ developers receiving weekly, data-driven cost-reduction blueprints and production-ready agent guidelines.
