pgvector is not a vector database — it is a Postgres extension that teaches an existing relational engine to store embeddings and rank them by distance. That framing is the whole point. Instead of running a separate vector service beside your primary store, you add a vector column to a table you already have, index it, and query it with plain SQL — joins, WHERE clauses, transactions, and all. The trade is that pgvector inherits Postgres’ strengths (durability, filtering, tooling) and its constraints (a page-based engine never designed for high-dimensional math). This piece works through the parts that decide whether that trade pays off: the vector type, the distance operators and operator classes, exact versus approximate search, the IVFFlat and HNSW indexes with their lists/probes and m/ef knobs, SQL-native filtering, and build memory.
The vector type: a column of floats
pgvector adds a first-class vector type. You declare a column as vector(d) where d is the fixed dimensionality — vector(384) for an all-MiniLM embedding, vector(1536) for an OpenAI text-embedding-3-small vector. Physically it is stored as an array of 32-bit floats plus a small header, so one vector(1536) occupies roughly 1536 × 4 = 6144 bytes, about 6 KB per row before overhead.
Because it is a real column type, an embedding lives in the same row as the text, the author id, and every other attribute. There is no separate collection to keep in sync, no dual-write problem, no eventual consistency between a document store and a vector store — an INSERT writes the row and its embedding atomically, in one transaction. pgvector also ships halfvec (16-bit floats, half the storage), bit, and sparsevec, but the dense vector type carries most workloads and is the one this article’s math assumes.
Distance operators and what they compute
Similarity search is really distance ranking, and pgvector exposes distance as infix operators so a query reads like ordinary SQL. The three that matter:
a <-> b L2 (Euclidean): sqrt( Σ_i (a_i - b_i)^2 )
a <=> b cosine distance: 1 - (a·b) / (|a| |b|)
a <#> b inner product: -(a·b) (negated)The inner-product operator returns the negative dot product on purpose: Postgres orders ascending, and negating turns ‘largest similarity’ into ‘smallest value,’ so ORDER BY embedding <#> query still puts the best match first. A nearest-neighbour query is then simply SELECT id FROM docs ORDER BY embedding <=> '[...]' LIMIT 10. Cosine ignores magnitude and compares direction, which suits most text embeddings; L2 accounts for magnitude; inner product is fastest and is correct when vectors are already normalized, because for unit vectors a·b and cosine similarity coincide — so matching the operator to how the model was trained changes which rows come back.
Operator classes: binding an index to a metric
Here is the pgvector detail that trips people up. An index does not accelerate every distance operator; it accelerates one metric, fixed at creation time by the operator class you name. The three classes mirror the three operators: vector_l2_ops, vector_cosine_ops, and vector_ip_ops.
So CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops) builds a structure that only answers <=> queries quickly. If your query orders by <-> against that index, Postgres cannot use it and silently falls back to a sequential scan — correct results at exact-search cost, exactly the slowness the index was meant to remove. The operator in your ORDER BY must match the operator class in your CREATE INDEX; to rank by two metrics you build two indexes. When a query is unexpectedly slow, this mismatch is the first thing to check with EXPLAIN.
Exact search and why you eventually leave it
With no vector index, pgvector answers a nearest-neighbour query exactly: it computes the distance from the query to every candidate row and keeps the top k. This is a brute-force k-NN, an O(N · d) scan, and its virtue is that recall is 100 % by definition — no approximation to be wrong about.
For small tables that is the right answer: scanning ten thousand vector(768) rows is microseconds of work, and an approximate index would add build and maintenance cost for no felt benefit. The problem is linear growth: at one million rows every query touches a million vectors, and at ten million the sequential scan dominates your request. That is where you trade a little correctness for a large speedup and reach for an approximate-nearest-neighbour (ANN) index — and pgvector lets you make that switch without leaving Postgres, the same column and query with an index underneath.
IVFFlat: partition, then probe a few cells
IVFFlat (inverted file with flat storage) is the simpler ANN index. At build time it runs k-means over a sample of your vectors to pick lists centroids, carving the space into that many Voronoi cells and assigning every vector to its nearest centroid. A query compares itself to the centroids and searches only the closest probes cells instead of the whole table.
Two knobs govern it. lists is set at index creation — a common starting heuristic is rows / 1000 for up to a million rows, then sqrt(rows) beyond that. probes is set per query with SET ivfflat.probes and trades recall for speed directly: probes = 1 is fastest and least accurate, and as probes climb toward lists you approach exact search. Concretely, on a million rows at lists = 1000 each cell holds ~1000 vectors, so probes = 1 scans ~1000 comparisons instead of a million (a 1000× cut) but misses true neighbours across a cell boundary, and recall might sit near 0.7; probes = 10 scans ten cells and often lifts recall past 0.95 — which is why you validate by measuring recall@k rather than trusting a default. The catch pgvector is explicit about: build the IVFFlat index only after the table has representative data, because the centroids are learned from whatever is present — index an empty or tiny table and the partitioning is garbage.
HNSW: a navigable graph of vectors
HNSW (Hierarchical Navigable Small World) is the higher-recall, higher-cost index and usually the default today. It builds a multi-layer proximity graph: each vector is a node linked to its near neighbours, with sparse long-range links in upper layers and dense local links in lower ones. A search enters at the top, greedily hops toward the query through progressively finer layers, and converges on a good neighbourhood in roughly logarithmic steps rather than scanning cells.
Its parameters are m and ef_construction at build time and ef_search at query time. m is the links per node (typically 16); larger m means a denser graph — better recall, more memory, a bigger index. ef_construction (default 64) is how wide the candidate list is while building; higher gives a better graph but slower builds. ef_search (default 40, set with SET hnsw.ef_search) is the runtime recall/speed dial, the HNSW analogue of IVFFlat’s probes. Unlike IVFFlat, HNSW needs no training pass, so it can be built on an empty table and populated incrementally. As a rule, default to HNSW when quality matters and the index fits in RAM (most RAG workloads) and reach for IVFFlat when the dataset is huge, memory is tight, or the corpus is static enough to train centroids once.
The SQL-native filtering advantage
pgvector’s sharpest edge over standalone vector databases is that a similarity search is an ordinary SQL query, so it composes with everything else Postgres does. You filter, join, and rank in one statement: SELECT ... FROM docs WHERE tenant_id = 42 AND created_at > now() - interval '30 days' ORDER BY embedding <=> '[...]' LIMIT 10. The vector search and the relational predicates share the same planner, transaction, and permission model.
The subtlety is how the filter and the ANN index interact. A restrictive WHERE clause can shrink the candidate set so far that the ANN walk returns fewer than k matching rows — it finds neighbours, but they get filtered out afterwards, so recall silently drops. pgvector addresses this with iterative index scans that keep pulling candidates until enough survive the filter. The honest guidance is to test filtered queries specifically: an index tuned for unfiltered recall can behave very differently once a selective predicate sits on top.
Index build memory and maintenance
Building a vector index is memory-hungry, governed by maintenance_work_mem. For HNSW especially, pgvector wants the whole graph to fit in that budget while it is assembled; if it outgrows the budget the build spills to disk and slows sharply. So before a large build, raise that setting for the session — often a few gigabytes — and, since HNSW builds parallelize, allow several max_parallel_maintenance_workers.
Runtime memory matters just as much. ANN indexes only deliver their speed when resident in RAM; an HNSW graph that spills out of shared_buffers and the page cache and is read from disk per query loses most of its advantage — a page-based engine paging a random-access graph off disk is slow. Sizing the machine so the working set fits in memory is the difference between millisecond and multi-second queries.
For CPU-bound and local SLM stacks this shapes the sweet spot. A retrieval corpus of thousands to low millions of rows — the size most local assistants and internal tools actually have — fits comfortably in RAM, so a well-sized HNSW index answers in single-digit milliseconds with one engine to operate and no separate service. Only at hundreds of millions of vectors, or under heavy write churn, does a purpose-built engine’s specialized storage pull ahead — and for most workloads pgvector’s operational simplicity and SQL-native filtering are worth more than that last increment of scale.