Skip to main content
pgvector in Production: RAG Inside Your Own Database

pgvector in Production: RAG Inside Your Own Database

AI Integration
••5 min read•By Daily Miranda Pardo

Most teams building RAG for the first time follow the same path: they find a tutorial, sign up for Pinecone, and within two weeks they have two sources of truth drifting apart, one more service to monitor, and a monthly bill nobody budgeted.

If you already have PostgreSQL running in production, you're looking at the answer. pgvector turns your existing database into a vector search engine — no new infrastructure, no sync headaches, no extra cost.

The Pattern That Repeats on Every RAG Project

The usual flow: you decide to add RAG to your agent, read some framework docs, the example uses Pinecone, you follow the example. Outcome: your app now has two databases that must stay in sync.

When you update a document in PostgreSQL, you have to remember to update the embedding in Pinecone. When Pinecone has an outage, your agent stops retrieving context. When your data grows, the Pinecone bill grows with it.

The alternative requires migrating nothing: pgvector is a PostgreSQL extension that adds a vector type and similarity operators. Your existing database, with one extension.

Indexes: HNSW vs IVFFlat

pgvector offers two index types:

IVFFlat partitions the vector space into clusters and only searches the nearest ones. Fast to build, but needs existing data to cluster properly. If you build the index on an empty table, recall will be poor.

HNSW (hierarchical navigable small world) builds a multi-layer graph structure. Slower to build, better recall, lower query latency, and works well from the very first row.

-- HNSW: production starting point
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Increase recall at search time (at latency cost):
SET hnsw.ef_search = 100;

m controls connections per node: more connections = better recall, more memory. ef_construction controls precision during index build. For most projects, m=16, ef_construction=64 is the right starting point. Tune ef_search at runtime based on your latency SLAs.

TypeScript Setup

import { Pool } from 'pg';
import { embed } from 'ai';
import { openai } from '@ai-sdk/openai';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function setupSchema() {
  await pool.query(`
    CREATE EXTENSION IF NOT EXISTS vector;

    CREATE TABLE IF NOT EXISTS documents (
      id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      content     TEXT NOT NULL,
      metadata    JSONB DEFAULT '{}',
      embedding   vector(1536),
      created_at  TIMESTAMPTZ DEFAULT NOW()
    );

    CREATE INDEX IF NOT EXISTS documents_embedding_idx
    ON documents USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);
  `);
}

async function indexDocument(content: string, metadata: object) {
  const { embedding } = await embed({
    model: openai.embedding('text-embedding-3-small'),
    value: content,
  });
  await pool.query(
    'INSERT INTO documents (content, metadata, embedding) VALUES ($1, $2, $3)',
    [content, metadata, JSON.stringify(embedding)]
  );
}

async function search(query: string, limit = 5) {
  const { embedding } = await embed({
    model: openai.embedding('text-embedding-3-small'),
    value: query,
  });
  const { rows } = await pool.query(
    `SELECT id, content, metadata,
            1 - (embedding <=> $1::vector) AS similarity
     FROM documents
     ORDER BY embedding <=> $1::vector
     LIMIT $2`,
    [JSON.stringify(embedding), limit]
  );
  return rows;
}

Note the explicit $1::vector cast: without it, PostgreSQL can't infer the parameter type and the HNSW index won't be used — the query falls back to a full sequential scan.

Hybrid Search: Vector + Full-Text

Vector search fails on exact terms: IDs, proper names, reference numbers, product codes. Full-text search fails on synonyms and semantic context. Combining both with Reciprocal Rank Fusion (RRF) gets you the best of each:

async function hybridSearch(query: string, limit = 5) {
  const { embedding } = await embed({
    model: openai.embedding('text-embedding-3-small'),
    value: query,
  });

  const { rows } = await pool.query(`
    WITH
      vector_results AS (
        SELECT id,
               ROW_NUMBER() OVER (ORDER BY embedding <=> $1::vector) AS rank
        FROM documents
        ORDER BY embedding <=> $1::vector
        LIMIT 20
      ),
      text_results AS (
        SELECT id,
               ROW_NUMBER() OVER (
                 ORDER BY ts_rank(
                   to_tsvector('english', content),
                   plainto_tsquery('english', $2)
                 ) DESC
               ) AS rank
        FROM documents
        WHERE to_tsvector('english', content) @@
              plainto_tsquery('english', $2)
        LIMIT 20
      )
    SELECT d.id, d.content, d.metadata,
           COALESCE(1.0 / (60 + vr.rank), 0) +
           COALESCE(1.0 / (60 + tr.rank), 0) AS rrf_score
    FROM documents d
    LEFT JOIN vector_results vr ON d.id = vr.id
    LEFT JOIN text_results tr ON d.id = tr.id
    WHERE vr.id IS NOT NULL OR tr.id IS NOT NULL
    ORDER BY rrf_score DESC
    LIMIT $3
  `, [JSON.stringify(embedding), query, limit]);

  return rows;
}

The 60 in the denominator is the standard RRF parameter — it controls the weight of lower-ranked results. You can tune it, but 60 is a solid default.

This pattern is part of the standard stack we use in AI integration projects built on top of existing PostgreSQL infrastructure.

When You Actually Need a Dedicated Vector Store

pgvector isn't the universal answer. There are cases where Pinecone, Qdrant, or Weaviate make sense:

  • 10M+ vectors with sub-50ms latency SLAs under heavy load
  • High-throughput real-time writes — thousands of inserts per second with concurrent searches
  • Complex metadata filtering at scale: Qdrant's filter engine outperforms PostgreSQL's jsonb @> at high cardinality
  • Multi-tenancy at hundreds of clients where schema-level isolation becomes an operational bottleneck

For 90% of RAG projects at mid-size companies, none of this applies. If you need to migrate later, the dual-index migration pattern works here the same way it does for embedding model upgrades.

Get the Setup Right From Day One

The choice to use pgvector isn't just technical — it's operational. One service to monitor instead of two, a single database connection, backups that include embeddings automatically, and zero sync time between sources of truth.

Correct setup — index type, parameters, hybrid search — is what separates a prototype from a system that holds in production without surprises three months in.

If your team is building a RAG agent and wants to implement it on your existing PostgreSQL without fragile architecture, let's talk.

Share article

Repetitive processes in your business?

Download the free AI Automation Map — the 5 most time-consuming processes and how to fix them.

Controller: DailyMP (Daily Miranda Pardo). We use your email only to send you the PDF, unless you tick the box below. More info in the privacy policy.

Written by Daily Miranda Pardo

I help businesses automate processes, build AI agents and connect intelligent systems.