iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Build a production RAG system as two connected pipelines: an ingestion job that keeps a searchable, permission-aware document index current, and an online API that retrieves evidence, reranks it, and generates a cited answer. Python and PostgreSQL with pgvector can support both dense vector search and lexical full-text search, but chunk size, index type, candidate counts, models, and hosting must be tested against your workload.
What should you decide before building?
Start by defining what the system is allowed to answer and how you will tell whether it answered well. Those requirements determine ingestion, authorization, retrieval, and generation decisions; a working demo alone does not establish answer quality.
- Corpus and freshness: list the document sources, how often content changes, and how quickly updates must become searchable.
- Identity and permissions: define tenants, user roles, and which documents each request may retrieve. Do not rely on the model prompt to enforce access.
- Answer contract: specify whether answers need source citations, what counts as a sufficient citation, and when the system must say the corpus does not contain enough evidence.
- Service budgets: set latency, request-volume, and model-cost limits for the online path, plus operational limits for ingestion and reindexing.
- Evaluation questions: assemble representative questions, expected source passages or answer requirements, and questions the collection cannot answer. Keep this set versioned so you can compare changes.
There is no universal minimum size or quality threshold for an evaluation set. A small initial set is useful for catching regressions, but it should grow with the variety of documents, users, and real production failures.
How should ingestion and online answering be separated?
Ingestion changes the index; answering serves a user request. Keeping them as distinct jobs lets you schedule or retry indexing without putting document parsing and embedding work on every user request.
#1 Best Overall
| Pipeline | Responsibilities | Typical trigger |
|---|---|---|
| Ingestion | Load sources, normalize text, split into chunks, preserve metadata, create embeddings, and insert or replace indexed records. | A schedule, a source update, or a controlled reindex. |
| Online answering | Authenticate and authorize, retrieve dense and lexical candidates, combine and rerank results, construct evidence, and generate a cited response. | An authenticated question from a user. |
Track the source document identity and the embedding model or version used for each indexed chunk. When a document changes, replace or upsert its chunks rather than creating duplicates. A controlled reindex should make it possible to rebuild embeddings when the model, chunking strategy, or index representation changes.
How do you design chunks and preserve provenance?
Normalize source content while retaining useful structure. Split along headings, sections, and coherent paragraphs where possible; keep tables and code together when they are meaningful as a unit. Arbitrary fixed-character cuts can separate a statement from the context needed to interpret it.
ProductionAIEngineer’s 2026 implementation tutorial offers 300–800 tokens per prose chunk and 10–15% overlap as starting examples, not proven best settings. Test alternatives using your own retrieval questions: small chunks can make evidence precise but strip away context, while large chunks can preserve context at the cost of less focused retrieval and more prompt space.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Assign stable identifiers to documents and chunks and retain enough metadata to trace each passage back to a usable source. A relational schema can look like this:
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE rag_chunks (
chunk_id text PRIMARY KEY,
document_id text NOT NULL,
tenant_id text NOT NULL,
source_uri text NOT NULL,
title text,
section text,
content_hash text NOT NULL,
content text NOT NULL,
embedding vector(D) NOT NULL,
embedding_model text NOT NULL,
search_vector tsvector GENERATED ALWAYS AS
(to_tsvector('english', content)) STORED
);D is notation for the dimension selected for your embedding model, not literal SQL: replace it with that model’s supported dimension. The example uses PostgreSQL’s English text-search configuration; choose a configuration appropriate to the corpus language and vocabulary. Keep titles, section names, and source identifiers available even if you also include some of them in the text used for search.
Use the stable source metadata to build citations that resolve to a document and section, rather than asking the model to invent a source reference from text alone.
How do you add vector search in PostgreSQL?
With pgvector, store an embedding beside each chunk and compare a query embedding using the distance operator that matches your chosen retrieval metric. The vector column dimensions, query operator, and index operator class must agree with the embedding model and distance choice. pgvector supports multiple distance measures, including L2, inner product, and cosine distance.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteStart with exact nearest-neighbor search when establishing a retrieval baseline. The pgvector project documentation states: “By default, pgvector performs exact nearest neighbor search, which provides perfect recall.” That baseline is useful for measuring whether an approximate index changes which relevant chunks are found.
For a cosine-distance query, the shape is:
SELECT chunk_id, document_id, source_uri, title, section, content,
embedding <=> :query_embedding AS distance
FROM rag_chunks
WHERE tenant_id = :tenant_id
ORDER BY embedding <=> :query_embedding
LIMIT :candidate_count;The named parameters are illustrative placeholders for bound query parameters in your database driver. Apply the caller’s actual authorization constraints in this retrieval branch; tenant filtering alone is sufficient only if tenant membership is the complete access policy.
After measuring the exact-search baseline, test approximate indexing if latency or scale warrants it. pgvector documents HNSW and IVFFlat as options with different speed, recall, memory, and build/update trade-offs. Benchmark against the corpus and query patterns you expect rather than assuming one index is best.
How do hybrid search and reranking fit together?
Dense vector search finds passages with related meaning, including paraphrases. PostgreSQL full-text search can help with exact names, phrases, and technical identifiers that matter even when their surrounding meaning is similar. Hybrid search combines these distinct signals; neither is a substitute for applying the same authorization rules in both branches.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallA lexical branch can use PostgreSQL’s text-search vector and a query parsed with the corpus-appropriate configuration. In the schema example, an English-language query could be shaped as follows:
SELECT chunk_id,
ts_rank(search_vector, websearch_to_tsquery('english', :query)) AS score
FROM rag_chunks
WHERE tenant_id = :tenant_id
AND search_vector @@ websearch_to_tsquery('english', :query)
ORDER BY score DESC
LIMIT :candidate_count;PostgreSQL full-text search behavior depends on parsing, dictionaries, ranking, and language configuration. Inspect results for the actual names, identifiers, and languages in your documents instead of treating this example configuration as universal.
Fuse rankings before comparing unlike scores
Vector distance and lexical rank are not naturally comparable score scales. Reciprocal rank fusion (RRF) combines each branch’s rank position instead of adding uncalibrated raw scores. Retrieve a candidate list from each authorized branch, combine their ranks, and deduplicate by chunk ID.
WITH dense AS (
SELECT chunk_id,
row_number() OVER (ORDER BY embedding <=> :query_embedding) AS r
FROM rag_chunks
WHERE tenant_id = :tenant_id
ORDER BY embedding <=> :query_embedding
LIMIT :dense_count
),
lexical AS (
SELECT chunk_id,
row_number() OVER (
ORDER BY ts_rank(search_vector,
websearch_to_tsquery('english', :query)) DESC
) AS r
FROM rag_chunks
WHERE tenant_id = :tenant_id
AND search_vector @@ websearch_to_tsquery('english', :query)
ORDER BY ts_rank(search_vector,
websearch_to_tsquery('english', :query)) DESC
LIMIT :lexical_count
),
combined AS (
SELECT chunk_id, sum(1.0 / (60 + r)) AS fused_score
FROM (
SELECT chunk_id, r FROM dense
UNION ALL
SELECT chunk_id, r FROM lexical
) ranked
GROUP BY chunk_id
)
SELECT c.chunk_id, c.content, c.source_uri, c.title, c.section,
combined.fused_score
FROM combined
JOIN rag_chunks c USING (chunk_id)
WHERE c.tenant_id = :tenant_id
ORDER BY combined.fused_score DESC
LIMIT :rerank_count;This is an illustrative RRF query, including a common rank constant of 60; it is not a tuned value for every application. If access rules are more specific than tenant membership, put those constraints into both candidate branches and retain them when fetching the final chunk records. Where practical, enforce the same policy at the database layer as well as in application query construction.
Use a reranker to refine candidates
RRF fuses retrieval lists; a cross-encoder reranker is a separate optional step that scores query-passage relevance for the fused candidates. Reranking can improve ordering among retrieved passages, but it cannot recover evidence that neither retrieval branch found. pgvector’s documentation discusses combining search with reciprocal rank fusion or a cross-encoder; the choice depends on your quality and latency needs.
ProductionAIEngineer’s 2026 tutorial uses about 20 candidates reranked to 4 passages as an example pipeline, not a universal optimum. Increasing candidate counts or adding a model call may increase latency and cost, while sending too few passages may omit needed evidence. Tune both retrieval depth and final context size against your evaluation set and service budgets.
How do you generate grounded answers and citations?
Give the generator a bounded evidence set and an explicit response contract. Include the question, retrieved passages with stable source identifiers, and instructions to distinguish supported claims from missing evidence. The system should abstain or state that the corpus does not answer the question when the retrieved material is insufficient, rather than fill gaps with plausible-sounding claims.
Keep citation construction under application control. Associate each evidence passage with its document and section metadata, then require answer citations to refer only to those supplied identifiers. Validate that every returned citation maps to an accessible source; do not treat a citation-shaped string generated by a model as proof that the source exists or supports the claim.
Separate retrieval, reranking, prompt construction, and generation into testable components. This makes it possible to identify whether a failure came from missing evidence, poor ordering, insufficient context, or unsupported generation rather than treating every bad answer as a single model problem.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How should you evaluate retrieval and answer quality?
Score retrieval and generation separately. A correct answer can sometimes be guessed without the intended evidence, while a good retrieval result can still be mishandled by answer generation.
- Retrieval: check whether relevant source chunks appear in the candidate list and in the final context. Track an appropriate hit or recall measure at the candidate and context cutoffs you use.
- Answer quality: check correctness and required facts against the expected evidence, and inspect whether citations support the claims they accompany.
- Abstention: include questions the corpus cannot answer and verify that the system does not present unsupported answers as established facts.
- Regression: rerun the versioned question set after changes to prompts, models, chunking, retrieval settings, or indexes, and add representative real-world misses.
Compare approximate pgvector search with exact search on representative queries to estimate recall loss. The acceptable balance depends on your latency and quality requirements; the tutorial and project documentation do not establish universal quality thresholds.
What needs to be in place before deployment?
A Python API such as FastAPI can expose the online path, while a separate worker or scheduled job runs ingestion. The framework and container are implementation choices; deployment needs depend on your hosting environment.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
- Bound work: set request-size, retrieval-count, context-size, and model-call limits so a single request cannot expand without limit.
- Handle failures: configure timeouts and bounded retries for database and model-provider calls, and return a controlled error when a dependency is unavailable.
- Observe the path: record latency by stage, retrieval counts, failures, and model usage without logging secrets or exposing protected document text unnecessarily.
- Protect credentials: keep database and model credentials in the hosting platform’s secret-management mechanism rather than source code.
- Make operations repeatable: schedule ingestion explicitly, make jobs safe to retry, monitor job failures, and version prompts and evaluation data.
- Check service health: expose health and readiness behavior appropriate to the host, and document how operators identify stale ingestion or unavailable dependencies.
FastAPI’s deployment guidance covers framework-level deployment concepts; adapt it to the selected hosting environment. A managed database or cloud RAG service may reduce some operational responsibilities, while self-managed PostgreSQL can offer a different balance of control, portability, and maintenance. Google’s Cloud SQL and Vertex AI lab documents one integration path, not a universal deployment winner.
Which choices should you benchmark rather than assume?
| Decision | Options to compare | Evaluate against |
|---|---|---|
| Vector index | Exact scan; approximate HNSW or IVFFlat | Recall, latency, memory or index size, and build/update behavior on your data. |
| Retrieval signals | Dense only; dense plus PostgreSQL full-text search | Paraphrases versus exact terms, retrieval quality, and query cost. |
| Ranking | RRF fusion; optional cross-encoder reranking after retrieval | Ordering quality and the latency and cost added by reranking. |
| Embedding deployment | Hosted API; locally hosted model or another provider | Domain retrieval quality, privacy, latency, operations, model lifecycle, and total cost. |
| Hosting | Self-managed PostgreSQL and API; managed database or cloud RAG services | Operational responsibility, data location, integration, scaling, cost, and portability. |
Model names, dimensions, prices, and service terms change. Select a provider and deployment based on current requirements and measured behavior; the available implementation examples do not establish a best provider or universally best hosting arrangement.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
