Every team building a retrieval feature — semantic search, "chat with your documents," recommendations — hits the same early decision: where do the vectors go? The internet will tell you that you need a dedicated vector database. Sometimes you do. Often you already have the answer installed: Postgres with the pgvector extension.
This post is about making that call deliberately, instead of reaching for a new piece of infrastructure because a blog post said to.
What a vector store actually has to do
Strip away the marketing and a vector store has one core job: given a query vector, find the stored vectors closest to it, fast. "Closest" means by cosine similarity or Euclidean distance. Doing this exactly means comparing the query against every stored vector — fine for ten thousand rows, painful for ten million.
So the real product is approximate nearest neighbor (ANN) search: an index that trades a little accuracy for a large speedup. The two index families you'll see everywhere are:
- HNSW (Hierarchical Navigable Small World) — a graph you walk to find near neighbors. Fast queries, high recall, slower to build and heavier on memory.
- IVFFlat — cluster the vectors, then only search the nearest clusters. Cheaper to build, lighter on memory, a bit more tuning to get recall right.
Every option below — pgvector, Pinecone, Qdrant, Weaviate, Milvus — is some packaging of these ideas. The differences that matter for you are rarely the ANN algorithm. They're operational: what you already run, how much data you have, and what else the query needs to do.
The case for Postgres + pgvector
pgvector adds a vector column type and ANN indexes to Postgres. Your embeddings live in a normal table, next to the row they describe.
CREATE EXTENSION vector;
CREATE TABLE documents (
id bigserial PRIMARY KEY,
tenant_id bigint NOT NULL,
title text,
body text,
embedding vector(1536)
);
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);
A similarity query is just SQL:
SELECT id, title
FROM documents
WHERE tenant_id = 42
ORDER BY embedding <=> $1
LIMIT 10;
That <=> operator is cosine distance. Notice what came for free: the WHERE tenant_id = 42 filter, run by the same engine, in the same transaction, with the same access rules as the rest of your app. That's the whole argument. When your vectors live in your primary database, you get things a standalone vector store makes you rebuild:
- Real joins. Retrieve chunks and their parent document's metadata, author, and permissions in one query.
- Transactions. Insert a document and its embedding atomically. No "the row saved but the vector didn't" drift between two systems.
- One backup, one access model, one thing to monitor. Your ops story doesn't grow.
- Filtering that actually uses your data. Tenant isolation, date ranges, status flags — ordinary indexed SQL predicates.
For a large share of real projects — internal knowledge search, a support-ticket assistant, document Q&A for a few thousand to a few million chunks — this is not a compromise. It's the right architecture. You already run Postgres, your team already knows it, and you've removed an entire system from the diagram.
Where pgvector starts to strain
It's not free of limits. The honest failure modes:
Scale of vectors. Postgres handles millions of vectors comfortably on decent hardware. As you climb toward the high tens of millions and beyond, HNSW index memory, build time, and query latency become a real tuning project. Dedicated engines are built for exactly this and will do it with less hand-holding.
Write-heavy, high-churn indexes. If you're constantly re-embedding and replacing large fractions of the corpus, ANN index maintenance competes with your transactional workload on the same box. Vector-native systems separate these concerns.
Recall/latency tuning under load. You can tune pgvector (HNSW ef_search, IVFFlat lists/probes), but the knobs are less discoverable and there's no built-in dashboard telling you your recall. Purpose-built databases surface this more directly.
Resource contention. Vector search is CPU- and memory-hungry. Running it on the same instance as your latency-sensitive OLTP traffic can mean one workload starving the other. At some point you want them on separate hardware — which you can do by giving vectors their own Postgres replica, or by moving to a dedicated store.
What dedicated vector databases actually buy you
Pinecone, Qdrant, Weaviate, and Milvus aren't snake oil. They earn their place when the vector workload is the main workload, not a feature bolted onto an app. What you're really paying for:
- Horizontal scale. Sharding across many nodes for hundreds of millions or billions of vectors, which is awkward to do yourself in Postgres.
- Operational features tuned for search: rich metadata filtering combined with ANN, hybrid dense-plus-sparse search, quantization to shrink memory, and managed index rebuilds.
- A managed option that removes the ops entirely (Pinecone especially) — no index to babysit, you send vectors and queries.
A rough map, without pretending the categories are clean:
| Option | Sweet spot | Main cost |
|---|---|---|
| Postgres + pgvector | You already run Postgres; up to low tens of millions of vectors; vectors are a feature of a larger app | Tuning and resource contention at high scale |
| Qdrant / Weaviate / Milvus (self-hosted) | Large or fast-growing corpora; search is a core workload; you want control | Another system to run, monitor, and back up |
| Pinecone (managed) | You want zero index ops and predictable scaling | Recurring cost; data lives in a third-party service |
The columns that decide it are almost never "which has the best ANN recall." They're how much data, is search the product or a feature, and who operates it.
A decision path that works
Rather than benchmarking five systems for a week, answer these in order:
- Are you already running Postgres? If not, and search is your product, start with a dedicated store and skip the rest. If yes, keep going.
- How many vectors, realistically, in a year? Under ~10 million: pgvector is very likely enough. Into the hundreds of millions: plan for a dedicated system.
- Does retrieval need to join or filter on your relational data? Heavy tenant isolation, permission checks, and metadata joins strongly favor keeping vectors in Postgres.
- Is vector search your core workload or a feature? A feature leans pgvector. A product-defining workload — a search engine, a recommendation core — leans dedicated.
- Who's going to operate it at 2 a.m.? A small team with no dedicated infra people is usually better served by fewer systems (pgvector) or a fully managed one (Pinecone), not a self-hosted cluster.
The migration path is real, so start simple
The strongest argument for beginning with pgvector is that you're rarely trapped by it. Your embeddings are just rows; the retrieval logic is a query behind an interface. If you later outgrow Postgres, you export the vectors and their metadata and load them into a dedicated store. The embeddings themselves don't change — only where they're indexed.
That means the expensive decision isn't "which vector database." It's whether your retrieval quality is good: sensible chunking, a decent embedding model, hybrid search when keywords matter, and an evaluation set so you can tell whether a change helped. Those determine whether users get good answers. The storage engine mostly determines cost and scale.
So the pragmatic path for most teams: start with pgvector. Get retrieval quality right. Watch your vector count, query latency, and how much the vector workload contends with the rest of your database. Move to a dedicated store when — and only when — one of those genuinely hurts. "We might need billions of vectors someday" is not that day.
Choosing infrastructure for the scale you have, not the scale you imagine, is one of the highest-leverage habits in building AI features. Most of the time, the boring answer — the database you already run — is also the right one.
Deciding where your vectors should live, or wrestling with retrieval quality? Get in touch — we help teams design and build AI systems that fit the job.