Build a RAG pipeline with pgvector

Prerequisites

Step-by-Step

1. 1

   Enable the extension

   Run once per database. Most providers (Neon, Supabase) just require this single statement.

   CREATE EXTENSION IF NOT EXISTS vector;

2. 2

   Create the table and index

   Use HNSW for the index - it beats IVFFlat on recall vs query speed for most workloads.

   CREATE TABLE docs (
     id bigserial PRIMARY KEY,
     source text,
     content text,
     embedding vector(1536)
   );
   CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);

3. 3

   Embed and insert from Node

   Use a real Postgres client and bind the embedding as a string-cast vector literal.

   import { Pool } from 'pg';
   const pool = new Pool({ connectionString: process.env.DATABASE_URL });
   const e = await openai.embeddings.create({ model: 'text-embedding-3-small', input: chunk });
   await pool.query('INSERT INTO docs (source, content, embedding) VALUES ($1, $2, $3)', [
     source, chunk, JSON.stringify(e.data[0].embedding),
   ]);

4. 4

   Query with cosine distance

   The <=> operator computes cosine distance. Order ascending and limit to topK.

   SELECT id, source, content
   FROM docs
   ORDER BY embedding <=> $1
   LIMIT 5;

5. 5

   Combine with SQL filters

   This is pgvector's superpower: combine vector search with arbitrary SQL WHERE clauses, joins, and full-text search.

   SELECT * FROM docs
   WHERE tenant_id = $2 AND created_at > now() - interval '30 days'
   ORDER BY embedding <=> $1 LIMIT 10;

6. 6

   Tune index parameters

   HNSW exposes m and ef_construction at index time, plus ef_search per query. Higher = more recall, slower.

Common Pitfalls

What's Next

• ->Combine pgvector with Postgres full-text search for hybrid retrieval.
• ->Move to dedicated vector DBs only when you exceed ~10M vectors.

More Build a RAG pipeline