pgvector Explained: Vector Search Inside PostgreSQL

pgvector is a PostgreSQL extension that adds vector similarity search to the database you already run - a

vector

Embeddings, in one section

An embedding model turns content - text, images, code - into a list of numbers (a vector, typically 384 to 3,072 dimensions) where semantic similarity becomes geometric closeness. 'Reset my password' and 'I can't log in' land near each other in that space despite sharing no words. Similarity search is then just: given a query vector, find the stored vectors closest to it. That is the entire trick behind RAG, semantic search and 'related items'.

The type and the operators

Enable the extension, add a column, insert vectors from your embedding model:

CREATE EXTENSION IF NOT EXISTS vector;
​
CREATE TABLE docs (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  content   text NOT NULL,
  tenant_id bigint NOT NULL,
  embedding vector(1536)  -- match your model's dimensions
);
​
-- nearest neighbours by cosine distance
SELECT id, content
FROM docs
ORDER BY embedding <=> $1
LIMIT 10;

Three distance operators cover the standard metrics:

<->

<=>

<#>

Indexes: HNSW and IVFFlat

Without an index, every similarity query compares the query against every stored vector - exact, but linear, and linear stops being acceptable somewhere around a hundred thousand rows. Both pgvector index families fix that by making search approximate: they accept a small chance of missing a true nearest neighbour in exchange for being orders of magnitude faster. They just spend that budget in opposite ways - and since both names are acronyms nobody expands, start there, because unpacking them tells you most of what you need.

IVFFlat = Inverted File with Flat compression

The inverted file half is the oldest idea in search: instead of scanning the content, keep a lookup that goes straight from a key to the small set of items filed under it - exactly what the index at the back of a book does. The flat half means the vectors inside each of those sets are stored whole, at full precision, with no compression. (Other systems quantise them to save memory; pgvector's IVFFlat deliberately does not, which is why it is 'flat'.)

Building the index runs k-means over a sample of your data, carving the vector space into

lists

probes

The analogy: postcodes. To find the nearest coffee shop, you don't walk every street in the city. You work out which postcode you are standing in and search that one. It is dramatically faster - and if the nearest shop happens to be one street the other side of the postcode boundary, you miss it. That miss is the recall you traded away, and

probes

probes = 1

-- lists ~= rows / 1000 for up to 1M rows, then ~= sqrt(rows)
CREATE INDEX ON docs USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
​
-- per-session recall/latency dial: how many clusters to actually scan
SET ivfflat.probes = 10;

Two consequences fall straight out of that design, and they are the whole reason IVFFlat is not the default. It cannot be built on an empty table - k-means has nothing to cluster, so you must load data first. And it goes stale: the centroids describe the data as it was at build time, so as rows pour in, the clusters stop matching reality and recall quietly drifts downwards until you rebuild.

HNSW = Hierarchical Navigable Small World

Three words, three ideas, and they compose. A small world is a graph where any node reaches any other in a handful of hops - the six-degrees-of-separation property. Navigable means you can find your way using only local information: from wherever you stand, step to whichever neighbour is closest to your target, and repeat. Hierarchical means the graph is stacked in layers - the top sparse with very long edges, each layer below denser with shorter ones, the bottom containing every vector.

A search enters at the top layer and greedily hops toward the query vector until no neighbour is any closer. It then drops a layer, where the edges are shorter and the neighbours finer-grained, and repeats - each layer refining the answer the one above roughed out. By the time it reaches the bottom it is already in the right neighbourhood and only has to look around locally.

The analogy: getting to a house in another country. You don't consult a map of every street on the continent. You take a long-haul flight to the nearest big city, a train to the right district, then walk the last few streets. Each stage covers less ground than the one before but with more precision, and you never survey the whole country. HNSW's layers are exactly those three modes of travel: the top layer is the flight, the bottom layer is the walking.

CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
​
-- recall/latency dial: how wide to search at each layer (default 40)
SET hnsw.ef_search = 100;

Because there is no training step, HNSW builds on an empty table and stays accurate as rows arrive - each new vector simply wires itself into the graph. That, plus better recall at the same latency, is why it is the default choice. You pay for it at build time: constructing the graph is slow and wants to hold the whole index in memory.

So which one?

Start with HNSW. Reach for IVFFlat when the corpus is large and barely changes, or when the HNSW build time or memory footprint is genuinely the constraint you are up against - not before.

Filtered search: the quiet superpower

Real applications never search all vectors - they search this tenant's documents, published articles, products in stock. In a dedicated vector store, combining filters with similarity is a feature you configure and often fight. In Postgres it is a WHERE clause:

SELECT id, content
FROM docs
WHERE tenant_id = $2          -- just SQL
ORDER BY embedding <=> $1
LIMIT 10;

And because the vectors live in the same transactional database as the rows they describe, there is no sync pipeline to build and no window where a deleted document still surfaces in search results. Delete the row, the embedding is gone - atomically. That single property eliminates a whole class of RAG bugs.

A working RAG schema

Retrieval-augmented generation needs exactly three things from the database: store chunks with their embeddings, retrieve the nearest chunks for a question, and keep them scoped to the right user or tenant. In Postgres that is one table and one query:

CREATE TABLE chunks (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source_id  bigint NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
  tenant_id  bigint NOT NULL,
  body       text NOT NULL,
  embedding  vector(1536) NOT NULL
);
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON chunks (tenant_id);
​
-- retrieval for one tenant's question
SELECT body FROM chunks
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 8;

The ON DELETE CASCADE line is doing more work than it looks like: when a source document is removed, its chunks and embeddings vanish in the same transaction. In a two-database architecture that guarantee is a sync pipeline you have to build, monitor and apologise for.

Tuning that actually matters

• Index build memory. Set maintenance_work_mem generously before CREATE INDEX on a large table - HNSW builds are dramatically faster when they fit in memory.

• Recall vs speed at query time. hnsw.ef_search (default 40) is the dial: raise it for better recall, lower it for latency. Test against a labelled sample rather than guessing.

• Dimensions cost real money. 1,536 dimensions is ~6 KB per row; models with 3,072 double that. If your model supports shortened embeddings, smaller often loses little recall and halves storage and index size.

• Vacuum still applies. Embeddings update when content changes; dead tuples bloat the index like any other. Ordinary Postgres hygiene carries over unchanged.

Where the honest ceiling is

pgvector comfortably serves millions of vectors on ordinary hardware - HNSW queries in single-digit milliseconds, with memory (the index wants to live in RAM) as the main sizing constraint. The dedicated stores - Pinecone, Qdrant, Weaviate, Milvus - earn their place past tens of millions of vectors, at very high query throughput, or for specialized capabilities like tuned hybrid reranking at scale. If you are not sure whether you are past the ceiling, you are not past it.

The mistakes everyone makes once

• Mismatched metrics. Building the index with vector_l2_ops and querying with <=> means the index is silently ignored and every query scans the table. Same metric in both places, always.

• Forgetting the LIMIT. An approximate index answers 'top K nearest'; a similarity query without LIMIT degenerates into sorting the whole table.

• Embedding at query time, twice. Cache the query embedding if the same question feeds retrieval and reranking - embedding API calls are the slow, billable part of the pipeline.

• Testing recall on toy data. Ten sample rows make every index look perfect. Evaluate against a realistic corpus before promising quality.

Running it in production

pgvector is among the most widely supported extensions on managed Postgres platforms - though each provider allows a vetted list, so verify before committing (our PostgreSQL hosting comparison covers how to evaluate providers generally). Size RAM for the index, monitor recall if your data churns, and remember the embedding column dominates storage: 1,536 floats is ~6 KB per row before the text itself. A million embedded documents is a perfectly ordinary Postgres database - which is precisely the point.

Prices quoted here were checked in July 2026. Vendors change them; treat the structure as the durable part and re-check the current numbers before you budget.