We paid for a vector database for a year. It was faster than Postgres at the one thing it does. We deleted it anyway.
- managed vector store 3.2M vectors, its own SLA, its own bill
- sync pipeline 1,100 lines keeping it in step with Postgres
+ one column embedding vector(1536)
+ one index hnsw
I want to be careful about the "faster," because the headline oversells it and the truth is more useful. On a pure nearest-neighbour benchmark — a bare k nearest vectors, no filter, warm cache — the dedicated store won, every time. It is built for that and it is very good at it.
Our problem was that we never once ran that query in production.
What we actually asked it, every time
Nobody in a real product searches the whole corpus. They search their corpus. Every retrieval we ran looked like this in English:
Find the chunks most similar to this question — among documents this user is allowed to see, that are published, in their workspace, not archived.
That is not nearest-neighbour search. It is nearest-neighbour search with a WHERE clause, and the WHERE clause is the whole problem, because a dedicated vector store filters in one of two bad ways.
Pre-filter, and it walks the metadata first, then does brute-force similarity over what survives — throwing away the ANN index, the only reason you bought the thing.
Post-filter, and it takes the top k by vector distance and then drops the ones that fail the filter. Ask for 10, match on similarity, discover 7 of them belong to a different tenant, return 3. To get 10 back you over-fetch — top 100, top 500 — and hope. Recall becomes a function of how badly the filter correlates with the vector space, which is to say: unknowable, and worst exactly when the filter is selective, which is exactly when you needed it.
We shipped the post-filter version. Then we bolted a fetch-back onto it: take the surviving IDs, go to Postgres for the metadata the vector store didn't hold, filter again to be sure. A second network hop, on every query, to ask the database the question we should have asked it in the first place.
The seam that was actually driving all of it
Here is the part that made me stop optimising the pipeline and delete it instead.
The embedding is not data. It is a pure function of data — run the document through the model, get the vector. It is derived, the way a thumbnail is derived from an image. And we were storing the derived thing in a different database from the thing it was derived from.
Which means every write was two writes:
await db.update(documents, { id, body }) // system 1: the truth
await vectorStore.upsert(id, await embed(body)) // system 2: the copy
Two systems. One of them can fail.
If the embed-and-upsert fails after the document commits — the model API times out, the upsert 500s, the retry also fails and eventually logs a warning nobody reads — you now have a document whose row says one thing and whose searchable vector says another. No exception in your tracker. No alert. Just a document that quietly cannot be found, or worse, one that is found by its old content because the vector is stale.
The mirror case is uglier. Delete a document from Postgres, fail to delete it from the vector store, and now your search confidently returns a chunk of a document that no longer exists — to a user who may no longer be allowed to see it. A deleted row is a permission you revoked. A surviving vector is that permission, still granted, in a system nobody thinks of as holding permissions.
This has a name, or near enough — it is the dual-write problem, the same seam the transactional-outbox people have been shouting about for a decade. If you run a vector store beside your database, you have shipped it. You may just be calling its symptoms "the index is a bit stale sometimes."
The standard fix is a change-data-capture pipeline: tail the Postgres WAL, transform, re-embed, upsert, with a dead-letter for the embeds that fail and a reconciliation job that periodically re-scans everything because you know the pipeline drifts. That works. It is also a distributed system whose entire job is to paper over the fact that a derived value is stored away from its source. It was most of our 1,100 lines.
We deleted the source of the drift instead of building a machine to chase it.
The column
This is the entire vector store now. It is a column on the table that already existed.
create extension if not exists vector;
alter table documents
add column embedding vector(1536); -- text-embedding-3-small
vector(1536) is a first-class type from pgvector. The distance operators come with it:
<-> L2 distance
<=> cosine distance
<#> negative inner product
And the index that makes it fast is one statement:
create index on documents
using hnsw (embedding vector_cosine_ops)
with (m = 16, ef_construction = 64);
HNSW — the good ANN index, the same graph algorithm the dedicated stores use — has been in pgvector since 0.5.0, released August 2023. This is the fact the "just use a real vector database" crowd is usually a year behind on. It is not IVFFlat, it is not a toy, it is the actual state of the art, running inside the database that already holds your data.
The query that was the whole point
The filtered search that cost us two hops and a prayer is now one statement with one planner:
select id, chunk
from documents
where workspace_id = $1
and status = 'published'
and not archived
order by embedding <=> $2 -- $2 is the question's embedding
limit 10;
The WHERE and the ORDER BY embedding <=> are planned together. Postgres decides, per query, whether to use the HNSW index and check the filter, or use a btree on workspace_id and sort by distance — based on how selective your filter actually is. The thing the dedicated store made me choose between at architecture time, the query planner now chooses at runtime, per query, with statistics.
There is one detail that is not optional and is where people get burned, so it gets its own section.
The detail nobody tells you: iterative scan
An HNSW index returns a fixed number of candidates and then your WHERE clause filters them. Selective filter, and you hit the exact post-filter problem I described above — the index hands up 40 candidates, 37 fail your filter, you asked for 10 and get 3. For a while this was pgvector's real weakness and the honest reason to reach for a dedicated store.
It was fixed. pgvector 0.8.0, October 2024, added iterative index scans: when a filtered search comes up short, the index keeps walking the graph and returns more candidates until your LIMIT is satisfied or the search is exhausted.
set hnsw.iterative_scan = 'strict_order';
set hnsw.ef_search = 100; -- widen the candidate list; the recall/latency dial
If you evaluated pgvector before late 2024, filtered recall is the thing you found wanting, and it is the thing that changed. Re-run your benchmark. This is the single most important sentence in this post.
What got deleted along with the database
- The CDC pipeline. WAL tail, transformer, embed worker, upsert, dead-letter, the lot. This was the 1,100 lines.
- The reconciliation job. The nightly full-scan that existed only because we knew the pipeline drifted. You do not reconcile a system against itself.
- The fetch-back hop. Metadata lives on the same row as the vector. The join is free; it is the same row.
- A second SLA, a second bill, a second dashboard, a second thing to be paged about.
- "Is the index stale?" — the question that preceded every "why can't the user find this document" investigation, and which no longer has a mechanism to be true.
Ninety days later
| Before | After | |
|---|---|---|
| Systems in the retrieval path | 2 | 1 |
| p95 filtered retrieval | 220ms | 41ms |
| Sync / reconciliation code | 1,100 lines | 0 |
| Monthly vector-store bill | $840 | $0 (folded into RDS) |
| Documents findable but deleted | non-zero | 0 |
The latency number is the one people fixate on, so let me undersell it correctly: this is not pgvector's ANN beating the dedicated store's ANN. It is not. On the bare benchmark theirs still wins. The 220ms was two network round-trips, a post-filter over-fetch, and a metadata fetch-back — and we deleted all three. We made the path shorter, not the search faster. If your retrieval is a single unfiltered ANN call inside your own VPC, you will not see this.
The last row is the only one I would have done this for. "Documents findable but deleted" was a class of incident. Now it is a state the system cannot be in, because there is no second copy to be wrong.
The five objections, scored honestly
Every one of these was said to me by someone who knew more about vector search than I did.
1. "Postgres can't do ANN at scale."
It has done HNSW since August 2023 — the same algorithm, in-process. "At scale" is the real question and it is objection 2. As a flat "can't," this is a year or two out of date. Cargo cult.
2. "It won't scale to billions of vectors."
Correct, and this is the honest boundary. Somewhere north of tens of millions of vectors at sustained high QPS, a purpose-built store earns its price: better memory layout, real quantization, distributed sharding you do not want to build on Postgres. Ours was 3.2 million at 30 queries a second. Ask what your number actually is before you architect for someone else's. Real — check your number first.
3. "The HNSW index build will melt your database."
This one is real and under-warned. Building an HNSW index over millions of rows is memory-hungry and slow, and it competes with your live traffic. Build it with maintenance_work_mem cranked up, use parallel workers, and treat a full rebuild as a maintenance event, not a migration you run at 5pm on a Friday. halfvec (pgvector 0.7.0) halves the storage and the build cost with negligible recall loss at 1536 dims. Real. Budget for it.
4. "You're coupling your search to your primary database."
We were already coupled — the vectors were derived from the primary and had to be kept in step with it forever. What we removed was the pretence that a copy in another system had decoupled anything. A stale copy is tighter coupling than a column, because a column cannot lag. Cargo cult, in our case — check whether it is in yours.
5. "Dedicated stores have reranking, hybrid search, multi-tenancy features."
Some do, and if you are using them, this trade is different for you. But hybrid search is tsvector and a vector column in the same WHERE, reranking is a cross-encoder call you make after retrieval either way, and multi-tenancy is a column you already have. We were paying for a feature list to use one item on it. Check the list against what you actually call.
When this is the wrong call
I would not do this if:
- You are past tens of millions of vectors at high, sustained QPS. Buy the specialised store. The index-build tuning stops being a config change and becomes a full-time interest, and quantization and sharding are genuinely better over there.
- Embeddings are the product. If you are running billion-scale semantic search as your core offering, this is your database, not a column, and you should treat it that way.
- Your vectors have no source of truth in Postgres. The entire argument here is that the embedding is derived from a row you already store. If the vectors stand alone — you never store the source text, only the vector — then there is no seam to delete and the calculus is just raw ANN performance, which the dedicated store wins.
- You need a distance metric or index type pgvector doesn't have. Rare, but check. Do not discover it after the migration.
What I would tell myself a year earlier
The embedding was never a database. It was a column we had exiled to another system, and then hired a pipeline to visit it and keep its story straight.
Before you stand up a vector database, ask the question we skipped: is the vector derived from a row I already store, and are my real queries filtered? If both are yes, you are not buying nearest-neighbour search. You are buying a second copy of your data, a network hop, and a synchronisation problem — to get a WHERE clause your database already had.
pgvector shipped HNSW in 2023 and filtered iterative scan in 2024, and did not send anyone a migration notice.
Four questions I would genuinely like answered in the comments:
- What is your actual vector count and peak QPS — the numbers in your dashboard, not the ones in your architecture doc?
- If you run a dedicated store, how do you handle deletes propagating from your primary — and are you sure they do?
- Has anyone benchmarked pgvector 0.8 iterative scan against a dedicated store on filtered queries specifically? That is the comparison that matters and the one nobody posts.
- Who moved the other way — Postgres → dedicated store — and what number forced it?
Top comments (0)