pgvector

pgvector is a PostgreSQL extension that adds vector types, distance operators and two approximate index types. The important consequence is not that PostgreSQL can now store embeddings (an array of floats would do that) but that similarity search becomes an ordinary part of a SQL query, inside a transaction, covered by the write-ahead log, and therefore by streaming replication and point-in-time recovery. Joins, filters and ORDER BY compose with it because it is just an operator and an index access method.

The second thing to understand before anything else: an approximate index changes your results. By default pgvector performs exact nearest-neighbour search, which has perfect recall. Adding an HNSW or IVFFlat index trades some recall for speed, and the README says so directly. You will see different results for the same query after adding one. Nothing in PostgreSQL warns you. This is not a performance knob; it is an accuracy setting with a performance side effect, and the rest of this page is largely about how to keep it honest.

Version 0.8.6 (29 July 2026) is current. The 0.8.x line supports PostgreSQL 13 and later, with PostgreSQL 18 support added in 0.8.1. Several 0.8.x patch releases fixed index corruption and buffer overflows: 0.8.3 fixed possible HNSW index corruption during vacuuming, 0.8.4 fixed an hnsw graph not repaired error and IVFFlat builds exceeding maintenance_work_mem, and 0.8.2 fixed a buffer overflow in parallel HNSW builds. Run the latest patch release, not whatever your distribution packaged.

What the extension adds

CREATE EXTENSION vector;

CREATE TABLE documents (
  id          bigserial PRIMARY KEY,
  chunk_id    int NOT NULL,
  tenant_id   int NOT NULL,
  body        text NOT NULL,
  embedding   vector(1536)
);

Four types, differing in what they store and what they cost:

All elements must be finite: NaN, Infinity and -Infinity are rejected. sparsevec literals use the form {index:value,index:value}/dimensions, with indices starting at 1 like SQL arrays.

A column can also be declared as bare vector with no dimension, which allows rows of differing dimensionality in one column, which is useful when you store embeddings from several models side by side. The cost is that you can only index a subset of consistent rows, using an expression index with a partial predicate:

CREATE TABLE embeddings (model_id bigint, item_id bigint, embedding vector,
                         PRIMARY KEY (model_id, item_id));

CREATE INDEX ON embeddings USING hnsw ((embedding::vector(768)) vector_l2_ops)
  WHERE (model_id = 123);

Six distance operators, each with a matching operator class:

<#> returns the negative inner product, because PostgreSQL supports only ascending-order index scans on operators. Multiply by -1 to recover the inner product, and use 1 - (a <=> b) for cosine similarity. If your embeddings are already normalised to unit length - as OpenAI’s are - inner product is the cheapest of the three and gives the same ranking as cosine.

An index is only used when the ORDER BY is a bare distance operator in ascending order, with a LIMIT. This does not use the index:

-- no index: the ORDER BY expression is not the operator result
SELECT * FROM documents ORDER BY 1 - (embedding <=> $1) DESC LIMIT 5;

This does:

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

NULL vectors are not indexed, and neither are zero vectors for cosine distance. If a document pipeline can emit either, those rows silently disappear from indexed results.

Where the data actually lives

“Store your vectors with the rest of your data” is the extension’s own summary, and it is accurate, but it hides a detail that decides both storage size and query cost. A single embedded chunk is written to three places, not one.

The third row is the one that surprises people. An HNSW index entry is not a pointer to the vector in the table. The index tuple carries the vector itself alongside the heap pointers, because graph traversal computes distances at every hop and cannot afford a heap fetch per comparison. Budget for the vector twice.

Whether your vectors are in the heap or in TOAST

PostgreSQL keeps a row on one 8 kB page, so it moves oversized values out of line once a row exceeds TOAST_TUPLE_THRESHOLD, normally 2 kB. A vector occupies 4 × dimensions + 8 bytes, so the threshold falls at roughly 500 dimensions for the column alone, and lower once the chunk text shares the row.

Ordinary embedding sizes are well past it. A 1,536-dimension vector is 6,152 bytes, so it is stored out of line by default, split across several rows of the TOAST table, with an 18-byte pointer left behind in the heap tuple. At 3,072 dimensions it is 12,296 bytes.

The extension declares the type with STORAGE = external, which PostgreSQL defines as out-of-line permitted, compression not. That choice is deliberate. Compressing a dense float array buys little and costs decompression on every read. The consequence to remember is that a vector fetched from the heap frequently costs an extra read against the TOAST relation, and that the planner does not account for out-of-line storage in its cost estimates, which is why an exact scan sometimes picks a serial plan when a parallel one would win.

Keeping vectors inline is possible where the row still fits a page, and helps scan-heavy work:

ALTER TABLE documents ALTER COLUMN embedding SET STORAGE PLAIN;

That trades heap size for the removal of the TOAST hop. It is worth measuring rather than assuming, and it is unavailable for large vectors, which cannot fit a page at all.

The write path

Bulk loading therefore wants the index built afterwards, not before. Every insert into an existing HNSW index pays graph traversal, and the same vectors indexed in one pass are cheaper than the same vectors indexed one row at a time.

The read path

Two properties of that path matter for retrieval quality. The index returns candidates, not answers, so anything the WHERE clause removes afterwards shrinks the result below the k that was asked for. And the heap fetch is unavoidable, because the index holds the vector but not the chunk text, which is the thing the model actually needs.

HNSW

An HNSW index is a multilayer proximity graph. Each layer is a navigable small-world graph over a subset of the vectors, with the upper layers sparse and the bottom layer containing everything; a search descends from the top, greedily following edges towards the query, and refines at the bottom layer using a bounded candidate list. Because there is no training step, an HNSW index can be created on an empty table and stays correct as rows arrive.

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

hnsw.ef_search is the parameter you will actually tune, because it is per query and costs nothing to change:

BEGIN;
SET LOCAL hnsw.ef_search = 200;
SELECT id FROM documents ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

The README’s guidance on the build parameters is to leave them alone unless you are seeing low recall, and to reach for ef_search first. That is sound: the build parameters cost a rebuild to change, and m in particular multiplies index size.

Build time is dominated by whether the graph fits in memory. pgvector emits a notice when it stops fitting:

NOTICE:  hnsw graph no longer fits into maintenance_work_mem after 100000 tuples
DETAIL:  Building will take significantly more time.
HINT:  Increase maintenance_work_mem to speed up builds.

Raise maintenance_work_mem, but not past what the server can spare, and remember that PostgreSQL allocates it per maintenance operation, so parallel index build workers multiply it. Parallel builds default to 2 workers, bounded by max_parallel_maintenance_workers and ultimately by max_parallel_workers (default 8):

SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 7;   -- plus the leader
CREATE INDEX CONCURRENTLY ON documents USING hnsw (embedding vector_cosine_ops);

Use CONCURRENTLY in production so the build does not block writes; the trade is a longer build and the possibility of an INVALID index if it fails, which you then have to drop and retry.

Progress reporting is limited to two phases for HNSW, initializing and loading tuples:

SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

Vacuuming an HNSW index is slow, because removing a node means repairing the graph around it. The documented mitigation is to reindex first:

REINDEX INDEX CONCURRENTLY documents_embedding_idx;
VACUUM documents;

On a table with heavy embedding churn this is a real operational cost, and it is the strongest argument for treating embeddings as append-mostly data: insert new versions and delete in batches instead of updating in place.

IVFFlat

An IVFFlat index runs k-means over the vectors to produce a set of lists (cells) with centroids, and assigns each vector to its nearest centroid. A query compares against centroids, picks the closest probes lists, and scans them exhaustively. Build is far cheaper than HNSW and the index is smaller, but the speed/recall curve is worse.

CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 1000);

The default of one probe is the trap. An IVFFlat index built and queried at defaults searches a single cell, which on a 1000-list index means roughly a thousandth of the table. The README’s three rules exist for exactly this reason:

1. Build the index after the table has data, because k-means needs data to cluster. A build on a nearly-empty table produces useless centroids, and the symptom is missing results afterwards. The remedy is to drop the index and rebuild once the table is populated.
2. Start with lists = rows / 1000 up to a million rows, and lists = sqrt(rows) beyond that.
3. Start with probes = sqrt(lists).

Applied to a ten-million-row table: lists ≈ 3162, probes ≈ 56. Applied to defaults, probes = 1, which is 56 times less of the index and correspondingly worse recall. Anyone who benchmarks IVFFlat without setting probes is measuring the wrong thing.

Build progress has four phases (initializing, performing k-means, assigning tuples, loading tuples) and the percentage is only populated during the last of them.

Choosing between them

HNSW is the default choice for a serving workload. IVFFlat earns its place when build time or index size is the binding constraint: a nightly full rebuild of a very large table, or an index that has to fit alongside a large buffer pool. It also needs you to be prepared to rebuild it as the distribution of your vectors drifts.

Neither index needs to fit in memory, but both perform better when they do. Check the size and decide deliberately:

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE relname = 'documents';

Two levers shrink both: storing halfvec instead of vector halves the working set, and binary quantisation via an expression index shrinks it much further at the cost of recall, which you buy back by re-ranking the candidate set against the original vectors:

CREATE INDEX ON documents USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops);

SELECT * FROM (
  SELECT id, body, embedding FROM documents
  ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize($1)
  LIMIT 200
) candidates
ORDER BY embedding <=> $1
LIMIT 10;

Dimension limits

Indexed and unindexed columns have different ceilings, and this catches people using large embedding models.

So a 3072-dimension embedding can be stored as vector(3072) but cannot be indexed as one. The options are all expression indexes: cast to halfvec on the way into the index, binary-quantize, index a subvector if the model supports truncation, or reduce dimensionality upstream.

-- store at full precision, index at half precision
CREATE INDEX ON documents USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);

-- the query must use the same expression or the index will not be chosen
SELECT id FROM documents ORDER BY embedding::halfvec(3072) <=> $1::halfvec(3072) LIMIT 10;

The rule that catches people: an expression index is only used by a query whose ORDER BY contains the same expression. Changing the index without changing the query gives you a large unused index and no error.

Filtering: where recall breaks

This is the part that breaks in production. With an approximate index, filtering is applied after the index is scanned, not before. The index returns its candidate set, then the WHERE clause removes rows from it.

The README’s arithmetic is worth internalising: if a condition matches 10% of rows, with HNSW at the default hnsw.ef_search of 40, only about four rows survive on average. A query written to return ten results returns four, with no error and no warning.

There are four responses, and which one is right depends on selectivity.

A plain index on the filter column. For conditions matching a small fraction of rows, this gives fast exact search. PostgreSQL uses the B-tree, then sorts the small result by distance. This is frequently the correct answer and is the one people skip because they came looking for a vector index.

CREATE INDEX ON documents (tenant_id);
CREATE INDEX ON documents (tenant_id, chunk_id);

Partial indexes, when there are few distinct filter values:

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops) WHERE (tenant_id = 123);

Partitioning, when there are many:

CREATE TABLE documents (tenant_id int, embedding vector(1536)) PARTITION BY LIST (tenant_id);

Partitioning is also the answer to multi-tenancy generally. Sharing one approximate index across tenants means one tenant’s vectors affect both recall and latency for every other tenant, which is a correctness and a noisy-neighbour problem at once. List partitioning or separate tables give isolation.

Iterative index scans, added in 0.8.0, which keep scanning more of the index until enough rows are found or a ceiling is hit:

SET hnsw.iterative_scan = strict_order;   -- exact distance ordering
SET hnsw.iterative_scan = relaxed_order;  -- slightly out of order, better recall
SET ivfflat.iterative_scan = relaxed_order;

Relaxed ordering gives better recall but results may be slightly out of distance order. You can recover strict ordering with a materialised CTE:

WITH relaxed AS MATERIALIZED (
  SELECT id, embedding <=> $1 AS distance
  FROM documents
  WHERE tenant_id = 123
  ORDER BY distance
  LIMIT 10
)
SELECT * FROM relaxed ORDER BY distance + 0;

The + 0 is required on PostgreSQL 17 and later to stop the planner collapsing the ordering. The same materialised-CTE pattern is needed for distance thresholds, with the distance filter placed outside the CTE and everything else inside:

WITH nearest AS MATERIALIZED (
  SELECT id, embedding <=> $1 AS distance
  FROM documents WHERE tenant_id = 123 ORDER BY distance LIMIT 10
)
SELECT * FROM nearest WHERE distance < 0.35 ORDER BY distance;

Measuring recall

Since the index changes results, recall is a number you have to produce yourself. The mechanism is in the README: disable index scans for one session and compare.

-- ground truth
BEGIN;
SET LOCAL enable_indexscan = off;
SET LOCAL enable_seqscan = on;
CREATE TEMP TABLE truth AS
  SELECT id, row_number() OVER (ORDER BY embedding <=> $1) AS rank
  FROM documents ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

-- approximate result at the current settings
CREATE TEMP TABLE approx AS
  SELECT id FROM documents ORDER BY embedding <=> $1 LIMIT 10;

SELECT count(*)::numeric / 10 AS recall_at_10
FROM approx a JOIN truth t USING (id);

Run that over a sample of real query vectors, not synthetic ones, and record the number alongside hnsw.ef_search. Then it becomes a decision you can make: recall 0.94 at ef_search = 40, 0.99 at ef_search = 200, with the latency for each. Without it you are choosing an accuracy level by accident.

EXPLAIN (ANALYZE, BUFFERS) tells you whether the index was used at all and where the time went:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM documents WHERE tenant_id = 123 ORDER BY embedding <=> $1 LIMIT 10;

If you see a sequential scan where you expected an index scan, check the three conditions: ORDER BY on a bare distance operator, ascending, with a LIMIT. If all three hold and the planner still chooses a scan, the table may simply be small enough that it is right. SET LOCAL enable_seqscan = off in a transaction settles the question.

One planner quirk is specific to vectors: PostgreSQL does not account for out-of-line (TOAST) storage in cost estimates, which makes a serial scan look cheaper than a parallel one. For exact search over a large table, either lower the parallel costs for the query or store vectors inline:

SET max_parallel_workers_per_gather = 4;
ALTER TABLE documents ALTER COLUMN embedding SET STORAGE PLAIN;

STORAGE PLAIN keeps vectors in the heap tuple rather than TOAST, which helps scan performance and costs heap size. It only works while the row fits in a page, so it is not available for very large vectors.

For bulk loading, use COPY with the binary format and build indexes afterwards:

COPY documents (chunk_id, tenant_id, body, embedding) FROM STDIN WITH (FORMAT BINARY);

Where the vectors come from

Everything above describes how a vector is stored once it arrives. Nothing in PostgreSQL produces one. The vector is computed by an embedding model upstream - the application sends text, an image or audio to a model, gets back a fixed-length float array, and writes it into the row. That hand-off has two rules that matter more than anything the database does:

Pin the model and the version. Two vectors are only comparable when they were produced by the same embedding space. A column of text-embedding-3-small vectors and a column of BGE vectors occupy different spaces: the distance between a query from one and a row from the other is a meaningless number that still returns results. The pragmatic pattern is to record embedding_model on every row and, on a model upgrade, re-embed and rebuild the index rather than mixing spaces.

The dimension is fixed at the column. vector(1536) rejects any other length, which is the protection you want - it means a mismatched embedding fails at write time instead of corrupting similarity silently. Bare vector without a dimension exists for holding several models side by side, at the cost of needing an expression index per space.

How it fits the AI loop

pgvector’s role in an AI system is narrow and specific: it is the memory the model reads from. A language model has no memory of your documents and no ability to change what it knows at query time, so the standard architecture keeps knowledge outside the model and retrieves the relevant pieces into the prompt for each question. That retrieval is exactly the ORDER BY embedding <=> $1 above.

Three things in that loop are worth naming because they are where implementations fail:

• The store is shared state, not model state. Nothing about an update to documents retrains or updates the model. Knowledge lives in rows; the model just reads them. That is what makes the data correctable at all - fix the row, and the next query retrieves the fix.
• Agent memory is the same pattern with longer-lived rows. An agent that remembers past interactions is storing a row per memory - content, timestamp, importance - with its embedding, and querying by similarity plus recency. The database gives you WHERE over both in one query, which a dedicated vector store would need a second system for.
• Retrieval quality is measurable in the database. The recall measurement above compares approximate against exact search, and for the end-to-end question - did the model answer from the right evidence - the site has evaluation and AI evals for the model side.

Hybrid retrieval

Dense vectors capture meaning but miss exact terms - a product code, an identifier, a name the embedding model never learnt. The README’s answer is to run full-text search alongside the vector query and fuse the rankings:

-- keyword side, using a tsvector column and a GIN index
SELECT id, body FROM documents, plainto_tsquery('refund policy') q
WHERE textsearch @@ q
ORDER BY ts_rank_cd(textsearch, q) DESC
LIMIT 5;

The two ranked lists are then combined with reciprocal rank fusion in the application (rank each list, sum 1/(rank + k) per document, order by the sum) or passed through a cross-encoder for re-ranking. pgvector’s part ends at producing the vector half of the list; the fusion step lives in the application, which is one of the places a dedicated engine differs - it ships the fusion in the query layer.

When pgvector is enough, and when it is not

pgvector is the right choice when your vectors belong to rows you already keep in PostgreSQL. The argument is not that it is faster than a dedicated vector database (at large scale and high query rates it generally is not) but that it removes an entire class of problem. There is one system of record, one transaction boundary, one backup, one replication stream, one access-control model. A vector written in the same transaction as the row it describes cannot be orphaned, and a point-in-time restore brings both back consistently. Filters, joins and aggregates over your relational data compose with similarity search because it is the same query planner. If your working set fits on one machine and you can scale it vertically with read replicas, that consolidation is worth a great deal.

A dedicated vector store is warranted when specific things are true, and it is worth being precise about which:

• Scale beyond one machine. pgvector scales vertically, or horizontally with replicas for reads, or by sharding through Citus or PgDog. Sharding a PostgreSQL cluster to serve vector search is a significant undertaking; systems designed for distribution do it as a configuration option.
• Quantisation and compression as first-class features. pgvector offers halfvec and binary quantisation through expression indexes with manual re-ranking. Dedicated stores typically implement product quantisation and similar schemes natively, with tuning and re-ranking handled internally, which matters when the index cannot fit in memory.
• High-cardinality metadata filtering at scale. The post-filtering problem above is structural. Partial indexes and partitioning solve it for tens or hundreds of filter values, not for tens of thousands. Stores with filter-aware graph traversal handle this case directly.
• Heavy vector churn. HNSW vacuuming is expensive and the mitigation is a reindex. A workload that continuously updates embeddings in place will spend real operational effort here.
• Very high query concurrency against a large index. PostgreSQL’s process-per-connection model means vector search competes with the rest of your OLTP traffic for the same buffer pool and CPU. Separating the two is a legitimate architectural choice, and one you can make later by moving the vectors out, not by starting elsewhere.

The reverse is also worth stating plainly. If your corpus is in the tens of millions of vectors, your query rate is in the tens or low hundreds per second, and your filters are selective, a dedicated vector store buys you a second datastore to operate, keep in sync and back up, in exchange for latency you were not short of. That trade is usually bad, and the default should be to start with pgvector and move only when a measured limit forces it.

Whichever you choose, the retrieval quality question does not go away: chunking, embedding model, recall target and re-ranking dominate end-to-end quality far more than the index type. See RAG for that side of the problem, and PostgreSQL for the vacuum, maintenance_work_mem and replication behaviour that pgvector inherits wholesale.