Vector Databases vs. Postgres + pgvector: Choosing for Your First AI Project

Every AI feature built on embeddings (semantic search, RAG, recommendations, de-duplication) needs somewhere to store vectors and find the nearest ones fast. The first architectural question is usually: a dedicated vector database, or an extension to the database we already run?

What a vector store has to do

At its core, a vector store answers one query: given this vector, return the k most similar vectors, usually by cosine similarity or inner product. Doing that exactly means comparing against every row, which gets slow past a few hundred thousand vectors. So vector stores build approximate nearest neighbour (ANN) indexes, such as HNSW or IVF, trading a little recall for a lot of speed.

Option 1: Postgres with pgvector

pgvector adds a vector column type, distance operators and ANN indexes (HNSW and IVFFlat) to PostgreSQL.

CREATE EXTENSION IF NOT EXISTS vector;

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

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- 5 nearest documents for one tenant
SELECT id, content
FROM documents
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 5;

Strengths

• One database to run, back up and secure. You probably already have it.
• Vectors live next to the data they describe, so filters, joins and transactions just work.
• Mature tooling: migrations, monitoring, managed hosting everywhere.

Watch out for

• Very large collections (tens of millions of vectors and up) need careful index tuning and plenty of memory.
• Heavy similarity search competes for resources with your transactional workload.

Option 2: A dedicated vector database

Purpose-built systems (managed services and open-source engines) are designed around ANN search from the ground up.

Strengths

• Built to scale to very large collections, often with horizontal sharding.
• Extra search features out of the box, like hybrid keyword + vector search, metadata filtering at scale and namespaces.
• Search load is isolated from your primary database.

Watch out for

• Another system to operate, pay for and keep in sync. Every document now lives in two places, and the two can drift.
• Joins with your relational data happen in application code.

How we decide

If…	Lean towards
You already run Postgres and have up to a few million vectors	pgvector
You need transactions or joins between vectors and business data	pgvector
You expect tens of millions of vectors or very high query volume	Dedicated vector DB
Search is the product, and you need advanced retrieval features	Dedicated vector DB

For most first AI projects, start with pgvector. It removes a whole system from your architecture, and the embedding and retrieval code you write is portable. If you outgrow it, migrating means re-indexing vectors, not rewriting your application.

Whichever you pick, the bigger wins usually come from upstream: good chunking, a suitable embedding model and a retrieval eval set. A perfectly tuned index can't rescue embeddings of badly split documents.