My LibraryBuy PDF
Part 6/Retrieval

Vector Search With pgvector

The bug report said “search is broken for the Enterprise plan.” Not slow. Broken. The exact query that returned ten solid results on my laptop returned nothing in production, and only for a handful of customers. No error in the logs. No stack trace. Just an empty array where results used to be.

The customers who hit it had one thing in common: they were small. A few dozen documents each, in a table with a few hundred thousand rows total. And the thing I had shipped the week before, proud of myself, was an approximate index to make search fast.

Here is what was actually happening. The index grabbed its 40 nearest candidate rows first, ranked by distance, ignoring everything else about them. Then Postgres applied my WHERE tenant_id = $1 filter to those 40. For a tiny tenant, all 40 candidates belonged to somebody else, so every single one got thrown away. Zero rows survived. The query was not broken. It was doing exactly what I told it to, in an order I had not thought about.

The fix was one line. The reason I could find and fix it at all is that this whole system lived in Postgres, a database I already understood, next to the users table and the billing rows and everything else. That is the case I want to make. If you already run Postgres, you almost certainly do not need a specialist database to ship semantic search. And learning how pgvector does it teaches you the concepts that every vector store, dedicated or not, is built on.

A vector is just another column

Embeddings turn a piece of text into a list of numbers, a point in high-dimensional space, where nearby points mean related text. pgvector is a Postgres extension that adds a column type to hold that list and a set of operators to measure distance between two of them. That is genuinely most of it.

You enable it once per database, then declare a column with a fixed dimension.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id          bigserial PRIMARY KEY,
  tenant_id   bigint      NOT NULL,
  category    text        NOT NULL,
  content     text        NOT NULL,
  embedding   vector(1536),            -- one embedding per row
  updated_at  timestamptz NOT NULL DEFAULT now()
);

The 1536 is the number of dimensions your embedding model produces, and it is not negotiable after the fact. Every vector in this column must have exactly that many components, so it has to match your model’s output. Switch to a model with a different output size later and this column has to change with it, which is a whole section on its own further down.

Notice what the vector is sitting next to: an ordinary bigint, an ordinary text, a timestamp. The embedding is not special infrastructure. It is a column in a normal table, and if SQL basics are already comfortable, you know how to query around it.

Storing one is a plain insert. pgvector accepts a vector as a text literal that looks like a JSON array, '[0.1, 0.2, 0.3]', which means from JavaScript you can hand it a stringified array and it just works.

import { Pool } from "pg";
const pool = new Pool();

// `embedding` is a plain number[] you got back from your embedding model
async function insertDoc({ tenantId, category, content, embedding }) {
  await pool.query(
    `INSERT INTO documents (tenant_id, category, content, embedding)
     VALUES ($1, $2, $3, $4)`,
    [tenantId, category, content, JSON.stringify(embedding)],
  );
}

JSON.stringify([0.1, 0.2]) produces "[0.1,0.2]", which is exactly the literal pgvector wants, and it goes in as a bound parameter so you get the usual injection safety for free. Most Postgres drivers also ship a small pgvector helper that registers the type so you can pass a raw array, but the stringify trick works everywhere and is worth knowing when you are debugging.

Ordering by nearness

Finding related documents is an ORDER BY on a distance operator, then a LIMIT. That is the entire query shape.

SELECT id, content
FROM documents
ORDER BY embedding <=> $1     -- $1 is the query embedding
LIMIT 5;

The <=> is cosine distance. pgvector gives you one operator per metric, and they all return a distance where smaller means closer, so you sort ascending and take the top few.

-- distance operators (smaller result = nearer)
--   <=>   cosine distance
--   <->   L2 / Euclidean distance
--   <#>   negative inner product
--   <+>   L1 / taxicab distance
a row stores content, metadata, and one embedding beside themid7contentReset your passwordtenant_idacmeembedding[0.02, -0.11, …]ORDER BY embedding <=> q LIMIT 3 (smaller distance = nearer)top 3 returned#1doc 7 · reset your password0.08#2doc 3 · change your password0.21#3doc 9 · update billing card0.37#4doc 2 · where is my parcel0.62
A row keeps content and metadata beside its embedding. A query orders every row by distance to the query vector and returns the nearest k.

Which operator you use is not a free choice. An embedding model is trained with one notion of distance in mind, and using a different one gives you plausible-looking rankings that are subtly wrong. The cosine, dot product and L2 comparison covers exactly why they disagree and when. The one-line version: most text models want cosine, so <=> is your default, and the operator has to match the operator class you build the index with or the index gets silently ignored.

Small tables do not need an index

Run that ORDER BY ... LIMIT with no index and Postgres does the honest thing: it computes the distance from your query to every single row, sorts, and takes the top five. This is a full scan, it is exact, and for a few thousand rows it finishes in under a millisecond with nothing to tune. Do not add an index yet. You would be solving a problem you do not have.

This mirrors ordinary B-tree indexing almost exactly. A sequential scan is the right plan on a small table, and the payoff from an index only shows up once the row count crosses into the range where scanning everything hurts. For vectors that range is smaller than you might guess, because each comparison is a thousand-plus multiply-adds rather than one integer compare, but the shape of the decision is the same.

Past somewhere in the tens of thousands of rows, you add an approximate index and accept results that are almost always exactly right in exchange for a large speedup. pgvector ships two kinds, and the HNSW versus IVFFlat tradeoff is covered in depth elsewhere, so here is just enough to build one.

-- HNSW: best speed at a given recall, no training step, more memory
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

m and ef_construction control how thorough the graph is at build time. The defaults (16 and 64 as of pgvector 0.8) are sensible starting points; raising them buys recall at the cost of build time and memory. The dial you touch most often is at query time.

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

That one setting is the recall-versus-latency knob for every query in the session. IVFFlat is the other option, cheaper to build and lighter on memory, but it needs a representative sample of your data to learn its clusters, so you cannot build a good one on an almost-empty table.

-- IVFFlat: cheaper build, needs training data already in the table
CREATE INDEX ON documents
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 200);       -- roughly rows / 1000, then sqrt(rows) past 1M
SET ivfflat.probes = 10;    -- default 1; more probes = better recall, slower
query latency as the table growslatencyrows in the table1k100k10Madd an index around hereexact scanHNSW indexscan wins here
Below a few thousand rows an exact scan wins outright: no index, full recall, nothing to tune. As the table grows the scan's cost climbs linearly while an approximate index stays nearly flat.

After you build an index, confirm the planner actually uses it. EXPLAIN ANALYZE on the query should show an Index Scan with an Order By on your operator, not a Seq Scan. If it says Seq Scan, the usual culprit is the operator-class mismatch from the callout above.

Filtering is the whole game

A query that only ranks by nearness is a demo. Every real product narrows the search first: this tenant’s documents, the ones this user is allowed to see, published this year, in this category. In pgvector that is an ordinary WHERE clause sitting in front of the ORDER BY.

SELECT id, content
FROM documents
WHERE tenant_id = $1
  AND category  = $2
ORDER BY embedding <=> $3
LIMIT 5;

This is the query that makes vector search useful, and it is also the query that makes it safe. That WHERE tenant_id = $1 is not a relevance tweak. It is a security boundary. Semantic search over a shared table is one forgotten filter away from returning one customer’s private documents to another customer, and the model reading those results will happily summarise them into an answer. Retrieval is subject to the same authorization rules as any other read, and in a multi-tenant system the tenant scope belongs in the query itself, not bolted on afterward in application code where it is easy to skip.

all rowsWHERE tenant_id = acmerank by distanceacme + globex mixedglobex dropped entirely#1 d 0.08#2 d 0.19#3 d 0.33ranked withinthe allowed set only
The metadata filter runs first and scopes the corpus to rows this tenant is allowed to see. Nearest-neighbour ranking then happens only inside that scoped set, so another tenant's rows can never surface.

The demo below makes the narrowing concrete. Pick a query theme and a filter, and watch the corpus shrink to the allowed rows before anything gets ranked. Switch the tenant and every one of the other tenant’s documents disappears from the results, not just drops in rank. It is fully simulated in the page, with six toy documents and hand-picked three-number vectors, no database and no model.

interactiveFilter then rank: how a WHERE clause narrows the candidates before cosine ranking (fully simulated)

Where filtering and the index fight

Here is the trap from the opening, spelled out. An approximate index is built to answer one question fast: what are the nearest vectors to this query. It knows nothing about your tenant_id or category. So Postgres uses the index to fetch a batch of nearest candidates (roughly ef_search of them for HNSW), and only then applies your WHERE to that batch. If your filter is selective, most or all of those candidates fail it, and you get back fewer rows than you asked for. Sometimes zero.

You can see it in the plan. The tell is a Filter line throwing away everything the index handed up.

Limit
  ->  Index Scan using documents_embedding_idx on documents
        Order By: (embedding <=> $3)
        Filter: (tenant_id = 42)
        Rows Removed by Filter: 40     <-- fetched 40 neighbours, kept none

Forty candidates in, forty removed, zero out, no error. This is the single most surprising thing about running vector search in Postgres, and it does not show up until a real query hits a real filter on real data.

single pass: 0 rows pass the filterqmatching rows sit outside the first-pass ringiterative scan: widen until k matchqwiden until 3 matching rows are foundwrong tenantmatches the filter
A single index pass fetches the k nearest candidates and only then applies the filter. If the matching rows sit further out, every candidate fails and you get nothing. An iterative scan keeps widening the search until enough rows pass the filter.

There are three ways out, and you usually reach for more than one.

Turn on iterative scans. This is pgvector 0.8’s answer to exactly this problem. Instead of one fixed batch, the index keeps scanning outward and re-applying the filter until it has collected enough matching rows or hits a safety limit.

BEGIN;
SET LOCAL hnsw.iterative_scan = 'relaxed_order';
SELECT id, content
FROM documents
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 5;
COMMIT;

It is off by default, so you have to opt in, and SET LOCAL inside a transaction is the right scope so you are not changing behaviour for the whole connection. There are two modes. relaxed_order is faster and returns results in approximately the right distance order, which is fine for search. strict_order guarantees exact ordering and costs a bit more. A safety valve, hnsw.max_scan_tuples (default 20,000), stops a pathologically selective filter from turning into a slow near-full-scan.

Give a dominant filter its own partial index. If one tenant or one category owns most of the interesting queries, index just that slice. A partial index is smaller and dramatically faster to build, and it sidesteps the over-filtering problem for that slice because the index only contains matching rows in the first place.

-- a big tenant that gets queried constantly
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops)
  WHERE tenant_id = 42;

-- plus a plain B-tree so the planner can cut rows cheaply on any tenant
CREATE INDEX ON documents (tenant_id, category);

Let the planner choose exact when the filter is tiny. pgvector 0.8 improved its cost estimates, so when a filter is very selective (it matches only a handful of rows), the planner can decide to skip the vector index entirely, pull those few rows with the B-tree, and compute exact distances on them. That is not a failure, it is the correct plan. A dozen exact comparisons beat a fuzzy graph walk every time.

Keeping the vectors in sync

An embedding is derived data. It is a cached function of the row’s text, and like any cache it goes stale the moment the source changes and nobody refreshes it. Edit a document’s content and its embedding column is now describing the old text. Searches keep matching the previous version, quietly, with no error to tell you the two have drifted apart.

content editedUPDATE documentsre-embedmodel callwrite vectorSET embeddingindex updatesautomaticskip re-embed = stale vector, wrong results
An embedding is derived from the content. When content changes you must re-embed and let the index update. Skip the middle step and the stored vector describes text that no longer exists.

Two patterns keep them together. The simplest is to make embedding part of the write path: whenever you insert or update the content, call the model and write the new vector in the same operation. That is fine at low volume. It couples every write to a slow, sometimes-flaky model call, though, so at higher volume you decouple them. Mark the row as needing work and let a background worker do the embedding.

-- clear the embedding whenever the content actually changes;
-- a worker re-embeds rows where embedding IS NULL
CREATE OR REPLACE FUNCTION mark_stale() RETURNS trigger AS $$
BEGIN
  IF NEW.content IS DISTINCT FROM OLD.content THEN
    NEW.embedding := NULL;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER documents_stale
  BEFORE UPDATE ON documents
  FOR EACH ROW EXECUTE FUNCTION mark_stale();

Now a change to content nulls the vector, a search can choose to skip or de-rank rows with no embedding, and a background job picks up the embedding IS NULL rows, calls the model, and fills them back in. The database is the queue. This is the same stale-then-refresh dance you would use for any expensive derived value.

Re-embedding when you change models

Switching embedding models is the bigger migration, and people underestimate it. A vector from model A is meaningless to model B. They are different coordinate systems, often with different dimension counts, so you cannot mix them in one column and you cannot compare across them. Changing models means re-embedding every row you have.

Do it blue-green so search never breaks. Add a second column, backfill it in batches with the new model while the old column keeps serving traffic, build the new index, then flip reads over and drop the old column once you are confident.

ALTER TABLE documents ADD COLUMN embedding_v2 vector(3072);
-- backfill embedding_v2 in batches with the new model,
-- build a fresh HNSW index on it, then switch queries to embedding_v2
-- and finally: ALTER TABLE documents DROP COLUMN embedding;

Budget for it. Re-embedding millions of rows is real model spend and real wall-clock time, and it is the main reason teams do not swap embedding models on a whim.

When to reach for a dedicated vector database

pgvector is not the only option, and pretending it is would be dishonest. There is a healthy field of purpose-built vector databases, and they earn their place at the extremes. The honest decision is about scale and features, not fashion.

The line is also moving. Extensions like pgvectorscale add a disk-based DiskANN index on top of Postgres, which pushes the scale where you would have to leave much higher than it used to be. So the safe move is to start in Postgres, measure, and switch only when a real number (latency at your p99, cost at your row count, a feature you genuinely need) tells you to. Migrating later is a data export and a re-index, not a rewrite of your app, because the concepts travel: distance metrics, an ANN index, and metadata filters exist in every one of these systems.

The bill: storage and memory

Vectors are big, and the arithmetic surprises people. A single vector(1536) costs 4 × 1536 + 8 bytes, about 6KB per row, because each dimension is a 32-bit float. A million of them is roughly 6GB just for the column, before the index, and an HNSW index wants to live in RAM to be fast, so it adds several more gigabytes you have to actually provision.

The cheapest lever is halfvec, a half-precision type that stores each dimension in 2 bytes instead of 4. It roughly halves storage and index memory (that 6KB row drops to about 3KB) for a recall hit that is usually small enough to ignore for search. When storage or memory is the thing hurting, reach for halfvec before you reach for a different database.

Summary

  • pgvector adds a vector(n) column to Postgres and distance operators, so an embedding is just a column beside your text and metadata, queried with ORDER BY embedding <=> $1 LIMIT k.
  • The operators return distance, so smaller is nearer. Match the operator (<=> cosine, <-> L2, <#> inner product) to the metric your model was trained for, and build the index with the matching operator class or it is silently ignored.
  • A full scan is exact and correct for a few thousand rows. Past that, add an approximate index: HNSW (best speed for the recall, no training, more memory) or IVFFlat (cheaper build, needs a training sample, tune probes).
  • Metadata filtering with a WHERE clause is what makes vector search usable and safe. In a multi-tenant app the tenant filter is a security boundary, not a relevance tweak.
  • Approximate indexes apply the filter after fetching nearest candidates, so a selective filter can return too few rows or none. Watch for Rows Removed by Filter. Fix it with iterative scans (pgvector 0.8, off by default), a partial index for a dominant slice, or letting the planner pick an exact plan on tiny filters.
  • Embeddings are derived data. Re-embed when content changes (inline or via a background worker that fills embedding IS NULL rows), and re-embed everything blue-green when you switch models, because vectors from different models are not comparable.
  • Stay in Postgres up to a few million vectors for the one-database simplicity. Move to a dedicated store for very large scale, strict tail latency, or native hybrid search. Storage is real: about 6KB per 1536-dim vector, halved with halfvec.