Skip the Vector Database: pgvector Is Enough for RAG

The "Just Add Pinecone" Reflex

Every time someone builds a RAG system, the first recommendation is to add a dedicated vector database. Pinecone, Qdrant, Weaviate, Chroma — pick your flavor. The pitch is always scale: these databases are purpose-built for vector similarity search, they'll handle millions of embeddings, they have ANN indexes optimized for high-dimensional space.

For most production applications — and certainly for anything under a few million vectors — you already have a database that can do this. It's PostgreSQL, and the pgvector extension handles the vector math.

I built the RAG chatbot for my portfolio on pgvector. Here's exactly what I did, what the tradeoffs are, and when you'd actually need something dedicated.

What pgvector Adds to PostgreSQL

CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE knowledge_base_chunks
  ADD COLUMN embedding vector(1536);

CREATE INDEX ON knowledge_base_chunks
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

Three lines of SQL and you have:

• –A vector column type that stores float arrays
• –Cosine similarity, inner product, and L2 distance operators
• –IVFFlat and HNSW approximate nearest-neighbor indexes

That's it. Your existing PostgreSQL instance now does vector search.

The Schema

model KnowledgeBaseDocument {
  id          String   @id @default(cuid())
  title       String
  content     String
  category    String
  createdAt   DateTime @default(now())
  chunks      KnowledgeBaseChunk[]
}

model KnowledgeBaseChunk {
  id         String                @id @default(cuid())
  content    String
  embedding  Unsupported("vector(1536)")?
  documentId String
  document   KnowledgeBaseDocument @relation(fields: [documentId], references: [id])
  chunkIndex Int
}

Prisma doesn't support the vector type natively (yet), so the embedding column is declared as Unsupported. You work around this with raw queries for the similarity search — everything else (inserts, deletes, joins) uses normal Prisma.

Embedding on Write

When a document is saved, a BullMQ job picks it up and chunks + embeds it:

@Processor('kb-embed')
export class KbEmbedProcessor {
  constructor(
    private readonly prisma: PrismaService,
    private readonly embedder: EmbeddingPort,
  ) {}

  @Process()
  async handle(job: Job<{ documentId: string }>) {
    const doc = await this.prisma.knowledgeBaseDocument.findUniqueOrThrow({
      where: { id: job.data.documentId },
    });

    const chunks = this.splitIntoChunks(doc.content, 400, 50);

    await this.prisma.knowledgeBaseChunk.deleteMany({
      where: { documentId: doc.id },
    });

    for (const [i, chunk] of chunks.entries()) {
      const embedding = await this.embedder.embed(chunk);
      await this.prisma.$executeRaw`
        INSERT INTO knowledge_base_chunks (id, content, embedding, document_id, chunk_index)
        VALUES (${cuid()}, ${chunk}, ${embedding}::vector, ${doc.id}, ${i})
      `;
    }
  }

  private splitIntoChunks(text: string, size: number, overlap: number): string[] {
    const words = text.split(/\s+/);
    const chunks: string[] = [];
    for (let i = 0; i < words.length; i += size - overlap) {
      chunks.push(words.slice(i, i + size).join(' '));
    }
    return chunks;
  }
}

The embedding model is behind an EmbeddingPort interface — the implementation calls OpenAI's embedding API (or DeepSeek's, or a local model). The processor doesn't care which.

Retrieval: One Raw Query

async search(query: string, topK: number): Promise<ChunkResult[]> {
  const queryEmbedding = await this.embedder.embed(query);

  const results = await this.prisma.$queryRaw<ChunkResult[]>`
    SELECT
      kbc.content,
      kbd.title AS "documentTitle",
      1 - (kbc.embedding <=> ${queryEmbedding}::vector) AS similarity
    FROM knowledge_base_chunks kbc
    JOIN knowledge_base_documents kbd ON kbc.document_id = kbd.id
    WHERE 1 - (kbc.embedding <=> ${queryEmbedding}::vector) > 0.7
    ORDER BY similarity DESC
    LIMIT ${topK}
  `;

  return results;
}

<=> is the cosine distance operator. 1 - distance gives you cosine similarity. A threshold of 0.7 filters out weak matches before they reach the LLM.

This query runs in ~20ms on a warm IVFFlat index with a few thousand chunks — well within acceptable latency for a chatbot response.

What You Get "For Free" With PostgreSQL

By keeping vectors in the same database as your content:

Transactional consistency. When a document is deleted, ON DELETE CASCADE removes its chunks. No orphaned vectors in a separate store. No sync job. No eventual consistency window.

Single backup story. Your pg_dump includes everything. You don't need to export from a vector DB and a relational DB separately and keep them in sync.

JOINs. The retrieval query joins chunks to documents in the same query. With a dedicated vector DB, you'd retrieve chunk IDs, then fetch document metadata in a second round trip.

One operational surface. If your team knows PostgreSQL, they know the tool. Monitoring, vacuuming, connection pooling — you already have processes for this.

The Tradeoffs

pgvector is not the right answer for everything.

At millions of vectors, the IVFFlat index recall starts degrading. HNSW performs better at scale, and pgvector supports it since 0.5.0 — but a dedicated ANN database with hardware-optimized SIMD operations will still outperform PostgreSQL at very high dimensionality and large vector counts.

Concurrent write performance degrades when you're embedding at high throughput. A dedicated vector store can often handle more parallel writes.

Advanced retrieval features like hybrid search (BM25 + vector), named vectors per object, or tenant isolation are easier in databases designed for them.

For my portfolio chatbot — a few hundred KB documents, sub-100ms retrieval requirement, single-tenant — pgvector handles it comfortably. For a product with millions of user-generated embeddings and sub-10ms P99 requirements, I'd revisit the decision.

The Setup in 5 Minutes

# Enable the extension (once, in a migration)
psql -c "CREATE EXTENSION IF NOT EXISTS vector;"

# Prisma migration
npx prisma migrate dev --name add_pgvector

In your NestJS module, the vector store is an adapter implementing a VectorStorePort interface. If you later need to swap to Qdrant, you write a QdrantAdapter — the use-case that calls the port doesn't change.

The Takeaway

pgvector isn't a workaround — it's a legitimate production choice for most RAG workloads. Before adding another managed service with its own pricing, SDK, and operational overhead, ask whether the database you already run can handle it. For most teams building their first RAG product, the answer is yes.

The constraint that forced me to think about this: I'm self-hosting on a single Contabo VPS. Every service I add is another thing to keep running. Keeping vectors in PostgreSQL means one less process, one less thing that can drift out of sync, and one more thing I genuinely understand end to end.