pgvector & Vector Search

A traditional database is brilliant at exact matches. Ask it for the customer with ID 4021, or every order placed on Tuesday, and it answers instantly. But ask it something a human finds trivial — "find me articles similar to this one," or "which support tickets are about the same problem?" — and it falls apart. It has no idea that "my card was declined" and "payment failed at checkout" mean nearly the same thing. The words don't match, so to a LIKE query they're strangers.

Vector search is how we teach a database to understand meaning instead of matching characters. And thanks to the pgvector extension, you can do it inside the PostgreSQL you already run — no new system to stand up, no data to keep in sync somewhere else. It's also the quiet engine behind nearly every "chat with your documents" AI feature you've used this year. Let's build the intuition first, then the real thing.

The Big Idea: Turning Meaning Into Numbers

Here's the trick that makes all of this work: the embedding. Feed a sentence — or a paragraph, or a whole document — into an embedding model, and it hands back a long list of numbers called a vector. Something like [0.021, -0.194, 0.882, ...], usually with 384, 768, or 1,536 numbers in it. Companies like OpenAI and Cohere sell these models as an API call; plenty of good open-source ones run on your own hardware too.

What makes it useful isn't the numbers themselves — it's where the model places each piece of text in that high-dimensional space. Items with similar meaning land close together; unrelated ones land far apart. "Dog" sits near "puppy" and "canine," and nowhere near "quarterly revenue." Crucially, it works across completely different wording: "my card was declined" and "payment failed at checkout" end up as near neighbors, even though the two sentences don't share a single meaningful word.

Once meaning is just coordinates, "find similar things" turns into "find nearby points" — an embedding is really just a geometry problem in disguise, and geometry is something a database is very good at.

Enter pgvector

The good news: you don't need a separate, specialized vector database to get started, and honestly, you may never need one. Andrew Kane built pgvector to add a native vector column type and a handful of similarity operators directly to Postgres. Install the extension once, and your existing relational database quietly picks up semantic search as a new skill:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE articles (
    id        SERIAL PRIMARY KEY,
    title     TEXT,
    body      TEXT,
    embedding vector(768)      -- 768 numbers per row
);

That last column holds the embedding for each row. You generate the vectors with your embedding model of choice in application code, then insert them the same way you'd insert anything else:

INSERT INTO articles (title, body, embedding)
VALUES ('Intro to Indexing', '...', '[0.021, -0.194, 0.882, ...]');

The real payoff of doing this inside Postgres, rather than in a bolt-on vector store, is that your vectors live right next to the rest of your data. You can filter, join, and transact over both in one query — nothing to keep in sync, nothing extra to babysit.

Asking "What's Similar?"

pgvector adds a few operators that measure the distance between two vectors, so finding "the articles most similar to this one" is just a matter of sorting by that distance and taking the closest few:

-- Find the 5 articles most similar to article #1
SELECT id, title
FROM articles
ORDER BY embedding <=> (SELECT embedding FROM articles WHERE id = 1)
LIMIT 5;

That <=> is cosine similarity (or rather cosine distance — pgvector flips the sign so smaller means closer), the go-to metric for text embeddings. There are two siblings worth knowing about: <-> for Euclidean distance, useful when a vector's magnitude carries meaning, and <#> for negative inner product, which some models are trained to expect. Match the operator to whatever your embedding model was built for and you'll rarely have to think about it again.

A real query usually starts from a search phrase rather than an existing row. Your application embeds the user's question with the same model it used for everything else, then hands that vector to Postgres:

-- $1 is the embedding of the user's search text
SELECT id, title
FROM articles
ORDER BY embedding <=> $1
LIMIT 5;

Making It Fast: Approximate Nearest Neighbor Indexes

Comparing a query against every single row — an exact nearest-neighbor search — is perfectly fine at a few thousand rows. At a few million, it starts to hurt. The fix is an index built for approximate nearest neighbors: trade a sliver of accuracy for a large speed-up, and nobody notices the difference. pgvector ships two.

HNSW (Hierarchical Navigable Small World) is the modern default. It builds a layered graph that lets a search hop straight toward the right neighborhood instead of wandering row by row — great query speed and recall, at the cost of more memory and a slower build:

CREATE INDEX ON articles
USING hnsw (embedding vector_cosine_ops);

IVFFlat takes a simpler approach: it buckets vectors into clusters up front and only searches the nearest few buckets. It's lighter on memory and quicker to build, but build it after loading a realistic amount of data — it learns its clusters from whatever's there at the time:

CREATE INDEX ON articles
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);   -- rows/1000 up to ~1M rows, sqrt(rows) beyond that

One easy trap: the operator class (vector_cosine_ops, vector_l2_ops, and so on) has to match the operator you actually query with. Get them out of step and Postgres will quietly skip the index instead of using it.

The Killer App: Retrieval-Augmented Generation

This is the reason vector search went from niche to everywhere almost overnight. Large language models are remarkably fluent, but they share two problems: they don't know your private data, and they'll confidently make things up when they're unsure. Retrieval-augmented generation, or RAG, fixes both by embedding your documents ahead of time, then — at question time — embedding the user's question and retrieving the handful of most relevant chunks to hand the model as context. Look closely at that retrieval step and you'll recognize it — it's the exact ORDER BY embedding <=> $1 LIMIT k query from a few sections ago. That one line is the beating heart of nearly every "chat with your PDFs / docs / codebase" product you've tried. (We covered the rest of the pipeline — chunking, re-ranking, agentic retrieval — in RAG: Giving LLMs a Memory.)

Where pgvector really earns its keep in a RAG pipeline is that you can combine semantic search with ordinary WHERE filters in a single statement — something that's surprisingly awkward with a bolt-on vector database:

-- Semantic search, but only within one customer's recent, published docs
SELECT id, title
FROM documents
WHERE customer_id = 42
  AND published = true
  AND created_at > now() - interval '1 year'
ORDER BY embedding <=> $1
LIMIT 5;

A Few Things That Trip People Up

Every real project runs into the same handful of surprises, so it's worth naming them up front.

The first one bites hardest: every vector in a table has to come from the same model. Embeddings from two different models — or even two versions of the same model — live in incompatible coordinate spaces, so mixing them produces confident-looking nonsense rather than an error. If you ever switch models, plan on re-embedding everything, not just the new rows.

Related to that, dimensions have to match exactly. A vector(768) column has no way to hold a 1,536-dimension embedding, so pick your model before you write the CREATE TABLE, not after.

One more limit worth knowing up front: HNSW and IVFFlat indexes cap out at 2,000 dimensions (a side effect of how much a single Postgres page can hold). Most embedding models fall well under that, but a few of the largest don't — if yours does, either ask the model for fewer dimensions (many APIs let you request that) or use pgvector's halfvec type, which halves the storage per number and doubles the indexable limit to 4,000.

Chunk size is a real design decision, not an afterthought. Chunk too large, and each piece becomes a blurry average of several topics; too small, and it loses the surrounding context that made it meaningful. Paragraph-to-page-sized chunks tend to be a reasonable starting point, but it's worth measuring against your own data rather than trusting a rule of thumb.

Keep in mind, too, that distance is relative, not absolute. A cosine distance of 0.2 isn't universally "similar" — what counts as a good match varies by model and by data. Calibrate a threshold against results you can eyeball rather than trusting a number that felt right on someone else's blog post.

And finally, remember that approximate really does mean approximate. An ANN index can occasionally miss a true nearest neighbor in exchange for its speed. If a particular query needs higher accuracy, both index types have a knob for it — HNSW's ef_search, IVFFlat's probes — and turning it up buys back recall at the cost of some speed.

Do You Even Need a Dedicated Vector Database?

There's a whole category of specialized vector databases built just for this — Pinecone, Weaviate, Qdrant, Milvus. They earn their keep at serious scale — hundreds of millions to billions of vectors, spread across many machines. But for the large majority of applications, pgvector is the pragmatic choice: one system to operate, transactional consistency with the rest of your data, and the full power of SQL filtering sitting right next to similarity search. Start with pgvector. Reach for a dedicated store only once you've genuinely outgrown it — most projects never do.

For decades, a database could only answer questions phrased in its own exact terms. Embeddings changed that by turning meaning into coordinates, and pgvector brings that ability straight into the reliable, familiar PostgreSQL you already trust. Whether you're building "more like this" recommendations, quietly deduplicating support tickets, or standing up a full RAG pipeline behind an AI assistant, it all starts with the same humble query: find the nearest points. Your database just learned what things mean.