Semantic Search with pgvector — Do You Even Need Pinecone?

The moment you add semantic search or RAG to a product, everyone reaches for a dedicated vector database. But if you already run Postgres, pgvector lets you store and query embeddings right next to your data — no new service, no extra bill. Here’s how to ship semantic search with pgvector, and an honest framework for when Pinecone (or another vector DB) is actually the right call.

What pgvector gives you

pgvector is a Postgres extension that adds a vector column type plus similarity operators. You store an embedding (say, 1536 floats from an OpenAI model) per row and query by nearest-neighbor distance — all inside the database you already back up, secure, and join against.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id        bigserial PRIMARY KEY,
  content   text NOT NULL,
  embedding vector(1536)
);

-- Approximate index for fast nearest-neighbour search
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops);

Store an embedding

Generate the embedding when you write the row. The vector is just an array of numbers:

const { data } = await openai.embeddings.create({
  model: 'text-embedding-3-small',
  input: content,
});
const embedding = data[0].embedding; // number[]

await sql`
  INSERT INTO documents (content, embedding)
  VALUES (${content}, ${JSON.stringify(embedding)})
`;

Query by similarity

Embed the user’s query, then sort by cosine distance with the <=> operator. Smaller distance = more similar:

SELECT content, 1 - (embedding <=> $1) AS similarity
FROM documents
ORDER BY embedding <=> $1   -- $1 = query embedding
LIMIT 5;

That’s the whole retrieval half of a RAG pipeline — feed those top chunks to your LLM as context.

pgvector vs Pinecone: the decision

Reach for pgvector when:

• You already run Postgres and have well under ~10M vectors
• You want to filter and join embeddings with your relational data in one query
• You value fewer moving parts, transactions, and one thing to back up

Reach for Pinecone / a dedicated vector DB when:

• You’re at tens of millions to billions of vectors, or need very high QPS
• You want managed scaling, sharding, and metadata filtering at huge scale handled for you
• Vector search is a core product surface, not a feature

The architecture takeaway

Start with the database you already operate. pgvector takes you impressively far, and "we outgrew Postgres for vectors" is a great problem to have later — far better than running a second stateful system you didn’t need yet. Optimize for the smallest architecture that solves today’s problem.

Related: Build a production RAG chatbot on pgvector · Technical decisions you won’t regret

The best infrastructure decision is often the one that adds nothing new.