RAG with pgvector: Vector Search in PostgreSQL

You're working with PostgreSQL, and suddenly you need semantic search over documents. Spinning up a separate vector DB means 3–5 extra days of setup, another service, new APIs, monitoring. pgvector is a PostgreSQL extension that adds the vector type and cosine distance operations right into your familiar database. pgvector brings vector search to PostgreSQL, making RAG pipelines simpler and cost-effective. We are a team with 10+ years in AI/ML and certified PostgreSQL engineers. Over recent years, we've deployed RAG with pgvector for 20+ projects, from startups to enterprise. Using pgvector can save you up to $6000 per year compared to standalone vector databases for 5M vectors. We guarantee stable performance under loads up to 10M vectors. Get a free consultation—we'll assess your project in one day.

How much does pgvector cost?

pgvector vs. standalone vector DB: cost and simplicity

If your data is already in PostgreSQL, adding pgvector requires no new component. Compare with Pinecone:

The trade-off between recall and latency can be managed by adjusting the HNSW index parameters such as ef_construction and m, which directly influence the k-NN graph quality. pgvector is better for moderate volumes (up to 5M vectors) and when you'd rather not add a new service. For scales >50M vectors or ultra-low latency (p99<2ms), Pinecone may be justified, but for 80% of RAG projects, pgvector is the optimal choice. pgvector confirms the extension supports all necessary operations for semantic search. In terms of cost-effectiveness, pgvector is up to 5x cheaper than Pinecone for datasets under 5M vectors when factoring in infrastructure and operational overhead.

Choosing an embedding model for pgvector

pgvector works with any model that returns a fixed-dimension vector. Most common are text-embedding-3-small from OpenAI (1536 dim), BERT-based models (768 dim), or open-source models from Sentence Transformers. Vector dimension affects performance: a 768-dim vector uses half the memory of a 1536-dim one but may have lower accuracy. For most RAG projects, we recommend text-embedding-3-small: a balance of quality and speed.

Troubleshooting pgvector performance

If search takes more than 20 ms, check:

• Is HNSW index used? IVFFlat is slower and less accurate.
• Limit candidates with the ef_search parameter (default 40, can be lowered to 20).
• Increase work_mem for sorting results.
• Check if you're filtering on a non-indexed column—that slows the query.

With proper tuning, pgvector delivers stable 5–15 ms on 1M vectors. For advanced optimization, consider adjusting index hyperparameters like m and ef_construction to improve recall@k.

Setting up a RAG pipeline with pgvector

Step 1: Install pgvector

-- Install extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Table for documents
CREATE TABLE document_chunks (
    id BIGSERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    source VARCHAR(512),
    doc_type VARCHAR(64),
    page_number INTEGER DEFAULT 0,
    metadata JSONB,
    embedding vector(1536),  -- dimension = embedding model
    created_at TIMESTAMP DEFAULT NOW()
);

-- HNSW index for fast search
CREATE INDEX ON document_chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Step 2: Indexing via Python

import psycopg2
from openai import OpenAI
import json

conn = psycopg2.connect("postgresql://user:pass@localhost:5432/ragdb")
openai_client = OpenAI()

def index_chunk(text: str, source: str, doc_type: str, metadata: dict):
    # Get embedding
    response = openai_client.embeddings.create(
        model="text-embedding-3-small",
        input=text,
    )
    embedding = response.data[0].embedding

    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO document_chunks (content, source, doc_type, metadata, embedding)
            VALUES (%s, %s, %s, %s, %s)
        """, (text, source, doc_type, json.dumps(metadata), embedding))
    conn.commit()

Step 3: Vector search with filtering

def search_similar(query: str, doc_type: str = None, limit: int = 5) -> list:
    query_embedding = openai_client.embeddings.create(
        model="text-embedding-3-small",
        input=query,
    ).data[0].embedding

    sql = """
        SELECT content, source, doc_type, metadata,
               1 - (embedding <=> %s::vector) AS similarity
        FROM document_chunks
        WHERE ($2::text IS NULL OR doc_type = $2)
        ORDER BY embedding <=> %s::vector
        LIMIT %s
    """

    with conn.cursor() as cur:
        cur.execute(sql, (query_embedding, doc_type, query_embedding, limit))
        results = cur.fetchall()

    return [
        {"text": r[0], "source": r[1], "similarity": r[4]}
        for r in results
    ]

pgvector operators:

What's included in our work

• Analysis: assess data volume, choose embedding model, design schema.
• pgvector setup: install extension, create indexes (HNSW/IVFFlat), tune PostgreSQL parameters for high load.
• Ingestion pipeline: Python scripts for document chunking, embedding generation, and writing to the table.
• RAG pipeline: implement search, ranking, and prompt construction for LLM.
• Testing: measure latency (p99), accuracy (Recall@k), stress tests.
• Documentation: architecture description, operational manual, restoration dump.
• Support: 2 weeks of post-deployment support—we help with adjustments for your scenarios.

Estimated timelines

The cost is calculated individually—depends on data volume and integration complexity. Order RAG implementation with pgvector—we'll help design a solution tailored to your data scale.