What is pgvector? A tutorial on vector search in Postgres
pgvector is a Postgres extension that stores embeddings and runs nearest-neighbor search in SQL. Types, operators, HNSW vs IVFFlat and the filtering trap.

pgvector is an open-source Postgres extension that adds a vector column type, distance operators and approximate nearest-neighbor indexes, so you can store embeddings next to your relational data and run similarity search with plain SQL. You write ORDER BY embedding <=> $1 LIMIT 10 and Postgres returns the ten closest rows. The current release is 0.8.7, from 1 October 2026, and it supports Postgres 13 and later.
Why put vectors in Postgres at all?
Because most applications that need vector search already have a Postgres database, and the vectors describe rows that live there. A support article, a product or a contract chunk has an owner, a tenant, a timestamp and permissions. With a separate vector database, you sync those fields into a second system, keep them consistent, and join the results back. With pgvector the embedding is another column. A DELETE removes the row and its vector in one transaction, backups and point-in-time recovery cover both, and row-level security applies to similarity search the same way it applies to everything else.
From a data-platform and privacy-compliance seat, the line I would circle is the DELETE. An embedding of a user’s text is derived from their data, so a deletion request has to reach it too. Every extra system that holds a copy is one more place that deletion has to land and be proven. pgvector does not remove that work, but it does not add a new copy.
How do I install pgvector and store embeddings?
The README lists Docker, Homebrew, APT, Yum, conda-forge and source builds, and says pgvector comes preinstalled on Postgres.app and many hosted providers. For a local database I use the official image (pgvector/pgvector:pg18-trixie at the time of writing). Then, in SQL:
CREATE EXTENSION vector;
CREATE TABLE chunks (
id bigserial PRIMARY KEY,
tenant_id int NOT NULL,
body text NOT NULL,
embedding vector(1536) -- must match your embedding model's output size
);
INSERT INTO chunks (tenant_id, body, embedding)
VALUES (42, 'Rotate the API key from settings', '[0.012, -0.031, ...]');
The ... stands for the rest of the 1,536 numbers; your application code produces them from an embedding model. For bulk loads, the README recommends COPY ... WITH (FORMAT BINARY) and creating indexes after the initial load, since building an index over existing data is faster than maintaining it row by row.
pgvector has four column types. From the README:
| Type | What it stores | Max dims stored | Max dims indexed |
|---|---|---|---|
vector |
32-bit floats | 16,000 | 2,000 |
halfvec |
16-bit floats | 16,000 | 4,000 |
bit |
binary vectors | 64,000 | |
sparsevec |
non-zero entries only | 16,000 non-zero | 1,000 non-zero |
The 2,000-dimension index limit on vector matters. A 3,072-dimension model fits in the column but cannot be indexed as vector; you would index it as halfvec, or keep fewer dimensions (more on that below).
How do I query nearest neighbors in pgvector?
Pick the distance operator that matches how your model was trained:
| Operator | Meaning |
|---|---|
<-> |
L2 (Euclidean) distance |
<#> |
negative inner product |
<=> |
cosine distance |
<+> |
L1 distance |
<~> |
Hamming distance (bit) |
<%> |
Jaccard distance (bit) |
SELECT id, body, embedding <=> $1 AS distance
FROM chunks
WHERE tenant_id = 42
ORDER BY embedding <=> $1
LIMIT 10;
<#> returns the negative inner product because, as the README explains, Postgres only runs index scans on operators in ascending order. Multiply by -1 to get the real score. If your embeddings are unit length, inner product and cosine give the same ranking and inner product is cheaper; cosine similarity covers why.
Without an index, this query is exact. The README is explicit that by default pgvector “performs exact nearest neighbor search, which provides perfect recall.” For tens of thousands of rows per tenant, that is often all you need.
HNSW vs IVFFlat in pgvector: which index should I use?
Both are approximate, and both trade some recall for speed. The README warns that, unlike ordinary indexes, “you will see different results for queries after adding an approximate index.”
-- HNSW: better speed-recall trade-off, slower build, more memory
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
SET hnsw.ef_search = 100; -- default 40; higher = better recall, slower
-- IVFFlat: faster build, less memory, needs data before you build it
CREATE INDEX ON chunks USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
SET ivfflat.probes = 10; -- default 1; sqrt(lists) is the suggested start
HNSW was added in 0.5.0 (28 August 2023) and is what I reach for first. It has no training step, so you can create it on an empty table. IVFFlat clusters the data with k-means at build time, so it should be built after the data is loaded; the README suggests lists = rows / 1000 up to a million rows and sqrt(rows) beyond. Setting probes equal to lists turns it back into an exact search. The index operator class has to match the query operator: vector_cosine_ops for <=>, vector_ip_ops for <#>, vector_l2_ops for <->. How the graph inside HNSW works, and what m and ef_search actually control, is in what is HNSW.
Two build tips from the README: HNSW builds “significantly faster when the graph fits into maintenance_work_mem”, and parallel workers (max_parallel_maintenance_workers) speed them up further. Postgres prints a notice when the graph stops fitting.
Why does my filtered pgvector query return fewer rows than LIMIT?
This is the trap that catches most people, and the README spells it out: “With approximate indexes, filtering is applied after the index is scanned. If a condition matches 10% of rows, with HNSW and the default hnsw.ef_search of 40, only 4 rows will match on average.”
In other words, HNSW hands Postgres 40 candidates, and your WHERE tenant_id = 42 keeps only the ones that belong to tenant 42. Here is the arithmetic as a simulation, assuming the filter is independent of vector position:
# postfilter.py: why "WHERE tenant = $1 ... LIMIT 10" can return fewer than 10 rows
# on an approximate index. Python 3 stdlib only; models pgvector's documented behaviour:
# the index yields ef_search candidates, then Postgres applies the WHERE clause.
import random
random.seed(5)
TRIALS, LIMIT = 10_000, 10
def run(ef_search, selectivity):
counts = []
for _ in range(TRIALS):
kept = sum(random.random() < selectivity for _ in range(ef_search))
counts.append(min(kept, LIMIT))
short = sum(c < LIMIT for c in counts) / TRIALS
return sum(counts) / TRIALS, short
print("selectivity ef_search avg rows returned queries short of LIMIT 10")
for sel in (0.5, 0.1, 0.01):
for ef in (40, 100, 400):
avg, short = run(ef, sel)
print(f"{sel:>10.0%} {ef:>9} {avg:>17.1f} {short:>24.0%}")
Output (Python 3.9.6):
selectivity ef_search avg rows returned queries short of LIMIT 10
50% 40 10.0 0%
50% 100 10.0 0%
50% 400 10.0 0%
10% 40 4.0 99%
10% 100 8.8 45%
10% 400 10.0 0%
1% 40 0.4 100%
1% 100 1.0 100%
1% 400 4.0 99%
The 10% and ef_search = 40 row reproduces the README’s “4 rows on average”. At 1% selectivity, which is what a mid-sized tenant in a multi-tenant table looks like, even ef_search = 400 returns four rows. Real data will not follow this model exactly. If a tenant’s documents sit in their own corner of the embedding space, away from where the query lands, the shortfall gets worse.
The fixes, all from the README:
- Iterative index scans (0.8.0 and later):
SET hnsw.iterative_scan = strict_order;(orrelaxed_orderfor better recall with slightly out-of-order results). The scan keeps going until it has enough rows or hitshnsw.max_scan_tuples, 20,000 by default. - A B-tree index on the filter column, so the planner can do an exact search over just the matching rows. The README notes exact indexes “work well for conditions that match a low percentage of rows.”
- Partial indexes when there are only a few filter values:
CREATE INDEX ... USING hnsw (...) WHERE (category_id = 123); - Partitioning by tenant. The README’s multitenancy section says a shared approximate index lets “vectors from one tenant … affect recall (and speed) for other tenants,” and recommends list partitioning or separate tables for isolation.
How do I make pgvector indexes smaller?
Three tools, each documented in the README:
- Half precision:
CREATE INDEX ON chunks USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops);halves the index size and raises the indexable limit to 4,000 dimensions. - Binary quantization: index
binary_quantize(embedding)::bit(1536)with Hamming distance, fetch more candidates than you need, then re-rank them by the full vector. The indexed vectors are 32 times smaller than float32. - Subvectors: index
subvector(embedding, 1, 512)and re-rank by the full vector. This only works well with Matryoshka embeddings, whose first dimensions carry most of the meaning.
For hybrid search, the README pairs pgvector with Postgres full-text search (tsvector, ts_rank_cd), then combines the two lists in application code or SQL.
What I would do
- Start with pgvector if you already run Postgres. Move to a dedicated engine only when you can name the limit you hit: index size beyond one machine’s RAM, write throughput, or filter patterns Postgres cannot plan well.
- Begin with exact search plus a B-tree index on
tenant_id. Add HNSW when latency demands it, not before. - When you add HNSW, turn on
hnsw.iterative_scanfor every filtered query path, and test with your smallest real tenant. - Store unit-length vectors and use
vector_ip_opswith<#>, orvector_cosine_opswith<=>. Do not mix them. - Record the embedding model name and version in a column. When you change models, you re-embed everything, and you will want to know which rows are done.
FAQ
Is pgvector free?
Yes. It is open source under the same permissive license text as PostgreSQL (see its LICENSE file), and the README lists many hosted Postgres providers that ship it preinstalled.
How many dimensions does pgvector support?
A vector column stores up to 16,000 dimensions and can be indexed (HNSW or IVFFlat) up to 2,000. halfvec indexes up to 4,000, and bit up to 64,000.
Is pgvector fast enough for production?
For many workloads, yes, especially with HNSW and enough memory to keep the index cached. The limits to watch are index build time, index size relative to RAM, and filtered queries, which need iterative scans or partitioning.
What is the difference between pgvector and a vector database?
pgvector adds vector search to Postgres, so you keep transactions, joins, backups and row-level security. A dedicated vector database is built around vector search and usually offers its own filtering, sharding and quantization, at the cost of a second system to run and keep in sync.
Does pgvector support hybrid search?
Yes, by combining it with Postgres full-text search and merging the two ranked lists, for example with reciprocal rank fusion.
Sources
- pgvector README, version 0.8.7, checked 2026-10-31
- pgvector CHANGELOG: HNSW in 0.5.0 (2023-08-28), halfvec, sparsevec and bit in 0.7.0 (2024-04-29), iterative index scans in 0.8.0 (2024-10-30), 0.8.7 (2026-10-01)
- Malkov and Yashunin, Hierarchical Navigable Small World graphs, arXiv, March 2016