How to Build a Production RAG System Step by Step (Python, pgvector, Hybrid Search, Reranking)

This tutorial constructs the storage layer for a production Retrieval Augmented Generation (RAG) system. It's not a notebook illustration, but the actual components required: tenant-safe vector search, hybrid retrieval, reranking, and grounded answers. The technology stack includes Python, Postgres with pgvector, OpenAI embeddings, rank_bm25, and sentence-transformers.

Step 1: Design a schema with tenant isolation. The database extension 'vector' is installed if it does not exist. A table named 'chunks' is created with the following columns: id (BIGSERIAL primary key), tenant_id (text not null), doc_title (text), section (text), content (text not null), and embedding (vector of size 1536). Two indexes are created on the table: one using the Hnsw algorithm with 'vector_cosine_ops' for the 'embedding' column, and another on the 'tenant_id' column.

The 'tenant_id' field is present in the table from the outset. SQL permissions are enforced, not in the prompt.

Step 2: Implement structure-aware chunking. The function 'chunk_by_headings' takes text, document title, and maximum characters as parameters. It splits the input text by headings (lines starting with '#' or numbers followed by a period), then it creates chunks of text, each with a maximum of 'max_chars' characters. The heading of each section is prefixed to the chunk to provide additional context for the embedding process.

This ensures that the embedding captures the structure of the document and not just a random slice of text.

Step 3: Embed and store the chunks. The 'emb' variable is an instance of OpenAIEmbeddings, initialized with the 'text-embedding-3-small' model. A connection to the Postgres database is established using the 'psycopg' library. The 'register_vector' function is used to register the vector data type with the database. The 'ingest' function takes tenant_id and a list of chunks as parameters.

It uses the 'emb' instance to generate embeddings for the content of each chunk. Then, it inserts each chunk into the 'chunks' table in the Postgres database, along with its tenant_id, doc_title, section, content, and embedding.

Step 4: Hybrid retrieval using Reciprocal Rank Fusion. The 'vector_search' function takes tenant_id, query, and k (default is 30) as parameters. It uses the 'emb' instance to generate an embedding for the query. Then it fetches the top 'k' chunks from the 'chunks' table for the specified tenant_id where the embedding is closest to the query embedding.

The 'keyword_search' function fetches the top 'k' chunks based on keyword search (i.e., without embedding) for the specified tenant_id. The function 'BM25Okapi' from 'rank_bm25' is used for keyword search. The hybrid retrieval combines these two methods, possibly using Reciprocal Rank Fusion to combine the relevance scores from both methods for a final ranking of the retrieved chunks.