Vector search in Postgres: the mental model behind pgvector

Where it lands is an ordinary column. If you've set up pgvector with Drizzle before this will look familiar (if not, I wrote the setup walkthrough separately):

That 1024 is the first decision you can't cheaply walk back. voyage-multimodal-3.5 will also hand you 256, 512, or 2048 if you ask, but whatever you pick, every row has to agree on it. Changing your mind later means re-embedding the whole table. We deal with that properly in Part 8. For now just notice what you already have: text and images, retrievable together, in a table that also has source, timestamps, foreign keys, and every other thing Postgres has always given you. The vectors moved in next to your real data instead of into a second system. Keep that in mind, it's the through-line of the whole series.

"Closest" depends on how you measure

Once everything is a point, search is just "which points are nearest the query." But nearest isn't one thing. The same vectors rank differently depending on the metric. Switch between them:

pgvector ships an operator for each: <=> for cosine distance, <-> for Euclidean (L2), <#> for negative inner product. They're not interchangeable. Cosine only cares about direction, so vector length is invisible to it. Inner product rewards longer vectors. With normalized embeddings some of these collapse to the same ordering, and knowing when they do is the difference between results that feel right and results you can't explain. I went deeper on what each operator actually means if you want the long version.

Your first real query, in Drizzle, is small:

cosineDistance returns a distance where 0 is identical, so 1 - distance flips it into a similarity where 1 is best. This is the query that "works" in every demo. It's also the query that's about to lie to you.

The point where the prototype starts lying

Here's the failure that sends people to this series.

The query above is honest. It compares the query against every row, exactly. At a few hundred rows that's instant and perfect, so you demo it, it looks great, and the plan ships.

Then the table grows. To stay fast you add an approximate index, HNSW or IVFFlat (that choice gets two whole posts later, and I've written a shorter overview too). Approximate is the word doing the damage. The index stops checking every row and checks a neighborhood instead. Untuned, it starts skipping the right answers. Nothing throws. The query still returns ten rows. They're just quietly, partially wrong.

Drag it until the message changes. That gap between "looks fine" and "is missing results" is invisible unless you go measure it on purpose. Most teams never do, which is why a lot of vector search in production is subtly broken with no number attached to how broken.

(Those numbers are illustrative. The real way to get them: run your query set through an exact scan to get the true neighbors, run the same set through the index, and diff the two. That's recall, and it needs no human labels. We build exactly that harness in Part 7.)

So here's the honest version: pgvector is not magic, and there is a point where a naive setup is wrong. The good news is that point is not "go buy a vector database." It's "go understand the tradeoffs." Postgres handles tens of millions of vectors comfortably once you know which knobs to turn, and you keep your joins, your filters, your transactions, and one system to operate at 2am instead of two. (The community decision tree keeps converging on the same line: pgvector until you have a specific reason not to.)

When Postgres is enough, and when it isn't

I'll steelman the other side, because the honest answer has an edge.

For a real slice of teams, a dedicated vector database is worth it. Hundreds of millions of vectors, a hard sub-10ms budget, relentless write-and-reindex churn: that's a real workload and a purpose-built engine earns its keep there.

But that's not most teams reaching for one. Most are running Postgres already, haven't tuned it at all, and are about to bolt on a second database to solve a problem the first one would handle fine. You don't need Pinecone to find out you needed an ef_search value. If your vectors belong next to relational data you already query, and your scale is millions to low tens of millions, the answer is the database you already operate. Fewer moving parts wins almost every time.

I've watched this play out enough times that the migration story is predictable: team picks a dedicated vector DB before they've measured anything, spends a quarter on integration, then quietly moves back to Postgres when the spec turns out to be 200k rows and a metadata filter the other system was bad at anyway. (One of the more honest takes I've seen on r/Rag was someone running 180k docs on a managed vector store and transitioning to pgvector because the dedicated store wasn't earning it.)

And if you do outgrow vanilla pgvector before you outgrow Postgres, the next step isn't a different database. It's pgvectorscale, Timescale's companion extension that pushes the index out further on the same stack. We'll come back to it in Part 8.

What the rest of the series does

From here on, every post takes one of these stuck points and turns it into a decision you can make in your own codebase, against one continuous TypeScript project: a multimodal docs assistant that retrieves across text and screenshots.

1. Setup (Part 2). Voyage, Drizzle, and a Postgres you self-host and control down to the build flags.

2. Relevance (Part 3). Chunking real documents and debugging results that look plausible and are wrong.

3. Speed (Parts 4 and 5). Choosing and building the index, then tuning Postgres and pgvector until recall and latency are both yours.

4. Retrieval (Part 6). Hybrid search, reranking, and the RAG pattern done properly instead of done once. The shape of that pipeline exists as a post already; Part 6 is where we make it good.

5. Evals (Part 7). Proving the system is good with a number, and catching the regression before your users do.

6. Operating it (Part 8). Zero-downtime re-embedding when the model changes, and watching the drift that quietly rots search over months.

You've got the model the rest of it stands on: embeddings are points, one multimodal model puts text and images in the same space, "nearest" depends on the metric, and an untuned index returns wrong results without an error.

None of this requires a new vendor, a second datastore, or a dashboard you check at 2am. It's the database you already trust, doing one more thing well. That's the version of vector search I want more teams to have: boring, inspectable, and yours. Part 2 turns it on.