Building a High-Performance RAG Solution with Pgvectorscale and Python

1. RAG (Retrieval-Augmented Generation)

RAG enhances the response generation process by retrieving relevant documents from an external knowledge base (e.g., a vector database) and using these documents to inform the generated responses. It combines:

2. Pgvectorscale

Pgvectorscale is an extension for PostgreSQL that enables high-performance vector similarity searches using embeddings. It builds on pgvector, allowing storage and indexing of high-dimensional vectors, making it suitable for large-scale RAG systems.

3. Setting Up the Components

To build the RAG solution, you'll need:

4. Build a High-Performance RAG Solution

Step 1: Install PostgreSQL, pgvector, and Pgvectorscale

Install the necessary components:


    CREATE EXTENSION vector;
    CREATE EXTENSION pgvectorscale;

Step 2: Generate Embeddings

Use a model to create embeddings for your documents:


    from sentence_transformers import SentenceTransformer
    model = SentenceTransformer('all-MiniLM-L6-v2')
    
    documents = ["Your document text here", "Another document text"]
    embeddings = model.encode(documents)

Step 3: Store Embeddings in PostgreSQL

Create a table to store the embeddings:


    CREATE TABLE documents (
        id SERIAL PRIMARY KEY,
        text TEXT,
        embedding VECTOR(768)
    );

Insert documents and embeddings:


    import psycopg2
    conn = psycopg2.connect("dbname=test user=postgres")
    cur = conn.cursor()

    cur.execute("INSERT INTO documents (text, embedding) VALUES (%s, %s)",
                (document_text, embedding.tolist()))
    conn.commit()

Step 4: Retrieve Relevant Documents

Query the vector database for the most relevant documents:


    SELECT * FROM documents
    ORDER BY embedding <=> query_embedding
    LIMIT 5;

Step 5: Generate Responses Using the Retrieved Documents

Use a generation model like GPT to create a response:


    from transformers import GPT2LMHeadModel, GPT2Tokenizer

    model = GPT2LMHeadModel.from_pretrained('gpt2')
    tokenizer = GPT2Tokenizer.from_pretrained('gpt2')

    prompt = f"Based on the following documents:\n{retrieved_documents}\nAnswer the question: {user_query}"

    inputs = tokenizer(prompt, return_tensors="pt")
    outputs = model.generate(inputs['input_ids'], max_length=500)
    response = tokenizer.decode(outputs[0])
    print(response)

5. Optimization for High Performance

To ensure high performance, use indexing and parallel queries in PostgreSQL. For large-scale datasets, distribute the retrieval tasks across multiple nodes.

6. Use Cases

References:

How the Pipeline Fits Together

RAG Pipeline: Indexing and Query Time DocumentsPDF, HTML, text Chunk300-800 tokens Embede.g. MiniLM, 384-d PostgreSQLpgvector + index User questionnatural language Embed querysame model Top-k searchcosine distance Prompt + contextLLM Answerwith citations Top row runs offline when documents change. Bottom row runs per request and must use the same embedding model as indexing.

A RAG system has two halves. The indexing half splits documents into chunks small enough to be specific but large enough to carry context, embeds each chunk into a vector, and stores the text, vector and metadata (source, page, timestamp) together. The query half embeds the user question with the same model, retrieves the nearest chunks by vector distance, and places them in the prompt with an instruction to answer only from the supplied context. The generator then produces an answer grounded in current, private data that the model was never trained on, and can cite the chunks it used.

Vector Storage in PostgreSQL

pgvector Index Choices No indexexact scan HNSWgraph, fast recall IVFFlatclusters, needs data StreamingDiskANNpgvectorscale Exact scans are fine up to tens of thousands of rows; approximate indexes trade a little recall for much lower latency at scale.

pgvector adds a vector(n) column type and distance operators: <-> for Euclidean distance, <=> for cosine distance and <#> for negative inner product. It provides HNSW and IVFFlat approximate indexes. pgvectorscale, from Timescale, adds a StreamingDiskANN index designed for large datasets that do not fit comfortably in memory, plus quantisation to reduce index size. Keeping vectors in PostgreSQL means metadata filters, joins, transactions and backups all work with the tools you already use, which is the main reason to choose it over a dedicated vector database at small to medium scale.

Corrected End-to-End Example

The steps above contain a few mismatches worth fixing: all-MiniLM-L6-v2 produces 384-dimensional vectors, not 768; the pgvectorscale extension is named vectorscale; and the retrieval query needs a distance operator. A consistent minimal version:

CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE;   -- also installs vector
CREATE TABLE documents (
    id        bigserial PRIMARY KEY,
    source    text,
    content   text,
    embedding vector(384)
);
CREATE INDEX ON documents USING diskann (embedding vector_cosine_ops);
-- or, with plain pgvector: USING hnsw (embedding vector_cosine_ops)
import psycopg2
from sentence_transformers import SentenceTransformer

model = SentenceTransformer("all-MiniLM-L6-v2")        # 384-d
conn = psycopg2.connect("dbname=rag user=postgres")
cur = conn.cursor()

def to_pg(vec):
    return "[" + ",".join(f"{x:.6f}" for x in vec) + "]"

chunks = [("handbook.pdf", "Employees accrue 1.5 vacation days per month."),
          ("handbook.pdf", "Remote work requires manager approval.")]
for src, text in chunks:
    emb = model.encode(text, normalize_embeddings=True)
    cur.execute("INSERT INTO documents (source, content, embedding) VALUES (%s, %s, %s::vector)",
                (src, text, to_pg(emb)))
conn.commit()

question = "How many vacation days do I get?"
q = to_pg(model.encode(question, normalize_embeddings=True))
cur.execute("""SELECT source, content, embedding <=> %s::vector AS dist
               FROM documents ORDER BY dist LIMIT 3""", (q,))
context = "\n".join(f"[{s}] {c}" for s, c, _ in cur.fetchall())
prompt = f"Answer using only this context:\n{context}\n\nQuestion: {question}"
print(prompt)   # send to the LLM of your choice

GPT-2, used in step 5, is a small base model without instruction tuning and will not follow the prompt reliably; any modern instruction-tuned model, hosted or local, is a better generator.

When RAG Is the Right Tool

ApproachUse whenLimitation
RAGknowledge changes often, answers need sources, data is privatequality depends on retrieval and chunking
Fine-tuningteach style, format or a narrow taskdoes not reliably add fresh facts; retraining cost
Long-context promptinga few documents fit in the context windowcost and latency grow with every request
Keyword search onlyexact terms, codes, namesmisses paraphrases; often combined with vectors (hybrid)

Pitfalls