pgvector: Vector Search in PostgreSQL Without a New Database

Every few months the industry discovers a new kind of database. This year it's vector databases — specialized stores for embeddings, the numerical fingerprints that power "semantic search," recommendations, and all the AI features landing in every product you use. The pitch is always the same: spin up this new service, sync your data into it, and pay for another always-on thing.

Before you do that: if you already run PostgreSQL, you probably don't need a new database at all.

pgvector is a Postgres extension that adds vector types, distance operators, and approximate-nearest-neighbor indexes to the database you're already running. I've now used it in production for semantic search over ~200k documents, and this post is everything I wish someone had told me on day one — the setup, the indexes, and the two traps that cost me a weekend.

The problem in one paragraph

Classic search matches words: you type "disk full," you get documents containing "disk full." Semantic search matches meaning: type "out of storage," and it finds the disk-full article too. Embeddings make this work — you send text to a model, it returns a few hundred floating-point numbers representing the meaning, and texts with similar meanings get similar numbers. Storing and querying those numbers at scale is the whole job. Postgres does it fine.

Setup is insultingly easy

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title     text NOT NULL,
    body      text NOT NULL,
    embedding vector(1536)
);

The vector(1536) matches the output width of OpenAI's text-embedding-3-small — the most common embedding model and a fine default. Whatever model you pick, the column width must match its output dimension, or inserts will fail loudly.

Insert and search:

-- insert (embedding generated by your app via the provider's API)
INSERT INTO documents (title, body, embedding)
VALUES ('Disk full', 'When df shows 100%...', '[0.012, -0.034, ...]');

-- find the 5 most similar documents to a query embedding
SELECT title, body, embedding <=> '[0.011, -0.032, ...]' AS distance
FROM documents
ORDER BY embedding <=> '[0.011, -0.032, ...]'
LIMIT 5;

The <=> operator is cosine distance — pgvector ships <-> (L2), <#> (inner product), and <=> (cosine). For normalized embeddings, inner product and cosine rank identically, and cosine is the safe default for text search.

The part everyone skips: indexes

Without an index, every query is a full table scan with a distance computation per row. Fine at 1,000 rows. Ugly at 100,000. Painful at a million.

pgvector gives you two approximate index types. Today, you want HNSW:

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

HNSW (Hierarchical Navigable Small World) is a graph index — fast to query, great recall, and it doesn't require the data to be present at creation time. The older ivfflat index needs a representative dataset before you build it, or its clusters are garbage; that requirement surprises people exactly once. Just use HNSW.

Two knobs matter at query time:

SET hnsw.ef_search = 40;  -- higher = better recall, slower

Default ef_search is 40, which is a good starting point. If results feel wrong, raise it before you touch anything else. Accuracy problems are almost always recall problems.

Trap #1: the distance threshold nobody agrees on

The query above returns the 5 nearest documents — even if none of them are remotely relevant. Your application needs a cutoff, and here pgvector's docs go silent. There is no universal number because it depends on your embedding model and your data. What worked for me: take 100 queries where you know the right answer, plot the distance of the correct hit, and pick a threshold just past the worst case. Mine landed at 0.30 for that model and dataset. Yours will differ. Measure it.

Trap #2: embeddings generated with different models don't mix

I learned this the expensive way. Early on, I regenerated embeddings with a newer model for some rows and left others alone. Cosine distance between two different embedding spaces is meaningless — your queries will return confident nonsense from the mismatched rows, intermittently, which is the worst kind of bug. Add a column to record which model produced each embedding, and if you ever switch models, re-embed everything in one pass:

ALTER TABLE documents ADD COLUMN embed_model text NOT NULL DEFAULT 'text-embedding-3-small';

Where do embeddings come from?

Any embedding API works — OpenAI, Voyage, Cohere. But if you've been following along with the local LLM setup, here's the part I find genuinely neat: Ollama runs embedding models locally too:

ollama pull nomic-embed-text
curl http://localhost:11434/api/embeddings -d '{"model": "nomic-embed-text", "prompt": "how do I free up disk space"}'

That returns a 768-dimensional vector — so your vector(...) column would be vector(768) for this model — generated on your own hardware, for free, with the text never leaving your network. For a privacy-first stack, local embeddings plus pgvector is a complete semantic search pipeline with zero third parties.

Do you actually need a dedicated vector database?

Honest answer: probably not, at your scale. pgvector comfortably handles millions of vectors at single-digit millisecond query times, and it inherits everything Postgres gives you for free — transactions, point-in-time recovery, the backup strategy you already have, row-level security, and joins with your actual relational data. A dedicated vector store makes sense when you're doing tens of millions of vectors with extreme QPS, or when you need GPU-accelerated indexes. Everything short of that is a second database to run for no reason.

The pattern I keep returning to: Postgres first, specialize only when Postgres measurably fails you. It has not failed me yet.