Somewhere in the last two years, "we're doing RAG" quietly became "so we need a vector database," and the second half of that sentence stopped getting questioned. You add Pinecone or Qdrant or Weaviate to the stack, wire up a sync job, and now you own two datastores that have to agree with each other forever. For a demo, fine. For most production systems, you just bought a distributed-systems problem to solve a problem you didn't have.
Here's the number that should have ended the debate before it started: pgvector, the vector extension that runs inside the Postgres you're already paying for, handles vector search comfortably into the low tens of millions of vectors on a single node. Not "for toy projects." Into the tens of millions. And it does it while your embeddings sit in the same transaction, the same backup, and the same WHERE tenant_id = ? as the rest of your data. The dedicated vector database is a real tool with a real job. That job just starts a lot further up the scale curve than the people selling it want you to think.
What a vector database actually does
Strip away the marketing and a vector database does one thing: approximate nearest-neighbor search over high-dimensional embeddings. You have a query vector, you have millions of stored vectors, and you want the closest few by cosine or L2 distance without comparing against every single one. The trick that makes it fast is an index, almost always HNSW (Hierarchical Navigable Small World), a graph you walk to find close neighbors in roughly logarithmic time instead of scanning the whole set.
That's it. That's the special sauce. And Postgres has had it since pgvector 0.5.0 shipped HNSW back in 2023. Here's the entire "vector database" you need for most apps:
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE chunks (
id bigserial PRIMARY KEY,
doc_id bigint NOT NULL REFERENCES documents(id),
tenant_id bigint NOT NULL,
content text,
embedding vector(1536) -- e.g. OpenAI text-embedding-3-small
);
-- The index that makes it fast. m and ef_construction trade build time for recall.
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
A nearest-neighbor query is an ORDER BY on a distance operator (<=> is cosine distance, <-> is L2, <#> is negative inner product):
SELECT id, content
FROM chunks
ORDER BY embedding <=> $1 -- $1 is your query embedding
LIMIT 10;
That query, against an HNSW index that fits in memory, is not meaningfully slower than the same query against Pinecone at the scales most teams operate at. Benchmarks have pgvector with HNSW matching or beating dedicated engines at around 1M vectors. So the honest question isn't "is pgvector good enough" at a million vectors. It obviously is. The question is what you give up by not adding a second system, and the answer is: nothing. You gain things.
The stuff you get for free (and would have to rebuild)
This is the part the comparison charts leave out, because it doesn't fit in a QPS column. When your vectors live in Postgres, every other thing Postgres does applies to them at the same time.
Filtering and multi-tenancy are just a WHERE clause. Real RAG is almost never "search all vectors." It's "search this tenant's documents," or "search docs this user can see, from the last 90 days, in the 'published' state." In Postgres that's the query you already know how to write, and it runs in the same index scan:
SELECT c.id, d.title, c.content
FROM chunks c
JOIN documents d ON d.id = c.doc_id
WHERE c.tenant_id = $1
AND d.status = 'published'
ORDER BY c.embedding <=> $2
LIMIT 10;
Look at that JOIN. Your embedding result comes back already stitched to the document's title, its author, its permissions, whatever you need, in one round trip. In a dedicated vector store you get back a list of IDs, and then you make a second call to Postgres to hydrate them, and now you're doing a distributed join by hand in application code and hoping the two systems didn't drift.
Row-level security means tenant isolation you can't forget. You can push tenancy down into the database so a missing WHERE clause can't leak one customer's chunks into another's results:
ALTER TABLE chunks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON chunks
USING (tenant_id = current_setting('app.tenant_id')::bigint);
Try enforcing that in a separate vector database. You can't, really, not at the storage layer. Tenant isolation becomes an application concern you re-implement and re-audit, which is exactly the kind of thing that turns into a security incident.
One transaction, one backup, one truth. When you insert a document and its chunks and their embeddings, that's one transaction. It commits or it doesn't. There's no window where the document exists but its vectors don't, or where you deleted a record but its embedding is still floating in another system returning ghosts in search. Your existing backup, your existing replica, your existing point-in-time recovery already cover the vectors. You didn't add an operational surface. You added a column.
That "one truth" point is the whole argument, honestly. The moment you split vectors into their own store, you own a synchronization problem: dual writes, eventual consistency between your source of truth and your search index, reconciliation jobs, and the 3am question of why a deleted record still shows up in RAG results. That problem is real work, and you took it on to save a latency difference you can't measure yet.
The two gotchas that actually bite
pgvector is not magic, and pretending it has no sharp edges is how you end up back in the dedicated-database camp for the wrong reasons. There are exactly two things that bite people, and both have answers.
Gotcha one: the HNSW index has to fit in memory. The single biggest factor in pgvector performance is whether the HNSW graph lives in RAM. When the index fits in shared_buffers and stays there, queries are fast and boring. When it spills to disk because the index outgrew memory or got evicted under load, tail latency falls off a cliff. So capacity-planning pgvector is really memory-planning: know your vector count times your dimensions times the index overhead, and make sure it fits with headroom. A 1536-dimension vector is about 6KB raw; ten million of them plus HNSW overhead is a real but very ordinary amount of RAM for a database server in 2026. This is also where halfvec earns its keep: store embeddings as 16-bit floats and you roughly halve the memory, which for 3072-dimension models is the difference between fitting and not.
Gotcha two: filtered search used to quietly return too few rows. This one burned people for years and is the single most common "pgvector is broken" complaint. Before pgvector 0.8.0, the HNSW index returned its candidate set first, and then your WHERE tenant_id = ? filter ran on that set. If your filter was selective, you'd ask for 10 results and get 3, because 7 of the index's candidates belonged to other tenants and got dropped after the fact. It looked like a correctness bug and it was really an ordering-of-operations problem.
pgvector 0.8.0 fixed it with iterative index scans: the planner keeps pulling more of the index until enough rows survive your filter. You turn it on per-session:
SET hnsw.iterative_scan = relaxed_order; -- keep scanning until LIMIT is satisfied
-- strict_order preserves exact distance order; relaxed_order trades a little
-- ordering for better recall under selective filters.
-- Bounds: hnsw.max_scan_tuples (default 20000), hnsw.scan_mem_multiplier (default 1).
SELECT id, content
FROM chunks
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 10;
If you evaluated pgvector before 0.8.0, hit the filtered-search wall, and concluded you needed a "real" vector database, that conclusion is now out of date. Re-check it. The version number matters here more than almost anywhere else in the Postgres world.
When you actually do need one
The contrarian take isn't "never use a dedicated vector database." It's "know the line, and don't cross it before you get there." The line is real, and it's roughly this:
Under roughly 10 million vectors, pgvector with a well-tuned HNSW index that fits in RAM matches or beats dedicated engines, and you keep all the free stuff above. From about 10 to 50 million, plain pgvector starts to strain, but you don't have to leave Postgres yet: the pgvectorscale extension adds a StreamingDiskANN index that stays fast when the dataset is larger than RAM, plus statistical binary quantization to shrink memory and label-aware filtering. In one benchmark it hit 471 queries per second at 99% recall on 50 million vectors, about 11 times Qdrant's throughput at the same recall. So the "you'll outgrow Postgres" story has a whole extra chapter before it's even true.
Past 50 to 100 million vectors, or when your workload is vector-search-first with brutal tail-latency SLAs and you want someone else to operate the scaling, the dedicated engines genuinely pull ahead. Pinecone is the fastest path to zero-ops scaling toward billions. Qdrant wins raw QPS and filtering if you want to stay open-source and run it yourself. Weaviate bundles embedding generation so you can hand it raw text. These are good products solving a real problem. They're just solving a problem that starts at a scale most applications will never reach, and they charge you the two-system tax the entire way there.
The mistake almost nobody regrets avoiding is starting on pgvector and migrating later. Moving 100 million vectors to a dedicated store when you actually hit the wall is a known, boring data-migration project you'll have the revenue to staff. Standing up a second stateful system on day one to serve 200,000 vectors is how you spend your scaling budget before you have anything to scale. Put the embeddings in the database that already holds your data, add the vector column, ship the feature, and add the dedicated engine the day the numbers, not the demo, tell you to.
Originally published at andriiboyko.com.
If you found this helpful, follow me here and on LinkedIn
Top comments (0)