Building Semantic Search and Retrieval-Augmented Generation (RAG) with PostgreSQL and pgvector

Introduction to Vector Search and RAG Architecture

Modern web applications increasingly demand context-aware artificial intelligence capabilities, specifically Retrieval-Augmented Generation (RAG). RAG allows Large Language Models (LLMs) to query external knowledge bases dynamically, drastically reducing hallucinations and keeping generated answers accurate and up-to-date. Traditionally, engineers deployed dedicated vector databases like Pinecone, Milvus, or Qdrant alongside their primary datastores, introducing complex operational overhead, network latency, and eventual consistency challenges.

At Tech Aditya, our engineering team frequently assists clients in optimizing their data architectures. By leveraging the pgvector extension inside PostgreSQL, you can store high-dimensional embeddings directly inside your relational tables. This allows you to perform lightning-fast semantic searches, run exact or approximate nearest neighbor queries, and execute complex business logic with full ACID compliance—all within a single database instance.

If you are scaling custom web solutions or enterprise AI workflows, exploring our Full-Stack Web Development and Laravel Custom Web Applications services can help streamline your backend architecture.

Setting Up PostgreSQL and the pgvector Extension

To begin building your semantic search pipeline, ensure your PostgreSQL instance (version 15 or higher recommended) has the pgvector extension installed. If you are running PostgreSQL via Docker or managed cloud providers like AWS RDS or Supabase, pgvector is typically pre-installed or available via a simple command.

First, enable the extension in your database:

CREATE EXTENSION IF NOT EXISTS vector;

Next, design a robust schema for your documents and chunks. In a production RAG system, long documents are typically split into manageable chunks (e.g., 500 to 1000 tokens) to ensure the embedding captures precise semantic meaning.

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE document_chunks (
    id SERIAL PRIMARY KEY,
    document_id INT REFERENCES documents(id) ON DELETE CASCADE,
    chunk_index INT NOT NULL,
    chunk_text TEXT NOT NULL,
    embedding vector(1536) -- Matches OpenAI text-embedding-3-small dimensions
);

Generating and Storing Embeddings with Python

Once your database schema is established, you need a pipeline to ingest documents, split them into chunks, generate vector embeddings using an LLM provider (such as OpenAI or Hugging Face), and insert them into PostgreSQL.

Below is a clean, production-ready Python script using psycopg and the OpenAI API:

import os
import psycopg
from openai import OpenAI

client = OpenAI(api_key=os.environ.get("OPENAI_API_KEY"))

def generate_embedding(text: str) -> list[float]:
    resp
        input=text,
        model="text-embedding-3-small"
    )
    return response.data[0].embedding

def store_document_chunk(document_id: int, chunk_index: int, chunk_text: str, conn):
    embedding = generate_embedding(chunk_text)
    with conn.cursor() as cur:
        cur.execute(
            """INSERT INTO document_chunks (document_id, chunk_index, chunk_text, embedding)
               VALUES (%s, %s, %s, %s)""",
            (document_id, chunk_index, chunk_text, embedding)
        )
    conn.commit()

Indexing Strategies: HNSW vs. IVFFlat

As your dataset grows past tens of thousands of vectors, sequential scans (exact nearest neighbor search) become computationally expensive, degrading query performance. You must create an approximate nearest neighbor (ANN) index. PostgreSQL with pgvector supports two primary indexing algorithms: IVFFlat and HNSW.

For most high-performance web applications, HNSW is the recommended choice due to superior recall and query speed. Here is how you create an HNSW index using cosine distance (vector_cosine_ops):

CREATE INDEX document_chunks_hnsw_idx 
ON document_chunks 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_c>

Pro-Tip: When building indexes on large tables, ensure your maintenance_work_mem is set sufficiently high in your postgresql.conf to prevent excessively long index creation times.

Executing Semantic Search and RAG Queries

With your index in place, performing a semantic similarity query is straightforward. Pgvector supports three distance operators:

• <->: Euclidean distance (L2 distance)
• <#>: Negative inner product
• <=>: Cosine distance

Here is an SQL query that accepts a query embedding vector and retrieves the top 5 most relevant context chunks:

SELECT 
    id,
    document_id,
    chunk_text,
    1 - (embedding <=> '[0.0123, -0.0456, ...]') AS similarity_score
FROM document_chunks
ORDER BY embedding <=> '[0.0123, -0.0456, ...]' ASC
LIMIT 5;

To complete the RAG loop, you pass these retrieved chunk texts as context into your LLM prompt:

def answer_user_query(user_query: str, conn):
    query_embedding = generate_embedding(user_query)
    
    # Retrieve relevant context from PostgreSQL
    with conn.cursor() as cur:
        cur.execute(
            """SELECT chunk_text FROM document_chunks 
               ORDER BY embedding <=> %s::vector LIMIT 5""",
            (query_embedding,)
        );
        rows = cur.fetchall()
    
    c.join([row[0] for row in rows])
    
    # Construct RAG prompt
    prompt = f"Use the following context to answer the user's question.\n\nContext:\n{context}\n\nQuestion: {user_query}"
    
    resp
        model="gpt-4o-mini",
        messages=[{"role": "user", "content": prompt}]
    )
    return response.choices[0].message.content

Performance Tuning and Production Best Practices

Operating vector searches at scale requires careful database tuning. Consider the following optimization guidelines:

1. Connection Pooling: Use tools like PgBouncer to manage database connections efficiently, especially in high-concurrency web applications.
2. Query-Time Parameters: For HNSW indexes, you can fine-tune recall versus speed dynamically per session by adjusting hnsw.ef_search:

SET hnsw.ef_search = 40; -- Higher values improve recall at the cost of query speed

For custom engineering consultations or tailored AI tool integration, feel free to explore our Web Engineering Services or reach out to our expert team directly.

Conclusion

Implementing vector search and Retrieval-Augmented Generation directly inside PostgreSQL using pgvector simplifies your infrastructure stack, guarantees ACID transactions, and eliminates the synchronization lag associated with external vector databases. By combining robust schema design, HNSW indexing, and precise embedding pipelines, developers can deliver lightning-fast, highly accurate AI experiences.