pgvector in Production: RAG Inside Your Own Database
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.