pgvector: similarity search in the database you already have

An embedding is an array of floats — 1536 of them for a typical OpenAI model. The moment you generate your first batch you have a storage problem, and the internet’s answer is a vector database. Usually the better answer is the database already sitting in your stack.

pgvector is a Postgres extension. It adds a column type for vectors, a few distance operators, and two index types built for nearest-neighbor search. That’s the whole thing.

What Postgres can’t do without it

You can already store floats in Postgres — real[] works. What you can’t do is find the closest one quickly.

Every index Postgres ships with assumes a total order or an equality test. A B-tree works because for any two values, one of them is smaller. Similarity has no such order. “Closest to this point” depends on the point, so no single sorted arrangement of the table answers it. Without an index, ORDER BY distance LIMIT 10 reads every row, does 1536 multiplications on each one, and sorts. At ten thousand rows nobody notices. At ten million it’s a timeout.

pgvector fills that gap with a vector type that stores the floats compactly and typed, distance operators the query planner understands, and two index structures — IVFFlat and HNSW — that trade exactness for speed.

That trade is the important part. These indexes are approximate: the same query, once indexed, can miss a genuine top-10 neighbor. You don’t eliminate that, you tune how often it happens.

Getting it running

The extension has to be installed on the server. RDS, Cloud SQL, Supabase, Neon and Azure all carry it; self-hosted, it’s a package or a make install. Then:

CREATE EXTENSION vector;

CREATE TABLE doc_chunk (
  id        bigserial PRIMARY KEY,
  doc_id    bigint NOT NULL REFERENCES doc(id) ON DELETE CASCADE,
  body      text NOT NULL,
  embedding vector(1536)
);

The dimension in vector(1536) is enforced on insert. That catches the classic bug of swapping embedding models halfway through a backfill and only finding out months later when search quality rots.

Inserting is a string literal, so most drivers need no special support:

INSERT INTO doc_chunk (doc_id, body, embedding) VALUES (1, 'text…', '[0.031,-0.0142,…]');

Querying

Three operators cover almost everything:

<-> — Euclidean (L2) distance
<=> — cosine distance
<#> — negative inner product

Pick the one your embedding model was trained for; the provider’s docs say which. Most say cosine. If your vectors are already unit length — most API embeddings are — inner product produces the identical ranking without the normalization, so <#> is the cheaper equivalent. It returns the negative so that an ascending ORDER BY still means most similar first.

SELECT id, body, embedding <=> $1 AS distance
FROM doc_chunk
ORDER BY embedding <=> $1
LIMIT 10;

Run that under EXPLAIN and you’ll see a sequential scan. It is correct and it is exact. That’s a fine place to stay until it’s measurably slow.

Adding the index

CREATE INDEX ON doc_chunk USING hnsw (embedding vector_cosine_ops);

Two rules catch people. The operator class has to match the operator in the query — vector_cosine_ops goes with <=>, vector_l2_ops with <->, vector_ip_ops with <#>. And the ORDER BY has to be the bare distance expression; wrap it in a function or add a second sort key and the planner falls back to a sequential scan without telling you.

Build quality is set by m and ef_construction, defaults 16 and 64. Raising them improves recall and costs build time and memory. Give the build room with maintenance_work_mem — if the graph doesn’t fit, pgvector builds it on disk and the job goes from minutes to hours.

Recall at query time is one session knob:

SET hnsw.ef_search = 100;  -- default 40

IVFFlat is the other option: faster to build, smaller on disk, but it clusters existing rows, so the table has to be populated first and quality drifts as the data changes. HNSW is the default choice now. Reach for IVFFlat when build time or index size is the binding constraint.

Where it actually bites: filters

Demo queries have no WHERE clause. Real ones do — one tenant, one language, published only.

An index scan walks the graph, collects a fixed number of candidates, and then your filter runs against them. If the filter is selective, one tenant out of a thousand, most candidates get discarded and a LIMIT 10 hands back three rows. Not an error, no warning: quietly incomplete results, which is the worst kind of bug to ship.

Three ways out, roughly in order of how often I use them:

Iterative scans (pgvector 0.8+). SET hnsw.iterative_scan = relaxed_order lets the scan keep pulling candidates until the limit is satisfied. strict_order guarantees exact ordering and costs more.
Raise ef_search. Blunt, but enough when the filter isn’t very selective.
A partial index per filter value, when that set of values is small and stable.

What it costs

A vector(1536) is 1536 × 4 bytes plus a header — about 6 KB per row. A million chunks is 6 GB of embeddings before you index anything, and the HNSW index is the same order of magnitude again. Two levers: halfvec(1536) stores float16 and halves the storage at a recall cost most workloads can’t measure, and several models will hand you shorter embeddings if you ask.

When it isn’t the answer

Postgres won’t be the fastest vector store you could buy. Purpose-built engines win on recall-per-millisecond at scale, and if you’re serving a billion vectors under a hard latency budget that gap will find you.

Below that line, the case for pgvector was never performance. It’s that the embedding lives in the same transaction as the row it describes, in the same backup, the same replica, the same permission model. Deleting a document deletes its chunks through a foreign key you already wrote. A similarity search joins the users table without a network hop, and there’s no second system to keep in sync at 3am.

Start with the sequential scan. Add HNSW when it’s slow. Move to a dedicated engine when Postgres genuinely stops keeping up — which is later than most people expect.